Apartment Unit Statistics The owners decided that unoccupied units should be remodeled if the…
Apartment Unit Statistics
The owners decided that unoccupied units should be remodeled if the last remodel took place before 2005. In addition, you need to identify the number of total units, number of occupied units, and occupancy rates by apartment complex. Other needed statistics include rankings, total potential rent, actual rent collected, and percentage of total rent. Use relative and mixed cell references correctly in all formulas and functions. Avoid absolute cell references to keep formulas shorter and easier to read.
a. Open e07c1apartment, and then save it as e07c1apartment_ LastnameFirstname.
b. Enter a nested function in the Recommendation column to indicate Need to remodel if the apartment is unoccupied and was last remodeled before 2005. For all other apartments, display No change.
c. Insert functions in the Total Units and Occupied Units columns to calculate the number of apartments and number of occupied apartments by apartment complex.
d. Calculate the occupancy rate and rank based on occupancy rate where the highest occupied complex is 1.
e. Insert the appropriate functions to calculate the potential rent (i.e., the total rent if all units at a complex were rented) and the actual amount of total rent by complex for occupied units.
f. Calculate the percentage of potential rent that is actually collected this month.
g. Insert functions in the range F15:F18 to calculate the total rent collected by apartment size (i.e., number of bedrooms) and total rent collected. The value in cell F18 should be identical to the total in cell G12.
