SAM Project Spreadsheets New Perspectives Excel 2019

User Generated

qfjnva027

Business Finance

Description

Open the file NP_EX19_CS5-8a_FirstLastName_1.xlsx, available for download from the SAM website.

  • Save the file as NP_EX19_CS5-8a_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • With the file NP_EX19_CS5-8a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • 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:

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

oSupport_EX19_CS5-8a_2020.xlsx

oSupport_EX19_CS5-8a_Management.docx

oIf cell B6 does not display your name, delete the file and download a new copy from the SAM website.

oIn 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

  • 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:
    • Group the U.S., Canada, and Mexico worksheets.
    • In cell F5, insert a formula using the SUM function that totals the Mini sales amounts for Quarters 1–4 (range B5:E5).
    • Fill the range F6:F7 with the formula in cell F5 to display the totals for the other types of portable speakers.
  • 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:
    • 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.
    • Fill the range C5:E5 with the formula in cell B5 to total the Mini sales for Quarters 2–4.
  • 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:
    • Create a defined name for the Waterproof sales amounts (range B7:E7) using Waterproof_Total as the name.
    • In cell F5, enter a formula using the SUM function to display the total of the sales amounts in the Mini_Total range.
    • In cell F6, enter a formula using the SUM function to display the total of the sales amounts in the Voice_Activated_Total range.
  • 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:
    • Open the workbook Support_EX19_CS5-8a_2020.xlsx.
    • Return to the All Locations worksheet in the original workbook.
    • In cell G5, enter a formula using an external reference to display the total sales of Mini products in 2020 (cell F5).
    • In the formula in cell G5, change the absolute reference to a mixed reference, with a relative reference to the row number.
    • Fill the range G6:G8 with the formula in cell G5, filling without formatting.
  • In the range B12:E12, Benicio wants to display a rating depending on the total sales for each quarter. He 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:
    • In cell B12, start to enter a formula using the HLOOKUP function.
    • Use the Total Q1 sales (cell B8) as the value to look up.
    • Use the Revenue Amts and Rating information (range $B$14:$F$15) as the table containing the lookup data, using absolute references to specify the range.
    • Specify that row 2 contains the value you want to return, which is the performance rating.
    • Specify an approximate match (TRUE) because the Revenue Amts represent ranges of values.
  • 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:
    • In cell I9, create a link to the Word document Support_EX19_CS5-8a_Management.docx.
    • Use Management Details as the text to display.
  • In column G, Benicio wants to indicate whether DIG Technology should send the customer a promotional offer. Customers are eligible for the offer if they purchased a Mini 2 in the U.S.
    Provide the promotional offer information for Benicio as follows:
    • In cell G5, start to enter a formula using the AND function and structured references.
    • The first condition tests whether the value in the Product column ([@Product]) equals "Mini 2", the product eligible for the promotional offer.
    • The second condition tests whether the value in the Location column ([@Location]) equals "U.S.", the location eligible for the promotional offer.
  • Benicio wants to make sure that the Sales table does not contain any duplicate records, which would make any sales analysis incorrect.
    Identify and remove duplicate records in the Sales table as follows:
    • In the range A5:A40, create a conditional formatting Highlight Cells Rule that formats Duplicate Values in Light Red Fill with Dark Red Text.
  • Benicio wants to examine the January, 2022 sales by country and channel. Create a PivotTable based on the Sales table as follows:
    • On a new worksheet, insert a PivotTable based on the data in the Sales table, and use January Pivot as the name of the worksheet.
    • Display the Channel Type values as column headings.
    • Display the Location values as row headings.
  • Format the PivotTable as follows to make it easier for Benicio and others to interpret:
    • Apply the Currency number format with 0 decimal places and the $ symbol to the Sum of Amount values.
    • Use Sales (000s) as the custom name of the Sum of Amount field.
    • In cell A4, use Country to identify the row headings.
    • In cell B3, use Channel to identify the column headings.
  • Benicio wants to isolate sales for each product. Insert a slicer as follows to filter the PivotTable:
    • Insert a slicer based on the Product field.
  • 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:
    • Insert a Stacked Column PivotChart based on the data in the PivotTable.
  • Format and filter the PivotChart as follows to meet Benicio's requests:
    • Change the colors of the PivotChart to Monochromatic Palette 6 to coordinate with the PivotTable.
  • 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:
    • Add the IFERROR function to the formula in cell B17.
  • 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:
    • For the range D4:K9, create a two-variable data table using the price per unit (cell B6) as the Row input cell.
  • Go to the Suppliers worksheet, where Benicio wants to determine whether subcontracting could reduce the cost of the new virtual assistant product.
    Run Solver to solve this problem as follows:
    • Set the objective as minimizing (Min) the total cost (cell E10).
    • Use the units produced values (range B5:D5) as the changing variable cells.
    • Adjust the number of units produced by each supplier using the following constraints:
      Set the total number of virtual assistants produced (cell E5) as greater than or equal to 11,000, DIG Technology's minimum production goal.
      Set the total cost (cell E10) to be less than or equal to 925,000, the maximum total cost DIG Technology wants to spend.
      Set the total number of virtual assistants produced by a single supplier (range B5:D5) to be less than or equal to 4000 to balance the production among the suppliers.
      Make sure the values in the range B5:D5 are integers since DIG Technology cannot sell a fraction of a product.
  • Go to the Product Mix worksheet, which calculates the profit from each model of portable speaker. Benicio wants to compare three scenarios: one with the current prices and costs, another with the prices raised $10.00, and a third that reduces the variable costs per unit by $5.00. He has already created the first two scenarios and defined names for cells and ranges.
    Create a third scenario as follows:
    • In the Scenario Manager, add a scenario using Reduced Costs as the name.
    • Accept the same changing cells (nonadjacent ranges B6:F6 and B11:F12) as the other two scenarios.
    • Reduce each variable cost per unit value (Mini1_Variable_Cost, Mini2_Variable_Cost, VoiceXP_Variable_Cost, VoiceXP10_Variable_Cost, and Waterproof_Variable_Cost) by $5.00.
  • 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:
    • Create a Scenario PivotTable report using the profit per unit sold (range B17:F17) as the result cells.
    • Remove the Filter field from the PivotTable.

d.Ungroup the worksheets and then check to confirm that all three worksheets reflect the changes you made in this step.

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.

d.In cell F7, enter a formula using the SUM function to display the total of the sales amounts in the Waterproof_Total range.

f.Close the workbook Support_EX19_CS5-8a_2020.xlsx.

f.Fill the range C12:E12 with the formula in cell B12 to enter ratings for Quarters 2–4.

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.

c.Use Access manager details as the ScreenTip text.

8.Go to the Current Sales worksheet, which contains a table listing sales data for January, 2022. Benicio wants to use the table data in formulas.
Assign the table name Sales to the table of sales data in the range A5:G40 to make it easy to refer to the data.

d.If Excel does not fill the column, fill the range G6:G40 with the formula in cell G5.

10.The Sales table is currently sorted by the values in the Sale ID column, but Benicio wants to sort the table by date, then amount to make it easier to track the data.
Apply a custom sort to the Sales table to sort it in ascending order first by the values in the Date column, and then by the values in the Amount column.

b.Delete the second duplicate record from the table. (Hint: Do not delete the row from the worksheet.)

12.Benicio might want to filter the Product Details data in the range I4:M16, so he asks you to format it as a table.
Format the range I4:M16 as a table using Light Gray, Table Style Medium 14 to match the Sales table.

d.Sum the Amount values

e.Change the PivotTable style to White, Pivot Style Medium 14 to coordinate with the tables on the Current Sales worksheet.

b.Move and resize the slicer so that it covers the range F3:G12.

b.Move and resize the PivotChart so that it covers the range A10:E25.

b.Use the slicer to display sales data for only the Mini 1 in the PivotTable and PivotChart.

b.Use "Divide total expenses by units manufactured" as the message to display in case of an error.

b.Use the units sold (cell B5) as the Column input cell.

d.Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model in the range A14:A21.

21.Run Solver again, create an Answer report, and then close the Solver Parameters dialog box.

d.Create a Scenario Summary report that summarizes the effect of the three scenarios. Use the profit per unit sold (range B17:F17) as the result cells.

c.Change the number format of the value fields to Currency with 2 decimal places and the $ symbol.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

The Answer Report 1 worksheet and Scenario Summary worksheet have intentionally not been shown.

Unformatted Attachment Preview

New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a NewSight Consulting ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS GETTING STARTED • Open the file NP_EX19_CS5-8a_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as NP_EX19_CS5-8a_FirstLastName_2.xlsx by changing the “1” to a “2”. o • • 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_EX19_CS5-8a_2020.xlsx o Support_EX19_CS5-8a_Management.docx With the file NP_EX19_CS5-8a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. 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. If cell B6 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, New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a Canada, and Mexico. The worksheets for the countries have a similar structure. Complete the worksheets as follows: 2. 3. 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. 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. 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 B7:E7) using Waterproof_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 Mini_Total range. c. In cell F6, enter a formula using the SUM function to display the total of the sales amounts in the Voice_Activated_Total range. d. In cell F7, enter a formula using the SUM function to display the total of the sales amounts in the Waterproof_Total range. New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a 4. 5. 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_EX19_CS5-8a_2020.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 Mini products in 2020 (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_EX19_CS5-8a_2020.xlsx. In the range B12:E12, Benicio wants to display a rating depending on the total sales for each quarter. He 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 HLOOKUP function. b. Use the Total Q1 sales (cell B8) as the value to look up. c. Use the Revenue Amts and Rating information (range $B$14:$F$15) as the table containing the lookup data, using absolute references to specify the range. d. Specify that row 2 contains the value you want to return, which is the performance rating. e. Specify an approximate match (TRUE) because the Revenue Amts represent ranges of values. f. Fill the range C12:E12 with the formula in cell B12 to enter ratings for Quarters 2– 4. 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. a. In cell I9, create a link to the Word document Support_EX19_CS58a_Management.docx. b. Use Management Details as the text to display. c. Use Access manager details as the ScreenTip text. Go to the Current Sales worksheet, which contains a table listing sales data for January, 2022. Benicio wants to use the table data in formulas. Assign the table name Sales to the table of sales data in the range A5:G40 to make it easy to refer to the data. New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a 9. In column G, Benicio wants to indicate whether DIG Technology should send the customer a promotional offer. Customers are eligible for the offer if they purchased a Mini 2 in the U.S. Provide the promotional offer information for Benicio as follows: a. In cell G5, start to enter a formula using the AND function and structured references. b. The first condition tests whether the value in the Product column ([@Product]) equals "Mini 2", the product eligible for the promotional offer. c. The second condition tests whether the value in the Location column ([@Location]) equals "U.S.", the location eligible for the promotional offer. d. If Excel does not fill the column, fill the range G6:G40 with the formula in cell G5. 10. The Sales table is currently sorted by the values in the Sale ID column, but Benicio wants to sort the table by date, then amount to make it easier to track the data. Apply a custom sort to the Sales table to sort it in ascending order first by the values in the Date column, and then by the values in the Amount column. 11. Benicio wants to make sure that the Sales table does not contain any duplicate records, which would make any sales analysis incorrect. Identify and remove duplicate records in the Sales table as follows: a. In the range A5:A40, create a conditional formatting Highlight Cells Rule that formats Duplicate Values in Light Red Fill with Dark Red Text. b. Delete the second duplicate record from the table. (Hint: Do not delete the row from the worksheet.) 12. Benicio might want to filter the Product Details data in the range I4:M16, so he asks you to format it as a table. Format the range I4:M16 as a table using Light Gray, Table Style Medium 14 to match the Sales table. 13. Benicio wants to examine the January, 2022 sales by country and channel. Create a PivotTable based on the Sales table as follows: 14. a. On a new worksheet, insert a PivotTable based on the data in the Sales table, and use January Pivot as the name of the worksheet. b. Display the Channel Type values as column headings. c. Display the Location values as row headings. d. Sum the Amount values Format the PivotTable as follows to make it easier for Benicio and others to interpret: a. Apply the Currency number format with 0 decimal places and the $ symbol to the Sum of Amount values. b. Use Sales (000s) as the custom name of the Sum of Amount field. c. In cell A4, use Country to identify the row headings. d. In cell B3, use Channel to identify the column headings. e. Change the PivotTable style to White, Pivot Style Medium 14 to coordinate with the tables on the Current Sales worksheet. New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a 15. 16. 17. 18. 19. 20. Benicio wants to isolate sales for each product. Insert a slicer as follows to filter the PivotTable: a. Insert a slicer based on the Product field. b. Move and resize the slicer so that it covers the range F3:G12. 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: a. Insert a Stacked Column PivotChart based on the data in the PivotTable. b. Move and resize the PivotChart so that it covers the range A10:E25. 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. 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. 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. Go to the Suppliers worksheet, where Benicio wants to determine whether subcontracting could reduce the cost of the new virtual assistant product. Run Solver to solve this problem as follows: a. Set the objective as minimizing (Min) the total cost (cell E10). b. Use the units produced values (range B5:D5) as the changing variable cells. c. Adjust the number of units produced by each supplier using the following constraints: · Set the total number of virtual assistants produced (cell E5) as greater than or equal to 11,000, DIG Technology's minimum production goal. · Set the total cost (cell E10) to be less than or equal to 925,000, the maximum total cost DIG Technology wants to spend. · Set the total number of virtual assistants produced by a single supplier (range B5:D5) to be less than or equal to 4000 to balance the production among the New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a suppliers. · Make sure the values in the range B5:D5 are integers since DIG Technology cannot sell a fraction of a product. d. Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model in the range A14:A21. 21. Run Solver again, create an Answer report, and then close the Solver Parameters dialog box. 22. Go to the Product Mix worksheet, which calculates the profit from each model of portable speaker. Benicio wants to compare three scenarios: one with the current prices and costs, another with the prices raised $10.00, and a third that reduces the variable costs per unit by $5.00. He has already created the first two scenarios and defined names for cells and ranges. Create a third scenario as follows: 23. a. In the Scenario Manager, add a scenario using Reduced Costs as the name. b. Accept the same changing cells (nonadjacent ranges B6:F6 and B11:F12) as the other two scenarios. c. Reduce each variable cost per unit value (Mini1_Variable_Cost, Mini2_Variable_Cost, VoiceXP_Variable_Cost, VoiceXP10_Variable_Cost, and Waterproof_Variable_Cost) by $5.00. d. Create a Scenario Summary report that summarizes the effect of the three scenarios. Use the profit per unit sold (range B17:F17) as the result cells. 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: a. Create a Scenario PivotTable report using the profit per unit sold (range B17:F17) as the result cells. b. Remove the Filter field from the PivotTable. c. Change the number format of the value fields to Currency with 2 decimal places and the $ symbol. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. The Answer Report 1 worksheet and Scenario Summary worksheet have intentionally not been shown. New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a Final Figure 1: U.S. Worksheet New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a Final Figure 2: Canada Worksheet New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a Final Figure 3: Mexico Worksheet Final Figure 4: All Locations Worksheet New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a Final Figure 5: January Pivot Worksheet New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a Final Figure 6: Current Sales Worksheet Final Figure 7: Sales Projections Worksheet New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a Final Figure 8: Suppliers Worksheet New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a Final Figure 9: Scenario PivotTable Worksheet Final Figure 10: Product Mix Worksheet Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a NewSight Consulting ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS Desiree Swain Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. f the file from 2021 Sales: United States Type Q1 Q2 Q3 Mini $ 1,021 $ 1,025 $ 1,076 $ Voice-activated 987 990 1,015 Waterproof 815 826 834 Total $ 2,823 $ 2,841 $ 2,925 $ in $000 Q4 1,027 1,016 859 2,902 Total $ - Type Q1 Mini $ 604 Voice-activated 497 Waterproof 429 Total $ 1,530 in $000 2021 Sales: Canada Q2 Q3 $ 609 $ 601 495 502 423 431 $ 1,527 $ 1,534 Q4 $ 602 523 435 $ 1,560 Total $ - Type Q1 Mini $ 741 Voice-activated 328 Waterproof 504 Total $ 1,573 in $000 2021 Sales: Mexico Q2 Q3 $ 740 $ 744 312 327 506 499 $ 1,558 $ 1,570 Q4 $ 750 324 503 $ 1,577 Total $ - Type Mini Voice-activated Waterproof Total $ 2021 Sales: All Locations Q2 Q3 Q4 Q1 - $ - $ $ 5,800 Fair $ - $ Total - $ - in $000 Performance Revenue Amts $ 5,700 Rating Poor 5,900 Good $ 6,000 $ 6,100 Very good Fantastic 2020 Total Managers Country Name Location Email Address U.S. Todd Fenton Knoxville tfenton@example.com Canada Natalie Pierre Windsor npierre@example.com Mexico Vicente MedinaJuarez vmedina@example.com Management Details ` Sale ID DW-1033 DW-1039 DW-1040 DW-1049 DW-1053 DW-1059 DW-1061 DW-1062 DW-1065 DW-1066 DW-1070 DW-1071 OW-1041 OW-1047 OW-1102 OW-1102 OW-1104 OW-1108 OW-1123 OW-1126 OW-1130 OW-1132 OW-1134 OW-1139 OW-1141 OW-1144 OW-1145 OW-1148 OW-1150 OW-1155 ST-1134 ST-1142 ST-1148 ST-1151 ST-1152 ST-1153 Channel Type DIG website DIG website DIG website DIG website DIG website DIG website DIG website DIG website DIG website DIG website DIG website DIG website Other website Other website Other website Other website Other website Other website Other website Other website Other website Other website Other website Other website Other website Other website Other website Other website Other website Other website Store Store Store Store Store Store Monthly Sales: January 2022 Date Product Location 1/3/2022 Mini 2 U.S. 1/4/2022 Mini 1 Canada 1/4/2022 Voice XP10 U.S. 1/7/2022 Mini 2 U.S. 1/8/2022 Mini 1 U.S. 1/13/2022 Mini 1 U.S. 1/18/2022 Mini 2 U.S. 1/22/2022 Waterproof Mexico 1/26/2022 Voice XP Canada 1/30/2022 Waterproof U.S. 1/6/2022 Voice XP10 Canada 1/17/2022 Mini 2 U.S. 1/3/2022 Voice XP U.S. 1/5/2022 Waterproof Mexico 1/10/2022 Voice XP Mexico 1/10/2022 Voice XP Mexico 1/11/2022 Mini 2 U.S. 1/12/2022 Waterproof U.S. 1/15/2022 Voice XP U.S. 1/16/2022 Voice XP10 U.S. 1/17/2022 Mini 2 U.S. 1/19/2022 Voice XP Canada 1/20/2022 Mini 1 U.S. 1/21/2022 Mini 1 U.S. 1/25/2022 Mini 2 U.S. 1/27/2022 Mini 1 U.S. 1/28/2022 Mini 2 U.S. 1/29/2022 Voice XP U.S. 1/12/2022 Mini 1 U.S. 1/20/2022 Mini 1 U.S. 1/5/2022 Mini 1 Mexico 1/7/2022 Voice XP Canada 1/14/2022 Mini 1 Canada 1/23/2022 Voice XP10 U.S. 1/24/2022 Mini 2 U.S. 1/31/2022 Mini 2 U.S. Amount 129.99 119.99 145.95 129.99 119.99 119.99 129.99 135.95 139.99 135.95 145.95 129.99 139.99 135.95 139.99 139.99 129.99 135.95 139.99 145.95 129.99 139.99 119.99 119.99 129.99 119.99 129.99 139.99 119.99 119.99 119.99 139.99 119.99 145.95 129.99 129.99 Promotion? Product ID Name M2016 Mini 1 M2018 Mini 1 M2020 Mini 1 M2205 Mini 2 M2210 Mini 2 M2215 Mini 2 V3010 Voice XP V3012 Voice XP V3220 Voice XP 10 V3225 Voice XP 10 W4150 Waterproof W4155 Waterproof Product Details Color No. of Speakers Retail Price Black 1 119.99 Silver 1 119.99 White 1 119.99 Black 2 129.99 Silver 2 129.99 White 2 129.99 Black 2 139.99 White 2 139.99 Black 3 145.95 White 3 145.95 Black 2 135.95 White 2 135.95 New Product: Virtual Assistant Sales Units sold 22,000 Price per unit $ 159.99 Total sales $ 3,519,780.00 Expenses Variable expenses Units manufactured 22,000 Variable cost per unit $ 135.00 Total variable costs $ 2,970,000.00 Fixed Expenses Total fixed cost $ 255,000.00 Total expenses $ 3,225,000.00 Total expense per unit sold $ 146.59 Profit Total sales Total expenses Gross profit $ 3,519,780.00 $ 3,225,000.00 $ 294,780.00 $294,780.00 18,000 20,000 22,000 24,000 26,000 $ 129.99 Virtual Assistant: Gross Pr $ 139.99 Virtual Assistant: Gross Profit Analysis $ 149.99 $ 159.99 $ 169.99 $ 179.99 $ 189.99 Virtual Assistant Suppliers Jenji ManufacturingAS&P ElectronicsHancock Global Total Units Produced 3,900 3,600 4,500 12,000 Fixed costs $ 70,500.00 $ 74,625.00 $ 84,120.00 Fixed costs per unit 18.08 20.73 18.69 Variable costs 234,975.00 218,700.00 272,250.00 Variable cost per unit 60.25 60.75 60.50 Total costs $ 305,475.00 $ 293,325.00 $ 356,370.00 $ 955,170.00 Minimum total cost model Mini 1 Mini 2 Voice XP Voice XP10 Waterproof 20,150 $ 119.99 $2,417,798.50 17,025 $ 129.99 $2,213,079.75 13,530 $ 139.99 $1,894,064.70 12,945 $ 145.95 $1,889,322.75 10,750 $ 135.95 $1,461,462.50 Cost of goods sold Units manufactured 20,150 Fixed costs $ 265,000.00 Variable cost per unit$ 95.00 Variable costs $1,914,250.00 Total costs $2,179,250.00 17,025 $ 270,000.00 $ 105.00 $1,787,625.00 $2,057,625.00 13,530 $ 279,000.00 $ 110.00 $1,488,300.00 $1,767,300.00 12,945 $ 282,000.00 $ 115.00 $1,488,675.00 $1,770,675.00 10,750 $ 280,000.00 $ 102.00 $1,096,500.00 $1,376,500.00 Revenue Units sold Price per unit Total sales Total profit $ Profit per unit sold 238,549 $ $11.84 155,455 $ $9.13 126,765 $ $9.37 118,648 $ $9.17 84,962 $7.90 2020 Sales: United States Type Q1 Q2 Q3 Q4 Mini $ 986 $ 985 $ 988 $ 982 Voice-activated 801 805 806 802 Waterproof 682 687 684 688 Total $ 2,469 $ 2,477 $ 2,478 $ 2,472 in $000 $ $ $ $ Total 3,941 3,214 2,741 9,896 Type Q1 Mini $ 584 Voice-activated 414 Waterproof 390 Total $ 1,388 in $000 2020 Sales: Canada Q2 Q3 $ 588 $ 588 409 411 390 392 $ 1,387 $ 1,391 Q4 $ 589 412 394 $ 1,395 $ $ $ $ Total 2,349 1,646 1,566 5,561 Type Q1 Mini $ 720 Voice-activated 294 Waterproof 445 Total $ 1,459 in $000 2020 Sales: Mexico Q2 Q3 $ 724 $ 722 295 297 443 448 $ 1,462 $ 1,467 Q4 $ 718 297 446 $ 1,461 $ $ $ $ Total 2,884 1,183 1,782 5,849 Type Mini $ Voice-activated $ Waterproof $ Total $ in $000 2020 Sales: All Locations Q1 Q2 Q3 2,290 $ 2,297 $ 2,298 $ 1,509 $ 1,509 $ 1,514 $ 1,517 $ 1,520 $ 1,524 $ 5,316 $ 5,326 $ 5,336 $ Q4 2,289 1,511 1,528 5,328 $ $ $ $ Total 9,174 6,043 6,089 21,306 DIG Portable Speakers National Managers United States Todd Fenton Knoxville, TN 37919 (865) 555-2771 Canada Natalie Pierre Windsor, ON N8N OA6 (519) 555-3135 Mexico Vicente Medina Partido Senecu, Ciudad Juarez, CP 32543 (656) 555-0228
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

The soluti...


Anonymous
Really helpful material, saved me a great deal of time.

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Related Tags