1) Using Solver, minimize the Payroll/Week for the resort by changing the values in cells D5:D11. Remember these points as you complete the Solver Parameters dialog box: The number of employee work days in the range F13:L13 must be greater than or equal to the demand (range F15:L15), so there are enough people working for that day’s needs. The number of employees scheduled in D5:D11 must be >=1. 2) Solve this model using the Simplex LP method and keep the Solver solution.
3) In the Partafime Expenses sheet, insert into cell E8 a formula to calculate the total annual part-time e pense. The Benefit % is an estimate in the form of a percentage of total wages and needs to be added to the cost of wages based on the average part-time hours multiplied by the average hourly rate.
Schedule Days off Employees Sun Mon Tue Wed Thr Fri Sat
A Sunday, Monday 5 0 0 1 1 1 1 1
B Monday, Tuesday 1 0 0 1 1 1 1 1
C Tuesday, Wednesday 5 1 1 0 0 1 1 1
D Wednesday, Thursday5 1 1 1 0 0 1 1
E Thursaday, Friday 5 1 1 1 1 0 0 1
F Friday, Saturday 4 1 1 1 1 1 0 0
G Saturday, Sunday 5 0 1 1 1 1 1 0
Schedule Total 22 24 24 22 22 23 23
Total Needed 22 15 13 18 15 20 24
Total shifts schedule 160
Pay/Employee Day 5105.00
Payroll/Week Schedule $16.800.00
Expenses
Average Part-time hours 2250000
Average Hourly Rate $11.50
Benefit% 30.00%