New Perspectives Excel 365

New Perspectives Excel 365 | Modules 9-12: SAM Capstone Project A Tahoe Healthcare Consulting

ANALYZE DATA AND WORK WITH FINANCIAL TOOLS

September 3, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file NP_EX365_CS9-12A_FirstLastName_1.xlsm as NP_EX365_CS9-12A_FirstLastName_2.xlsm

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

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

2. To complete this Project, you will also need the following files:

Support_EX365_CS9-12A_Consultants.accdb

Support_EX365_CS9-12A_QuarterlySales.csv

3. With the file NP_EX365_CS9-12A_FirstLastName_2.xlsm 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.

4. To complete this project, you need to add the Power Pivot tab to the ribbon as follows: o From the File tab, click the Options button. In the Data Options section of the Data tab, click the check box next to Enable Data Analysis add-ins: Power Pivot, Power View and 3D Map, and click OK.

5. To complete this project, you need to add the Developer tab. If this tab is not displayed, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the Developer check box, and click OK.

PROJECT STEPS

1. Viraj Pandey is an associate consultant at Tahoe Healthcare Consulting in Palo Alto, California. He is using an Excel workbook to analyze the firm's financial data for consulting projects, recent revenue, and funding for a new location in Los Angeles. He asks for your help in analyzing data, making financial calculations, and correcting errors. Go to the Overview worksheet and then unprotect it so you can edit the contents.

2. In the Consulting table, Viraj wants you to make sure that everyone entering project information enters the correct services, which are listed in the range I4:I7. He asks you to ensure users enter a value from this range. Create a data validation rule as follows:

a. In the range C4:C37, create a data validation rule that accepts only List values from the range I4:I7.

b. Create an input message that uses Service as the title and the following text as the input message: Enter a consulting service.

c. Create a Stop style error alert that uses Invalid Service as the title and the following text as the error message: Enter a service listed in column I.

3. Identify and correct any invalid values as follows to make sure all of the categories entered in the Service column of the Consulting table are valid:

a. In the range C4:C37, circle invalid data.

b. Change the invalid entry to IT to correct the error.

c. Clear any remaining validation circles.

4. In the range I9:J23, Viraj asks you to provide project information by contract amount, client type, and service. In cell J11, enter a formula using the DCOUNT function to count the number of projects with contracts over $100,000. Use the entire Consulting table as the database, "Contract" as the field name, and the values in the range I9:I10 as the criteria.

5. In cell J15, enter a formula using the DSUM function to total the initial payments for Biotech clients. Use the entire Consulting table as the database, "Initial Payment" as the field name, and the values in the range I13:I14 as the criteria.

6. In cell J19, enter a formula using the DMAX function to identify the maximum initial payment for a Biotech client. Use the entire Consulting table as the database, "Initial Payment" as the field name, and the values in the range I17:I18 as the criteria.

7. In cell J23, enter a formula using the DAVERAGE function to identify the average contract amount for Financial services. Use the entire Consulting table as the database, "Contract" as the field name, and the values in the range I21:I22 as the criteria.

8. Viraj created a macro named CopySummary to copy the project summary data in the range I3:L23, and then paste it on a new worksheet. When he runs the CopySummary macro, however, it includes extra data from column G and omits data in row 23. View and edit the macro as follows:

a. Open the CopySummary macro in the Visual Basic Editor. The macro should select and copy the range I3:L23.

b. In the CopySummary macro VBA code, change the range specified in the first line of code after the comments (Range("G3:L22").Select) to use I3:L23 as the range.

c. Save and close the CopySummary macro.

d. Run the CopySummary macro.

e. Use Project Summary as the name of the new worksheet the macro created.

9. Return to the Overview worksheet. Viraj also created a macro named AddRow that inserts a new blank row in the Consulting table in the range A4:G37. He wants you to assign the AddRow macro to a new button to make it easy to run the macro and add a new consulting project. Create the button as follows:

a. Insert a Button (Form Control) button in cell I1 to the left of the Print Summary button.

b. Assign the AddRow macro to the new button.

c. Change the height of the new button to 0.3" and the width to 1".

d. Align the new button to the left side of cell I1, and then align its top with the top of the Print Summary button.

e. Edit the text to display New Project on the new button.

10. Go to the Projects by State worksheet. Viraj has created a PivotTable on this worksheet to list consulting projects by state, start date, and service. Refresh the PivotTable so it displays the changes you made to the Service data on the Overview worksheet.

11. Reduce the clutter and organize the data in the PivotTable by modifying it as follows:

a. Change the report layout to show the PivotTable in Outline Form.

b. Group the Start Date values only by Months.

c. Sort the values in the State field in ascending order.

12. Return to the Overview worksheet. In the range I26:J29, Viraj wants you to display the project contract amounts per state. You can retrieve this information from the PivotTable on the Projects by State worksheet. Display the contract amounts per state as follows:

a. In cell J26, insert a formula using the GETPIVOTDATA function to display the total contract amount for projects in Arizona from cell G5 on the Projects by State worksheet.

b. In cell J27, insert a formula using the GETPIVOTDATA function to display the total contract amount for projects in California from cell G11 on the Projects by State worksheet.

c. In cell J28, insert a formula using the GETPIVOTDATA function to display the total contract amount for projects in Nevada from cell G18 on the Projects by State worksheet.

d. In cell J29, insert a formula using the GETPIVOTDATA function to display the total contract amount for projects in Oregon from cell G23 on the Projects by State worksheet.

13. Go to the Project Payments worksheet, which contains a PivotTable showing the contract and total paid amounts for each client. Viraj wants to know the difference and the percentage of difference between the contract and the initial payment amounts. Provide this information for Viraj as follows:

a. Insert a calculated field named Difference that subtracts the Initial Payment field amount from the Contract field amount.

b. Insert another calculated field named % Difference that subtracts the Initial Payment field amounts from the Contract field amounts, and then divides the result by the Contract field amount.

c. Change the number format of the Sum of % Difference amounts to Percentage with 2 decimal points.

d. Change the column heading in cell D4 to Balance and change the column heading in cell E4 to % Owed to simplify the column headings.

14. Viraj asks you to display project payment information for consulting services in Nevada. Modify and filter the PivotTable as follows:

a. Add Service as the second field in the Rows area to include the service performed for each project in the PivotTable.

b. Add State as a Filter field for the PivotTable.

c. Filter the PivotTable to show project payment information only for Nevada (NV).

15. Go to the Projects by Service worksheet, where Viraj has inserted a PivotTable showing the total contract amounts per service. He wants to further divide the project information by state. Display the State field values as column headings.

16. Viraj asks you to format the contract amount values to indicate how they relate to each other. Apply conditional formatting to the contract amounts in the PivotTable (range B5:E8) using the Blue – White – Red Color Scale.

17. Go to the Sales per Quarter worksheet, where Viraj wants you to list the sales per quarter for the last three years. He has a text file that already contains this data. Use Power Query to create a query and load data from a CSV file into a new table as follows:

a. Create a new query that imports data from the Support_EX365_CS9-12A_QuarterlySales.csv text file.

b. Transform the data to remove the Projects and Notes columns.

c. Close and load the query data to a table in cell A3 of the existing worksheet.

d. Change the table style to Turquoise, Table Style Medium 2 to coordinate with the rest of the workbook.

e. Format the values of the Sales ($mil) column as Currency with 0 decimal places and the $ symbol.

18. Go to the Consultants worksheet. Viraj wants you to display information about consultants in the six locations where Tahoe Healthcare Consulting has offices. This information is available in an Access database. Import the data from the Access database and insert a PivotTable as follows:

a. Create a new query that imports data from the Support_EX365_CS9-12A_Consultants.accdb database.

b. Select the Consultants and Offices tables for the import.

c. Only create a connection to the data and add the data to the Data Model.

d. In cell A3 of the Consultants worksheet, insert a PivotTable based on the data in the Data Model.

19. Viraj wants to show the number of consultants by title and the average of their years employed at each Tahoe Healthcare Consulting office. Modify the new PivotTable on the Consultants worksheet as follows to provide this information:

a. In the new PivotTable, display the OfficeCity field from the Offices table as the row headings.

b. Display the Title field from the Consultants table as the first Values field.

c. Display the YearsEmployed field from the Consultants table as the second Values field.

d. Change the summary function of the Sum of YearsEmployed field to calculate the average number of years.

e. Change the number format of the Average of YearsEmployed field to use the Number number format with one decimal place.

20. To relate the data in the Consultants and Offices tables to make a proper comparison, create a relationship between the Consultants and Offices tables based on the OfficeID field.

21. Viraj occasionally would like to focus on the office data according to the date the office was established. Add a Timeline Slicer as follows to the Consultants worksheet:

a. Insert a Timeline Slicer based on the OfficeEst field.

b. Move and resize the Timeline Slicer so it covers the range A12:D19.

c. Adjust the Timeline Slicer to display years instead of months.

22. Go to the New Office Loan worksheet, which contains data about a loan to set up a new office in Los Angeles. Viraj wants you to include a worksheet title to match the format of the other worksheets in the workbook. Insert and format WordArt as follows:

a. Insert WordArt using the Fill: Black Text Color 1; Shadow style.

b. Type New Office Loan as the worksheet title.

c. Change the text fill of the WordArt to Black, Text 1, Lighter 25%.

d. Change the font size to 28 point.

e. Move the WordArt to row 1 so that it spans columns C:E.

23. Before performing any calculations, Viraj asks you to correct the errors in the worksheet.

a. In cell L16, use the Error Checking command to identify the error in the cell.

b. Correct the error to total the values in the range B16:K16.

c. Use Trace Precedents arrows to find the source of the #DIV/0! error in cell B19.

d. Correct the formula in cell B19, which should divide the remaining principal (cell B18) by the loan amount (cell C4) to find the percentage of remaining principal.

e. Fill the range C19:K19 with the formula in cell B19 without formatting to correct the remaining #DIV/0! errors.

f. Remove any remaining trace arrows.

24. Viraj wants you to calculate the annual principal and interest payments for the expansion loan. Start by calculating the cumulative interest payments as follows:

a. In cell B16, enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan for Year 1 (payment 1 in cell B14 through payment 12 in cell B15). Use 0 as the type argument in your formula because payments are made at the end of the period.

b. Use absolute references for the rate, nper, and pv arguments, which are listed in the range C4:C10.

c. Use relative references for the start and end arguments.

d. Fill the range C16:K16 with the formula in cell B16 without formatting to calculate the interest paid in Years 2–10 and the total interest.

25. Calculate the cumulative principal payments as follows:

a. In cell B17, enter a formula using the CUMPRINC function to calculate the cumulative principal paid for Year 1 (payment 1 in cell B14 through payment 12 in cell B15). Use 0 as the type argument in your formula because payments are made at the end of the period.

b. Use absolute references for the rate, nper, and pv arguments, which are listed in the range C4:C10.

c. Use relative references for the start and end arguments.

d. Fill the range C17:K17 with the formula in cell B17 without formatting to calculate the principal paid in Years 2–10 and the total principal.

26. Viraj wants you to compare straight-line depreciation amounts with declining balance depreciation amounts to determine which method is more favorable for the firm's balance sheet. In the range G4:G6, he estimates that the new office space will be worth $950,000 in tangible assets at the beginning of the loan, and that the useful life of these assets is 10 years with a salvage value of $145,000. Start by calculating the straight-line depreciation amounts as follows:

a. In cell B23, enter a formula using the SLN function to calculate the straight-line depreciation for the new office space during the first year of operation.

b. Use absolute references for the cost, salvage, and life arguments in the SLN formula.

c. Fill the range C23:K23 with the formula in cell B23 without formatting to calculate the annual and cumulative straight-line depreciation in Years 2–10.

27. Calculate the declining balance depreciation amounts for the new office space as follows:

a. In cell B29, enter a formula using the DB function to calculate the declining balance depreciation for the office space during the first year of operation.

b. Use Year 1 (cell B28) as the current period.

c. Use absolute references only for the cost, salvage, and life arguments in the DB formula.

d. Fill the range C29:K29 with the formula in cell B29 without formatting to calculate the annual and cumulative declining balance depreciation in Years 2–10.

28. Viraj also wants you to determine the depreciation balance for the first year and the last year of the useful life of the office space assets. Determine these amounts as follows:

a. In cell B34, enter a formula using the SYD function to calculate the depreciation balance for the first year.

b. Use Year 1 (cell B28) as the current period.

c. In cell B35, enter a formula using the SYD function to calculate the depreciation balance for the last year.

d. Use Year 10 (cell K28) as the current period.

29. Go to the Investments worksheet. Besides using a bank loan to fund their upcoming expansion, Tahoe Healthcare Consulting is looking for investors. This worksheet should show the returns potential investors could realize if they invested a total of $500,000 in the firm. Viraj knows that a desirable rate of return would be 10 percent. He also estimates the investment would pay different amounts each year (range B6:B14). Calculate the present value of the investment and the internal rate of return as follows:

a. In cell B18, enter a formula that uses the NPV function to calculate the present value of the investment in funding the firm's expansion.

b. Use the target rate of return value (cell B17) as the rate argument.

c. Use the payments in Years 1–10 (range B5:B14) as the returns paid to investors. (Hint: If a Formula Omits Adjacent Cell error warning appears, ignore it.)

d. In cell B20, enter a formula that uses the IRR function to calculate the internal rate of return for the investment.

e. Use the payments for Years 1–10 (range B5:B14) as the returns paid to investors.

30. The Monthly Revenue Projections scatter chart in the range E3:K20 is based on monthly revenue estimates listed on the Monthly Revenue Projections worksheet. Viraj wants to include a trendline for this chart that shows investors how revenues will most likely increase quickly at first and then level off in later months. Modify the Monthly Revenue Projections scatter chart as follows to include a logarithmic trendline:

a. Add a Trendline to the Monthly Revenue Projections scatter chart.

b. Format the trendline to use the Logarithmic option.

31. Go to the Monthly Revenue Projections worksheet, which contains the source data for the Monthly Revenue Projections scatter chart. Viraj asks you to keep this data private. Hide the Monthly Revenue Projections worksheet.

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: Overview Worksheet

Windows, Access, Excel, Word, 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: Project Summary Worksheet

Final Figure 3: Projects by State Worksheet

Final Figure 4: Project Payments Worksheet

Final Figure 5: Projects by Service Worksheet

Final Figure 6: Sales per Quarter Worksheet

Final Figure 7: Consultants Worksheet

Final Figure 8: New Office Loan Worksheet

Final Figure 9: Investments Worksheet

Need help with this project?