lab assignment
Mercym54
1620495316CH06_LabAssignment_Data2.xlsmAssignment 1 Staff Schedule
| Shift | Daily Pay Rate | |
| 7:00 a.m. − 11:00 a.m. | $32 | |
| 7:00 a.m. − 3:00 p.m. | $80 | |
| 11:00 a.m. − 3:00 p.m. | $32 | |
| 11:00 a.m. − 7:00 p.m. | $80 | |
| 3:00 p.m. − 7:00 p.m. | $32 | |
| 3:00 p.m. − 11:00 p.m. | $80 | |
| 7:00 p.m. − 11:00 p.m. | $32 | |
| Hours | Workers Needed | |
| 7:00 a.m. − 11:00 a.m. | 11 | |
| 11:00 a.m. − 1:00 p.m. | 24 | |
| 1:00 p.m. − 3:00 p.m. | 16 | |
| 3:00 p.m. − 5:00 p.m. | 10 | |
| 5:00 p.m. − 7:00 p.m. | 22 | |
| 7:00 p.m. − 9:00 p.m. | 17 | |
| 9:00 p.m. − 11:00 p.m. | 6 |
Snookers Restaurant is open from 8:00 a.m. to 10:00 p.m. daily. Besides the hours they are open for business, workers are needed an hour before opening and an hour after closing for setup and clean up activities. The restaurant operates with both full-time and part-time workers on the following shifts:
The following numbers of workers are needed during each of the indicated time blocks.
At least one full time worker must be available during the hour before opening and after closing. Additionally, at least 30% of the employees should be full-time (8-hour) workers during the restaurant’s busy periods from 11:00 a.m. − 1:00 p.m. and 5:00 p.m. − 7:00 p.m. Question 1. Formulate an ILP for this problem with the objective of minimizing total daily labor costs. Question 2. Implement your model in a spreadsheet and solve it. What is the optimal solution?
Question 1
Formulate an ILP for this problem with the objective of minimizing total daily labor costs.
Insert Your Answer Here.
Question 2
| S h i f t | Workers | |||||||||||||||||||||||||||||
| Hours | 1 | 2 | 3 | 4 | 5 | 6 | 7 | Available | Required | |||||||||||||||||||||
| 7:00 - 8:00 | ||||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 8:00 - 9:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 9:00 - 10:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 10:00 - 11:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 11:00 - 12:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 12:00 - 1:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 1:00 - 2:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 2:00 - 3:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 3:00 - 4:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 4:00 - 5:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 5:00 - 6:00 | |||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell | 6:00 - 7:00 | |||||||||||||||||||||||||||||
| 7:00 - 8:00 | ||||||||||||||||||||||||||||||
| 8:00 - 9:00 | ||||||||||||||||||||||||||||||
| 9:00 - 10:00 | ||||||||||||||||||||||||||||||
| 10:00 - 11:00 | ||||||||||||||||||||||||||||||
| Pay Rate | ||||||||||||||||||||||||||||||
| Workers | 0 Cliff Ragsdale: Variable cell |
Cliff Ragsdale: Constraint cell | 0 Cliff Ragsdale: Variable cell |
Cliff Ragsdale: Constraint cell | 0 Cliff Ragsdale: Variable cell |
Cliff Ragsdale: Constraint cell | 0 Cliff Ragsdale: Variable cell |
Cliff Ragsdale: Constraint cell | 0 Cliff Ragsdale: Variable cell | 0 Cliff Ragsdale: Variable cell | 0 Cliff Ragsdale: Variable cell |
|||||||||||||||||||
| Minimum | ||||||||||||||||||||||||||||||
| FT Workers | Req'd FT Workers | |||||||||||||||||||||||||||||
| 11:00 - 1:00 | ||||||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell |
Cliff Ragsdale: Constraint cell |
Cliff Ragsdale: Variable cell | 5:00 - 7:00 | |||||||||||||||||||||||||||
|
Cliff Ragsdale: Constraint cell |
Cliff Ragsdale: Objective cell |
Cliff Ragsdale: Variable cell |
Snookers Restaurant
Implement your model in a spreadsheet and solve it.
