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. | |||||||||||