New Perspectives Excel 365 2021 | Modules 5-8 | SAM Capstone Project 1a | NP-EX365/2021-CS5-8a-1
Hellow guys, Welcome to my website, and you are watching New Perspectives Excel 365 2021 | Modules 5-8 | SAM Capstone Project 1a | NP-EX365/2021-CS5-8a-1. and this vIdeo is uploaded by Education Expert at 2024-03-21T22:46:48-07:00. We are pramote this video only for entertainment and educational perpose only. So, I hop you like our website.
Info About This Video
Name |
New Perspectives Excel 365 2021 | Modules 5-8 | SAM Capstone Project 1a | NP-EX365/2021-CS5-8a-1 |
Video Uploader |
Video From Education Expert |
Upload Date |
This Video Uploaded At 22-03-2024 05:46:48 |
Video Discription |
To Get this Solution Contact us on WhatsApp or Email
WhatsApp: +92312-8248802
Email address: mylabit65@gmail.com
WhatsApp Link : https://wa.link/hpc6ld
#NP_EX365_2021_CS5-8a
#NP_EX365_2021_CS5-8a_FirstLastName
#NP_EX365_2021_CS5-8a_FirstLastName_1
#NP_EX365_2021_CS5-8a_FirstLastName_1.xlsx
#NP_EX365_2021_CS5-8a_FirstLastName_2.xlsx
#Support_EX365_2021_CS5-8a_2023.xlsx
#Support_EX365_2021_CS5-8a_Management.docx
#NewPerspectivesExcel365/2021
#Modules5-8
#SAMCapstoneProject1a
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:
a. 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.
b. Fill the range C5:E5 with the formula in cell B5 to total the Mini 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. 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.
17. Format and filter the PivotChart as follows to meet Benicio's requests:
a. Change the colors of the PivotChart to Monochromatic Palette 6 to coordinate with the PivotTable.
b. Use the slicer to display sales data for only the Mini 1 in the PivotTable and PivotChart.
18. Go to the Sales Projections worksheet, which provides a profit analysis of a new virtual assistant product DIG Technology is planning to develop. Benicio wants to make sure the total expense per unit sold calculation in cell B17 does not produce a divide by zero error.
Modify the formula in cell B17 as follows:
a. Add the IFERROR function to the formula in cell B17.
b. Use "Divide total expenses by units manufactured" as the message to display in case of an error.
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. He wants to see how the gross profit changes if the price or unit sales are different.
Create a two-variable data table as follows to calculate gross profit as the price and unit sales change:
a. For the range D4:K9, create a two-variable data table using the price per unit (cell B6) as the Row input cell.
b. Use the units sold (cell B5) as the Column input cell. |
Category |
Education |
Tags |
#NP_EX365_2021_CS5-8a | NP_EX365_2021_CS5-8a_FirstLastName | NP_EX365_2021_CS5-8a_FirstLastName_1 | NP_EX365_2021_CS5-8a_FirstLastName_1.xlsx | NP_EX365_2021_CS5-8a_FirstLastName_2.xlsx | Support_EX365_2021_CS5-8a_2023.xlsx | Support_EX365_2021_CS5-8a_Management.docx | New Perspectives Excel 365/2021 | Modules 5-8 | SAM Capstone Project 1a | new perspectives excel 2019 module 1 sam project 1a | new perspectives word 2019 | module 10: sam project 1a | shelly cashman excel 2019 | module 1: sam project 1b |
More Videos