Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your models.


 

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 G Total Preference
Employee 1 4 6 5 3 2 7 1 0 0 0 0 0 0 0 0 0  
Employee 2 3 4 5 2 1 7 6 0 0 0 0 0 0 0 0 0  
Employee 3 6 5 2 7 4 3 1 0 0 0 0 0 0 0 0 0  
Employee 4 4 5 3 6 2 7 1 0 0 0 0 0 0 0 0 0  
Employee 5 5 4 2 6 7 3 1 0 0 0 0 0 0 0 0 0  
Employee 6 1 2 3 4 5 6 7 0 0 0 0 0 0 0 0 0  
Employee 7 7 5 3 1 6 4 2 0 0 0 0 0 0 0 0 0  
Employee 8 4 3 2 5 6 1 7 0 0 0 0 0 0 0 0 0  
Employee 9 5 4 3 2 7 6 1 0 0 0 0 0 0 0 0 0  
Employee 10 1 3 2 5 6 7 4 0 0 0 0 0 0 0 0 0  
Employee 11 6 7 2 1 3 4 5 0 0 0 0 0 0 0 0 0  
Employee 12 5 4 6 7 3 2 1 0 0 0 0 0 0 0 0 0  
Employee 13 1 2 3 4 5 6 7 0 0 0 0 0 0 0 0 0  
Employee 14 4 6 7 3 2 5 1 0 0 0 0 0 0 0 0 0  
Employee 15 4 5 7 6 3 2 1 0 0 0 0 0 0 0 0 0  
Employee 16 4 3 2 5 6 7 1 0 0 0 0 0 0 0 0 0  
Employee 17 6 5 7 3 2 1 4 0 0 0 0 0 0 0 0 0  
Employee 18 7 6 5 4 3 2 1 0 0 0 0 0 0 0 0 0  
Employee 19 6 5 3 4 2 7 1 0 0 0 0 0 0 0 0 0  
Employee 20 4 5 3 6 7 2 1 0 0 0 0 0 0 0 0 0  
Employee 21 7 6 5 3 4 1 2 0 0 0 0 0 0 0 0 0  
Employee 22 7 6 2 5 3 4 1 0 0 0 0 0 0 0 0 0  
Employee 23 6 5 7 3 4 2 1 0 0 0 0 0 0 0 0 0  
Employee 24 5 6 4 3 1 2 7 0 0 0 0 0 0 0 0 0  
Employee 25 6 4 7 5 3 2 1 0 0 0 0 0 0 0 0 0  
   
  Totals 0 0 0 0 0 0 0 0  
   
  Demand 3 5 7 3 7 0 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
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.                

To Learn More:

For instant access to our white papers, example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, register now with no obligation.
User type
Email address
Name
First Last
Company
University
Phone