USA: +1(669)289-8683 
Sign in
E_CH08_EXPV2_EOC_Instructions x
Office 2010 – myitlab:grader – Instructions Exploring Excel Ch. 08 – EOC Project
Downtown Theater Sales
Project Description:
You are an accounting assistant for Downtown Theater in San Diego. Your task is to analyze the weekly and monthly ticket sales by seating type for the fourth quarter of the year. To complete this project, you will create validation rules, locate and fix invalid data, enter and format data on grouped worksheets, create 3-D formulas, and insert hyperlinks. Additionally, you will use the Error Checking feature to locate and correct errors in the formulas.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Download, save, and open the Excel workbook named Exploring_e08_Grader_EOC.xlsx. Click OK to acknowledge the error. 0
2 On the Week 1 worksheet, create a validation rule for the range C3:G3 so that only whole numbers that are less than or equal to 86 are accepted. Create an input message using the text Orchestra Front as the title and Enter the number of tickets sold per day. (including the period) as the message. Create an error alert using the stop style. Title the error alert Invalid Entry and enter Please enter a whole number that is less than or equal to 86. (including the period) as the message. 8
3 Circle the invalid data on the Week 1 worksheet, and then change each invalid entry to the maximum number of applicable seats. 4
4 Group the Week 1, Week 2, Week 3, and Week 4 worksheets together. In cell C11, insert a formula that will calculate Sunday’s Orchestra Front revenue, which is based on the number of seats sold and the price per seat. Modify the price per seat reference so that the column reference is absolute. Copy the formula to the range C11:G14. 9
5 With the worksheets grouped together, in cell H11, insert a formula to calculate the weekly seating totals. Copy the formula down through cell H14. 9
6 With the worksheets still grouped together, in cell C15, insert a formula to calculate the total daily revenue. Copy the formula across through cell H15. 9
7 With the worksheets still grouped together, apply the accounting number format with zero decimal places to the Orchestra Front revenue and the total revenue rows. Apply the comma style with zero decimal places to the remaining seating revenue rows. 6
8 With the worksheets still grouped together, apply a regular underline to the data in the range containing the Balcony Level 2 revenue (just like in cells C6:H6). Apply a double underline for the total revenue values in the last row (just like in cells C7:H7). Ungroup the worksheets. 4
9 Display the Week 1 worksheet, and then create a hyperlink in cell A1 to the Documentation worksheet. 5
10 Group the Week 1, Week 2, Week 3, Week 4, and October worksheets. With the Week 1 worksheet displayed, use the Fill Across Worksheets command to copy the link and formatting in cell A1 to the other worksheets. Ungroup the worksheets and test the hyperlinks. 4
11 Display the October worksheet. In cell C11, insert a 3-D formula to calculate the total Sunday Orchestra Front revenue for all four weeks. Copy the formula for the remaining seating types, weekdays, total row, and total column. 10
12 Copy the formatting in the range C11:H15 on the Week 4 worksheet to the same range on the October worksheet. 5
13 On the October worksheet, in cell C3, insert a 3-D formula to calculate the overall percentage of total Sunday Orchestra Front tickets sold based on the total available Orchestra Front seating. Modify the total available seating reference so that the column reference is absolute. Copy the formula to the range C3:G7. 10
14 On the October worksheet, in cell H3, insert a formula to calculate the average daily percentage of seats sold. Do not use a 3-D formula. Copy the formula down through H7. 6
15 Display the November worksheet. Show precedents for cell H11, and then correct the formula so that the seat price is not included in the weekly total. 2
16 Activate the Error Checking dialog box to find the first potential error on the November worksheet. When detected, correct the error in cell H15 so that the formula calculates the total sales revenue for the month. Ignore all other potential errors on the worksheet. 2
17 Use Error Checking to identify the circular reference on the November worksheet. Display the precedents arrow for the cell, and then fix the error. 2
18 Display the Fourth Quarter worksheet, and then open the downloaded Excel file named e08theater. Tile the windows. In cell E3 on the Fourth Quarter worksheet, insert a reference to the weekly total for Orchestra Front seating in the month of December. Continue creating links to the remaining individual monthly seat revenue and the monthly total for December. Close the e08theater workbook. 5
19 Ensure that the worksheets in the Exploring_e08_Grader_EOC file are correctly named and placed in the following order in the workbook: Fourth Quarter, Documentation, Week 1, Week 2, Week 3, Week 4, October, and November. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100
Updated on: 5/24/2010 1 E_CH08_EXPV2_EOC_Instructions x
Exploring_e08_Grader_EOC.xlsx
Fourth Quarter
Total Revenue by Month
Seating
Seat Price
October
November
December
Orchestra Front
$ 168
$ – 0
$ 280,560
Orchestra Back
$ 148
$ – 0
$ 302,068
Balcony Level 1
$ 95
$ – 0
$ 80,085
Balcony Level 2
$ 75
$ – 0
$ 56,850
Totals
$ – 0
ERROR:#REF!
Documentation
Creator:
Jean Henderson
Date:
17-Jul-12
Purpose:
Store daily ticket sales by seating group.
Calculate daily and weekly revenue.
Calculate monthly seating revenue.
Worksheets:
Week 1
Week 2
Week 3
Week 4
October Summary Worksheet
Week 1
Home
Number of Seats Sold per Day
Seating
Available
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
86
88
84
86
86
86
430
Orchestra Back
108
96
83
104
108
108
499
Balcony Level 1
46
32
42
44
46
46
210
Balcony Level 2
44
24
40
44
44
49
201
Totals
284
240
249
278
284
289
1,340
Revenue per Day
Seating
Seat Price
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
$ 168
Orchestra Back
$ 148
Balcony Level 1
$ 95
Balcony Level 2
$ 75
Totals
Week 2
Home
Number of Seats Sold per Day
Seating
Available
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
86
84
86
86
86
86
428
Orchestra Back
108
106
94
106
108
108
522
Balcony Level 1
46
41
41
46
46
46
220
Balcony Level 2
44
36
30
44
44
44
198
Totals
284
267
251
282
284
284
1,368
Revenue per Day
Seating
Seat Price
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
$ 168
Orchestra Back
$ 148
Balcony Level 1
$ 95
Balcony Level 2
$ 75
Totals
Week 3
Home
Number of Seats Sold per Day
Seating
Available
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
86
80
72
86
86
86
410
Orchestra Back
108
96
92
102
108
108
506
Balcony Level 1
46
40
40
40
40
46
206
Balcony Level 2
44
42
32
38
38
44
194
Totals
284
258
236
266
272
284
1,316
Revenue per Day
Seating
Seat Price
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
$ 168
Orchestra Back
$ 148
Balcony Level 1
$ 95
Balcony Level 2
$ 75
Totals
Week 4
Home
Number of Seats Sold per Day
Seating
Available
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
86
86
84
86
84
86
426
Orchestra Back
108
100
88
106
104
108
506
Balcony Level 1
46
44
46
42
44
46
222
Balcony Level 2
44
42
44
42
42
44
214
Totals
284
272
262
276
274
284
1,368
Revenue per Day
Seating
Seat Price
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
$ 168
Orchestra Back
$ 148
Balcony Level 1
$ 95
Balcony Level 2
$ 75
Totals
October
Percentage of Seats Sold by Weekday for Month
Seating
Available
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Avg Daily %
Orchestra Front
86
Orchestra Back
108
Balcony Level 1
46
Balcony Level 2
44
Avg. Daily Capacity %
284
Total Revenue by Weekday for Month
Seating
Seat Price
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
$ 168
Orchestra Back
$ 148
Balcony Level 1
$ 95
Balcony Level 2
$ 75
Totals
November
Percentage of Seats Sold by Weekday for Month
Seating
Available
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Avg Daily %
Orchestra Front
86
93.3%
94.8%
97.7%
99.4%
100.0%
97.0%
Orchestra Back
108
95.1%
86.6%
96.8%
94.0%
100.0%
94.5%
Balcony Level 1
46
89.7%
86.4%
89.7%
95.7%
96.7%
91.6%
Balcony Level 2
44
77.8%
77.8%
86.9%
89.8%
98.3%
86.1%
Avg. Daily Capacity %
0
90.6%
90.6%
96.0%
96.2%
99.2%
94.5%
Total Revenue by Weekday for Month
Seating
Seat Price
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
$ 168
$ 53,928
$ 54,768
$ 56,448
$ 57,456
$ 57,792
$ 280,560
Orchestra Back
$ 148
60,828
55,352
61,864
60,088
63,936
302,068
Balcony Level 1
$ 95
15,675
15,105
15,675
16,720
16,910
80,085
Balcony Level 2
$ 75
10,275
10,275
11,475
11,850
12,975
56,850
Totals
$ 140,706
$ 135,500
$ 145,462
$ 146,114
$ 151,613
ERROR:#REF!
e08theater.xlsx
December
Percentage of Seats Sold by Weekday for Month
Seating
Available
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Avg Daily %
Orchestra Front
86
99.4%
95.9%
98.3%
97.7%
100.0%
98.3%
Orchestra Back
108
96.3%
94.2%
97.7%
98.1%
100.0%
97.3%
Balcony Level 1
46
95.7%
96.7%
92.4%
96.2%
100.0%
96.2%
Balcony Level 2
44
88.1%
98.9%
95.5%
94.3%
100.0%
95.3%
Avg. Daily Capacity %
284
95.0%
94.3%
96.4%
96.7%
100.0%
96.5%
Total Revenue by Weekday for Month
Seating
Seat Price
Sunday
Wednesday
Friday
Saturday Matinee
Saturday Evening
Weekly Totals
Orchestra Front
$ 168
$ 57,456
$ 55,440
$ 56,784
$ 56,448
$ 57,792
$ 283,920
Orchestra Back
$ 148
61,568
60,236
62,456
62,752
63,936
310,948
Balcony Level 1
$ 95
16,720
16,910
16,150
16,815
17,480
84,075
Balcony Level 2
$ 75
11,625
13,050
12,600
12,450
13,200
62,925
Totals
$ 147,369
$ 145,636
$ 147,990
$ 148,465
$ 152,408
$ 741,868
E_CH09_EXPV2_EOC_Instructions x
Office 2010 – myitlab:grader – Instructions Exploring Excel Ch. 09 – EOC Project
Airline Data
Project Description:
Your supervisors travel frequently for business. You want to create a workbook that provides airline data, up-to-date arrival and departure information, and flights that your supervisors will be on. In this project, you will import text, create Web queries, insert text functions, and import XML data.
Instructions:
For the purpose of grading of the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Download, save, and open the workbook file Exploring_e09_Grader_EOC.xlsx. 0
2 On the Data tab, in the Get External Data group, use the From Text command to insert the text from the downloaded, tab-delimited text file Exploring_e09_Grader_EOC.txt starting in cell A1 of the Sheet1 worksheet. 3
3 Insert a new column between the two existing columns. Separate the Airline-Code column into two columns, Airline and Code, using the hyphen. Change the name of the worksheet to Airlines. 5
4 Adjust the width of column A to 15.00 (110 pixels). Make sure the width of column C is 30.00 (215 pixels). Apply bold formatting with Aqua, Accent 5, Lighter 80% fill color and Aqua, Accent 5, Darker 50% font color to the column labels in row 1. 7
5 Insert a new worksheet named Arrivals to the right of the Airlines worksheet. Create a Web query in cell A2 of the new worksheet to the arrival table in http://media.pearsoncmg.com/ph/bp/myitlab/docs/Exp09GraderEOC/Arrivals.htm. Cut and paste the data from row 2 to row 1. Delete the contents of cell B1. Type Airline in cell C1. Hide all blank rows in the data, as well as column B. 14
6 Apply bold formatting with the Aqua, Accent 5, Lighter 80% fill color and Aqua, Accent 5, Darker 50% font color to the column labels in row 1 of the Arrivals worksheet. 5
7 Insert a new worksheet named Departures to the right of the Arrivals worksheet. Create a Web query in cell A2 of the new worksheet to the departure table in http://media.pearsoncmg.com/ph/bp/myitlab/docs/Exp09GraderEOC/Departures.htm. Cut and paste the data from row 2 to row 1. Delete the contents of cell B1. Type Airline in cell C1. Hide all blank rows in the data, as well as column B. 14
8 Apply bold formatting with the Aqua, Accent 5, Lighter 80% fill color and Aqua, Accent 5, Darker 50% font color to the column labels in row 1 of the Departures worksheet. 6
9 Type Remarks in cell H1 of the Arrivals worksheet. Insert a function in cell H3 that will display the remarks from column G with initial capitalization only. Copy the function to the range H4:H43. Hide column G. 8
10 Type Flight # into cell H1 of the Departures worksheet. Type Remarks into cell I1. Insert a function in cell I3 that will display the remarks from column G with initial capitalization only. Copy the function to the range I4:I35. Hide column G. 8
11 Create a nested function in cell H3 that looks up an exact match to the airline code from the Airlines worksheet and combines the code with the flight number in the Departures worksheet to display the result as, for example, DL 5150. Copy the function to the range H4:H35. 7
12 Open the file e09c1flights.xml file in Notepad. Verify that the flight numbers and departure times still match for the two flights in the Departures worksheet (if not, fix them in the XML file and save and close the file). Insert a new worksheet named Today to the right of the Departures worksheet. Create a connection in cell A1 of the new worksheet to the e09c1flights.xml file. 6
13 Type Status in cell C1 of the Today worksheet. Enter a function in cell C2 that will look up an exact match to the flight number and display the status from the Remarks Column of the Departure worksheet. 9
14 Edit the XML file to include flight number G4 403, departing at 2:00 PM using the correct XML tags. Save and close the file. Refresh the XML data only on the Today worksheet (do not refresh all connections). 8
15 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Airlines, Arrivals, Departures, and Today. Delete the Sheet2 and Sheet3 worksheets. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100
Updated on: 2/15/2011 2 E_CH09_EXPV2_EOC_Instructions x
Exploring_e09_Grader_EOC.xlsx
Sheet1
Sheet2
Sheet3
Exploring_e09_Grader_EOC.txt
Airline-Code Web Site
Allegiant-G4 http://www.allegiantair.com
American-AA http://www.aa.com
Continental-CO http://www.continental.com
Delta-DL http://www.delta.com
Frontier-F9 http://www.frontierairlines.com
JetBlue-B6 http://www.jetblue.com
Southwest-WN http://www.southwest.com
United-UA http://www.united.com
e09c1flights.xml
DL 5061
3:07 PM
UA 6083
7:25 PM
E_CH11_EXPV2_EOC_Instructions x
Office 2010 – myitlab:grader – Instructions Exploring Series Vol. 2, Chapter 11, EOC
RoomSort Macro
Project Description:
In this project you will create a macro that copies and pastes a range of formatted data into a different sheet. The macro will sort the data by three fields, including by a custom list. You will create a command button, attach the macro to it, and then run the macro from the button.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Download, save, and open the Excel workbook named Exploring_e11_Grader_EOC.xlsm. 0
2 Apply the Horizon theme to the workbook. 8
3 In the Sequential sheet, apply the Heading 3 cell style to the range A3:J3. 10
4 Apply the Waveform theme colors to the workbook. 8
5 Begin to record a macro named RoomSort. Accept the default settings in the dialog box. While recording the macro, be sure to follow the steps exactly as specified. Do not switch between windows or worksheets in between steps. 10
6 In recording mode, on the Sequential sheet, select the range A3:J16. Copy the selected range, and then paste it starting in cell A4 of the Room sheet. 10
7 Use the Go To command to go to cell A4 of the Room sheet. 8
8 In the Room sheet, create a multi-level custom sort. First sort by Room in ascending order. Add a sort level to sort by Days, using a custom sort list as follows: MWF, MW, M, W, TR, T, R, S. Then sort by Start Time from earliest to latest.
Stop recording the macro. 15
9 In the Sequential worksheet, draw a Form Control button from cells D1:D2. Assign the button to run the RoomSort macro. Set the caption of the button to Run Room Sort, the height to 0.4″, and the width to 2.5″. 12
10 In Excel, on the Room sheet, delete the data (but not the formatting) in the range A4:J17. Return to the Sequential sheet and deselect Design Mode for the button, if necessary. Click the Run Room Sort button to run the macro and ensure that the macro works as expected. 9
11 In the Microsoft Visual Basic for Applications window, delete any empty comment lines or blank lines in the code, and then copy the entire RoomSort macro from the Sub statement to the End Sub statement. Paste the code into cell A1 of the Code worksheet (the VBA code will appear in cells A1:A28 when pasted). 10
12 Save the macro-enabled workbook as an Excel workbook, using the same file name. Click Yes to save it as a macro-free workbook. Close the workbook. Exit Excel. Submit the Exploring_e11_Grader_EOC.xlsx workbook as directed. 0
Total Points 100
Updated on: 12/28/2010 2 E_CH11_EXPV2_EOC_Instructions x
Exploring_e11_Grader_EOC.xlsm
Sequential
Fall 2012
ID
Number
Section
Course Title
Days
Start Time
End Time
Room
Instructor
Max
12352
1420
001
Comp Arch Systems Software
TR
0.6041666667
0.65625
CS 617
Busby, Linda
28
12353
1510
001
Intro to Sys Admin UNIX Linux
MWF
0.5416666667
0.5763888889
CS 614
Eberling, Amanda
32
12354
1510
601
Intro to Sys Admin UNIX Linux
TR
0.7916666667
0.84375
CS 614
Busby, Linda
32
12355
2030
001
Business Data Communications
TR
0.6666666667
0.71875
CS 708
Grodsky, Philip
35
12356
2050
001
Database Fundamentals
TR
0.4166666667
0.46875
CS 617
Mast, Kevin
28
12357
2200
001
Computer Programming II (C#)
TR
0.6041666667
0.65625
CS 614
Wong, Tim
32
12358
2450
001
Web Application Design
TR
0.5416666667
0.59375
CS 617
Bartholomew, Katrina
30
12359
2450
701
Web Application Design
TBA
Bartholomew, Katrina
35
12360
2660
001
Info Security Fundamentals
MWF
0.375
0.4097222222
CS 708
Eberling, Amanda
25
12361
2660
801
Info Security Fundamentals
S
0.3333333333
0.4375
CS 708
Busby, Linda
32
12363
3030
001
Networks and Internetworking
MWF
0.4583333333
0.4930555556
CS 517
Grodsky, Philip
18
12364
3050
001
Database Systems
MWF
0.4166666667
0.4513888889
CS 617
Mast, Kevin
26
12365
3120
001
Management Information Systems
TR
0.3541666667
0.40625
CS 615
Mast, Kevin
35
Room
Fall 2012
Code
>Summary
:
partment omplex
Units
.
Apartment Complex ed
Occupied Rolling Meadows 1
1 0
1 75
No No change Lakeview Apartments 1 No Need to romdel 50
No 0
No change Mountaintop View C 3 Yes No change 1 Yes 2010 No change 1 550 No 2010 No change 1 550 No Need to romdel 1 Yes 2004 No change 1 1,000 Yes 2004 No change Oak Tree Living 2 1,200 No 2004 Need to romdel Development Unit # Rental Price Occupied Last Remodel Recommendation List of Rental Property Yes 2004 No change 5 Mountaintop View B 2 No 2010 No change 10 Sunset Valley 201 1 550 Yes 2003 No change 12 Office 2010 – myitlab:grader – Instructions Exploring Series Excel Ch07 EOC Project Apartment Complex Analysis Project Description:
In this project, you will create functions that calculate statistical data for apartment complexes. You will then filter the data based on multiple criteria and calculate the payments for a loan on a new complex.
Instructions: Updated on: 17/11/2010 2 E_CH07_EXPV2_EOC _Instructions x
2
Search Engine
Search For Ranking:
Result
Development
Summary Statistics
A
C
Total Units
Occupied
Occupancy Rate
Rank by Occ Rate
Potential Rent
Actual Rent
% of Potential Rent
Lakeview Apartments
Mountaintop View
Oak Tree Living
Rolling Meadows
Sunset Valley
Total Rent:
List of Rental Property
Seq
No
Unit #
#
B
Rental Price
Last Remodel
Recommendation
1
10
7
5
Yes
200
4
No change
2 Lakeview Apartments
11
8
2008
3
12
875
2001
Need to romdel
4 Mountaintop View A 1 875 No
2004
5 Mountaintop View B 2
9
201
6
1,200
2010
7 Sunset Valley
101
550
8 Sunset Valley
102
9 Sunset Valley
103
2003
10 Sunset Valley 201 1 550 Yes 2003 No change
11 Oak Tree Living
21
1,000
12 Oak Tree Living
22
13
23
Database
Criteria Range
Seq No.
# Bed
2
Need to remodel
3 Need to remodel
Database Statistics
No. of Apts. to Remodel
Value of Lost Rent
Year of Oldest Remodel
Seq No. Development Unit # # Bed Rental Price Occupied Last Remodel Recommendation
1 Rolling Meadows 101 1 750
2 Lakeview Apartments 11 1 875 No 2008 No change 3 Lakeview Apartments 12 1 875 No 2001 Need to remodel
4 Mountaintop View A 1 875 No 2004 Need to remodel
950
6 Mountaintop View C 3 1,200 Yes 2010 No change
7 Sunset Valley 101 1 550 Yes 2010 No change
8 Sunset Valley 102 1 550 No 2010 No change 9 Sunset Valley 103 1 550 No 2003 Need to remodel
11 Oak Tree Living 21 1 1,000 Yes 2004 No change
12 Oak Tree Living 22 1 1,000 Yes 2004 No change 13 Oak Tree Living 23 2 1,200 No 2004 Need to remodel
Loan
Input Area
Summary Calculations
Complex Cost
$ 850,000.00
Loan Amount
$ 475,000.00
Down Payment
$ 375,000.00
No. Periods
360
# of Pmts per Year
Monthly Rate
0.48%
Years
30
Monthly Payment
APR
5.75%
1st Payment Date
3/20/12
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Download, save, and open the Excel workbook named Exploring_e07_Grader_EOC.xlsx. 0
2 On the Summary worksheet, in cell H14, enter a nested function that will return Need to remodel as the result if the apartment is unoccupied and was last remodeled before 2005. Otherwise, the function should return No change as the result. Copy the function down through H26. 10
3 In cell B6, insert a function to calculate the total number of units in the apartment complex in A6. Be sure to enter the criteria range as an absolute reference. Copy the function down through B10. 10
4 In cell C6, insert a function that will calculate the number of occupied units in the apartment complex in A6. Be sure to enter the criteria ranges as absolute references. Copy the function down through C10. 10
5 In cell D6, calculate the percentage of units that are occupied in the Lakeview Apartments complex. Copy the formula down through D10. 6
6 In cell E6, insert a function that will rank the value in D6 based on the occupancy rates of all five apartment complexes, in descending order. Enter the range as an absolute reference. Copy the function down through E10. 10
7 In cell F6, insert a function that will sum the potential rent (i.e., the total rent if all units are occupied) for the apartment complex in A6. Copy the function down through F10. 10
8 In cell G6, insert a function that will sum the actual amount of rent (i.e. total rent for occupied units) for the apartment complex in A6. 8
9 Copy the function in G6 down through G10. 2
10 In cell H6, calculate the percentage of potential rent collected for the Lakeview Apartments complex. Copy the formula down through H10. 6
11 Enter the value 4 in cell B2. In B3, insert a nested function that will look up the ranking entered in cell B2 and return the respective apartment complex. Make sure the sample ranking of 4 returns the correct apartment complex indicated in the Summary Statistics area. 6
12 Enter the value 1 in cell B2 and observe how the INDEX function returns the apartment complex that is ranked first. 2
13 On the Database worksheet, perform an advanced filter on the list in the range A11:H24, using the criteria range A2:H4. Filter the data in place. 10
14 On the Loan worksheet, in cell E5, insert a PMT function, using the values in E2:E4 for the arguments. 10
15 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100
