I have a financial model I am building in excel. There are multiple unit types and each unit type has a monthly absorption rate.
I have attached the workbook.
The columns I am referencing are on the Project Assumptions sheet, columns Q, R & W
Column Q - Monthly Absorption
Column R - # of Units by Unit Type
Column W - Monthly Unit Rent
I am trying to figure how to write an efficient formula that will calc the rent based absorption rates and sum the unit types. The monthly absorption is the number of units that lease up on a monthly basis. Rental income is calculated by multiplying the monthly rent by the monthly absorption plus the previous months rental income.
The formula I have written is "=G19+IF(H2*'Project Assumptions'!$Q4>='Project Assumptions'!$R4,0,'Project Assumptions'!$Q4*'Project Assumptions'!$W4)" and this works for 1 unit type. I would have to write this same formula 48 times to account for every unit type. The formula is on row 19 on The Operating Forecast tab.
Is there a better way?
Bookmarks