Please indepth explain the steps below by Preferences per shift $1=$ most preferred, $5=$ least preferred

Employee

Day $1$ AM Shift

Total shifts per employee

Stewart

Eagan

Jelani

Rafael

Total employees per shift

Number of shifts per day

Shift Limits

Day $1$

Stewart

Eagan

Jelani

Rafael

Rafael

FrontDeskShifts

Forecast telling me where exactly to access each excel tool and how to aplly it$.$ Thanks. Start Excel. Open the downloaded file named Excel$\_$CH$10\_$PS$1\_$FrontDesk.xlsx$.$ Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. $0$

$2$ The FrontDeskShifts worksheet contains a template for the next two day schedule. The employee preferences are listed in the table in cells C$8$:G$12.$ Cells C$15$:G$19$ contains a template for two days$$ worth of shifts at the front desk. These cells should contain a $1$ if a given employee from column C works a shift and a $0$ if they do not. This range will need to be changed to fulfill the scheduling needs.

Each employee must be scheduled to work two shifts over the course of the schedule. In cell H$16,$ enter a formula that will calculate the total number of shifts from cells D$16$:G$16.$ Copy the formula down to cell H$19.$

$8$

$3$ Each shift must have two employees assigned to it$.$ In cell D$20,$ calculate the total number of employees scheduled, using the range D$16$:D$19.$ Copy the formula across to cell G$20.8$

$4$ Each employee can only work a single shift per day. In cell D$25$ enter a formula that calculates the number of Day $1$ shifts for Stewart. Fill the formula down to cell D$28$ using fill without formatting. $8$

$5$ In cell E$25$ calculate the total number of Day $2$ shifts for Stewart. Fill the formula down to cell E$28$ using fill without formatting. $8$

$6$ In order to construct a schedule that meets the employees most preferred shifts, calculate the total preference rating in D$30.$ This calculation should multiply each cell from the range D$9$:G$12$ with the corresponding cells in D$16$:G$19.$ This can be accomplished with several Excel functions. It may be most effective to multiply the range D$9$:G$12$ with D$16$:G$19$ within a sum function. $2$

$7$ Using Solver, minimize the Preference Total by determining which shift each employee works. Remember these points as you complete the Solver Parameters dialog box.

$$Each employee must work two shifts.

$$Each shift must have two employees assigned.

$$The number of shifts per day for each employee must be less than or equal to one.

$$Solve the model using Simplex LP and keep the Solver Solution.

$13$

$8$ On the Forecast sheet, in cell C$9,$ insert a formula to calculate the gross revenue from spa appointments and product sales.

$5$

$9$ In cell G$10,$ insert a formula to calculate the total fixed costs from employee salaries and operating costs.

$5$

$10$ In cell G$13,$ insert a formula to calculate the employee commissions from the value in F$13$ and the revenue from spa appointments.

$5$

$11$ In cell G$14,$ insert a formula to calculate the total expenses from the total fixed costs and the employee commissions.

$6$

$12$ In cell B$15,$ insert a formula to calculate the net income from gross revenue and total expenses. $5$

$13$ Create three scenarios based on the values in the Forecast sheet. The spa management would like to consider scenarios where the revenue from spa appointments and product sales, and operating costs could vary.

The first scenario is based on the values already in the sheet. Name this scenario Best Case

Create a new scenario named Most Likely where the revenue from the spa appointments is $$145,000,$ the revenue from the Product Sales is $$60,000,$ and Operating Costs are $$20,000.$

Create a final scenario named Worst Case where the revenue from the spa appointments is $$130,000,$ the revenue from the Product Sales is $$50,000,$ and Operating Costs are $$25,000.$

$18$

$14$ Display the results of the three scenarios created in the prior step by creating a Scenario PivotTable report using the net income as the results cell. $4$

$15$ On the Scenario PivotTable sheet, replace the text in A$1$ with Spa Forecast and in cell A$2$ enter the text Scenario PivotTable Report and change the font to be bold and $14$ point. Merge and center cell A$2$ across cell B$2.$ Adjust the width of the columns to display all text. Format cells B$4$:B$6$ as Currency. Replace the text in cell A$3$ with Scenarios and replace the text in cell B$3$ with Net Income

$3$

$16$ Save and close Excel$\_$Ch$10\_$PS$1\_$Schedule.xlsx$.$ Exit Excel. Submit your files as directed. $0$