## Saturday, January 23, 2016

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

 (Office Building Glamour Shot)

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 6, we'll discuss the "Absorption and Turnover Vacancy" line item under the "Income Adjustments" category.

First, what does absorption and turnover vacancy mean? This is the income that is lost due to a tenant leaving a suite and the downtime between finding a replacement tenant. Remember that "Gross Potential Income" didn't factor in the downtime and calculated the income as if all suites were occupied. Well, the absorption and turnover vacancy line item is where we account for that lost income.

## Downtime in a Suite

In a perfect world, a landlord would find out a tenant wanted to vacate their space 6 months prior to lease expiration, begin searching frantically for a new tenant, and have a replacement ready to occupy the space as soon as the current tenant vacates the suite. Of course, it never works this way in the real world. Tenants will go bankrupt and break their lease early, they'll move to a different property and use lawyers to find a way out of their lease, and even if they don't leave until their lease expires, they might not give much notice.

Not to mention it typically takes a decent amount of time to find a replacement tenant, especially if the suite has some sort of peculiarity to it. For example, if the suite is 10,000+ square feet, finding a replacement tenant could take a year or longer. That's a year of downtime! If the suite is poorly located within the building it might take many months to lease. You get the idea.

On top of the downtime needed to find a replacement tenant, the new tenant will often want changes made to the suite that can take several months to construct. These changes are called "Tenant Improvements."

## Tenant Improvements

Picture this scenario: You're moving to a new city and looking for an apartment to live in. You are talking to one apartment owner that is willing to rent an apartment to you for \$1,000 per month, but the appliances are 20 years old, the carpet is 10+ years old, and there's wood-paneling throughout. Another apartment owner tells you that they'll rent an apartment to you for \$1,300, same size and location, but they're going to update the kitchen with brand new appliances, granite counter-tops, new laminate flooring throughout, and upgrade the bathrooms as well. You might seriously consider taking that modern, improved apartment over the older, dated one in spite of the higher price per month.

The same scenario happens in commercial real estate. Tenants want a good looking, modern office both for their workers to have a good experience and to impress clients. They'll, as part of their lease negotiations, often require the landlord to pay a lump of cash towards improving the suite. These are called tenant improvements, or TI's.

TI's factor into the downtime because the tenant can't occupy the space while construction is being performed, creating even more downtime. Extensive tenant improvement work can take upwards of 6 months. We need to factor this into our absorption and turnover vacancy line item.

## How to Calculate Absorption and Turnover Vacancy

In our example pro forma, I've assumed Tenant #1 vacates their suite at the very beginning of year 2, and it takes 9 months to find a new tenant, along with 3 months of tenant improvements, totaling 12 months of downtime for that suite.

Tenant #1 was scheduled to pay \$309k (25,000 * 1.03 * 12) that year in rent. They were also scheduled to reimburse half of all the operating expenses (pro rata share of 50% * forecasted opex of 554,882), which is 277,411. Since we're missing out on all that rent and reimbursement income, our absorption and turnover vacancy line is 309,000 + 277,411 = 586,411, but we're not quite done.

We need to adjust for a lower "other income" amount as well. The assumption is that parking income will go down proportionate to the pro rata share of the suite. Think of a retail center that's 100% occupied by strong tenants. There's a good chance the parking lot is usually fairly full (think of that shopping center near you with the Chipotle, Starbucks, and Trader Joe's...). Now think of a retail center that's only 50% full with so-so tenants. Chances are the parking lot is only half full. In order to account for this in our pro forma, we need to also add in half of the projected other income, which is just 50% * 10,300 = 5,150.

Our final absorption and turnover vacancy number for year 2 is then 586,411 + 5,150 = 591,561.

Stay tuned for the next post which will cover the "General Vacancy Allowance" line item--one of the most important concepts in commercial real estate valuation!