90.2K

Verified Solution

Link Copied!

excel problems need all solved. thanks

The 1s represent the days worked, and the 0s represent the days off (so each schedule has two days in a row off). The range D5:D11 contains the number of employees assigned to the schedule in the corresponding row. This range will need to be changed to fulfill the scheduling needs. In cell F13 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 D5, by the value representing whether.or. ost employees are working that day in cell F5. Be sure to make the reference to cell D5 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. Copy the formula from cell F13 to the right through cell L13. Insert a SUM function in D18 that adds all the shifts scheduled in the range F13:L13. Enter a formula in cell D20 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 by changing the values in cells D5:D11. Remember these points as you complete the Solver Parameters dialog box. The number of employee work days in the range F13:L13 must be greater than or equal to the demand (range F15:L15), so there are enough people working for that day's needs. The number of employees scheduled in D5:D11 must be >=1. 7 Solve this model usinq 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. 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. 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 85% and 120% 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

Answer & Explanation Solved by verified expert

See Answer