PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS GETTING STARTED Open the file NP EX19 3b FirstLastName 1 xlsx , available for download from the SAM website Save the file as NP EX19 3b FirstLastName 2 xlsx by changing the 1 to a 2 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 With the file NP EX19 3b FirstLastName 2 xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet If cell B6 does not display your name, delete the file and download a new copy from the SAM website PROJECT STEPS Amy Zhao is a financial analyst with MedSight Instruments, a global manufacturer of medical instruments and devices The MedSight Auditory Instruments Division is planning to manufacture and market an innovative hearing aid suitable for all ages Amy is using an Excel workbook to estimate the sales, expenses, and profits of the new device Switch to the Unit Sales Projections worksheet Use the values in the range C5 D5 to fill the range E5 I5 with a series of incremented percentages Use the values and formatting in the range B6 B7 to extend the list of years to the range B8 B10 and project five years of growth Use AutoFill to fill the range C8 I10 with the formatting from the range C6 I7 to apply a consistent format in the projections Enter formulas as follows to complete the table of projections in the range C6 I10 In cell C7, create a formula without using a function that multiplies the sales projection for the first year (cell C6 ) by the first growth rate (cell C5 ), and adds the sales projection for the first year (cell C6 ) to the result to determine the first estimate for 2022 Use an absolute reference to row 5 in the reference to cell C5 Copy the formula from cell C7 to the range C8 C10, and then copy the formulas from the range C7 C10 to the range D7 I10 to complete the table of projections Switch to the Cost Analysis worksheet, which analyzes the vendor and shipping costs per unit to select a vendor and shipper for the new product In cell B10, find the average vendor cost per unit by creating a formula using the AVERAGE function to calculate the average of the values in the range B5 B8 Copy the formula to cell E10 to find the average shipping cost per unit In cell B11, find the highest vendor cost per unit by creating a formula using the MAX function to identify the maximum value in the range B5 B8 Copy the formula to cell E11 to find the highest shipping cost per unit In cell B12, find the lowest vendor cost per unit by creating a formula using the MIN function to identify the minimum value in the range B5 B8 Copy the formula to cell E12 to find the lowest shipping cost per unit In cell C14, identify the vendor with the lowest cost per unit by creating a formula using the VLOOKUP function to look up the value from cell B12 (the lowest vendor cost) in the range B5 C8 (the vendor cost per unit table), return the name of the vendor (column 2 ), and specify an exact match Copy the formula to cell F14 to identify the shipper with the lowest cost per unit Switch to the Profit Projections worksheet, which sets a schedule for developing and launching the new product and projects its profits for five years In cell I7, use the TODAY function to insert the current date In cell I9, create a formula that uses the NETWORKDAYS function to calculate the number of working days between the dates in cells I7 and I8 Use the values in the range C14 D14 to fill the range E14 I14 with a series of incremented percentages Use the values in the range B15 B16 to extend the list of years to the range B17 B19 for five years of projections Estimate the gross profit for 2021 as follows In cell C15, create a formula without using a function that subtracts the vendor cost (cell C7 ) and the shipping cost (cell C9 ) from the sales price (cell C8 ), and then multiplies the result by the projected number of units for sale in 2021 (cell C10 ) Use absolute references to each cell in the formula Copy the formula from cell C15 to the range D15 I15 to complete the estimates for 2021 Complete the growth estimates for the years 2022 to 2025 as follows In cell C16, create a formula that adds 1 to the growth rate (cell C14 ) and multiplies the result by the estimated profit from 2021 (cell C15 ) Use an absolute row reference to cell C14 Copy the formula from cell C16 to the range C17 C19 to enter the first estimates for 20232025 Copy the formulas from the range C16 C19 to the range D16 I19 to enter the remaining sales projections Delete row 21 since the worksheet is now complete Your workbook should look like the Final Figures on the following pages The values in cells I7 and I9 of the Profit Projections worksheet have been intentionally blurred as they will never be constant Save your changes, close the workbook, and then exit Excel Follow the directions on the SAM website to submit your completed project New Perspectives Excel 2019 Module 3 SAM Project 1b Final Figure 1 Unit Sales Projections Worksheet E H 1 N 3 4 8 5 6 7 8 9 MedSight Auditory Instruments Unit Sales Projections Five year growth estimates 4 12 16 20 10 000 10 000 10 000 10 000 10 000 10,400 10 800 11 200 11,600 12,000 10 816 11 664 12 544 13 456 14,400 11 249 12 597 14,049 15,609 17 280 11 699 13 605 15,735 18 106 20 736 2021 2022 2023 2024 2025 24 10 000 12,400 15 376 19 066 23,642 28 10,000 12 800 16,384 20 972 26,844 10 11 12 13 14 15 16 17 18 19 Documentation Unit Sales Projections Cost Analysis Profit Projections New Perspectives Excel 2019 Module 3 SAM Project lb Final Figure 2 Cost Analysis Worksheet B D E F G 1 2 3 MedSight Auditory Instruments Cost Analysis Vendor Cost Per Unit Shipping Cost Per Unit 47 48 InStar Global $ 8 12 COM Shipping 52 12 Diamonte Manufacturing $ 8 22 SafeCargo 45 98 Laser BioMedical $ 7 98 Llu Chen Logistics 51 36 Tanaka Innovations 8 43 Johansen Maritime A A A A $ 5 6 7 8 9 10 11 12 13 $ $ $ 49 24 Average cost 52 12 Highest cost 45 98 Lowest cost $ $ $ 8 19 Average cost 8 43 Highest cost 7 98 Lowest cost Selected vendor Laser BioMedical Selected shipper Liu Chen Logistics 14 15 16 17 18 19 Documentation Unit Sales Projections Cost Analysis Profit Projections Final Figure 3 Profit Projections Worksheet D 1 2 3 4 MedSight Auditory Instruments Profit Projections Five verestimates 5 Unit Projection Vendor cost Set price $ Shipping cost $ 2021 souris Schedule Current dole Product launch Factory days to market 45 98 125 00 799 10 000 12 1 2021 6 7 8 2 10 11 12 13 14 15 16 17 18 19 20 2021 2022 $ 2023 $ 20245 2025$ 710 000 $ 738 816 5 76 39 $ 799 100 831068 $ Gross Profit From Sales Projections Growth estimates 8 12 16 710 400 5 710 400 S 710 400 767,232 $ 795648 5 824 064 $ 628 611 5 891 126 955914 5 894 099 $ 998 0615 1 106 8615 966,491 $ 1 117828 1 286 278 $ 20 710 400 $ 852460 5 1022 976 5 1 220 5715 1 473 085 5 24 710 400 $ 860 896 5 1 0923115 1 354 466 $ 1 679,5375 26 710 00 909 312 2 163 919 1 489 817 1 906 965 Documentation Unt Sales Pron Cast Analysis Profil Projections New Perspectives Excel 2019 Module 3 SAM Project 1b Final Figure 1 Unit Sales Projections Worksheet E H 1 N 3 4 8 5 6 7 8 9 MedSight Auditory Instruments Unit Sales Projections Five year growth estimates 4 12 16 20 10 000 10 000 10 000 10 000 10 000 10,400 10 800 11 200 11,600 12,000 10 816 11 664 12 544 13 456 14,400 11 249 12 597 14,049 15,609 17 280 11 699 13 605 15,735 18 106 20 736 2021 2022 2023 2024 2025 24 10 000 12,400 15 376 19 066 23,642 28 10,000 12 800 16,384 20 972 26,844 10 11 12 13 14 15 16 17 18 19 Documentation Unit Sales Projections Cost Analysis Profit Projections New Perspectives Excel 2019 Module 3 SAM Project lb Final Figure 2 Cost Analysis Worksheet B D E F G 1 2 3 MedSight Auditory Instruments Cost Analysis Vendor Cost Per Unit Shipping Cost Per Unit 47 48 InStar Global $ 8 12 COM Shipping 52 12 Diamonte Manufacturing $ 8 22 SafeCargo 45 98 Laser BioMedical $ 7 98 Llu Chen Logistics 51 36 Tanaka Innovations 8 43 Johansen Maritime A A A A $ 5 6 7 8 9 10 11 12 13 $ $ $ 49 24 Average cost 52 12 Highest cost 45 98 Lowest cost $ $ $ 8 19 Average cost 8 43 Highest cost 7 98 Lowest cost Selected vendor Laser BioMedical Selected shipper Liu Chen Logistics 14 15 16 17 18 19 Documentation Unit Sales Projections Cost Analysis Profit Projections Final Figure 3 Profit Projections Worksheet D 1 2 3 4 MedSight Auditory Instruments Profit Projections Five verestimates 5 Unit Projection Vendor cost Set price $ Shipping cost $ 2021 souris Schedule Current dole Product launch Factory days to market 45 98 125 00 799 10 000 12 1 2021 6 7 8 2 10 11 12 13 14 15 16 17 18 19 20 2021 2022 $ 2023 $ 20245 2025$ 710 000 $ 738 816 5 76 39 $ 799 100 831068 $ Gross Profit From Sales Projections Growth estimates 8 12 16 710 400 5 710 400 S 710 400 767,232 $ 795648 5 824 064 $ 628 611 5 891 126 955914 5 894 099 $ 998 0615 1 106 8615 966,491 $ 1 117828 1 286 278 $ 20 710 400 $ 852460 5 1022 976 5 1 220 5715 1 473 085 5 24 710 400 $ 860 896 5 1 0923115 1 354 466 $ 1 679,5375 26 710 00 909 312 2 163 919 1 489 817 1 906 965 Documentation Unt Sales Pron Cast Analysis Profil Projections

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 07, 2024

PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS GETTING STARTED Open the file NP_EX19_3b_ FirstLastName _1.xlsx , available for download from the SAM website. Save the file

PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS

GETTING STARTED

Open the file NP_EX19_3b_FirstLastName_1.xlsx, available for download from the SAM website.
Save the file as NP_EX19_3b_FirstLastName_2.xlsx by changing the 1 to a 2.
- 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.
With the file NP_EX19_3b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
- If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

Amy Zhao is a financial analyst with MedSight Instruments, a global manufacturer of medical instruments and devices. The MedSight Auditory Instruments Division is planning to manufacture and market an innovative hearing aid suitable for all ages. Amy is using an Excel workbook to estimate the sales, expenses, and profits of the new device. Switch to the Unit Sales Projections worksheet. Use the values in the range C5:D5 to fill the range E5:I5 with a series of incremented percentages.
Use the values and formatting in the range B6:B7 to extend the list of years to the range B8:B10 and project five years of growth.
Use AutoFill to fill the range C8:I10 with the formatting from the range C6:I7 to apply a consistent format in the projections.
Enter formulas as follows to complete the table of projections in the range C6:I10:
1. In cell C7, create a formula without using a function that multiplies the sales projection for the first year (cell C6) by the first growth rate (cell C5), and adds the sales projection for the first year (cell C6) to the result to determine the first estimate for 2022.
2. Use an absolute reference to row 5 in the reference to cell C5.
3. Copy the formula from cell C7 to the range C8:C10, and then copy the formulas from the range C7:C10 to the range D7:I10 to complete the table of projections.
Switch to the Cost Analysis worksheet, which analyzes the vendor and shipping costs per unit to select a vendor and shipper for the new product. In cell B10, find the average vendor cost per unit by creating a formula using the AVERAGE function to calculate the average of the values in the range B5:B8. Copy the formula to cell E10 to find the average shipping cost per unit.
In cell B11, find the highest vendor cost per unit by creating a formula using the MAX function to identify the maximum value in the range B5:B8. Copy the formula to cell E11 to find the highest shipping cost per unit.
In cell B12, find the lowest vendor cost per unit by creating a formula using the MIN function to identify the minimum value in the range B5:B8. Copy the formula to cell E12 to find the lowest shipping cost per unit.
In cell C14, identify the vendor with the lowest cost per unit by creating a formula using the VLOOKUP function to look up the value from cell B12 (the lowest vendor cost) in the range B5:C8 (the vendor cost per unit table), return the name of the vendor (column 2), and specify an exact match. Copy the formula to cell F14 to identify the shipper with the lowest cost per unit.
Switch to the Profit Projections worksheet, which sets a schedule for developing and launching the new product and projects its profits for five years. In cell I7, use the TODAY function to insert the current date.
In cell I9, create a formula that uses the NETWORKDAYS function to calculate the number of working days between the dates in cells I7 and I8.
Use the values in the range C14:D14 to fill the range E14:I14 with a series of incremented percentages.
Use the values in the range B15:B16 to extend the list of years to the range B17:B19 for five years of projections.
Estimate the gross profit for 2021 as follows:
1. In cell C15, create a formula without using a function that subtracts the vendor cost (cell C7) and the shipping cost (cell C9) from the sales price (cell C8), and then multiplies the result by the projected number of units for sale in 2021 (cell C10).
2. Use absolute references to each cell in the formula.
3. Copy the formula from cell C15 to the range D15:I15 to complete the estimates for 2021.
Complete the growth estimates for the years 2022 to 2025 as follows:
1. In cell C16, create a formula that adds 1 to the growth rate (cell C14) and multiplies the result by the estimated profit from 2021 (cell C15).
2. Use an absolute row reference to cell C14.
3. Copy the formula from cell C16 to the range C17:C19 to enter the first estimates for 20232025.
4. Copy the formulas from the range C16:C19 to the range D16:I19 to enter the remaining sales projections.
Delete row 21 since the worksheet is now complete.

Your workbook should look like the Final Figures on the following pages. The values in cells I7 and I9 of the Profit Projections worksheet have been intentionally blurred as they will never be constant. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

image text in transcribed

New Perspectives Excel 2019 | Module 3: SAM Project 1b Final Figure 1: Unit Sales Projections Worksheet E H 1 N 3 4 8% 5 6 7 8 9 MedSight Auditory Instruments Unit Sales Projections Five-year growth estimates 4% 12% 16% 20% 10.000 10.000 10.000 10.000 10.000 10,400 10.800 11.200 11,600 12,000 10.816 11.664 12.544 13.456 14,400 11.249 12.597 14,049 15,609 17.280 11.699 13.605 15,735 18.106 20.736 2021 2022 2023 2024 2025 24% 10.000 12,400 15.376 19.066 23,642 28% 10,000 12.800 16,384 20.972 26,844 10 11 12 13 14 15 16 17 18 19 Documentation Unit Sales Projections Cost Analysis Profit Projections + New Perspectives Excel 2019 Module 3: SAM Project lb Final Figure 2: Cost Analysis Worksheet B D E F G 1 2 3 MedSight Auditory Instruments Cost Analysis Vendor Cost Per Unit Shipping Cost Per Unit 47.48 InStar Global $ 8.12 COM Shipping 52.12 Diamonte Manufacturing $ 8.22 SafeCargo 45.98 Laser BioMedical $ 7.98 Llu-Chen Logistics 51.36 Tanaka Innovations 8.43 Johansen Maritime A A A A $ 5 6 7 8 9 10 11 12 13 $ $ $ 49.24 Average cost 52.12 Highest cost 45.98 Lowest cost $ $ $ 8.19 Average cost 8.43 Highest cost 7.98 Lowest cost Selected vendor Laser BioMedical Selected shipper Liu-Chen Logistics 14 15 16 17 18 19 Documentation Unit Sales Projections Cost Analysis Profit Projections Final Figure 3: Profit Projections Worksheet D 1 2 3 4 MedSight Auditory Instruments Profit Projections Five-verestimates 5 Unit Projection Vendor cost Set price $ Shipping cost $ 2021 souris Schedule Current dole Product launch Factory days to market 45.98 125.00 799 10.000 12/1/2021 6 7 8 2 10 11 12 13 14 15 16 17 18 19 20 2021 % 2022 $ 2023 $ 20245 2025$ 710.000 $ 738 816 5 76.39 $ 799.100 831068 $ Gross Profit From Sales Projections Growth estimates 8% 12% 16% 710.400 5 710.400 S 710.400 767,232 $ 795648 5 824.064 $ 628.611 5 891.126 955914 5 894.099 $ 998.0615 1.106.8615 966,491 $ 1.117828 1.286.278 $ 20% 710.400 $ 852460 5 1022.976 5 1.220.5715 1.473.085 5 24% 710.400 $ 860.896 5 1.0923115 1.354.466 $ 1.679,5375 26% 710.00 909 312 2.163.919 1.489 817 1.906.965 Documentation Unt Sales Pron Cast Analysis Profil Projections . New Perspectives Excel 2019 | Module 3: SAM Project 1b Final Figure 1: Unit Sales Projections Worksheet E H 1 N 3 4 8% 5 6 7 8 9 MedSight Auditory Instruments Unit Sales Projections Five-year growth estimates 4% 12% 16% 20% 10.000 10.000 10.000 10.000 10.000 10,400 10.800 11.200 11,600 12,000 10.816 11.664 12.544 13.456 14,400 11.249 12.597 14,049 15,609 17.280 11.699 13.605 15,735 18.106 20.736 2021 2022 2023 2024 2025 24% 10.000 12,400 15.376 19.066 23,642 28% 10,000 12.800 16,384 20.972 26,844 10 11 12 13 14 15 16 17 18 19 Documentation Unit Sales Projections Cost Analysis Profit Projections + New Perspectives Excel 2019 Module 3: SAM Project lb Final Figure 2: Cost Analysis Worksheet B D E F G 1 2 3 MedSight Auditory Instruments Cost Analysis Vendor Cost Per Unit Shipping Cost Per Unit 47.48 InStar Global $ 8.12 COM Shipping 52.12 Diamonte Manufacturing $ 8.22 SafeCargo 45.98 Laser BioMedical $ 7.98 Llu-Chen Logistics 51.36 Tanaka Innovations 8.43 Johansen Maritime A A A A $ 5 6 7 8 9 10 11 12 13 $ $ $ 49.24 Average cost 52.12 Highest cost 45.98 Lowest cost $ $ $ 8.19 Average cost 8.43 Highest cost 7.98 Lowest cost Selected vendor Laser BioMedical Selected shipper Liu-Chen Logistics 14 15 16 17 18 19 Documentation Unit Sales Projections Cost Analysis Profit Projections Final Figure 3: Profit Projections Worksheet D 1 2 3 4 MedSight Auditory Instruments Profit Projections Five-verestimates 5 Unit Projection Vendor cost Set price $ Shipping cost $ 2021 souris Schedule Current dole Product launch Factory days to market 45.98 125.00 799 10.000 12/1/2021 6 7 8 2 10 11 12 13 14 15 16 17 18 19 20 2021 % 2022 $ 2023 $ 20245 2025$ 710.000 $ 738 816 5 76.39 $ 799.100 831068 $ Gross Profit From Sales Projections Growth estimates 8% 12% 16% 710.400 5 710.400 S 710.400 767,232 $ 795648 5 824.064 $ 628.611 5 891.126 955914 5 894.099 $ 998.0615 1.106.8615 966,491 $ 1.117828 1.286.278 $ 20% 710.400 $ 852460 5 1022.976 5 1.220.5715 1.473.085 5 24% 710.400 $ 860.896 5 1.0923115 1.354.466 $ 1.679,5375 26% 710.00 909 312 2.163.919 1.489 817 1.906.965 Documentation Unt Sales Pron Cast Analysis Profil Projections