Homework answers / question archive / New Perspectives Excel 365 | Modules 5-8: SAM Critical Thinking Capstone Project C VacuPRO ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS GETTING STARTED Save the file NP_EX365_CT_CS5-8C_FirstLastName_1

New Perspectives Excel 365 | Modules 5-8: SAM Critical Thinking Capstone Project C VacuPRO ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS GETTING STARTED Save the file NP_EX365_CT_CS5-8C_FirstLastName_1

MS Excel

Share With

---

New Perspectives Excel 365 | Modules 5-8: SAM Critical Thinking Capstone Project C

VacuPRO

ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS

GETTING STARTED

Save the file NP_EX365_CT_CS5-8C_FirstLastName_1.xlsx as NP_EX365_CT_CS5-8C_FirstLastName_2.xlsx 

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

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

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

Support_EX365_CT_CS5-8C_Management.docx

Support_EX365_CT_CS5-8C_2026.xlsx

With the file NP_EX365_CT_CS5-8C_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. 

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

· 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:

o PC - 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.

o Mac - Click Tools, and then click Excel Add-Ins. Click the Solver Add-in option to enable it. Then, click OK.

 

PROJECT STEPS

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. To complete the worksheets, group the U.S., Canada, and Mexico worksheets and then sum values: 

 In the approriate cell, insert a formula using a function that totals the Cordless sales amounts for Quarters 1–4, then fill the range.

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

Go to the All Locations worksheet and summarize and consolidate the quarterly and annual totals from the three locations for each type of product: 

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

 Fill the appropriate ranges to total the Cordless sales for Quarters 2–4, and the sales for the other products in Quarters 1–4.

In the All Locations worksheet, create a defined name for the Cannister sales amounts using Cannister_Total as the name, and then in the appropriate cells, use a function that totals to calculate annual sales for each product: Cordless, Upright, and Cannister. 

In the All Locations worksheet, compare the sales of each product in 2027 with the sales in 2026 by adding the 2026 sales data from the Support_EX365_CT_CS5-8C_2026.xlsx using a formula with an external reference in the appropriate cell to display the total sales of Cordless products in 2026. 

In the formula showing the 2026 total for Cordless products, change the absolute reference to a mixed reference, with a relative reference to the row number, fill the column without formatting, then close the workbook Support_EX365_CT_CS5-8C_2026.xlsx.

In the Performance section, use a lookup function to display a rating for each revenue amount that depends on the total sales for each quarter according to the Performance rating criteria, then fill the appropriate range to enter ratings for Quarters 2–4. Specify an exact match or next smaller item as the match mode.

In the Managers section, create a link to the kparker@cengage.com email address without changing the display text.

In the appropriate cell, create a link to the Word document Support_EX365_CT_CS5-8C_Management.docx , using Management Details as the text to display and Access manager details as the ScreenTip text.

In the Current Sales worksheet, in the Promotion? column, enter a formula using a function and structured references to indicate whether VacuPRO should send the customer a promotional offer based on whether the product is both a Cordless Stick and located in the U.S. Fill the range with the formula.

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.

To identify and remove duplicate records in the Sales table, create a conditional formatting in the Sales ID column with a Highlight Cells Rule that formats Duplicate Values in Light Red Fill with Dark Red Text, then delete the second duplicate record from the table. (Hint: Do not delete the row from the worksheet.)

Format the cells containing the product details as a table using the formatting that matches the Sales table.

Assign the table name Details to the table you just created.

To help Fiona examine the March, 2028 sales by country and channel, create a PivotTable based on the Sales table as follows:

 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.

 Display the Channel Type values as column headings.

 Display the Location values as row headings.

 Sum the Amount values.

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

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

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

 Use Country to identify the row headings.

 Use Channel to identify the column headings.

 Change the PivotTable style to a style that matches the tables on the Current Sales worksheet.

To isolate sales for each product, insert a slicer based on the Product field. Move and resize the slicer so that it covers the range F3:G12.

To help Fiona compare the sales data by country in a visual format, create a Clustered Column PivotChart based on the data in the PivotTable and format as follows:

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

 Change the colors of the PivotChart to Monochromatic Palette 1.

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

Go to the Sales Projections worksheet, which provides a profit analysis of a new robot vacuum that VacuPRO is planning to develop, and then use a function to modify the formula for Total expense per unit sold to make sure the calculation does not produce a divide by zero error, using the message "Divide total expenses by units manufactured" as the message to display in case of an error.

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. To show how the gross profit changes if the price or unit sales are different, create a two-variable data table in the range D4:K9 using the price per unit as the Row input cell and the units sold as the Column input cell to calculate gross profit as the price and unit sales change.

In the Suppliers worksheet, use Solver to determine whether subcontracting could reduce the cost of the new robot vacuum product as follows:

 Set the objective as minimizing the total cost.

 Use the units produced values by each supplier as the changing variable cells.

 Adjust the number of units produced by each supplier using the following constraints:

 · Set the total number of units produced as greater than or equal to 5300, VacuPRO's minimum production goal.

 · Set the total cost to be less than or equal to 575,000, the maximum total cost VacuPRO wants to spend.

 · Set the total number of units produced by a single supplier to be less than or equal to 2000 to balance the production among the suppliers.

 · Make sure the units produced values are integers since VacuPRO cannot sell a fraction of a product.

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

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

In the Product Mix worksheet, which calculates the profit from each model of vacuum, view the two scenarios already created: one with the current prices and costs and the second with the prices raised $10.00, and then create a third scenario as follows:

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

 Accept the same changing cells as the other two scenarios.

 Reduce each variable cost per unit value by $5.00.

 Create a Scenario Summary report that summarizes the effect of the three scenarios. Use the profit per unit sold for each model as the result cells.

In the Product Mix worksheet, use the Scenario Manager to create a Scenario PivotTable report using the profit per unit sold for each model as the result cells, and then format as follows:

 Remove the Filter field from the PivotTable

 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

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