GETTING STARTED

$$ Open the file NP$\_$EX$365\_2021\_$CT$\_$CS$5-8$c$\_$FirstLastName$\_1.$xlsx$,$ available for download.

$$ Save the file NP$\_$EX$365\_2021\_$CT$\_$CS$5-8$c$\_$FirstLastName$\_1.$xlsx as NP$\_$EX$365\_2021\_$CT$\_$CS$5-8$c$\_$FirstLastName$\_2.$xlsx

o Edit the file name by changing $1$ to $2.$

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

o Support$\_$EX$365\_2021\_$CT$\_$CS$5-8$c$\_2023.$xlsx

o Support$\_$EX$365\_2021\_$CT$\_$CS$5-8$c$\_$Management.docx

$$ With the file NP$\_$EX$365\_2021\_$CT$\_$CS$5-8$c$\_$FirstLastName$\_2.$xlsx open, ensure that your first and last name is displayed in cell B$6$ of the Documentation worksheet.

o If cell B$6$ 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 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 by using a function to total the Mini sales amounts for Q$1$ through Q$4$ in the U$.$S$.,$ Canada, and Mexico worksheets. Fill the formula down to display the remaining totals for each type.

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

Use a function to total the Mini sales values in Q$1$ from the U$.$S$.,$ Canada, and Mexico worksheets. Use the same formula to consolidate and fill the remaining product totals in the table through Q$4.$

$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 a defined name for the Waterproof sales amounts from Q$1$ through Q$4$ using Waterproof$\_$Total as the name. Use a function to display the total of the sales amounts in the Mini$\_$Total range in the Total column. Use the same function to total the Voice$\_$Activated$\_$Total range and the Waterproof$\_$Total range in the appropriate cells.

$4.$ Benicio wants to compare the sales of each product in $2024$ with the sales in $2023.$ He has the $2023$ sales data stored in a separate workbook. Add the $2023$ sales data to the All Locations worksheet.

Use a reference to pull in the total sales of Mini products in $2023$ from the Support$\_$EX$365\_2021\_$CT$\_$CS$5-8$c$\_2023.$xlsx workbook. Make sure the formula always references the same column but not the same row, and then fill the rest of the $2023$ totals with the formula, but not the formatting.

$5.$ In the appropriate cell, use a function to look up the value in the Total sales column for Q$1.$ Retrieve the performance rating from the table showing the revenue amounts and ratings. The formula should always use the exact range and find an approximate match. Use the formula to display the performance ratings for the rest of the quarters.

$6.$ Benicio listed information about the managers of the DIG Technology main offices in the U$.$S$.,$ Canada, and Mexico. He needs to add a link to the email address of the U$.$S$.$ manager.

In the appropriate cell, create a link to Todd Fenton's email address without changing the display text.

$7.$ Benicio also wants to make it easy to access more detailed information about the managers, which he has stored in a Word document.

In the cell containing the text "Management Details", create a link to the Word document Support$\_$EX$365\_2021\_$CT$\_$CS$5-8$c$\_$Management.docx without changing the display text. Use Access manager details as the ScreenTip text.

$8.$ Go to the Current Sales worksheet, which contains a table listing sales data for January, $2025.$ To make it easy to refer to the data, the table has been renamed "Sales". Benicio wants to use the table data in formulas.

In the column labeled "Promotion?", Benicio wants