Q CIS250 – Advanced Excel Case Study 3 Copyright 2022 Post University, ALL RIGHTS RESERVED Due Date: 11:59 pm EST Sunday of Unit 5 Points: 100 Overview: In this case study, you will use Solver and create Scenario Summaries for two options to determine the optimum schedule for part-time servers. Instructions: Using Solver 1. Open the file CaseStudy3_Staff.xlxs and using your own name, save it as Lastname_Firstname_CaseStudy3_Staff.xlsx. 2. Be sure that Solver is installed. 3. Use Solver to determine the optimum schedule for part-time servers for the weekday schedule at the Raleigh, North Carolina restaurant. 4. Solver for Option 1, in which you want the objective (Cell D16) to have the minimum (Min) number of employees scheduled by changing the number of employees (D4:D9). Make sure to add the constraints below: a. The first constraint is to be sure that you have enough (>=) employees (E11:Q11) to meet expected demand (E13:Q13) b. The second constraint is to be sure that only whole numbers are used for the Employees (D4:D9) (because you cannot schedule part of a person to work). Copyright 2022 Post University, ALL RIGHTS RESERVED 5. Make sure you see this solution: This solution used 97.00 hours and has 9 employees scheduled at 10:30 in the morning, which are 7 more than needed. If you do not, please try again. 6. Keep the Solver Solution by generating an Answer Report. This will create a new sheet titled Answer Report 1. Rename the sheet to Option 1 and change the color to Yellow. 7. Keep this solution, and solver for Option 2 by adding an additional constraint that allows no more than two employees to be scheduled for the first shift, (Hint: D4<=2). 8. Make sure you see this solution: This solution sets Total Hours to 100.50. In this scenario, the greatest surplus of staff (7 employees) occurs between 2:00 and 3:00. Copyright 2022 Post University, ALL RIGHTS RESERVED 9. Keep the Solver Solution by generating an Answer Report. This will create a new sheet titled Answer Report 2 Rename the sheet to Option 2 and change the color to Green. Using Scenario Manager 1. Create a Scenario Summary for the two options: a. Name the first scenario as Option 1. b. Use D4:D9 as the changing cells. c. Do not change the values in Scenario Values fields. d. Click OK e. Add another scenario. f. Name it Option 2. g. Use D4:D9 as the changing cells. h. Use the following values for each of the respective fields: ? 2 ? 8 ? 2 ? 3 ? 7 ? 3 i. Click OK j. Select Option 1 and click the Show Button. k. The total hours should be 100.50. l. Select Option 2 and click the Show Button. m. The total hours should be 104.50 2. Create a Summary worksheet by clicking on the Summary worksheet. 3. Use D11:Q11,D16 for the Results Cells. NOTE, you will need to select D16 as non-adjacent cell. Use the Ctrl Key for the PC and the Command Key for the MAC to select that field. 4. A worksheet will be created named Scenario Summary. Copyright 2022 Post University, ALL RIGHTS RESERVED Requirements: ? Submit the completed Excel worksheet. Be sure to read the criteria below by which your work will be evaluated. Copyright 2022 Post University, ALL RIGHTS RESERVED Evaluation Rubric for Case Study 3 Assignment CRITERIA POINTS Save as Lastname_Firstname_CaseStudy3_Staff.xlxs 2 Sheet named Option 1 created 3 Tab color changed to Yellow 1 Objective Cell (Min) shown with Final Value as 97 7 Variable cell range D4:D9 6 Constraints range E11:Q11 6 Constraints range D4:D9 set as an integer 6 Sheet named Option 2 created 3 Tab color changed to Green 1 Objective Cell (Min) shown with Final Value as 100.5 7 Variable cell range D4:D9 6 Constraints range E11:Q11 6 Constraints range D4:D9 set as an integer 6 Constraint range D4<=2 6 Sheet named Scenario Summary created 6 Option 1 Changing Cells set: 2,8,2,3,7,3 7 Option 2 Changing Cells set: 2,8,3,7,3,3 7 Option 1 Total_Hours equals 100.50 7 Option 2 Total_Hours equals 104.50 7