Shelly Cashman Excel 2019 | Module 9: End of Module Project 1 | Forno Pizza (Full answer in 2025)

Forno Pizza
FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING

GETTING STARTED
• Open the file SC_EX19_EOM9-1_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_EOM9-1_FirstLastName_2.xlsx by changing the “1” to a “2”.
o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file SC_EX19_EOM9-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
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 Staff 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.
Table 1: Cell Values for the Raise Prices Scenario

Cell Value
Diners_per_week (cell B4) 1400
Average_order (cell B5) 12
Food_and_beverage (cell B9) 4000
Supplies (cell B10) 1300
Payroll (cell B14) 6880
Rent (cell B15) 800
Utilities (cell B16) 400
Insurance (cell B17) 250
Miscellaneous (cell B18) 200

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.
Table 2: Solver Constraints

Constraint Cell or Range
The number of employees must be greater than or equal 2 C4:C10
The number of employees must be less than or equal 8 C4:C10
The number of employees must be an integer C4:C10
Employees working each day must be greater than or equal to the staff needed D11:J11 and D12:J12

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.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
 
Final Figure 1: Staff Worksheet

Final Figure 2: Weekly Budget Scenario Report Worksheet

Final Figure 3: Weekly Budget PivotTable Worksheet

Final Figure 4: Budget Worksheet

Final Figure 5: Schedule Answer Report Worksheet

Final Figure 6: Schedule Worksheet

#MicrosoftExcel
#MicrosoftOffice
#Microsoft
#ShellyCashman
#ShellyChasmanExcel
#SAMProject