|
Solver.com
From Frontline Systems, developers of the Excel Solver.
|
|
|
|
|
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. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
