Employee Scheduling Problem by dqm36629

VIEWS: 51 PAGES: 15

Employee Scheduling Problem document sample

• pg 1
Scheduling Examples
On each example worksheet, read the comments at the bottom of the sheet, then
click Tools Solver... to examine the decision variables, constraints, and objective.
To find the optimal solution, click the Solve button.
In this series of models we will see how the Solver can help in staff scheduling with employee
preferences, assigning people to offices, and in larger-scale problems such as airline crew
scheduling and employee hiring, firing and training.

In the Crew worksheet, we look at a very simplified application where a small airline needs to
schedule crews to operate various flights. A crew 'rotation' must begin and end in the same city.

In the Offices worksheet, we look at the common problem of assigning employees to offices,
taking into account employee preferences.

In the three Scheduling worksheets, an amusement park needs to assign employees to
different work schedules to meet the demand for operation of the various rides on busy days.
In Sched2, we take into account employee preferences in scheduling. In Sched3, we also take
into account seniority and assign greater weight to senior employees' preferences.

In the HireFire worksheet, we consider a company that has to change the composition of its
workforce towards more highly trained employees. It can hire, fire and train employees at
various costs. Different objectives are possible, such as minimizing costs or employee turnover.

In the Troops worksheet, an army needs to move troops from 3 different training camps to 4
bases. The army can minimize cost or time by using this transportation model. Similar models
occur in the Logistics Examples workbook.
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

Crew Scheduling
A small airline company maintains 2 daily flights between Salt Lake City, Chicago and Dallas.
How should the company schedule the crews to minimize cost?

Flight Schedule
From                 To           Departure       Arrival      Departure       Arrival
Salt Lake City          Dallas          9:00 AM       12:00 PM       2:00 PM       5:00 PM
Salt Lake City         Chicago          10:00 AM       2:00 PM       3:00 PM       7:00 PM
Dallas         Salt Lake City      8:00 AM       11:00 AM       2:00 PM       5:00 PM
Dallas            Chicago          9:00 AM       11:00 AM       3:00 PM       5:00 PM
Chicago          Salt Lake City      8:00 AM       12:00 PM       2:00 PM       6:00 PM
Chicago              Dallas          10:00 AM      12:00 PM       4:00 PM       6:00 PM

A crew must leave and arrive in the same city. It is possible to fly the crew back aboard another
airline. This would always be on a 8:00 PM flight. There are 6 airplanes in use.
When a crew is actually flying a plane, the entire crew is paid \$200 per hour. The other time spent
(waiting between flights or flying aboard another airplane) costs the company \$75 per hour.

Possible Crew Rotations
(S=Salt Lake City, D=Dallas, C=Chicago, ( )=Back with other company)
Flying Hours     Other Hours        Cost        Decision
SD+DS                       6                2         \$1,350             0
SD+(DS)                     3               11         \$1,425             0
SD+DC+(CS)                  5               10         \$1,750             0
SC+(CS)                     4               10         \$1,550             0
SC+CD+(DS)                  6                5         \$1,575             0
DS+SD                       6                3         \$1,425             0
DS+(SD)                     3               12         \$1,500             0
DS+SC+(CD)                  7                7         \$1,925             0
DC+CS+(SD)                  6                5         \$1,575             0
DC+CD                       4                5         \$1,175             0
CS+SD+(DC)                  7                7         \$1,925             0
CS+SC                       8                3         \$1,825             0
CD+DC                       4                3         \$1,025             0
CD+DS+(SC)                  7                9         \$2,075             0
Total Cost       \$0
Twelve Flight Constraints
Flight       Number of crews
SD 1                  0
SD 2                  0
SC 1                  0
SC 2                  0
DS 1                  0
DS 2                  0
DC 1                  0
DC 2                  0
CS 1                  0
CS 2                  0
CD 1                  0
CD 2                  0

Page 2
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

Problem
An airline company maintains a schedule of two daily flights between Salt Lake City, Dallas and
Chicago. A crew that leaves a city in the morning has to return there at night. The crew can be
brought back on another airline. There are 6 airplanes in use. When a crew is flying, the cost is \$200
per hour. When a crew is waiting or being flown back, the cost is \$75. How should the company
schedule its crews to minimize cost?

Solution
1) The airline has already determined what all the possible crew rotations can be. The variables are
the binary integer decisions to accept rotations. In worksheet Crew these are defined as
Rotation_decisions.
2) The constraints are simple. We want only one crew per flight. This gives
Crews_on_flight = 1
and the logical constraint gives
Rotation_decisions = binary
3) The objective is to minimize total cost. On worksheet Crew this cell is given the name Total_cost.

Remarks
Please confirm for yourself that the crew rotations chosen meet the required schedule. More
sphisticated versions of this model are widely used in the airline industry, but the same approach
can be used in scheduling truck drivers, boat crews, etc.

Page 3
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

Office Assignment
A company wants to assign 14 employees to 10 offices. Each employee has a preference for a certain office.
How should the company assign employees to the offices to maximize the preference of all employees?

Preferences (1=first, 10=last choice)

Office 10
Office 1

Office 2

Office 3

Office 4

Office 5

Office 6

Office 7

Office 8

Office 9
Employee 1          3  2  1                          4          6          5          8  9 10 7
Employee 2          5  3  2                          6          1          7          9  8  4 10
Employee 3         10 8   1                          9          7          4          3  6  2  5
Employee 4          7  3  2                          9          5          4          8  6  1 10
Employee 5          1  3  6                          8          5          2          9 10 7   4
Employee 6          4  9  1                          5          6          8          2  7 10 3
Employee 7          2  1 10                          9          5          3          6  8  4  7
Employee 8          6  5  1                          3          2          4          7  8  9 10
Employee 9          8  9 10                          5          4          3          2  1  6  7
Employee 10         9 10 3                           2          5          4          1  7  8  6
Employee 11         7  3  5                          2          9          8          1 10 4   6
Employee 12         6  5  1                          9         10          2          3  4  7  8
Employee 13         6  8 10                          9          1          2          3  4  5  7
Employee 14         6  3  5                          9          1          2         10 4   8  7

Preference
Office 10
Office 1

Office 2

Office 3

Office 4

Office 5

Office 6

Office 7

Office 8

Office 9

Total

Assignments
Employee 1          0          0          0          0          0          0          0          0          0          0          0        0
Employee 2          0          0          0          0          0          0          0          0          0          0          0        0
Employee 3          0          0          0          0          0          0          0          0          0          0          0        0
Employee 4          0          0          0          0          0          0          0          0          0          0          0        0
Employee 5          0          0          0          0          0          0          0          0          0          0          0        0
Employee 6          0          0          0          0          0          0          0          0          0          0          0        0
Employee 7          0          0          0          0          0          0          0          0          0          0          0        0
Employee 8          0          0          0          0          0          0          0          0          0          0          0        0
Employee 9          0          0          0          0          0          0          0          0          0          0          0        0
Employee 10         0          0          0          0          0          0          0          0          0          0          0        0
Employee 11         0          0          0          0          0          0          0          0          0          0          0        0
Employee 12         0          0          0          0          0          0          0          0          0          0          0        0
Employee 13         0          0          0          0          0          0          0          0          0          0          0        0
Employee 14         0          0          0          0          0          0          0          0          0          0          0        0
Total               0          0          0          0          0          0          0          0          0          0                   0
Required            1          1          1          1          2          1          2          2          2          1

Problem
A company wants to assign 14 employees to 10 offices. There are four offices that require 2 people. Each
employee has given their preference. A 1 means first choice, 2 means second choice, etc. How should the
company assign the people to the offices to optimize the preferences of the employees?

Solution

Page 4
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

1) The variables are the assignments of the people to different offices. On worksheet Offices these are
given the name Assignments.
2) There are the following logical constraints
Assignments = binary
and the other constraints
Assignments_per_employee = 1
Total_employees = Required_employees
3) The objective is to optimize the preference of the employees. That means we have to minimize the sum
of the total preferences given to the assigned offices, defined on the worksheet as Total_preference.

Remarks
When everybody wants a different office, there will be no problems. If all employees prefer the same
office (more likely!), the problem gets more difficult and it might be necessary to give an employee 7th or
8th choice. It might be wise, in that case, to add a constraint to say that no assignment worse than 5th
choice is given, for instance. This may cause the problem to be infeasible, i.e., there is no possible
solution. If this happens, you will have to relax the constraint on the assignments, e.g. no worse than 6th
or even 7th choice.

Page 5
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

Personnel scheduling for an Amusement Park 1
For employees working five consecutive days with two days off, find the schedule
which meets demand from attendance levels while minimizing payroll costs.

Sch.     Days off              Employees          Sun Mon Tue Wed Thu               Fri   Sat
A     Sunday, Monday              3               0   0   1   1   1                 1     1
B     Monday, Tuesday             5               1   0   0   1   1                 1     1
C     Tuesday, Wed.               6               1   1   0   0   1                 1     1
D     Wed., Thursday              4               1   1   1   0   0                 1     1
E     Thursday, Friday            6               1   1   1   1   0                 0     1
F     Friday, Saturday            1               1   1   1   1   1                 0     0
G     Saturday, Sunday            0               0   1   1   1   1                 1     0

Schedule Totals:       25               22    17     14     15     15     18   24

Total Demand:                         22    17     13     14     15     18   24

Pay/Employee/Day:         \$40
Payroll/Week:             \$5,000

Problem
An amusement park needs a certain number of employees each day of the week.
Every employee must be on a schedule that gives him/her two consecutive days off.
How many employees should the park hire and what schedule should they be on to
minimize total payroll cost?

Solution
1) The variables are the number of people hired for each of the 7 possible schedules.
On worksheet Sched1 these are given the name Employees_per_schedule.
2) The logical constraints are
Employees_per_schedule >= 0 via the Assume Non-Negative option
Employees_per_schedule = integer
There is also the constraint to have enough employees to operate the rides each day:
Employees_per_day >= Required_per_day
3) The objective is to minimize payroll. This is defined on the worksheet as Payroll.

Remarks
This is an example of a simple, but classic personnel scheduling problem. Hospitals,
schools, police forces, etc., can all use a model like this to optimize their personnel
scheduling.

Page 6
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

Personnel scheduling for an Amusement Park 2
Maximize the preference of the employees' schedules while maintaining the schedule
that minimizes payroll costs.

Sch.     Days off             Employees      Sun Mon Tue Wed Thu              Fri    Sat
A     Sunday, Monday             3           0   0   1   1   1                1      1
B     Monday, Tuesday            5           1   0   0   1   1                1      1
C     Tuesday, Wed.              7           1   1   0   0   1                1      1
D     Wed., Thursday             3           1   1   1   0   0                1      1
E     Thursday, Friday           7           1   1   1   1   0                0      1
F     Friday, Saturday           0           1   1   1   1   1                0      0
G     Saturday, Sunday           0           0   1   1   1   1                1      0

Schedule Totals:      25           22     17    13    15      15   18     25

Total Demand:                    22     17    13    14      15   18     24

Pay/Employee/Day:        \$40
Payroll/Week:            \$5,000

Preference of employees (7=first ,1=last choice)                         Schedule assigned to
A      B      C      D E         F    G             A    B      C     D   E   F
Employee 1           4      6      5      3    2       7    1              0    0     0     0   0   0
Employee 2           3      4      5      2    1       7    6              0    0     0     0   0   0
Employee 3           6      5      2      7    4       3    1              0    0     0     0   0   0
Employee 4           4      5      3      6    2       7    1              0    0     0     0   0   0
Employee 5           5      4      2      6    7       3    1              0    0     0     0   0   0
Employee 6           1      2      3      4    5       6    7              0    0     0     0   0   0
Employee 7           7      5      3      1    6       4    2              0    0     0     0   0   0
Employee 8           4      3      2      5    6       1    7              0    0     0     0   0   0
Employee 9           5      4      3      2    7       6    1              0    0     0     0   0   0
Employee 10          1      3      2      5    6       7    4              0    0     0     0   0   0
Employee 11          6      7      2      1    3       4    5              0    0     0     0   0   0
Employee 12          5      4      6      7    3       2    1              0    0     0     0   0   0
Employee 13          1      2      3      4    5       6    7              0    0     0     0   0   0
Employee 14          4      6      7      3    2       5    1              0    0     0     0   0   0
Employee 15          4      5      7      6    3       2    1              0    0     0     0   0   0
Employee 16          4      3      2      5    6       7    1              0    0     0     0   0   0
Employee 17          6      5      7      3    2       1    4              0    0     0     0   0   0
Employee 18          7      6      5      4    3       2    1              0    0     0     0   0   0
Employee 19          6      5      3      4    2       7    1              0    0     0     0   0   0
Employee 20          4      5      3      6    7       2    1              0    0     0     0   0   0
Employee 21          7      6      5      3    4       1    2              0    0     0     0   0   0
Employee 22          7      6      2      5    3       4    1              0    0     0     0   0   0
Employee 23          6      5      7      3    4       2    1              0    0     0     0   0   0
Employee 24          5      6      4      3    1       2    7              0    0     0     0   0   0
Employee 25          6      4      7      5    3       2    1              0    0     0     0   0   0

Totals                      0     0    0     0    0   0

Demand                      3     5    7     3    7   0

Problem
The amusement park discussed in model Sched1 has hired the necessary employees. It now needs to decide
which employee goes on which schedule. Each employee has given a list with his/her preferences. A 7 means
the most desired schedule, a 1means the least desired. How should the park divide the schedules among the
employees?

Solution
1) The variables are the schedules to which each employee is assigned. On worksheet Sched2 these are given

Page 7
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

the name Schedules.
2) The logical constraints are
Schedules = binary
The other constraints are
Schedule_per_employee =1
Employees_scheduled = Employees_required
3) The objective is to optimize employee preference, and in this case that means maximize the sum of the
preferences. This sum is defined on the worksheet as Total_preference.

Remarks
In this model we solved the problem after finding the optimal solution of Sched1. It is possible to find both
solutions automatically. You can write a VBA macro to do this.

Page 8
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

G   Total   Preference
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0
0      0      0

0             0

0

Page 9
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

Personnel scheduling for an Amusement Park 3
Maximize the preference of the employees' schedules, taking into account seniority,
while maintaining the schedule that minimizes payroll costs.

Sch.     Days off             Employees        Sun Mon Tue Wed Thu                Fri   Sat
A     Sunday, Monday             3             0   0   1   1   1                  1     1
B     Monday, Tuesday            5             1   0   0   1   1                  1     1
C     Tuesday, Wed.              7             1   1   0   0   1                  1     1
D     Wed., Thursday             3             1   1   1   0   0                  1     1
E     Thursday, Friday           7             1   1   1   1   0                  0     1
F     Friday, Saturday           0             1   1   1   1   1                  0     0
G     Saturday, Sunday           0             0   1   1   1   1                  1     0

Schedule Totals:      25             22    17     13     15      15    18    25

Total Demand:                      22    17     13     14      15    18    24

Pay/Employee/Day:        \$40
Payroll/Week:            \$5,000

Preference of employees                              Schedule assigned to
Seniority (Years)       A    B     C        D         E           A    B     C     D        E Total   Preference
Employee 1           5                   5    3     2        4      1              0    0     0     0     0        0       0
Employee 2           6                   3    4     5        2      1              0    0     0     0     0        0       0
Employee 3           4                   4    3     2        1      5              0    0     0     0     0        0       0
Employee 4           7                   3    4     2        1      5              0    0     0     0     0        0       0
Employee 5           3                   1    3     4        2      5              0    0     0     0     0        0       0
Employee 6           2                   4    5     3        1      2              0    0     0     0     0        0       0
Employee 7           7                   3    4     2        5      1              0    0     0     0     0        0       0
Employee 8           5                   2    4     3        1      5              0    0     0     0     0        0       0
Employee 9           3                   4    3     2        5      1              0    0     0     0     0        0       0
Employee 10          2                   3    4     2        5      1              0    0     0     0     0        0       0
Employee 11          5                   4    2     5        1      3              0    0     0     0     0        0       0
Employee 12          7                   5    3     2        4      1              0    0     0     0     0        0       0
Employee 13          5                   1    2     3        4      5              0    0     0     0     0        0       0
Employee 14          4                   4    2     5        3      1              0    0     0     0     0        0       0
Employee 15          9                   5    4     3        1      2              0    0     0     0     0        0       0
Employee 16          5                   3    5     1        4      2              0    0     0     0     0        0       0
Employee 17          6                   4    3     5        2      1              0    0     0     0     0        0       0
Employee 18          7                   3    4     2        5      1              0    0     0     0     0        0       0
Employee 19          6                   5    4     3        2      1              0    0     0     0     0        0       0
Employee 20          4                   3    5     4        2      1              0    0     0     0     0        0       0
Employee 21          3                   2    5     1        3      4              0    0     0     0     0        0       0
Employee 22          4                   2    5     1        3      4              0    0     0     0     0        0       0
Employee 23          6                   5    2     4        3      1              0    0     0     0     0        0       0
Employee 24          6                   3    1     5        2      4              0    0     0     0     0        0       0
Employee 25          7                   1    4     3        5      2              0    0     0     0     0        0       0

Totals           0     0    0       0   0                0

Demand           3     5    7       3   7

Problem
We now extend the model as seen in Sched2 by adding another factor. When deciding which employee
goes on which schedule, the amusement park decides to let the seniority of the employees affect the
decision. The seniority is simply measured in years of employment at the park. How should the company
assign the schedules to the employees?

Solution

Page 10
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

The solution is almost identical to the one in Sched2. The difference is that instead of just adding the
preferences, we now weight them by multiplying each preference by the employee's seniority.

Remarks
There are many ways of adjusting for seniority and other factors. By increasing and decreasing the size of
the seniority factors, you can adjust the importance of the seniority to the desired level.

Page 11
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

Preference

Page 12
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

Company Reorganization
A company wants to reorganize its labour force. It currently has 3 different kind of employees; untrained,
moderately trained and highly trained. Over the next 3 years, the company expects a necessary shift to
more trained employees. How should the company reorganize to minimize cost? Or minimize the number
of employees that have to be laid off?

Employee information
Available to
Cost of lay- be hired per    Cost of
off          year         hiring     Retraining of employees                           Cost
Highly Trained             \$700           500          \$250       Untrained -> Moderately trained                   \$400
Moderately Trained         \$500           800          \$150       Moderately trained-> Highly trained               \$500
Untrained                  \$350          1200          \$100
Estimated number of employees that are required.
Current       Year 1        Year 2        Year 3
Highly Trained              800          1200          1500          2000
Moderately Trained         1500          1500          2000          2500
Untrained                  2000          1600          1000            0
Number of employees that are trained, hired or laid off.
Number of employees trained
Year 1        Year 2        Year 3          Cost
Untrained -> Moderately trained             0             0            0                    \$0
Moderately trained-> Highly trained         0             0            0                    \$0
Number of employees hired
Year 1        Year 2        Year 3          Cost
Highly Trained                              0             0            0                    \$0
Moderately Trained                          0             0            0                    \$0
Untrained                                   0             0            0                    \$0
Number of employees laid off
Year 1        Year 2        Year 3          Cost
Highly Trained                              0             0            0                    \$0
Moderately Trained                          0             0            0                    \$0
Untrained                                   0             0            0                    \$0
Total number of employees laid off             0

Number of employees working
Year 1        Year 2        Year 3
Highly Trained                            800           800           800
Moderately Trained                       1500          1500          1500
Untrained                                2000          2000          2000
Total cost of reorganizing                            \$0

Problem
A company has three different kinds of employees. These are highly trained, moderately trained and untrained
workers. The company expects a shift towards more highly trained employees necessary over the next few
years. It is possible to train people at a certain cost Laying people off also costs a certain amount. How should
the company reorganize to save costs and/or have as few lay-offs as possible?

Solution
1) The variables are the number of people that are trained, hired and laid off. On worksheet HireFire these are

Page 13
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

given the names Trainees, Employees_hired, and Employees_laid_off.
2) The constraints can be divided into 2 parts.
First, there are the logical constraints, all of which are defined via the Assume Non-Negative option:
Trainees >= 0
Employees_hired >= 0
Employees_laid_off >= 0
Second, we have the training, laying off and hiring constraints. These do not use defined names, but are
represented on the worksheet by the following cells:
C22 <= B17
C23 <= B16
C26 : C28 <= C9 : C11
C31 : C33 <= B15 : B17
C40 : E42 = C15 : E17
D22 <= C41
D23 <= C42
D26 : D28 <= C9 : C11
D31 : D33 <= C40 : C42
E22 <= D41
E23 <= D42
E26 : E28 <= C9 : C11
E31 : E33 <= D40 : D42
In general, these constraints reflect the movement of employees from being hired untrained to becoming
moderately trained or highly trained.

3) The main objective is to minimize cost. This is defined on the worksheet as Total_cost.

Remarks
The model as presented here will find the method of organization that has the lowest cost. It can involve large
lay-offs. It is even possible that there are alternate solutions that require fewer lay-offs! To check this, you can
add the constraint Total_cost = Solution, where Solution is the amount previously found by the solver. Then
change the objective to minimize lay-offs. This way you are sure to find the solution that is least expensive and
involves the fewest layoffs. If the number of lay-offs is still unacceptable, you could solve the original problem
again and this time include a constraint like total_laid_off = 0, or * 1000. When this problem is solved you
can use the sensitivity analysis report to see how much an extra lay off would cost.

Page 14
65c95a68-f884-4833-ba7f-0e8a883d1b58.xls

Troop Movement
An army wants to move troops from 3 training camps to 4 different bases. How should
the troops be moved to minimize cost?

Moving Cost Per Man
Base 1        Base 2         Base 3         Base 4
Camp 1            \$34               \$26         \$29           \$31
Camp 2            \$42               \$33         \$28           \$35
Camp 3            \$36               \$29         \$32           \$38
Number Of Troops Moved
Base 1        Base 2         Base 3         Base 4         Total         Available
Camp 1             100           100            100           100             400             500
Camp 2             100           100            100           100             400             400
Camp 3             100           100            100           100             400             400
Total              300           300            300           300
Required           200           250            350           300
Cost             \$11,200       \$8,800         \$8,900        \$10,400       \$39,300

Problem
An army wants to move troops from 3 training camps to 4 different bases. All costs of moving a
soldier from any camp to any base are known. How should the army move the troops to
minimize cost?

Solution
1) The variables are the number of soldiers that are moved from each camp to each base. On
worksheet Troops these are given the name Troops_moved.
2) The constraints are
Troops_moved >= 0 via the Assume Non-Negative option
Troops_per_camp <= Troops_available
Troops_per_base = Troops_required
3) The objective is to minimize the total cost. This is defined on the worksheet as Total_cost.

Remarks
This model is a transportation model, like those shown in the Logistics Examples workbook. You
might wonder why there is no constraint to assure that the numbers of troops moved are integers.
It is a mathematical property of these types of problems that if the constants in the constraints are
integers, the solution values for the variables are always integers. It is beyond the scope of these
examples to further explore this.

Page 15

To top