Answered You can hire a professional tutor to get the answer.
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...
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 ushers—14 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 located in the Excel10 > Case4 folder included with your Data Files,
and then save the workbook as Crystal Screen in the location specified by your instructor.
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 day's 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:
