New Perspectives Excel 2019 | Module 3: SAM Project 1a Scrub Jay Peripherals PERFORMING CALCULATIONS WITH FORMULAS AND FUNCTIONS GETTING STARTED ? Open the file NP_EX19_3a_ FirstLastName _1

Subject:MS ExcelPrice:20.87 Bought15

Share With

New Perspectives Excel 2019 | Module 3: SAM Project 1a

Scrub Jay Peripherals

PERFORMING CALCULATIONS WITH FORMULAS AND FUNCTIONS

GETTING STARTED

?

Open the file

NP_EX19_3a_

FirstLastName

_1.xlsx

, available for download from the

SAM website.

?

Save the file as

NP_EX19_3a_

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_3a_

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.

Danilo Lewellen is an entrepreneur planning to manufacture and market an innovative

handheld controller for virtual reality gaming. Danilo is building a business plan as he

recruits investors and plans logistics for the launch of his new company, Scrub Jay

Peripherals, and wants to use Excel to summarize data and create projections.

Switch to the

Sales Projections

worksheet. Use the values in the range B6:C6 to extend

the incremented percentages to the range D6:H6.

2.

Use the values in the range A7:A8 to extend the list of years to the range A9:A11.

3.

Use

AutoFill

to fill the range A9:H11 with the formatting from the range A7:H8.

4.

In cell B8, create a formula without using a function that multiplies the value in cell

B7

by the value in cell

B6

and then adds the value in

B7

. Update the cell reference to cell

B6, from a relative reference to a mixed reference by making an absolute reference to

row 6. Copy the formula from cell B8 to the range B9:B11 and then copy the formulas

from the range B8:B11 to the range C8:H11.

5.

Switch to the

Cost Estimates

worksheet. In cell A9, create a formula using the

AVERAGE

function that calculates the average of the values in the range

A5:A7

, then copy your

formula to cell D9.

6.

In cell A10, create a formula using the

MAX

function that identifies the maximum value

in the range

A5:A7

and then copy your formula to cell D10.

7.

In cell A11, create a formula using the

MIN

function that identifies the minimum value in

the range

A5:A7

and then copy your formula to cell D11.

8.

In cell B13, create a formula using the

VLOOKUP

function that looks up the value from

cell

A11

in the range

A5:B7

, returns the value in column

2

, and specifies an exact

match. Copy the formula to cell E13.

9.

Switch to the

Profit Projections

worksheet. In cell H5, use the

TODAY

function to insert

the current date.

10.

In cell H7, create a formula that uses the

NETWORKDAYS

function to calculate the

number of working days between the dates in cells

H5

and

H6

.

New Perspectives Excel 2019 | Module 3: SAM Project 1a

 

New Perspectives Excel 2019 | Module 3: SAM Project 1a

11.

Use the values in the range B12:C12 to extend the incremented percentages to the

range D12:H12.

12.

Use the values in the range A13:A14 to extend the list of years to the range A15:A17.

13.

In cell B13, create a formula without a function using absolute references that subtracts

the values of cells

B5

and

B7

from cell

B6

and then multiplies the result by cell

B8

. Copy

the formula from cell B13 to the range C13:H13.

14.

In cell B14, create a formula without using a function that adds

1

to the value in cell

B12

and then multiplies the result by the value in cell

B13

. Update the reference to cell B12,

from a relative reference to a mixed reference by making an absolute reference to row

12. Copy the formula from cell B14 to the range B15:B17 and then copy the formulas

from the range B14:B17 to the range C14:H17.

15.

Delete row 19.

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.

(Note: The value in cell H7 of the Profit Projections worksheet may differ from that shown below.)

 

New Perspectives Excel 2019 | Module 3: SAM Project 1a

Final Figure 1: Sales Projections Worksheet

Final Figure 2: Cost Estimates Worksheet

Final Figure 3: Profit Projections Worksheet