Purpose
The purpose of this post is to outline the key functions of the Pro Forma feature in CREModels multifamily acquisition models. These features should be universal across all acquisition models unless noted otherwise.
Income/Expense Line Items
The Income and Expense name inputs in Pro Forma Assumptions are the basis for the bucketing done in the ‘HISTORICAL DATA’ tab of the model. Based on the bucketing, the model will sum the historical data and show those totals to the right of the Broker OM inputs in the Comparison Columns of the Pro Forma Assumptions section.
Note: Shown below are the outputs of the Income/Expense names on the ‘HISTORICAL DATA’ tab.
Comparison Columns
The Broker OM column (the first column of the Pro Forma Assumptions table) provides inputs for the user to add pro forma assumptions from the broker’s offering memorandum to be used in comparison to the historical data of the property. To aid in comparison of the Broker OM assumptions for each income and expense group, the user has the option to customize the income/expense data displayed in the two columns directly to the right of the Broker OM column.
In order to display data from 2 or 3 Cldr Yrs Prior, the user must enter this data in the Historical Data tab in the expanded columns.
The first column allows the user to display data from the previous calendar year (the T-12 data entered on the Historical Data tab), or data from each of the two prior calendar years. In order to display data from 2 or 3 Cldr Yrs Prior, the user must enter this data in the Historical Data tab in the expanded columns.
The second column to the right of the Broker OM column allows the user to display the annualized income/expense values from a varying number of recent months (e.g., T-1, T-2, T-3, etc.) within the last year. Additionally, the first column under Annualized Period can be adjusted in the same way. For example, this feature can be especially useful if there is only historical data available for the past 6 months, in which case, the user may want to change the Annualized Period column to “T-6” and the second comparison column to “T-3”.
Replacement Reserves
Replacement Reserves can be added to the model on a per unit basis. This input is considered a capital expense and will not influence the NOI.
Adjustments
The Multi-Family Acquisition model gives the user the capability to adjust individual income/expense groups for Year 1 of the pro forma statement using the override columns. Overrides can be made in the following forms: $/Yr, $ Per Unit, $ Per Occupied Unit, $ Per Vacant Unit, and $ Per Unit Turn. The user may adjust the override form in the Adjustment Type column by choosing between one of the dropdown selections noted above. If no adjustment is to be made, then the user may select ‘No Adjustment.’
The first override column ($ Override) allows the user to enter a projected expected $ value for the income/expense group during pro forma Year 1, which value will be used to calculate the annual amount based on the Adjustment Type. For example, below the user has selected $/Yr for Parking Garage Income as the adjustment type. Since the user entered $7,680, the model is using that adjusted amount as the total $/Yr amount. The user also input an adjustment for Electric Reimbursement on a $ Per Occupied Unit basis. The amount input is $1,440 per unit on an annual basis; thus, the model will multiply that amount by the total occupied units in Y1 to get the total annual amount for the property.
If ‘$ Per Unit Turn’ is selected as the Adjustment Type, then the user has the ability to enter the override amount and the annual turnover rate as a percentage, found in the second column of the Adjustments section.
Note: Property taxes cannot be adjusted in the Pro Forma Assumptions section, however, a button is provided to take the user to the Real Estate Tax section of the model where the user can override the taxes or detail the taxes, as explained in the Real Estate Tax Assumptions tutorial.
Management Fees
Management Fees are an expense that is directly correlated with income. To more accurately project future Management Fees expense, the Multi-Family Acquisition model provides an optional input to enter the Management fees as a percentage of either Total Effective Income or Net Rental Income. The user can select either % of Total Effective Income or % of Net Rental Income from the dropdown to determine the method of calculation for Management Fees. The user may also enter a Minimum Monthly Management Fee the property will expend if the entered % for the Management Fee on the chosen income each month does not reach a certain amount on a monthly basis. For example, below the Minimum Monthly Management Fee is $750 and the Management Fee % is 4% of the Total Effective Income. If 4% is applied to the Total Effective Income for each month and does not reach $750, then a management fee of $750 will be applied instead for each month the management fee is lower than $750.
Using the renovation assumptions entered in the Unit Mix (number of units renovated, average reno downtime, and current rent/unit), the model calculates the expected Down Units (For Renovations) loss for Analysis Year 1 and includes this value within the Year 1 Pro Forma of the Pro Forma Assumptions section. This loss is also calculated for Analysis Years 2 and 3 (if applicable) and incorporated into the model’s calculations but is not displayed on the INPUTS tab.
Growth %
The Multi-Family Acquisition model allows the user to customize the growth rate expected for individual income or expense groups during Analysis Years 2, 3, and 4. These rates can be changed by entering the expected growth rate for the target income/expense group in the corresponding Year [X} Growth % input (highlighted below).
Gross Rental Income, Physical Vacancy, and Bad Debt growth rates can be adjusted within the Income Growth table of the model. Down Units (For Renovations) loss is based on the renovation assumptions entered in the Unit Mix and cannot be manipulated by an override growth %. A separate post has been made addressing the Income Growth table.
Note: Replacement Reserves can only be grown at one fixed annual rate.