Wednesday, February 10, 2016

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

(Stockholm)

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.

Now that we've calculated down to the NOI, we'll start looking at what happens below the NOI. However, before we start going into specific line items, we'll need to create a "Sources and Uses" table. A sources and uses table is basically a budget of where the money goes at the time of acquisition and how the project is funded.

Sources


This is a breakdown of how a project is funded. This part of the table will have the debt amount, equity amount, and amounts for any other capital sources (preferred equity, mezz debt, etc...). The total should equal the total of the uses section.

Uses


The uses section is a breakdown of what you need to pay, or set aside money, for at the time of acquisition. Some typical acquisition costs are the property purchase price (obviously), there's usually a fee to the bank for the loan, any repairs that need to immediately be done to the building should be budgeted for (CapEx), and others.

Sources and Uses Calculations in Our Pro Forma


In the example pro forma linked to at the top of the post, we now have a sources and uses table. To create a sources and uses table, you, somewhat counter-intuitively, need to first create the uses portion. You must create the uses section first because you typically know what costs you're going to have and it's fairly easy to budget for them. You then want to reverse engineer how much equity the project will require. That might be a bit confusing, so let's go into the example and take it step by step.

In our uses section, I've listed some typical expenses / things a new owner should budget for. Of course we have the purchase price of 10M. I've also assumed we need to make various improvements to the building that will cost 500k, so we budget 500k for the CapEx. I've budgeted 50k worth of due diligence costs. Due diligence costs are things like a property inspection, environmental report, title report, and others. I've budgeted 25k worth of legal fees (greedy lawyers). Lawyers often play a role in real estate transactions because contracts can become complicated and require the expertise of a lawyer. I've also budgeted a fee of 35k to the bank for originating the loan.

The other 2 uses are leasing commissions and tenant improvements, totaling 200k. Remember how one of our tenants is assumed to vacate their suite in year 2? Well, in order to find a replacement tenant, it'll require giving a 100k tenant improvement allowance and we'll need to pay the broker a 100k fee as well. It's best to budget for those costs up front and leave the money in the bank.

The sources section then becomes quite easy, but it's important to note how the calculations are actually done. First, we carry the total from the uses section over the sources. Our funding must match the amount of money we spend / budget for at acquisition. Second, we assume the bank has agreed to give us a 7M loan, so we hard code that as an input. Third, and lastly, we back into the equity required for the project by subtracting the loan amount from the total that's carried over from the uses section.

In this example we've just assumed a loan size; however, projecting the size of a loan you can get on a project is actually a fairly difficult exercise. In fact, the size of the loan will be impacted the cash flows we project. Because of that, we need the equity amount to be dynamic / flexible and fill in whatever gap there is between the total project cost and loan size.

In the next part of the series, we'll dive into loan sizing and tackle some of the tricky aspects of it.

No comments:

Post a Comment