Mathematics and Apartment Complex
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: 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 |
