a Explore 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. Explore 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 requirements, all 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. B D E F G H | J K M N L Total Total Shifts Mon Tue Wed Thu Fri Sat Sun Hours 0 0 0 0 0 0 0 12 0 Ooo 0 0 10 10 32 256 0 0 0 0 0 0 0 0 0 0 90000000 0 0 0 0 E00000000000 0 0 0 0 0 0 0 0 0 0 4 Status Schedule Requests 5 Full Time Cannot work more than 24 hours this week 6 Full Time 7 Full Time 8 Full Time Can't work Saturday and Sunday 9 Full Time 10 Full Time Can't work this week 11 Full Time 12 Full Time 13 Full Time 14 Full Time Can't work Friday and Saturday 15 Full Time 16 Full Time 17 Full Time Can't work Thursday through Sunday 18 Full Time 19. Full Time Documentation Schedule # Ready Accessibility: Investigate 0 0 0 0 0 300|0|00|00|0|0|00|0 0 0 0 0 0 0 0 0|00|00|00|00|0 0 0 0 0 0 0 0 ooooo Oo oo Oooooo 0 0 0 0 0 O O 0 0 0 0 0 0 0 0 0 O 0 n + Average: 4.571428571 Count: 7 Sum: 32 - t 120% B D E F . J K L M N I O 0 0 0 0 Ooo 0 0 0 0 0 0 u oooooo OOOOO 0 ooooooo - ooo ooo OOOOOO Joooooo 0 0 0 0 0 Oo oo OOO Ooo 0 0 0 19 Full Time 20 Full Time Can't work Wednesday through Sunday 21 Part Time Can't work Monday through Wednesday 22 Part Time 23 Part Time 24 Part Time Can't work Monday and Saturday 25 26 Nurses Required 27 Nurses Scheduled 28 Daily Shortfall 29 30 Total Shortfall Across All Days 31 12 0 0 O O 10 10 32 22 a Explore 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. Explore 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 requirements, all 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. B D E F G H | J K M N L Total Total Shifts Mon Tue Wed Thu Fri Sat Sun Hours 0 0 0 0 0 0 0 12 0 Ooo 0 0 10 10 32 256 0 0 0 0 0 0 0 0 0 0 90000000 0 0 0 0 E00000000000 0 0 0 0 0 0 0 0 0 0 4 Status Schedule Requests 5 Full Time Cannot work more than 24 hours this week 6 Full Time 7 Full Time 8 Full Time Can't work Saturday and Sunday 9 Full Time 10 Full Time Can't work this week 11 Full Time 12 Full Time 13 Full Time 14 Full Time Can't work Friday and Saturday 15 Full Time 16 Full Time 17 Full Time Can't work Thursday through Sunday 18 Full Time 19. Full Time Documentation Schedule # Ready Accessibility: Investigate 0 0 0 0 0 300|0|00|00|0|0|00|0 0 0 0 0 0 0 0 0|00|00|00|00|0 0 0 0 0 0 0 0 ooooo Oo oo Oooooo 0 0 0 0 0 O O 0 0 0 0 0 0 0 0 0 O 0 n + Average: 4.571428571 Count: 7 Sum: 32 - t 120% B D E F . J K L M N I O 0 0 0 0 Ooo 0 0 0 0 0 0 u oooooo OOOOO 0 ooooooo - ooo ooo OOOOOO Joooooo 0 0 0 0 0 Oo oo OOO Ooo 0 0 0 19 Full Time 20 Full Time Can't work Wednesday through Sunday 21 Part Time Can't work Monday through Wednesday 22 Part Time 23 Part Time 24 Part Time Can't work Monday and Saturday 25 26 Nurses Required 27 Nurses Scheduled 28 Daily Shortfall 29 30 Total Shortfall Across All Days 31 12 0 0 O O 10 10 32 22