Shelly Cashman Excel 2019 | Module 9: End of Module Project 1 lilyona martinez 1

Subject:MS ExcelPrice:19.87 Bought4

Share With

Shelly Cashman Excel 2019 | Module 9: End of Module Project 1

lilyona martinez

1. 

Enzo Diprima is the operations manager for the Forno Pizza franchise in Providence, Rhode Island. He is developing

an Excel workbook to send to franchise owners and managers to help them with payroll, budget, and staff

scheduling. He asks for your help in correcting errors and solving problems with the data.

Go to the

Staf

worksheet, which lists employees for a typical Forno Pizza franchise and includes a sample paystub.

Enzo asks you to correct the errors in the worksheet. Use any error-checking method to determine the source of the

#NAME? error in cell F13, which should calculate the average daily wage. Correct the error by editing the formula in

cell F13.

2. In cell F22, use the Trace Dependents arrows to identify cells that depend on cell F22. Edit the formula to correct the

#NAME? error in cell F22 and its dependent cell.

3. 

Use the Trace Precedents arrows to find the source of the #DIV/0! error in cell F25. Correct the formula in cell F25,

which should divide the net pay (cell F23) by the number of hours worked (cell F18). Remove the trace arrows.

4. Enzo wants to make sure that the hourly wages entered in the range E4:E11 are between $10 and $20 per hour.

Set a data validation rule for the range E4:E11 that allows only decimal number values from 10 to 20. Add an Input

Message using

Hourly Wage

as the Input Message title and the following text as the Input message:

Enter a number from 10 to 20.

Add an Error Alert using the Stop style,

Invalid Wage

as the Error Alert title, and the following text as the Error

message:

Confirm the hourly wage.

5. 

Circle the invalid data in the worksheet. Type

20

as the hourly wage in the cell containing invalid data.

6. Go to the

Budget

worksheet, which contains a typical weekly budget for a Forno Pizza franchise. Enzo has already

created a scenario named Promotions that calculates the weekly profit when the restaurant runs a promotion to

increase business. He wants to create another scenario that compares the weekly profit if the franchise raises

prices.

Add a new scenario named

Raise Prices

that uses the revenue in the range

B4:B5

, the variable costs in the range

B9:B10

, and the fixed costs in the range

B14:B18

as the changing cells. Enter cell values for the Raise Prices

scenario as shown in bold in Table 1, which are the same as the current values except for a change to the Average

order per diner.

7. 

Enzo is ready to compare the weekly profit for the current values with those in the Promotions and Raise Prices

scenarios.

Create a Scenario Summary report using the weekly profit (cell

B26

) as the result cell to show how the weekly profit

changes depending on the revenue and cost changes. Use

Weekly Budget Scenario Report

as the name of the

worksheet containing the report.

8. 

Return to the

Budget

worksheet. Enzo also wants to compare the changes to the total costs and the weekly profit

with the two scenarios.

Create a Scenario PivotTable report using the total costs and weekly profit (range

B25:B26

) as the result cells. Use

Weekly Budget PivotTable

as the name of the worksheet containing the PivotTable. Format cells B4:C5 in the

Weekly Budget PivotTable

worksheet using the Currency number format with 0 decimal places and $ as the symbol.

9. Return to the

Budget

worksheet. Forno Pizza traditionally holds an opening night party for each franchise, inviting

local media and others to introduce the restaurant to a new location. Each restaurant has a budget of $300 for the

opening night. Enzo wants to know how many guests can attend with the costs in the range E4:E7.

Use Goal Seek to set the budget (cell

E7

) to

300

by changing the number of guests (cell

E6

).

10. Go to the

Schedule

worksheet, which provides a sample schedule for employees working five consecutive days with

two days off. Enzo wants to find the best schedule for these employees that provides enough staff while minimizing

payroll costs.

In Solver, use the weekly payroll (cell C15, named

Weekly_payroll

) as the objective cell in the Solver model, with

the goal of determining the minimum value for that cell. Use the number of staff on each schedule (range

C4:C10

)

as the changing variable cells. Enter the constraints based on the information provided in Table 2.

Use Simplex LP as the solving method to find a global optimal solution. Save the Solver model in cell B18. Solve the

model, keeping the Solver solution.

11. 

Enzo wants to document the answer Solver found, including the constraints and a list of the values Solver changed

to solve the problem.

Solve the model again, this time choosing to produce an Answer report. Use

Schedule Answer Report

as the

name of the worksheet containing the Answer report.