Shelly Cashman Excel 365

Shelly Cashman Excel 365 | Module 9: SAM Project B Top End Tours

FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING

September 28, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file SC_EX365_9B_FirstLastName_1.xlsx as SC_EX365_9B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_EX365_9B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

3. This project requires you to use the Solver add-in. If this add-in is not available on the Data tab in the Analyze group (or if the Analyze group is not available), install Solver as follows:

a. In Excel, click the File tab, and then click the Options button in the left navigation bar. Click the Add-Ins option in the left pane of the Excel Options dialog box. Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button. In the Add-Ins dialog box, click the Solver Add-In check box and then click the OK button. Follow any remaining prompts to install Solver.

PROJECT STEPS

1. Leo Torres runs the main office of Top End Tours, a company in Cambridge, Massachusetts, that organizes travel experiences around the world. He is using an Excel workbook to analyze the company's financials and asks for your help in correcting errors and solving problems with the data. Go to the Tours worksheet. Leo asks you to correct the errors in the worksheet. Correct the first error as follows:

a. Use the Trace Precedents arrows to find the source of the #VALUE! error in cell G7.

b. Use the Trace Dependents arrows to determine whether the formula in cell G7 causes other errors in the worksheet.

c. Correct the formula in cell G7, which should multiply the Adventure per person fee (cell G3) by the minimum number of guests (cell G5), and then add the lectures fee (cell G6) to that result.

d. Remove the trace arrows.

2. Correct the #NAME? error in cell C21 as follows:

a. Use any error-checking method to determine the source of the error in cell C21, which should calculate the average income per day.

b. Correct the error by editing the formula in cell C21.

3. Correct the divide-by-zero errors as follows:

a. Evaluate the formula in cell C17 to determine which cell is causing the divide-by-zero error.

b. Correct the formula in cell C17, which should divide the income per tour (cell C15) by the minimum number of guests (cell C5).

c. Fill the range D17:G17 with the formula in cell C17. (Hint: You will correct the new error in cell D17 in the following steps.)

4. Leo suspects that the two remaining errors are related to the zero value in cell D5. He asks you to make sure that anyone entering the minimum number of guests enters a number greater than zero to avoid divide by zero errors. Add data validation to the range C5:G5 as follows:

a. Set a data validation rule for the range C5:G5 that allows only whole number values greater than 0.

b. Add an Input Message using Number of Guests as the Input Message title and the following text as the Input message: Enter the minimum number of guests for this tour.

c. Add an Error Alert using the Stop style, Guest Error as the Error Alert title, and the following text as the Error message: The minimum number of guests must be greater than 0.

5. Identify the invalid data in the worksheet and correct the entry as follows:

a. Circle the invalid data in the worksheet.

b. Type 4 as the minimum number of guests for a convention (cell D5).

c. Verify that this change cleared the remaining errors in the worksheet.

6. Go to the Corporate worksheet. This worksheet analyzes financial data for corporate tours, which Top End Tours organizes for businesses and other organizations. Leo has already created a scenario named Current Enrollment that calculates profit based on the current number of clients enrolled for each tour. He also wants to calculate profit based on the maximum number of clients. Add a new scenario to compare the profit with maximum enrollments as follows:

a. Use Max Enrollment as the scenario name.

b. Use the enrolled clients per day data (range C9:G9) as the changing cells.

c. Enter cell values for the Max Enrollment scenario as shown in bold in Table 1, which are the same values as in the range C8:G8.

Table 1: Cell Values for the Max Enrollment Scenario

7. Leo also wants to calculate profit based on the minimum number of clients. Add another new scenario to compare the profit with low tour enrollment as follows:

a. Add a scenario to the worksheet using Low Enrollment as the scenario name.

b. Use the enrolled clients per day data (range C9:G9) as the changing cells.

c. Enter cell values for the Low Enrollment scenario as shown in bold in Table 2.

Table 2: Cell Values for the Low Enrollment Scenario

8. Show the Low Enrollment scenario values in the Corporate worksheet, and then close the Scenario Manager window.

9. Go to the Corporate Updates worksheet. Leo is considering whether to change the fees for the corporate tours. He has created three scenarios on the Corporate Updates worksheet showing the profit with a $10 or $15 fee increase or a $5 fee decrease. Compare the average profit per tour based on the scenarios as follows:

a. Create a Scenario Summary report using the average profit per tour (range C11:G11) as the result cells to show how the average profit changes depending on the fee changes.

b. Use New Fees Report as the name of the worksheet containing the report.

10. Leo also wants to focus on one or two types of corporate tours at a time when comparing the average profit per event. Return to the Corporate Updates worksheet and create another type of report as follows:

a. Create a Scenario PivotTable report using the average profit per tour (range C11:G11) as the result cells to compare the average profit depending on the fee changes in a PivotTable.

b. Use New Fees PivotTable as the name of the worksheet containing the PivotTable.

c. Format cells B4:F6 in the New Fees PivotTable worksheet using the Accounting number format with 0 decimal places and $ as the symbol.

11. Go to the Business Training worksheet. Leo wants to determine the number of corporate travel training sessions the company can hold on Tuesdays and Thursdays to make the highest weekly profit without interfering with consultations, which are also scheduled for Tuesdays and Thursdays and use the same resources. Use Solver to find this information as follows:

a. Use the total weekly profit (Total_Weekly_Profit) as the objective cell in the Solver model, with the goal of determining the maximum value for that cell.

b. Use the number of Tuesday and Thursday sessions for the five programs (range C3:G4) as the changing variable cells.

c. Determine and enter the constraints based on the information provided in Table 3.

d. Use Simplex LP as the solving method to find a global optimal solution.

e. Save the Solver model in cell B23.

f. Solve the model, keeping the Solver solution.

Table 3: Solver Constraints

Constraint | Cell or Range Each session is scheduled at least once on Tuesday and once on Thursday | C3:G4 Each Tuesday and Thursday session value is an integer | C3:G4 Each type of session is scheduled 1 time per week or more | C5:G5 Each type of session is scheduled 3 times per week or less | C5:G5 The total number of Tuesday sessions is 5 or less | Total_Tuesday_Sessions The total number of Thursday sessions is 15 or less | Total_Thursday_Sessions The total number of sessions per week is 13 | Total_Weekly_Sessions The total number of Tuesday consultations is 2 or less | Tuesday_Consultations The total number of Thursday consultations is 2 or less | Thursday_Consultations The total number of consultations per week is 5 or less | Total_Consultations

12. Leo wants to document the answer Solver found, including the constraints and a list of the values Solver changed to solve the problem. Produce an Answer report for the Solver model as follows:

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

b. Use Training 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 website to submit your completed project.

Final Figure 1: Tours Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Corporate Worksheet

Final Figure 3: New Fees Report Worksheet

Final Figure 4: New Fees PivotTable Worksheet

Final Figure 5: Corporate Updates Worksheet

Final Figure 6: Training Answer Report Worksheet

Final Figure 7: Business Training Worksheet

Need help with this project?