Illustrated Excel 2016 Module 5: SAM Project 1b

Old Town Art Gallery

ANALYZING DATA USING FORMULAS

GETTING STARTED

Open the file

IL_EX16_5b_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

Save the file as

IL_EX16_5b_

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

IL_EX16_5b_

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.

Richard is the membership director at the Old Town Art Gallery. He asked you to

help him analyze online and gallery ticket office sales of memberships. Richard

also needs you to look at loan options for a renovation of the gallery’s education

wing.

Switch to the

Gallery

worksheet. Assign the name

begin_date

to the range

C5:C13.

2.

In cell E5, enter a formula without using a function to determine the

membership renewal date by adding

365

to the beginning date in cell C5, using

the range name

begin_date

in the formula. Copy the formula in cell E5 into the

range E6:E13.

3.

Delete the range name “membership_number” from the workbook. (

Hint:

Do

not delete the cell contents in this range, only the custom range name.)

4.

Richard asks you to calculate the membership prices for the first quarter

memberships sold in the gallery ticket office. The individual membership price is

$50. Students pay a discounted price of $25.

In cell F5, create a formula using the

IF

function to determine the membership

price based on the following criteria:

a.

If cell

D5

(the Category) is

Student

the price is

25

.

(

Hint:

Student must

appear in quotes. Do not enter $ for the price.)

b.

All other individual members must pay

50

.

Copy the formula in cell F5 into the range F6:F13.

5.

In cell F14, create a formula using the

SUM

function to total the values in the

range

F5:F13

. Format cell F14 using

bold

formatting.

6.

Richard asks you to calculate the number of student memberships sold in the

gallery ticket office.

In cell D17, create a formula using the

COUNTIF

function to count the number

student memberships sold. Use

D5:D13

as the range and

Student

as the

criteria in your formula.

7.

Richard is curious about the average membership price paid on a monthly basis.

You will begin by looking at the average membership paid in January.

In cell D18, create a formula using the

AVERAGEIF

function to find the average

contribution prior to 2/1/2020. Use

C5:C13

as the range,

<2/1/2020

as the

criteria, and

F5:F13

as the average_range in your formula.

8.

Richard asks you to find the total amount paid for student memberships.

In cell D19, use the

SUMIF

function to total the student membership payments.

Use

D5:D13

as the range,

Student

as the criteria and

F5:F13

as the

sum_range in your formula.

9.

Format cells D18 and D19 using the

Currency

number format with

two

decimal

places and the

$

symbol.

10.

Richard would like you to calculate the membership sales for both the gallery

and online.

Switch to the

Summary

worksheet. In cell A5, create a formula using the

SUM

function and

3-D

references to total cell

F14

on the

Gallery

worksheet and cell

F14

on the

Online

worksheet.

11.

Format cell A5 using the

Accounting

number format with

two

decimal places

and the

$

symbol.

12.

Richard would like you to separate the member names from their categories.

Enter the text

Jean Hogg

in cell E5, then enter the text

Bert Marson

in cell E6,

and then select the range E5:E22 and use

Flash Fill

to fill in the range E7:E22.

Enter the text

Individual

in cell F5, then enter the text

Individual

in cell F6,

and then select the range F5:F22 and use

Flash Fill

to fill in the range F7:E22.

[Mac Hint: Flash Fill is not available in Excel 2016 for Mac, so refer to Final Figure

2 to enter the text.]

13.

The gallery is considering a renovation of the Education Wing. Richard asks you

to help with the analysis of the loan options to fund the renovation.

Switch to the

Education

Wing

worksheet, then create a formula using the

PMT

function in cell B8 to determine the monthly payment using the loan information

shown in the range B5:B7. In your formula, divide the rate (cell

B6

) by

12

, use

the term in months (cell

B7

) as the nper, and use a negative value for the pv

(cell

B5

). Copy the formula from cell B8 into the range C8:D8.

14.

In cell B9, enter a formula without using a function to calculate the total

payments by multiplying the monthly payments (cell

B8

) by the term in months

(cell

B7

) for Loan Option 1. Copy the formula from cell B9 into the range C9:D9.

15.

In cell B10, enter a formula without using a function that calculates the total

interest by subtracting the loan amount (cell

B5

) from the total payments (cell

B9

) for Loan Option 1. Copy the formula from cell B10 into the range C10:D10.

Your workbook should look like the Final Figures below. 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: Gallery Worksheet

Final Figure 2: Summary Worksheet

Final Figure 3: Education Wing Worksheet

pur-new-sol

Related Questions