Homework answers / question archive / Illustrated Excel 2016 | Modules 5–8: SAM Capstone Project 1a Paterson Arts Center MANAGING FORMULAS, DATA, AND TABLES GETTING STARTED   Open the file IL_EX16_CS5-8a_ FirstLastName _1

Illustrated Excel 2016 | Modules 5–8: SAM Capstone Project 1a Paterson Arts Center MANAGING FORMULAS, DATA, AND TABLES GETTING STARTED   Open the file IL_EX16_CS5-8a_ FirstLastName _1

MS Excel

Share With

---

Illustrated Excel 2016 | Modules 5–8: SAM Capstone Project 1a

Paterson Arts Center

MANAGING FORMULAS, DATA, AND TABLES

GETTING STARTED

 

Open the file

IL_EX16_CS5-8a_

FirstLastName

_1.xlsx

, available for download

from the SAM website.

 

Save the file as

IL_EX16_CS5-8a_

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_CS5-8a_

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.

You are an intern at the Paterson Arts Center in Paterson, New Jersey, and work

closely with Gwen Rayburn, the executive director of the center. Gwen is

seeking a bank loan to expand the center. In preparation for meeting with a

bank officer, she asked you to analyze data about the center’s group classes,

lessons, and students, and to compare the terms and payments of three loan

options.

Switch to the

Group Classes

worksheet, which Gwen has protected. Start by

correcting data as follows:

a.

Unprotect the worksheet.

b.

In cell C9, change the value to

1:00 PM

.

2.

To reduce the chance of input error, Gwen wants you to make the Location

column a dropdown list. Use data validation to create an in-cell dropdown list

that restricts data in the Location column in the table (the range E4:E13) to the

values

Basie Hall, Cassatt Studio, Chopin Room

.

3.

Unhide the

Students

worksheet to prepare for working with student data.

4.

Gwen added her name to the footer in each worksheet and now wants to

remove it. Use the Document Inspector to remove only footer information from

the workbook. (

Hint

: Do not save data when the dialog box appears.)

[Mac Hint:

The Document Inspector is not available on Excel for Mac, so manually remove

all footers from the workbook.]

5.

To organize the worksheets more logically, move the

Private Lessons

worksheet

so it appears between the

Group Classes

and

Students

worksheet.

6.

Display the full name of the arts center as a header on all the worksheets

except the

Documentation

worksheet as follows:

a.

Group the

Group Classes

,

Private Lessons

,

Students

, and

Expansion

worksheets.

b.

In cell A1, type

Paterson Arts Center

as the worksheet title.

c.

With the worksheets still grouped, add the

Sheet Name

to the center

header section.

Switch to Normal view, if necessary. Ungroup the worksheets.

7.

To make it easier to navigate the workbook, add hyperlinks as follows:

a.

In the

Group Classes

worksheet, add a hyperlink to the text in cell G16

that links to cell

A1

of the

Students

worksheet.

b.

Include

View weekly revenue

as the ScreenTip text.

c.

Copy the hyperlink from cell G16 in the

Group Classes

worksheet to cell

G16 of the

Private Lessons

worksheet.

8.

In the

Private Lessons

worksheet, use data validation to create an in-cell

dropdown list that restricts data in the Location column in the table (the range

E4:E13) to the values

Basie Hall, Chopin Room

.

9.

Gwen is adding two new private lessons on Saturdays and needs that reflected

in the schedule.

In the ranges A14:G14 and A15:G15, add two records for the new private

lessons using the data shown in Table 1 below. Use the in-cell dropdown list to

enter the location information.

Table 1: Data for Private Lessons Worksheet Table

A

B

C

D

E

F

G

14

PR11

0

Piano

1

0:

0

0

A

M

Sa

tu

rd

ay

C

h

o

pi

n

R

o

o

m

$25

Ta

ma

da

15

PR11

1

Voice

1

1:

0

0

A

M

Sa

tu

rd

ay

B

a

si

e

H

al

l

$25

Th

uro

w

10.

Gwen asks you to create a separate list of piano lessons on the

Private Lessons

worksheet. Create the list as follows:

a.

Enter the text

Piano

into cell A18.

b.

Using the range A17:A18 as the Criteria range, create an advanced filter

that extracts records from the Lessons table in the List range A3:G15 to

the range beginning in cell A20. Use the Copy to another location option.

11.

Gwen asks you to create a worksheet showing the Monday group classes

organized by the instructor name. Create this worksheet as follows:

a.

Create a copy of the

Group Classes

worksheet and position it immediately

before the

Students

worksheet.

b.

Use

Monday Classes

as the name of the new worksheet.

c.

In the

Monday Classes

worksheet, filter the Day column to display only

Monday classes.

d.

Sort the filtered table in ascending order by Instructor.

12.

Switch to the

Students

worksheet. Gwen offers a $5.00 discount to repeat

students. Apply this discount as appropriate to the student data as follows:

In cell H4, create a formula using the

IF

function and structured references to

determine the correct amount paid based on the following criteria:

a.

If the

Repeat?

value is

“Yes”

, calculate the amount paid by subtracting

5

from the

Fee

.

b.

Otherwise, the amount paid is the

Fee

value.

c.

Let Excel copy the formula in cell H4 into the range H5:H49.

d.

Format the range H4:H49 using the

Currency

number format with

zero

decimal places and the

$

symbol.

13.

Gwen created an area for retrieving information from the Students table, but it

needs to be filled in. In cell K4, enter a formula using the

VLOOKUP

function

and structured references to retrieve the class name as follows:

a.

Look up the value in cell

K3

.

b.

Use the Class Code and Class Name columns in the Students table

(

Students[[Class Code]:[Class Name]]

) as the table_array argument.

c.

Return the corresponding class name shown in column

2

of the specified

columns.

d.

Use

FALSE

as the range_lookup value.

14.

In cell J6, enter a formula using the

VLOOKUP

function and structured

references to retrieve the instructor’s name as follows:

a.

Look up the value in cell

K3

.

b.

Use the Class Code through Instructor columns in the Students table

(

Students[[Class Code]:[Instructor]]

) as the table_array argument.

c.

Return the corresponding class name shown in column

3

of the specified

columns.

d.

Use

FALSE

as the range_lookup value.

15.

In cell K7, enter a formula using the

DSUM

function and structured references

to calculate the total amount paid for a specified instructor as follows:

a.

Use the entire Students table including the header row (

Students[#All]

)

as the database argument.

b.

Use the

Amount Paid

header in cell H3 as the field argument.

c.

Use the range

J5:J6

as the criteria.

16.

In cell K8, enter a formula using the

DCOUNTA

function and structured

references to count the number of classes taught by a specified instructor as

follows:

a.

Use the entire Students table including the header row (

Students[#All]

)

as the database argument.

b.

Use the

Class Code

header in cell C3 as the field argument.

c.

Use the range

J5:J6

as the criteria.

17.

In her meeting with the bank officer, Gwen wants to show how much revenue

the center receives for each class in a typical week. Create a copy of the

Students

worksheet and then modify it to prepare for showing this information

as follows:

a.

Create a copy of the

Students

worksheet and position it immediately

before the

Expansion

worksheet.

b.

Use

Subtotals

as the name of the new worksheet.

c.

On the

Subtotals

worksheet, clear the contents and formatting from the

range J3:K8.

d.

Sort the table on the

Subtotals

worksheet in ascending order by class

name.

e.

Apply the

Table Style Medium 6

table style. (

Hint

: Depending on your

version of Office, the table style may be written as Gold, Table Style

Medium 6.)

f.

Convert the table to a range.

18.

Include subtotals on the

Subtotals

worksheet as follows:

a.

Add subtotals to the range A3:H49 so that for each change in the

Class

Name

value, the formula uses the

Sum

function to add subtotals to the

Amount Paid

field. Use the default settings for the bottom checkboxes.

b.

Use the outline buttons to display only the Class Name values with

subtotals and the grand total.

19.

Verify the grand total by switching to the

Students

worksheet and then adding a

Total row to the Students table.

20.

Switch to the

Expansion

worksheet. Gwen wants to expand the arts center into

the adjoining vacant office space, and then renovate the new space. On the

Expansion

worksheet, she asks you to help her analyze the loan options to

expand the business. Begin by determining the monthly payments as follows:

a.

In cell B9, enter a formula using the

PMT

function to calculate the

monthly payment using the loan information in the range B5:B7.

b.

In the formula, divide the rate (cell

B6

) by

12

.

c.

Use the term in months (cell

B7

) as the nper.

d.

Use a negative value for the pv (cell

B5

).

e.

Copy the formula from cell B9 into the range C9:D9.

21.

Calculate the total payments as follows:

a.

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

monthly payments (cell

B9

) by the term in months (cell

B7

) for Loan

Option 1.

b.

Copy the formula from cell B10 into the range C10:D10.

22.

Calculate the total interest as follows:

a.

In cell B11, enter a formula without using a function that subtracts the

loan amount (cell

B5

) from the total payments (cell

B10

) for Loan Option

1.

b.

Copy the formula from cell B11 into the range C11:D11.

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: Group Classes Worksheet

Final Figure 2: Private Lessons Worksheet

Final Figure 3: Monday Classes Worksheet

Final Figure 4: Students Worksheet

Final Figure 5: Subtotals Worksheet

Final Figure 6: Expansion Worksheet