## Monday, February 1, 2016

### Analyzing a Property's Cash Flow Statement & How to Create a Real Estate Pro Forma (part 8 of pro forma building series)

 (Retail Center)

Here's a list of everything we've covered so far:

Part 1 - Overview on how to calculate down to the NOI line item.
Part 2 - Intro to lease structures and calculating the gross potential revenue line item.
Part 3 - A look at reimbursement methods and how to calculate reimbursement income.
Part 4 - How to calculate the other income line item and an intro to income adjustments.
Part 5 - Rent abatements overview and calculation example.
Part 6 - Absorption and turnover vacancy explanation and intro to tenant improvements.
Part 7 - General vacancy allowance explanation and calculation example.
Part 8 - Operating expenses explanation.
Part 9 - Constructing a sources and uses table.
Part 10 - Building a debt schedule.
Part 11 - Calculating levered IRR.
Part 12 - DCF analysis.
Part 13 - Loan sizing.

Here's our simple example pro forma spreadsheet to follow along with as well.

In part 8 we'll look at the "Operating Expenses" category.

## What are Operating Expenses?

Operating expenses are the expenses any owner must pay in order to operate the building, regardless of management decisions. Utilities, for example, must be paid to keep electricity, water, and garbage pick up on the building. Different ownership groups must pay this expense, so it's an operating expense above the NOI. Property tax is another expense that must be paid in order to operate the building and that won't change based on the ownership group. Likewise with insurance, although different ownership groups will want more or less coverage than others. Other major operating expense items are repairs and maintenance, landscaping costs, janitorial costs, etc...

## How to Calculate Property Taxes

Property taxes are calculated by taking the "millage rate" multiplied the assessed value of the property. The millage rate is a percentage specified by the county the property is in. In many California counties, the millage rate is 1.12%, or close to it. If the assessed value of a property is \$1,000,000, then the property taxes for that year are calculated by taking .0112 * 1,000,000 = 11,200. Note that in many states property taxes are only allowed to increase by 2% per year, unless a reassessment is triggered because of a sale, refinancing, or capital improvement.

Typically there will be direct assessments on the property as well. Direct assessments are additional taxes levied on a property for specific purposes. For example, a new school in the area might be funded through a direct assessment. Often times the cost to an individual property is based on square footage. The good news is, once these costs are levied on a property, they are fixed for the most part and won't increase.

## Modeling Operating Expenses

Modeling the operating expenses is fairly simple for the most part. Insurance, utilities, and other operating expenses are estimated based on how much they cost at comparable properties on a per square foot basis. If the property next door costs .45 cents per month per square foot for utilities, and our building is 10,000 square feet, then we can estimate our utilities will cost 10,000 * .45 = 4,500 per month. Often you'll be provided historical operating expenses on the building as well. These will be the most accurate estimates of future operating expenses (aside from the property management fee and property taxes).

Most pro formas will then simply take the year 1 operating expenses and grow them by an assumed growth rate. In our example pro forma, I've assumed they grow by 3% per year (aside from property management fee and property taxes).

## Modeling Property Tax Expenses

Property taxes are a bit trickier to calculate. As mentioned above, a property transferring ownership will trigger a reassessment in most states. In the majority of cases, the county will assess the value of the building based on the recorded purchase price. For our pro forma, we'll need to calculate the property taxes based on our assumed purchase price. Since our purchase price is 10,000,000, and our county millage rate is assumed to be 1.1%, then our year 1 property tax expense is 110,000.

Notice that in future years, the property tax expense only grows by 2% because of the cap, not 3% like other operating expenses.

## Modeling the Management Fee

A lot of times landlords will hire 3rd party property management companies to manage the day-to-day operations of a building. These 3rd party property managers are compensated based on a specified percentage of the effective gross income, that way they are incentivized to run the property well and keep tenants happy and paying rent.

In our pro forma, we've assumed the property manager is compensated with 3% of the effective gross income, or EGI. To calculate the management fee in each year, we just take 3% * EGI.

The tricky part about this calculation is it creates what's called a circular reference. A circular reference means formula A depends on formula B, but formula B also depends on formula A. In the case of the management fee, the management fee is calculated based on the effective gross income, but the effective gross income depends on the management fee because the management fee affects the reimbursement income. Get all of that? Probably not, but that's okay. What's important to know is that in order for excel to calculate a circular reference properly and not create an error, you must go to "File" -> "Options" -> "Formulas" -> and then check "Enable iterative calculation." Excel can now calculate circular references.

## A Look at Where We Are

We've now calculated everything down to the NOI, which I mentioned before is the most important line item. You can get pretty far in real estate finance just being able to calculate down to the NOI. Lenders don't really calculate much further down and many equity investors just base investment decisions on the NOI / cap rate. However, we're ambitious real estate financiers and don't want to stop at the NOI line.

In the next post we'll look at building out a sources and uses table and then we'll take a look at creating a loan schedule. Stay tuned!