I have an excel project that I'm stuck with. I'm including screen shots of the project. One screen shot includes all the instructions to follow on the project. The second screen shot includes the excel file needed for it. I have been able (I think) to complete instructions a-e but i'm stuck on f. Can you please help? D21MPrinted Paradis4s Srh.Day effSunday, MunlayEmployenWindFnSanManTueThuSot1.1.Monday, TusoyTuesday. Wed1.1Wed, Toarsday1.1.10Thwsdoy friday61.1.11Friday, Soturdy1.1.1.12Saturdoy Sontay1.1314Schedale Totals:242424222022241516Total Needed:22171314161824171619Total stits schedule160PayEmeloyee DayFayroWesk20sa0 002122232425262728SchedulePart-Time ExpensesA2A.BDEPainted ParadiroREEORT O EPA34 ExpensesAverage Part-time hours2106006Average Hourly Rate$10.307Веnefit %28.00%8 Total PT Wage Expense1011121314151617181920212223242526SchedulePart-Time ExpensesF. Problem Solve 1- page 581Student data file needed: e05ch10Schedule.xlsx You will save your file as: e05ch10Schedule_LastFirst.xlsx Scheduling EmployeesThe Painted Paradise Resort & Spa is working on getting a handle on its expenditures on part-time labor. The management feels that there is someopportunity 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 arow off. With these constraints, they would like to build an optimal schedule (from a cost perspective). Management is also considering the impact ofraises and potential benefits increases due to new regulations.a. Open the Excel file, e05ch10Schedule. Save your file as e05ch10Schedule_Lastfirst using your last and first name. If necessary, enable content.b. The Schedule worksheet has the possible schedules each employee can work (rows 6-12). The 1s represent the days worked, and the Os represent thedays off (so each schedule has two days in a row off). The range D6:D12 contains the number of employees assigned to the schedule in the correspondingrow. This range will need to be changed to fulfill the scheduling needs.c. 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 thenumber 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 thereference 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 0because 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.d. Copy the formula over to L14.e. In cell D19, calculate the total number of shifts scheduled, using the range F14:L14.f. In cell D21, enter a formula that calculates the payroll for the week, which is the product of shifts scheduled and cost per employee per day.g. Using Solver, minimize the Payroll/Week for the resort by determining the optimal number of employees to have assigned to each schedule. Rememberthese points as you complete the Solver Parameters dialog box.• The number of people in the range F14:L14 must be greater than or equal to the demand (range F16:L16) so there are enough people working for thatday's needs.• The number of employees scheduled in D6:D12 must be greater than zero.• Solve this model, using the Simplex LP method.• Keep the Solver solution in the model.• Create an Answer report.h. In the Part-Time Expenses sheet, in cell E8, insert a formula to calculate the total annual part-time wage expense. The Benefit% is an estimate in theform of a percentage of total wages and needs to be added to the cost of wages based on the average part-time hours the average hourly rate.i. Create nine scenarios based on the Part-Time Expenses. These scenarios are based on the possibilities management see for next year.• The first three scenarios are based only on a variation in hours. The number given is the expected hours needed for next year. The actual hours may go aslow as 90% of the 210,600 hours expected and as high as 110% of the 210,600 hours expected. Create three scenarios that show the effect of average,minimum, and maximum usage of hours on the Total PT Wage Expense. Use the 210,600 expected hours for the Avg scenario. Name the scenarios AvgHours, Min Hours, and Max Hours.• The next three scenarios are for the same three levels of hours usage but with a 3% increase in the wage rate. Name these three scenarios Avg Hours w/Raises, Min Hours w/Raises, and Max Hours w/Raises.• The last three scenarios are with the same three hours levels but with a 3% increase in wage rate and a Benefit% estimate of 32%. Name these threescenarios Avg Hours w/Raises&Benefits, Min Hours w/Raises&Benefits, and Max Hours w/Raises&Benefits.j. Display the results of the nine scenarios created in the prior step by creating a scenario summary. Delete the row labels in column C. Replace the rowlabels by typing the following in the appropriate cell in column B: Part Time Hours, Part Time Wage, Part Time Benefits, and PT Wage Expense. Adjust thewidth of column B so that all labels are visible. The nine scenarios will display the data in a column for each scenario.k. Save the workbook, exit Excel, and then submit your file as directed by your instructor.

Name: I have an excel project that I'm stuck with. I'm including screen sho
Uploaded: 2024-03-20T06:57:05.000Z
Channel: Bartleby
Description: I have an excel project that I'm stuck with. I'm including screen shots of the project. One screen shot includes all the instructions to follow on the project. The second screen shot includes the excel file needed for it. I have been able (I thin

Question

I have an excel project that I'm stuck with.  I'm including screen shots of the project.  One screen shot includes all the instructions to follow on the project.  The second screen shot includes the excel file needed for it.  I have been able (I think) to complete instructions a-e but i'm stuck on f.  Can you please help? D21MPrinted Paradis4s Srh.Day effSunday, MunlayEmployenWindFnSanManTueThuSot1.1.Monday, TusoyTuesday. Wed1.1Wed, Toarsday1.1.10Thwsdoy friday61.1.11Friday, Soturdy1.1.1.12Saturdoy Sontay1.1314Schedale Totals:242424222022241516Total Needed:22171314161824171619Total stits schedule160PayEmeloyee DayFayroWesk20sa0 002122232425262728SchedulePart-Time ExpensesA2A.BDEPainted ParadiroREEORT O EPA34 ExpensesAverage Part-time hours2106006Average Hourly Rate$10.307Веnefit %28.00%8 Total PT Wage Expense1011121314151617181920212223242526SchedulePart-Time ExpensesF. Problem Solve 1- page 581Student data file needed: e05ch10Schedule.xlsx You will save your file as: e05ch10Schedule_LastFirst.xlsx Scheduling EmployeesThe Painted Paradise Resort & Spa is working on getting a handle on its expenditures on part-time labor. The management feels that there is someopportunity 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 arow off. With these constraints, they would like to build an optimal schedule (from a cost perspective). Management is also considering the impact ofraises and potential benefits increases due to new regulations.a. Open the Excel file, e05ch10Schedule. Save your file as e05ch10Schedule_Lastfirst using your last and first name. If necessary, enable content.b. The Schedule worksheet has the possible schedules each employee can work (rows 6-12). The 1s represent the days worked, and the Os represent thedays off (so each schedule has two days in a row off). The range D6:D12 contains the number of employees assigned to the schedule in the correspondingrow. This range will need to be changed to fulfill the scheduling needs.c. 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 thenumber 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 thereference 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 0because 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.d. Copy the formula over to L14.e. In cell D19, calculate the total number of shifts scheduled, using the range F14:L14.f. In cell D21, enter a formula that calculates the payroll for the week, which is the product of shifts scheduled and cost per employee per day.g. Using Solver, minimize the Payroll/Week for the resort by determining the optimal number of employees to have assigned to each schedule. Rememberthese points as you complete the Solver Parameters dialog box.• The number of people in the range F14:L14 must be greater than or equal to the demand (range F16:L16) so there are enough people working for thatday's needs.• The number of employees scheduled in D6:D12 must be greater than zero.• Solve this model, using the Simplex LP method.• Keep the Solver solution in the model.• Create an Answer report.h. In the Part-Time Expenses sheet, in cell E8, insert a formula to calculate the total annual part-time wage expense. The Benefit% is an estimate in theform of a percentage of total wages and needs to be added to the cost of wages based on the average part-time hours the average hourly rate.i. Create nine scenarios based on the Part-Time Expenses. These scenarios are based on the possibilities management see for next year.• The first three scenarios are based only on a variation in hours. The number given is the expected hours needed for next year. The actual hours may go aslow as 90% of the 210,600 hours expected and as high as 110% of the 210,600 hours expected. Create three scenarios that show the effect of average,minimum, and maximum usage of hours on the Total PT Wage Expense. Use the 210,600 expected hours for the Avg scenario. Name the scenarios AvgHours, Min Hours, and Max Hours.• The next three scenarios are for the same three levels of hours usage but with a 3% increase in the wage rate. Name these three scenarios Avg Hours w/Raises, Min Hours w/Raises, and Max Hours w/Raises.• The last three scenarios are with the same three hours levels but with a 3% increase in wage rate and a Benefit% estimate of 32%. Name these threescenarios Avg Hours w/Raises&Benefits, Min Hours w/Raises&Benefits, and Max Hours w/Raises&Benefits.j. Display the results of the nine scenarios created in the prior step by creating a scenario summary. Delete the row labels in column C. Replace the rowlabels by typing the following in the appropriate cell in column B: Part Time Hours, Part Time Wage, Part Time Benefits, and PT Wage Expense. Adjust thewidth of column B so that all labels are visible. The nine scenarios will display the data in a column for each scenario.k. Save the workbook, exit Excel, and then submit your file as directed by your instructor.

Accepted Answer

Find the given details below:
Total Shift schedule: 160
Pay/Employee/Day: $80