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

Subject:MS ExcelPrice:20.01 Bought7

Share With

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

 

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

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.

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

.

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.

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