Скачать или смотреть New Perspectives Excel 365 | Modules 5-8: SAM Capstone Project A VacuPRO

If you directly want to get the project from us then contact us on Whatsapp. Click on link below👇👇
https://wa.me/message/PPXGSJS2TLWUA1

Whatsapp Contact Number:
+91 8114420233

Email ID:
[email protected]

New Perspectives Excel 365 | Modules 5-8: SAM Capstone Project A VacuPRO ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS

PROJECT STEPS
1. 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. She asks you to 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 Cordless 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 vacuum cleaners.
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 Fiona wants you 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 B5, enter a formula using the SUM function and 3-D references that totals the Cordless sales values (cell B5) in Quarter 1 from the U.S., Canada, and Mexico worksheets.
b. Fill the range C5:E5 with the formula in cell B5 to total the Cordless sales for Quarters 2–4.
c. 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.
3. Fiona 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. She wants you to add a defined name for the Cannister 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 Cannister sales amounts (range B7:E7) using Cannister_Total as the name.
b. In cell F5, enter a formula using the SUM function to display the total of the sales amounts in the Cordless_Total range.
c. In cell F6, enter a formula using the SUM function to display the total of the sales amounts in the Upright_Total range.
d. In cell F7, enter a formula using the SUM function to display the total of the sales amounts in the Cannister_Total range.


4. Fiona wants to compare the sales of each product in 2027 with the sales in 2026. She has the 2026 sales data stored in a separate workbook.

She asks you to add the 2026 sales data to the All Locations worksheet as follows:
a. Open the workbook Support_EX365_CS5-8A_2026.xlsx.
b. Return to the All Locations worksheet in the original workbook.
c. In cell G5, enter a formula using an external reference to display the total sales of Cordless products in 2026 (cell F5).
d. In the formula in cell G5, change the absolute reference to a mixed reference, with a relative reference to the row number.
e. Fill the range G6:G8 with the formula in cell G5, filling without formatting.
f. Close the workbook Support_EX365_CS5-8A_2026.xlsx.
5. In the range B12:E12, Fiona wants you to display a rating depending on the total sales for each quarter. She listed the rating criteria in the range A14:F15. 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 B12, start to enter a formula using the XLOOKUP function.

6. In the range I3:L7, Fiona listed information about the managers of the VacuPRO main offices in the U.S., Canada, and Mexico. She needs you to add a link to the email address of the U.S. manager.

In cell L5, create a link to the [email protected] email address without changing the display text.
7. Fiona also wants to make it easy to access more detailed information about the managers, which she has stored in a Word document.

Create a link to a file as follows:
a. In cell I9, create a link to the Word document Support_EX365_CS5-8A_Management.docx.
8. data for March, 2028. To make it easy to refer to the data, the table has been renamed "Sales". Fiona wants to use the table data in formulas.


#capstoneassignment #samprojects #cengageassignments #NP_CS5-8a #NP_EXCEL #excel_assignments #excel_sam_projects #Modules5-8SamCapstoneProject #vacupro #new_perspectives_excel #NP_CS5-8a_FirstLastName_1.xlsx #NP_CS5-8a_FirstLastName_2.xlsx