Case Problem 2 Data File needed for this Case Problem: NP_EX_8-4.xlsx Hardin Medical Clinic Catherine Smythe is a personal manager for Hardin Medical Clinic in Toledo, Ohio. Part of Catherines job is to manage the weekly nursing schedule. The clinic employs 20 nurses16 full-time and 4 part-time. The clinic needs 12 nurses on weekdays and 10 on weekends. Catherine is working on the schedule for an upcoming week and is trying to accommodate all the vacation and sick-leave requests, while maintaining the required level of on-duty nurses. Catherine asks you to develop a schedule that meets the needs of the clinic and the requests of the nurses. Complete the following: 1. Open the NP_EX_8-4.xlsx workbook located in the Excel8 > Case2 folder included with your Data Files. Save the workbook as NP_EX_8_Clinic in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. In the Schedule worksheet, in the range D5:J24, enter 0 in all the cells. In this sheet, 0s and 1s indicate whether an employee is schedule for a shift that day (0 indicates an employee is not scheduled and 1 indicates an employee is scheduled 4. In the range K5:K24, enter formulas to sum the total number of shifts worked by each employee from Monday through Sunday. 5. In the range L5:L24, calculate the total number of hours worked by each employee during the week by multiplying the number of shifts worked by 8 (each shift is eight hours long). 6. In the range D26:J26, enter the required shifts per day. The clinic requires 12 nurses on the weekdays, and 10 nurses on Saturday and Sunday. 7. In the range D27:J27, enter formulas to sum the total number of shifts scheduled for nurses on each day. 8. In the range D28:J28, enter formulas to calculate the difference between the number of nurses scheduled and the number of nurses required. A negative value indicates that not enough nurses are scheduled to cover that days shifts. 9. In cell D30, calculate the total shortfall for the week by summing the daily shortfall values in the range D28:J28. 10. Create a Solver model that sets the value of cell D30 to 0 (indicating that all shifts are covered for every day of the week) by changing the values in the range D5:J24 under the following constraints: a. Add a binary constraint to force every value in the range D5:J24 to be either a 0 or a 1. b. Add a constraint to limit the total hours worked by each full-time employee to less than or equal to 40. c. Add a constraint to limit the total hours worked by each part-time employee to less than or equal to 24. d. Add a constraint to require that the difference values in the range D28:J28 all equal 0. e. Add more constraints based on the schedule requests in the range C5:C24 so that nurses are not scheduled to work shifts on days they have requested off. If a nurse has requested a day off, the cell corresponding to that day for that nurse must equal 0. 11. Run the Solver model using the Evolutionary method. (Solver might take a minute to arrive at a solution.) Confirm that the schedule generated by Solver fulfills all the requirementsall shifts are covered each day, no full-time nurse works more than 40 hours, no part-time nurse works more than 24 hours, and no nurse works on a requested day off. 12. Save the workbook, and then close it.

A Hardin Medical Center Nursing Schedule for the Week of August 9 to August 15 Nurses Required Nurses Scheduled Daily Shortfall Total Shortfall Across All Days