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

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

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.