1. In cell F14 of the Schedule worksheet, 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.
2. Copy the formula from cell F14 to the right through cell L14
3. Insert a SUM function in D19 that adds all the shifts scheduled in the range F14:L14.
4. Enter a formula in cell D21 that calculates the payroll for the week, which is the Total shifts scheduled multiplied by Pay/Employee/Day.
5. 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.
6. Solve this model using the Simplex LP method and keep the Solver solution.
7. 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.
8. 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 & name the scenarios (in this order) as Min Hours, Avg Hours, and Max Hours.
9. 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 & name these three scenarios (in this order) as Min Hours w/Raises, Avg Hours w/Raises, and Max Hours w/Raises.
10. 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 & 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.
11. Display the results of the nine scenarios by creating a scenario summary.
12. 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.

XI H 6 . 2 - 3. 0 205ch10_grader_h1_Schedule_HottSamantha - Excel ? - X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Samantha Hott Connections 24 4 Clear Flash Fill + Consolidate Group E+ ?+ Solver Get External Refresh Properties Sort Reapply Remove Duplicates ? What-If Analysis * Data * All - 3 Edit Links Filter 28 Ungroup Advanced Text to Columns Co Data Validation Relationships E Subtotal Connections Sort & Filter Data Tools Outline G Analysis A B C D E G H K M N Painted Paradise RESORT & SPA Expenses Average Part-time hours 189540 Average Hourly Rate $10.30 Benefit % 28.00% 8 Total PT Wage Expense $2,498,895 10 11 12 13 17 32 33 34 Schedule Part-Time Expenses + READY - + 100%205ch10_grader_h1_Schedule_HottSamantha - Excel ? - X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Samantha Hott ITConnections 24 24 x Clear Flash Fill + Consolidate Group - E+ ?+ Solver Get External Refresh Properties 19 Reapply All - es Edit Links Sort Filter Advanced Text to Remove Duplicates ? What-If Analysis * 2B Ungroup * 3 Data Columns Co Data Validation Relationships El Subtotal Connections Sort & Filter Data Tools Outline G Analysis B D E H M N Painted Paradise RESORT & SPA Days off Employees jun Mon Wed Sunday, Monday 4 Monday, Tuesday Tuesday, Wed. Wed., Thursday Thursday, Friday Friday, Saturday 12 Saturday, Sunday HOOHH H - 13 Schedule Totals: 0 0 4 0 6 4 Total Needed: 22 17 13 14 15 18 24 Total shifts schedule 18 Pay/Employee/Day: $80.00 Payroll/Week: $1,440.00 Schedule Part-Time Expenses + READY U -- -+ 84%