IllustratedExcel 2016 Modules | 5–8: SAM Capstone Project 1a
Illustrated Excel 2016 | Modules 5–8: SAM Capstone Project 1a
Paterson Arts Center
mANAGING FORMULAS, DATA, AND TABLES
GETTING STARTED
· Open the fileIL_EX16_CS5-8a_FirstLastName_1.xlsx, available for download from the SAM website.
· Save the file asIL_EX16_CS5-8a_FirstLastName_2.xlsxby changing the “1” to a “2”.
2. If you do not see the.xlsxfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
· With the fileIL_EX16_CS5-8a_FirstLastName_2.xlsxstill 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
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 theGroup Classesworksheet, which Gwen has protected. Start by correcting data as follows:
a. Unprotect the worksheet.
b. In cell C9, change the value to1:00 PM.
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 valuesBasie Hall, Cassatt Studio, Chopin Room.
Unhide theStudentsworksheet to prepare for working with student data.
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.]
To organize the worksheets more logically, move thePrivate Lessonsworksheet so it appears between theGroup ClassesandStudentsworksheet.
Display the full name of the arts center as a header on all the worksheets except theDocumentationworksheet as follows:
c. Group theGroup Classes,Private Lessons,Students, andExpansionworksheets.
d. In cell A1, typePaterson Arts Centeras the worksheet title.
e. With the worksheets still grouped, add theSheet Nameto the center header section.
Switch to Normal view, if necessary. Ungroup the worksheets.
To make it easier to navigate the workbook, add hyperlinks as follows:
f. In theGroup Classesworksheet, add a hyperlink to the text in cell G16 that links to cellA1of theStudentsworksheet.
g. IncludeView weekly revenueas the ScreenTip text.
h. Copy the hyperlink from cell G16 in theGroup Classesworksheet to cell G16 of thePrivate Lessonsworksheet.
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 | PR110 | Piano | 10:00 AM | Saturday | Chopin Room | $25 | Tamada |
| 15 | PR111 | Voice | 11:00 AM | Saturday | Basie Hall | $25 | Thurow |
Gwen asks you to create a separate list of piano lessons on thePrivate Lessonsworksheet. Create the list as follows:
i. Enter the textPianointo cell A18.
j. 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.
Gwen asks you to create a worksheet showing the Monday group classes organized by the instructor name. Create this worksheet as follows:
k. Create a copy of theGroup Classesworksheet and position it immediately before theStudentsworksheet.
l. UseMonday Classesas the name of the new worksheet.
m. In theMonday Classesworksheet, filter the Day column to display only Monday classes.
n. Sort the filtered table in ascending order by Instructor.
Switch to theStudentsworksheet. 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 theIFfunction and structured references to determine the correct amount paid based on the following criteria:
o. If theRepeat?value is“Yes”, calculate the amount paid by subtracting5from theFee.
p. Otherwise, the amount paid is theFeevalue.
q. Let Excel copy the formula in cell H4 into the range H5:H49.
r. Format the range H4:H49 using theCurrencynumber format withzerodecimal places and the$symbol.
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 theVLOOKUPfunction and structured references to retrieve the class name as follows:
s. Look up the value in cellK3.
t. Use the Class Code and Class Name columns in the Students table (Students[[Class Code]:[Class Name]]) as the table_array argument.
u. Return the corresponding class name shown in column2of the specified columns.
v. UseFALSEas the range_lookup value.
In cell J6, enter a formula using theVLOOKUPfunction and structured references to retrieve the instructor’s name as follows:
w. Look up the value in cellK3.
x. Use the Class Code through Instructor columns in the Students table (Students[[Class Code]:[Instructor]]) as the table_array argument.
y. Return the corresponding class name shown in column3of the specified columns.
z. UseFALSEas the range_lookup value.
In cell K7, enter a formula using theDSUMfunction and structured references to calculate the total amount paid for a specified instructor as follows:
aa. Use the entire Students table including the header row (Students[#All]) as the database argument.
ab. Use theAmount Paidheader in cell H3 as the field argument.
ac. Use the rangeJ5:J6as the criteria.
In cell K8, enter a formula using theDCOUNTAfunction and structured references to count the number of classes taught by a specified instructor as follows:
ad. Use the entire Students table including the header row (Students[#All]) as the database argument.
ae. Use theClass Codeheader in cell C3 as the field argument.
af. Use the rangeJ5:J6as the criteria.
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 theStudentsworksheet and then modify it to prepare for showing this information as follows:
ag. Create a copy of theStudentsworksheet and position it immediately before theExpansionworksheet.
ah. UseSubtotalsas the name of the new worksheet.
ai. On theSubtotalsworksheet, clear the contents and formatting from the range J3:K8.
aj. Sort the table on theSubtotalsworksheet in ascending order by class name.
ak. Apply theTable Style Medium 6table style. (Hint: Depending on your version of Office, the table style may be written as Gold, Table Style Medium 6.)
al. Convert the table to a range.
Include subtotals on theSubtotalsworksheet as follows:
am. Add subtotals to the range A3:H49 so that for each change in theClass Namevalue, the formula uses theSumfunction to add subtotals to theAmount Paidfield. Use the default settings for the bottom checkboxes.
an. Use the outline buttons to display only the Class Name values with subtotals and the grand total.
Verify the grand total by switching to theStudentsworksheet and then adding a Total row to the Students table.
Switch to theExpansionworksheet. Gwen wants to expand the arts center into the adjoining vacant office space, and then renovate the new space. On theExpansionworksheet, she asks you to help her analyze the loan options to expand the business. Begin by determining the monthly payments as follows:
ao. In cell B9, enter a formula using thePMTfunction to calculate the monthly payment using the loan information in the range B5:B7.
ap. In the formula, divide the rate (cellB6) by12.
aq. Use the term in months (cellB7) as the nper.
ar. Use a negative value for the pv (cellB5).
as. Copy the formula from cell B9 into the range C9:D9.
Calculate the total payments as follows:
at. In cell B10, enter a formula without using a function that multiplies the monthly payments (cellB9) by the term in months (cellB7) for Loan Option 1.
au. Copy the formula from cell B10 into the range C10:D10.
Calculate the total interest as follows:
av. In cell B11, enter a formula without using a function that subtracts the loan amount (cellB5) from the total payments (cellB10) for Loan Option 1.
aw. 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
Related Services
Cengage SAM Capstone Project Help
