New Perspectives Excel 2019 | Module 3: SAM Project 1b
MedSight Instruments
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”.
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.
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.
o If cell B6 does not display your name, delete the file and download a new copy
from the SAM website.
PROJECT STEPS
1. 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.
2. 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.
3. Use AutoFill to fill the range C8:I10 with the formatting from the range C6:I7 to apply a
consistent format in the projections.
4. Enter formulas as follows to complete the table of projections in the range C6:I10:
a. 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.
b. Use an absolute reference to row 5 in the reference to cell C5.
c. 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.
5. 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.
6. 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.
New Perspectives Excel 2019 | Module 3: SAM Project 1b
7. 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.
8. 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.
9. 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.
10. 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.
11. Use the values in the range C14:D14 to fill the range E14:I14 with a series of
incremented percentages.
12. Use the values in the range B15:B16 to extend the list of years to the range B17:B19 for
five years of projections.
13. Estimate the gross profit for 2021 as follows:
a. 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).
b. Use absolute references to each cell in the formula.
c. Copy the formula from cell C15 to the range D15:I15 to complete the estimates for
2021.
14. Complete the growth estimates for the years 2022 to 2025 as follows:
a. 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).
b. Use an absolute row reference to cell C14.
c. Copy the formula from cell C16 to the range C17:C19 to enter the first estimates
for 2023–2025.
d. Copy the formulas from the range C16:C19 to the range D16:I19 to enter the
remaining sales projections.
15. Delete row 21 since the worksheet is now complete.
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.
