Question: Crystal Screen Michael Boyle is a manager at the Crystal Screen movie theater in Oakland Park, Florida. One of his jobs is to create the
Crystal Screen Michael Boyle is a manager at the Crystal Screen movie theater in Oakland Park, Florida. One of his jobs is to create the weekly work schedule for the movie ushers. The theater employs 16 ushers14 are full-time and two are part-time. The theater needs eight ushers to work each weekday, 11 on Saturday, and 10 on Sunday. Michael is currently working on the usher schedule for the second week in August. Michael tries to accommodate all time-off requests while maintaining the required staff. He has been developing a workbook to automatically generate the work schedule. The workbook already contains the names of the ushers and their time-off requests. He asks you to determine which ushers will be working which days. Complete the following:
1
Open the Crystal workbook.
2
In the Documentation worksheet, enter your name and the date.
3
You will use the numbers 0 and 1 to indicate which shifts each employee is working. For example, 0 indicates an employee is not working that day, whereas 1 indicates that employee is scheduled to work. In the Schedule worksheet, in the range D5:J19, enter 0 in each cell to indicate that you have not yet scheduled any shift for any employee.
4
In the range K5:K19, enter formulas to calculate the total number of shifts for each employee.
5
In the range L5:L19, calculate the total number of hours worked by each employee. Each shift is eight hours.
6
In the range D21:J21, enter the number of shifts required per day. The theater requires eight shifts on weekdays, 11 shifts on Saturday, and 10 shifts on Sunday.
7
In the range D22:J22, enter a formula that calculates the total number of shifts actually scheduled for the employees on each day.
8
In the range D23:J23, enter a formula to subtract the attendants required value from the attendants actually scheduled value. A negative number indicates that not enough employees have been scheduled to cover the days shifts.
9
In cell D25, calculate the total shortfall in shifts by entering a formula to total the values in the range D23:J23.
10
Create a Solver model that sets the value of cell D25 to 0 (indicating that all shifts are covered) by changing the values in the range D5:J19 under the following constraints:
- Add a binary constraint to force every value in the range D5:J19 to be either a 0 or a 1.
- Add a constraint to limit the total hours worked by each full-time employee to less than or equal to 40.
- Add a constraint to limit the total hours worked by each part-time employee to less than or equal to 24.
- Add a constraint to require that the difference values in the range D23:J23 all equal 0.
- Based on the entries in the range C5:C19, add constraints so that employees are not scheduled to work shifts on days when they are unavailable to work.
11
Run the Solver model using the Evolutionary method. (Note that Solver might take a minute to arrive at a solution.) Confirm that the schedule generated by Solver fulfills all of the requirementsall shifts are covered each day, no employee works more hours than allowed by his or her full- or part-time status, and no employee works on a requested day off.
12
Because of increased movie attendance on the weekends, Michael wants to know whether he can schedule 11 employees on Sunday instead of 10. Revise the work schedule information, set the values in the range D5:J19 to 0, and then rerun Solver to determine whether the schedule can be revised to accommodate an extra Sunday shift. Write your analysis of the Solver results as a comment in the merged cell A21.
13
Restore the workbook to the original schedule where only 10 ushers are needed on Sunday.
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts