New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a

Please contact us if you need this project or any other project in any subject to be completed.
Whatsapp: +1-343-321-3276
Email: essayserviceusa@gmail.com
WhatsApp Link: https://wa.me/13433213276
#NewPerspectivesExcel2019 #Modules5-8SAMCapstoneProject1a
#NewSightConsulting

New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a
NewSight Consulting
ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS
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 F5, insert a formula using the SUM function that totals the Mini 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 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:
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:
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:
5. In the range B12:E12, Benicio wants to display a rating depending on the total sales for each quarter. He listed the rating:
6. In the range I3:L7, 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 cell L5, create a link to the tfenton@example.com 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.
Create a link to a file as follows:
8. Go to the Current Sales worksheet, which contains a table listing sales data for January, 2022. To make it easy to refer to the data.
9. The Sales table is currently sorted by the values in the Sale ID column, but Benicio wants to sort the table by date,
10. Benicio wants to make sure that the Sales table does not contain any duplicate records, which would make any sales analysis incorrect.
11. Benicio might want to filter the Product Details data in the range I4:M16, so he asks you to format it as a 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. Benicio wants to examine the January, 2022 sales by country and channel. Create a PivotTable based on the Sales table as follows:
14. Format the PivotTable as follows to make it easier for Benicio and others to interpret:
15. Benicio wants to isolate sales for each product. Insert a slicer as follows to filter the PivotTable:
16. Benicio also wants to compare the sales data by country in a visual format, and then display data only for the Mini 1, the company's best-selling product. Create a PivotChart as follows:
17. Format and filter the PivotChart as follows to meet Benicio's requests:
18. Go to the Sales Projections worksheet, which provides a profit analysis of a new virtual assistant product DIG Technology is planning to develop.
19. Benicio has already calculated that the gross profit for the new product could be nearly $295,000 based on a unit price of $159.99 and unit sales of 22,000.
20. Run Solver again, create an Answer report, and then close the Solver Parameters dialog box.
21. Go to the Product Mix worksheet, which calculates the profit from each model of portable speaker. Benicio wants to compare three scenarios:
22. Return to the Product Mix worksheet. Benicio wants 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: