Shelly Cashman Excel 365

Shelly Cashman Excel 365 | Module 9: SAM Critical Thinking Project C Full Circle Conferences

FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING

September 27, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file SC_EX365_CT9C_FirstLastName_1.xlsx as SC_EX365_CT9C_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_CT9C_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.

PROJECT STEPS

1. Martin Mosley runs the southern office of Full Circle Conferences, a company that organizes conferences, conventions, trade shows, and other events for North American organizations. 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 Income worksheet. Martin asks you to correct the errors in the worksheet. Start with the first error.

a. Use the Trace Precedents arrows to find the source of the #VALUE! error for the Workshop gross income, and then use the Trace Dependents arrows to determine whether the Workshop gross income formula causes other errors in the worksheet.

b. Correct the formula, which should multiply the Workshop attendance fee per person by the minimum number of attendees, and then add the recordings fee to that result.

c. Remove the trace arrows.

2. Correct the #NAME? error.

a. Use any error-checking method to determine the source of the #NAME? error, which should calculate the average income per session.

b. Correct the #NAME? error by editing the formula.

3. Correct the divide-by-zero errors.

a. Evaluate the formula that calculates the Conference income per attendee to determine which cell is causing the divide-by-zero error.

b. Correct the formula, which should divide the income per event by the minimum number of attendees.

c. Fill the rest of the per attendee cells with the formula you corrected. (Hint: You will correct the new error for the Convention income per attendee in the following steps.)

4. Martin suspects that the two remaining errors are related to the zero value for the minimum number of Convention attendees per session. He asks you to make sure that anyone entering the minimum number of attendees enters a number greater than zero to avoid divide by zero errors. Add data validation for the minimum number of attendees per session for the five types of sessions.

a. Set a data validation rule for the minimum attendees per session for the five types of sessions that allows only whole number values greater than 0.

b. Add an Input Message using Number of Attendees as the Input Message title and the following text as the Input message: Enter the minimum number of attendees for this event.

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

5. Identify the invalid data in the worksheet and correct the entry.

a. Circle the invalid data in the worksheet.

b. Type 25 as the minimum number of attendees for a convention, and then verify that this change cleared the remaining errors in the worksheet.

6. Go to the Consulting worksheet. This worksheet analyzes financial data for small-group consulting sessions, which Full Circle runs throughout the day of an event. Martin has already created a scenario named Current Enrollment that calculates profit based on the current number of people enrolled for each consulting session. He also wants to calculate profit based on the maximum number of attendees. Add a new scenario to compare the profit with maximum enrollments.

a. Use Max Attendance as the scenario name.

b. Use the enrolled clients per day data for the five types of sessions as the changing cells.

c. Enter cell values for the Max Attendance scenario as shown in bold in Table 1, which are the same values as for the maximum clients per day for the five types of sessions.

Table 1: Cell Values for the Max Attendance Scenario

7. Martin also wants to calculate profit based on the minimum number of attendees. Add another new scenario to compare the profit with low consulting session enrollment as follows:

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

b. Use the enrolled clients per day data for the five types of sessions as the changing cells.

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

Table 2: Cell Values for the Low Attendance Scenario

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

9. Go to the Proposed Consulting worksheet. Martin is considering whether to change the fees for the small-group consulting sessions. He has created three scenarios on the Proposed Consulting worksheet showing the profit with a $10 or $15 fee increase or a $5 fee decrease. Compare the average profit per event based on the scenarios.

a. Create a Scenario Summary report using the average profit per event for the five types of sessions as the result cells to show how the average profit changes depending on the fee changes.

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

10. Martin also wants to focus on one or two types of small-group consulting sessions at a time when comparing the average profit per event. Return to the Proposed Consulting worksheet and create another type of report.

a. Create a Scenario PivotTable report using the average profit per event for the five types of sessions 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 the numeric values in the New Fees PivotTable worksheet using the Accounting number format with 0 decimal places and $ as the symbol.

11. Go to the Virtual Training worksheet. Martin wants to determine the number of virtual 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.

a. Use the total weekly profit for all training programs 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 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 the cells below the "Maximum weekly profit model" text. 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. Martin 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 on a new worksheet that uses Training Answer Report as its name.

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: Income 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: Consulting Worksheet

Final Figure 3: New Fees Scenario Report Worksheet

Final Figure 4: New Fees PivotTable Worksheet

Final Figure 5: Proposed Consulting Worksheet

Final Figure 6: Training Answer Report Worksheet

Final Figure 7: Virtual Training Worksheet

Need help with this project?