ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS
Open the file NP_EX19_CS5-8a_FirstLastName_1.xlsx,
available for download from the SAM website.
Save the file as NP_EX19_CS5-8a_FirstLastName_2.xlsx
by changing the “1” to a “2”.
If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The
program will add the file extension for you automatically.
To complete this SAM Project, you will also need
to download and save the following data files from the SAM website onto your
With the file NP_EX19_CS5-8a_FirstLastName_2.xlsx
still open, ensure that your first and last name is displayed in cell B6 of the
If cell B6 does not display your name, delete
the file and download a new copy from the SAM website.
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:
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.
Benicio Cortez is a financial consultant with
NewSight Consulting in Denver, Colorado. He is working with DIG Technology, a
company that sells five models of portable speakers to consumers in North
America. In an Excel workbook, Benicio is analyzing the performance of each
speaker model and projecting sales for a new product. He asks for your help in
completing the analysis.
Go to the U.S. worksheet. Benicio has
received annual sales worksheets from the main offices in the three countries
where DIG Technology sells products: the United States, Canada, and Mexico. The
worksheets for the countries have a similar structure. Complete the worksheets
Group the U.S.,
Canada, and Mexico worksheets.
In cell F5, insert a formula using the SUM function that totals the Mini sales
amounts for Quarters 1–4 (range B5:E5).
Fill the range F6:F7 with the formula in cell F5
to display the totals for the other types of portable speakers.
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, where Benicio wants 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:
In cell B5, enter a formula using the SUM function and 3-D references that
totals the Mini sales values (cell B5)
in Quarter 1 from the U.S., Canada, and Mexico worksheets.
Fill the range C5:E5 with the formula in cell B5
to total the Mini sales for Quarters 2–4.
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.
Benicio 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. He wants you to
add a defined name for the Waterproof sales amounts and then find the total
annual sales for each product.
Create and use defined names as follows:
Create a defined name for the Waterproof sales
amounts (range B7:E7) using Waterproof_Total
as the name.
In cell F5, enter a formula using the SUM function to display the total of
the sales amounts in the Mini_Total
In cell F6, enter a formula using the SUM function to display the total of
the sales amounts in the Voice_Activated_Total
In cell F7, enter a formula using the SUM function to display the total of
the sales amounts in the Waterproof_Total