New Perspectives Excel 365

New Perspectives Excel 365 | Modules 5-8: SAM Capstone Project A VacuPRO

ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS

September 15, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file NP_EX365_CS5-8A_FirstLastName_1.xlsx as NP_EX365_CS5-8A_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. To complete this Project, you will also need the following files:

Support_EX365_CS5-8A_2026.xlsx

Support_EX365_CS5-8A_Management.docx

3. With the file NP_EX365_CS5-8A_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. Fiona Chan is a financial analyst with VacuPRO, a company based out of Milwaukee, Wisconsin that sells five models of vacuum cleaners. In an Excel workbook, Fiona is analyzing the performance of each vacuum model and projecting sales for a new product. She asks for your help in completing the analysis. Go to the U.S. worksheet. Fiona has received annual sales worksheets from the main offices in the three countries where VacuPRO sells products: the United States, Canada, and Mexico. The worksheets for the countries have a similar structure. She asks you to complete the worksheets as follows:

a. Group the U.S., Canada, and Mexico worksheets.

b. In cell F5, insert a formula using the SUM function that totals the Cordless sales amounts for Quarters 1–4 (range B5:E5).

c. Fill the range F6:F7 with the formula in cell F5 to display the totals for the other types of vacuum cleaners.

d. Ungroup the worksheets and then check to confirm that all three worksheets reflect the changes you made in this step.

2. Go to the All Locations worksheet, where Fiona wants you to summarize the quarterly and annual totals from the three locations for each type of product. Consolidate the sales data from the three locations as follows:

a. In cell B5, enter a formula using the SUM function and 3-D references that totals the Cordless sales values (cell B5) in Quarter 1 from the U.S., Canada, and Mexico worksheets.

b. Fill the range C5:E5 with the formula in cell B5 to total the Cordless sales for Quarters 2–4.

c. Fill the range B6:E7 with the formulas in the range B5:E5 to total the sales for the other products in Quarters 1–4.

3. Fiona started to define names for cells and ranges in the All Locations worksheet to make it easy to identify the total sales for each product. She wants you to add a defined name for the Cannister sales amounts and then find the total annual sales for each product. Create and use defined names as follows:

a. Create a defined name for the Cannister sales amounts (range B7:E7) using Cannister_Total as the name.

b. In cell F5, enter a formula using the SUM function to display the total of the sales amounts in the Cordless_Total range.

c. In cell F6, enter a formula using the SUM function to display the total of the sales amounts in the Upright_Total range.

d. In cell F7, enter a formula using the SUM function to display the total of the sales amounts in the Cannister_Total range.

4. Fiona wants to compare the sales of each product in 2027 with the sales in 2026. She has the 2026 sales data stored in a separate workbook. She asks you to add the 2026 sales data to the All Locations worksheet as follows:

a. Open the workbook Support_EX365_CS5-8A_2026.xlsx.

b. Return to the All Locations worksheet in the original workbook.

c. In cell G5, enter a formula using an external reference to display the total sales of Cordless products in 2026 (cell F5).

d. In the formula in cell G5, change the absolute reference to a mixed reference, with a relative reference to the row number.

e. Fill the range G6:G8 with the formula in cell G5, filling without formatting.

f. Close the workbook Support_EX365_CS5-8A_2026.xlsx.

5. In the range B12:E12, Fiona wants you to display a rating depending on the total sales for each quarter. She listed the rating criteria in the range A14:F15. For example, if total sales in Quarter 1 are between $5900 and $5999, the Performance rating is Good. Enter the performance ratings as follows:

a. In cell B12, start to enter a formula using the XLOOKUP function.

b. Use the Total Q1 sales (cell B8) as the value to look up.

c. Use the Revenue Amts information (range $B$14:$F$14) as the lookup data, and Rating information (range $B$15:$F$15) as the return data, using absolute references to specify the range.

d. Specify an exact match or next smaller item (-1) as the match mode.

e. Fill the range C12:E12 with the formula in cell B12 to enter ratings for Quarters 2–4.

6. In the range I3:L7, Fiona listed information about the managers of the VacuPRO main offices in the U.S., Canada, and Mexico. She needs you to add a link to the email address of the U.S. manager. In cell L5, create a link to the kparker@cengage.com email address without changing the display text.

7. Fiona also wants to make it easy to access more detailed information about the managers, which she has stored in a Word document. Create a link to a file as follows:

a. In cell I9, create a link to the Word document Support_EX365_CS5-8A_Management.docx.

b. Use Management Details as the text to display.

c. Use Access manager details as the ScreenTip text.

8. Go to the Current Sales worksheet, which contains a table listing sales data for March, 2028. To make it easy to refer to the data, the table has been renamed "Sales". Fiona wants to use the table data in formulas. In column G, Fiona wants you to indicate whether VacuPRO should send the customer a promotional offer. Customers are eligible for the offer if they purchased a Cordless Stick vacuum in the U.S. Provide the promotional offer information for Fiona as follows:

a. In cell G5, start to enter a formula using the AND function and structured references.

b. The first condition tests whether the value in the Product column ([@Product]) equals "Cordless Stick", the product eligible for the promotional offer.

c. The second condition tests whether the value in the Location column ([@Location]) equals "U.S.", the location eligible for the promotional offer.

d. If Excel does not fill the column, fill the range G6:G40 with the formula in cell G5.

9. The Sales table is currently sorted by the values in the Channel Type column, but Fiona wants you to sort the table by date, then amount to make it easier to track the data. Apply a custom sort to the Sales table to sort it in ascending order first by the values in the Date column, and then by the values in the Amount column.

10. Fiona wants you to make sure that the Sales table does not contain any duplicate records, which would make any sales analysis incorrect. Identify and remove duplicate records in the Sales table as follows:

a. In the range A5:A40, create a conditional formatting Highlight Cells Rule that formats Duplicate Values in Light Red Fill with Dark Red Text.

b. Delete the second duplicate record from the table. (Hint: Do not delete the row from the worksheet.)

11. Fiona might want to filter the Product Details data in the range I4:M16, so she asks you to format it as a table. Format the range I4:M16 as a table using Light Gray, Table Style Medium 14 to match the Sales table.

12. To make it easier to refer to the data, assign the table name Details to the table in the range I4:M16 to make it easy to refer to the data. (Hint: Rename the table, not the range.)

13. Fiona wants to examine the March, 2028 sales by country and channel. She asks you to create a PivotTable based on the Sales table as follows:

a. On a new worksheet, insert a PivotTable at cell A3 based on the data in the Sales table, and use March Pivot as the name of the PivotTable and the worksheet.

b. Display the Channel Type values as column headings.

c. Display the Location values as row headings.

d. Sum the Amount values.

14. Format the PivotTable as follows to make it easier for Fiona and others to interpret:

a. Apply the Currency number format with 0 decimal places and the $ symbol to the Sum of Amount values.

b. Use Sales (000s) as the custom name of the Sum of Amount field.

c. In cell A4, use Country to identify the row headings.

d. In cell B3, use Channel to identify the column headings.

e. Change the PivotTable style to White, Pivot Style Medium 14 to coordinate with the tables on the Current Sales worksheet.

15. Fiona wants you to isolate sales for each product. Insert a slicer as follows to filter the PivotTable:

a. Insert a slicer based on the Product field.

b. Move and resize the slicer so that it covers the range F3:G12.

16. Fiona also wants you to compare the sales data by country in a visual format, and then display data only for the Cordless Handheld vacuum, the company's best-selling product. Create a PivotChart as follows:

a. Insert a Clustered Column PivotChart based on the data in the PivotTable.

b. Move and resize the PivotChart so that it covers the range A10:E25.

17. Format and filter the PivotChart as follows to meet Fiona's requests:

a. Change the colors of the PivotChart to Monochromatic Palette 1.

b. Use the slicer to display sales data for only the Cordless Handheld in the PivotTable and PivotChart.

18. Go to the Sales Projections worksheet, which provides a profit analysis of a new robot vacuum that VacuPRO is planning to develop. Fiona wants you to make sure the total expense per unit sold calculation in cell B17 does not produce a divide by zero error. Modify the formula in cell B17 as follows:

a. Add the IFERROR function to the formula in cell B17.

b. Use "Divide total expenses by units manufactured" as the message to display in case of an error.

19. Fiona has already calculated that the gross profit for the new product could be nearly $290,000 based on a unit price of $329.99 and unit sales of 11,000. She wants you to show how the gross profit changes if the price or unit sales are different. Create a two-variable data table as follows to calculate gross profit as the price and unit sales change:

a. For the range D4:K9, create a two-variable data table using the price per unit (cell B6) as the Row input cell.

b. Use the units sold (cell B5) as the Column input cell.

20. Go to the Suppliers worksheet, where Fiona wants you to determine whether subcontracting could reduce the cost of the new robot vacuum product. Run Solver to solve this problem as follows:

a. Set the objective as minimizing (Min) the total cost (cell E10).

b. Use the units produced values (range B5:D5) as the changing variable cells.

c. Adjust the number of units produced by each supplier using the following constraints: · Set the total number of robot vacuums produced (cell E5) as greater than or equal to 5300, VacuPRO's minimum production goal. · Set the total cost (cell E10) to be less than or equal to 575,000, the maximum total cost VacuPRO wants to spend. · Set the total number of robot vacuums produced by a single supplier (range B5:D5) to be less than or equal to 2000 to balance the production among the suppliers. · Make sure the values in the range B5:D5 are integers since VacuPRO cannot sell a fraction of a product.

d. Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model in the range A14:A21.

21. Run Solver again, create an Answer report, and then close the Solver Parameters dialog box.

22. Go to the Product Mix worksheet, which calculates the profit from each model of vacuum. Fiona wants you to compare three scenarios: one with the current prices and costs, another with the prices raised $10.00, and a third that reduces the variable costs per unit by $5.00. She has already created the first two scenarios and defined names for cells and ranges. Create a third scenario as follows:

a. In the Scenario Manager, add a scenario using Reduced Costs as the name.

b. Accept the same changing cells (nonadjacent ranges B6:F6 and B11:F12) as the other two scenarios.

c. Reduce each variable cost per unit value (CordlessHandheld_Variable_Cost, CordlessStick_Variable_Cost, Upright_Basic_Variable_Cost, Upright_Pro_Variable_Cost, and Cannister_Variable_Cost) by $5.00.

d. Create a Scenario Summary report that summarizes the effect of the three scenarios. Use the profit per unit sold (range B17:F17) as the result cells.

23. Return to the Product Mix worksheet. Fiona wants you to provide a visual way to compare the scenarios. Use the Scenario Manager as follows to create a PivotTable that compares the profit per unit in each scenario as follows:

a. Create a Scenario PivotTable report using the profit per unit sold (range B17:F17) as the result cells.

b. Remove the Filter field from the PivotTable.

c. Change the number format of the value fields to Currency with 2 decimal places and the $ symbol.

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.

The Answer Report 1 worksheet has intentionally not been shown.

Final Figure 1: U.S. 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: Canada Worksheet

Final Figure 3: Mexico Worksheet

Final Figure 4: All Locations Worksheet

Final Figure 5: Current Sales Worksheet

Final Figure 6: March Pivot Worksheet

Final Figure 7: Sales Projections Worksheet

Final Figure 8: Suppliers Worksheet

Final Figure 9: Scenario Summary Worksheet

Final Figure 10: Scenario PivotTable Worksheet

Final Figure 11: Product Mix Worksheet

Need help with this project?