Assignment: Scheduling Employees
Project Description:
The Painted Paradise Resort 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 area. 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. Management has tasked you with developing the numbers.
Step Instructions
1 Start Excel. Open the downloaded spreadsheet named e05ws10_grader_a1.xlsx. Save the file with the name e05ws10_grader_a1_LastFirst, replacing LastFirst with your name.
2 In cell F14, insert a formula that will calculate the total number of employee shifts scheduled for Sunday. Multiply each value in column F by its corresponding value in column D, and add all of those products to produce the result. Use absolute cell referencing so that when the formula is copied to the right, any references to column D will not shift.
3 Copy the formula from cell F14 to the right through cell L14.
4 Insert a SUM function in D19 that adds all the shifts scheduled in the range F14:L14.
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.
6 Using Solver, minimize the Payroll/Week for the resort. 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 greater than zero.
7 Solve this model using the Simplex LP method and keep the Solver solution.
8 In the Part-Time Expenses sheet, insert into cell E8 a formula to calculate the total annual part-time expense.
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.
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.
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.
12 Display the results of the nine scenarios by creating a scenario summary.
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.
14 Ensure that the worksheets are in the correct order: Schedule, Scenario Summary, Part-Time Expenses. Save the workbook. Close the workbook. Exit Excel. Submit the workbook as directed.
Attachment:- mosken_e05ws10_grader_a1.xlsx