GETTING STARTED

$$ Open the file NP$\_$EX$19\_$CS$5-8$a$\_$FirstLastName$\_1.$xlsx$,$ available for download from the SAM website.

$$ Save the file as NP$\_$EX$19\_$CS$5-8$a$\_$FirstLastName$\_2.$xlsx by changing the $1$ to a $2.$

o 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 computer:

o Support$\_$EX$19\_$CS$5-8$a$\_2020.$xlsx

o Support$\_$EX$19\_$CS$5-8$a$\_$Management.docx

$$ With the file NP$\_$EX$19\_$CS$5-8$a$\_$FirstLastName$\_2.$xlsx still open, ensure that your first and last name is displayed in cell B$6$ of the Documentation sheet.

o If cell B$6$ 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:

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

PROJECT STEPS

$1.$ 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 as follows:

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

b$.$ In cell F$5,$ insert a formula using the SUM function that totals the Mini sales amounts for Quarters $14($range B$5$:E$5).$

c$.$ Fill the range F$6$:F$7$ with the formula in cell F$5$ to display the totals for the other types of portable speakers.

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

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

b$.$ Fill the range C$5$:E$5$ with the formula in cell B$5$ to total the Mini sales for Quarters $24.$

c$.$ Fill the range B$6$:E$7$ with the formulas in the range B$5$:E$5$ to total the sales for the other products in Quarters $14.$

$3.$ 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:

a$.$ Create a defined name for the Waterproof sales amounts $($range B$7$:E$7)$ using Waterproof$\_$Total as the name.

b$.$ In cell F$5,$ enter a formula using the SUM function to display the total of the sales amounts in the Mini$\_$Total range.

c$.$ In cell F$6,$ enter a formula using the SUM function to display the total of the sales amounts in the Voice$\_$Activated$\_$Total range.

d$.$ In cell F$7,$ enter a formula using the SUM function to display the total of the sales amounts in the Waterproof$\_$Total range.

$4.$ Benicio wants to compare the sales of each product in $2021$ with the sales in $2020.$ He has the $2020$ sales data stored in a separate workbook.

Add the $2020$ sales data to the All Locations worksheet as follows:

a$.$ Open the workbook Support$\_$EX$19\_$CS$5-8$a$\_2020.$xlsx$.$

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

c$.$ In cell G$5,$ enter a formula using an external reference to display the total sales of Mini products in $2020($cell F$5).$

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

e$.$ Fill the range G$6$:G$8$ with the formula in cell G$5,$ filling without formatting.

f$.$ Close the workbook Support$\_$EX$19\_$CS$5-8$a$\_2020.$xlsx$.$

$5.$ In the range B$12$:E$12,$ Benicio wants to display a rating depending on the total sales for each quarter. He listed the rating criteria in the range A$14$:F$15.$ 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 B$12,$ start to enter a f