Module 3- SAM Assignment 1B
New Perspectives Excel 2019 | Module 3: SAM Project 1b MedSight Instruments New Perspectives Excel 2019 | Module 3: SAM Project 1b 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. Leave the Holidays argument blank. 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 2023–2025. 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. Final Figure 1: Unit Sales Projections Worksheet Final Figure 2: Cost Analysis Worksheet Final Figure 3: Profit Projections Worksheet 2 Documentation New Perspectives Excel 2019 | Module 3: SAM Project 1b MedSight Instruments PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS Author:Shahmir Yahya 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. Unit Sales Projections MedSight Auditory Instruments Unit Sales Projections Five-year growth estimates 4%8% 202110,00010,00010,00010,00010,00010,00010,000 2022 Cost Analysis MedSight Auditory Instruments Cost Analysis Vendor Cost Per UnitShipping Cost Per Unit $ 47.48InStar Global$ 8.12COM Shipping $ 52.12Diamonte Manufacturing$ 8.22SafeCargo $ 45.98Laser BioMedical$ 7.98Liu-Chen Logistics $ 51.36Tanaka Innovations$ 8.43Johansen Maritime Average costAverage cost Highest costHighest cost Lowest costLowest cost Selected vendorSelected shipper Profit Projections MedSight Auditory Instruments Profit Projections Five-year estimates Unit ProjectionsSchedule Vendor cost$ 45.98Current date Sales price$ 125.00Product launch12/1/21 Shipping cost$ 7.98Factory days to market 2021 sales units10,000 Gross Profit From Sales Projections Growth estimates 4%8% 2021 2022 Incomplete worksheet