Scenarios and Solver Grader Project

Project Description: The Painted Paradise Resort & Spa is working on getting a handle on its expenditures on part-time labor. The management feels that there is some opportunity to improve scheduling to reduce costs in some areas. One area has the requirements that the schedules be five days a week with two days in a row off. With these constraints, they would like to build an optimal schedule (from a cost perspective). Management is also considering the impact of raises and potential benefits increases due to new regulations.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Open the downloaded spreadsheet named e05ch10_grader_h1_Schedule.xlsx. Save the file with the name e05ch10_grader_h1_Schedule_LastFirst, using your last and first name. If necessary, enable the content. 0.000 2 In cell F14, enter a formula that will calculate the total number of employees scheduled to work Sunday for all schedules A-G. Begin by multiplying the number of employees for schedule A, in cell D6, by the value representing whether or not employees are working that day in cell F6. Be sure to make the reference to cell D6 an absolute cell reference so that, when finished, the formula can be copied across the row. So far the result of the formula is 0 because schedule A has people scheduled off on Sunday. Next, add to the product, the number of employees scheduled to work Sunday for schedule B. Continue with the formula by adding a similar calculation for schedules C-G, making an absolute cell reference for each cell in column D. 10.000 3 Copy the formula from cell F14 to the right through cell L14. 5.000 4 Insert a SUM function in D19 that adds all the shifts scheduled in the range F14:L14. 10.000 5 Enter a formula in cell D21 that calculates the payroll for the week, which is the Total shifts scheduled multiplied by Pay/Employee/Day. 8.000 6 Using Solver, minimize the Payroll/Week for the resort by changing the values in cells D6:D12. Remember these points as you complete the Solver Parameters dialog box: The number of employee work days in the range F14:L14 must be greater than or equal to the demand (range F16:L16), so there are enough people working for that day’s needs. The number of employees scheduled in D6:D12 must be >=1. 11.000 7 Solve this model using the Simplex LP method and keep the Solver solution. 5.000 8 In the Part-Time Expenses sheet, insert into cell E8 a formula to calculate the total annual part-time expense. The Benefit % is an estimate in the form of a percentage of total wages and needs to be added to the cost of wages based on the average part-time hours multiplied by the average hourly rate. 5.000 9 Create three scenarios based only on a variation in part-time hours. The number given is the expected hours needed for next year. The actual hours are expected to vary between 90% and 110% of the projected hours. Create three scenarios that show the effect on a Min, Avg, or Max usage of hours on the Total PT Wage Expense. Create and name the scenarios (in this order) as Min Hours, Avg Hours, and Max Hours. 12.000 10 Create three new scenarios for the same three levels of hours usage but with a 3% increase in the wage rate (to 10.61). Create and name these three scenarios (in this order) as Min Hours w/Raises, Avg Hours w/Raises, and Max Hours w/Raises. 12.000 11 Create three new scenarios with the same three levels of hours but a 3% increase in wage rate (to 10.61) and a 4% increase in benefit costs (to .32). Create and name these three scenarios (in this order) Min Hours w/Raises&Benefits, Avg Hours w/Raises&Benefits, and Max Hours w/Raises&Benefits. Ensure that the Avg Hours scenario is shown. 12.000 12 Display the results of the nine scenarios by creating a scenario summary. 6.000 13 Label the rows (in B6:B8 respectively) as Part Time Hours, Part Time Wage, and Part Time Benefits. The expense row (in B10) needs to be named PT Wage Expense. Delete the existing values in C6:C8 and C10. 4.000 14 Save and close the workbook. Exit Excel. Submit the workbook as directed. 0.000 Total Points 100.000