Load coverage problem

Bus Company

Bus planning optimization

In this tutorial we show you how to solve the load coverage operations research problem, using XLOPTIM, the fastest and most reliable solver for Microsoft Excel.

1. Data

To download the data for this XLOPTIM use case, please click the link below:

Download data for load coverage problem

2. The problem

A bus company wishes to define its hiring policy over the next 6 in order to cover its new needs following the implementation of a new Boston - New York line.

Taking into account the planned trips, a team of data analysts carried out a statistical study upstream of our Operational Research project, to calculate the bus personnel needs expressed in driver-hours of driving.


The hiring of a bus driver is always followed of a month of training during which the new hires cannot ensure their regular service. This training is provided by experienced drivers.

The company's collective agreement stipulates that each new driver is taken care of by an experienced driver, for one month. This support relieves the experienced driver of 80 hours of driving during the month of training.

Experienced drivers do no more than 140 hours of driving per month. At the beginning of January, the company employs 55 drivers who are all experienced.

It will be assumed that the dismissal of staff is impossible. On the other hand, the analysis of the last years shows that every month 7% of the trained personnel leave the company. The cost of an experienced driver is $ 3,200 per month and a trainee driver $ 1,800 per month.

We are trying to propose a policy for hiring and training drivers for the next 6 months.

We seek to propose a linear modeling of this decision support problem.

3. Modélisation

We propose a modeling according to the 5 steps:

Step #1: What are the dimensions / clues of the problem?

  • t: 1 .. 6: the months of the year

Step #2: What are the data of the problem?

  • Total requirement in driver-hours per month (7,000 / 6,500 / 8,500 ...):


  • Monthly starting rate (7%)
  • Number of training hours (80 h)
  • Number of monthly working hours (140 h)
  • Cost of an experienced driver ($ 3,200)
  • Costs of a recently qualified driver ($ 1,800)
  • Initial number of conductors (55):


Step # 3: What are the decision variables?

  • Number of monthly recruitments (Decision variable):


  • Number of experienced drivers from February to June (Redundant decision variables):


Step # 4: What are the constraints (constraints)

  • Non-negativity of decision variables:

$$C_t \geqslant0$$ $$R_t \geqslant0$$

  • Cover the need for driver-hours of driving required each month:

$$\forall t \in [1..6], (140-80).R_t+140.(C_t-R_t) \geqslant B_t$$

  • Calculation of the number present at t (recurrence):

$$\forall t \in [2..6], C_t = C_{t-1} + R_{t-1}-7\%.C_{t-1}$$

Step # 5: What is the objective of the problem?

  • The objective here is to minimize the total salary cost over the 6 months:

$$Min \sum_{t=1}^{6}1\,800.R_t+3\,200.C_t$$

4. Translation in XLOPTIM


Definition of the main decision variable (with its limits):


Addition of the constraint on the need for load coverage:


Attachment of the objective:


Calculation of the optimal:


which gives a value of the following optimal objective function:


5. Conclusion

This result means that by deciding to choose this recruitment strategy, the total business cost will be € 1,448,647. The displayed result is optimal and obtained after 2 seconds.

This case study provides you with an example of a load coverage problem. They are common in transport companies, factories and even in restaurants.

You can change the size of the problem and the assumptions to vary the problem and therefore the solutions. Do not hesitate to contact us if you have any questions following the use of our XLOPTIM® solver.

To download the 14-day free trial version of the software, go to: Download the trial version