Clean Easy Commercial Real Estate Pro Forma

If you want to understand a company, look at its financial statements – and if you want to understand a property, look at its pro-forma.

Many websites and textbooks describe the real estate pro-forma as a "cash flow projection" for a property.

While that description is accurate, it's more useful to think of the pro-forma as a combined and simplified Income Statement and Cash Flow Statement – for a property rather than a company.

We'll discuss the main line items on the real estate pro-forma in this article and give you an Excel template, but let's start at the beginning with the "why."

Why is it possible to simplify the financial statements of a property and use just a single schedule rather than a full Income Statement, Balance Sheet, and Cash Flow Statement?

Simplification 101: Why the Real Estate Pro-Forma?

It's easiest to answer this question by pointing out that it's possible to do the same thing for companies.

For example, in a DCF, LBO, or merger model, you could skip the full 3-statement projections and do the following instead:

Project the company's revenue, expenses, and taxes on its Income Statement.
Then, create a "mini-Cash Flow Statement" and include only the key, recurring line items, such as Depreciation, the Change in Working Capital, and CapEx.

You don't need a Balance Sheet because you can track Cash, Debt, and Equity separately below these projections, and you can estimate the Change in Working Capital with a simple percentage.

And you don't need a full Cash Flow Statement because many of the items on it are non-recurring and, therefore, do not factor into the projections.

Properties are much simpler than large companies, so if it's feasible to simplify the financial statements like this for companies, it's even more feasible to simplify them for properties.

It's also simpler because Working Capital tends to be less important for properties, and you effectively use Cash Accounting rather than Accrual Accounting in the analysis.

Also, you usually ignore income taxes because properties tend to be owned by pass-through entities such as Partnerships, S Corporations, and REITs that do not pay corporate income taxes.

Finally, "other activities" on companies' Cash Flow Statements are often minimal for properties, and Debt and Equity Issuances and Debt Service can be handled directly on the pro-forma.

To be clear: properties still have full financial statements.

It's just that for financial modeling, valuation, and investment analysis, you almost always skip the full statements and focus on the pro-forma instead.

The Real Estate Pro-Forma Excel and Guide

Below, we'll walk through a pro-forma for an office/retail property with 3 tenants on different lease types (Full Service, Single Net, and Triple Net):

Pro-Forma – Office/Retail Property (XL)

This is a simplified pro-forma for a "core real estate deal" intended to illustrate the concepts. We cover more complex examples and cases in our full Real Estate Financial Modeling course.

Here's the high-level view (click the image to see a larger version):

Real Estate Pro-Forma: Revenue Line Items

You always start the pro-forma by showing the "potential" revenue of a property if it were 100% occupied and all tenants paid market rates.

Then, you make deductions and adjustments because:

Properties are rarely 100% occupied, and you do not earn rent from vacant space.
Not all tenants pay market rates; some might be paying lower rates, and some might be paying higher rates.
If a tenant's lease expires and that tenant chooses not to renew it, it will take time to find a new tenant. You will not earn rent in that downtime period.
When a new tenant moves in, you may give it "free rent" for a few months as an incentive to agree to a multi-year lease.
You might also earn income from "expense reimbursements," i.e., the portion of operating expenses that a tenant is responsible for paying.

Each line item in the Revenue section corresponds to one or more of these points:

Base Rental Income

This one represents the "Potential" Rental Income of the property if it were 100% occupied and all tenants paid market rent.

Simple Calculation: If the property has 10,000 rentable square feet and the market rate is $50 per square foot per year, the Base Rental Income is $500,000.

Absorption & Turnover Vacancy

This represents the foregone rental income when a tenant leaves and it takes several months to find a new tenant.

It's not a cash expense, but rather a loss of potential revenue.

Simple Calculation: If a tenant is renting 2,000 square feet for $50 per square foot per year, this tenant leaves, and it takes 6 months to find a new tenant, then the Absorption & Turnover Vacancy is 2,000 * $50 * (6 / 12) = $50,000.

Concessions & Free Rent

This line item corresponds to the "free rent" incentive described above.

Simple Calculation: If a new tenant moves into a 2,000-square-foot space with rent of $50 per square foot per year and receives 3 rent-free months, the "Concessions & Free Rent" line item will be 2,000 * $50 * (3 / 12) = $25,000.

Expense Reimbursements

This item represents the amount of property taxes, insurance, and maintenance/utilities for which the tenants are responsible.

The expenses that each tenant must pay are specified in its lease, and the terms tend to vary quite a bit.

Simple Calculation: If a tenant renting 2,000 square feet is responsible only for its share of property taxes, and property taxes for the entire 10,000-rentable-square-foot building are $50,000 per year, then this tenant must pay (2,000 / 10,000) * $50,000 = $10,000.

Potential Gross Revenue

If you sum up everything above, you'll calculate the Potential Gross Revenue or Potential Gross Income for the property.

This number represents the income from all space in the building, ignoring permanently vacant spaces and non-rent sources.

General Vacancy

This line item represents Potential Rental Income lost to spaces that are permanently vacant.

"Permanently vacant" means there is no tenant in the space currently, and the owner has no near-term plans to find a tenant for that space.

Simple Calculation: If this same 10,000-rentable-square-foot building has 1,000 square feet that is always empty, the General Vacancy line item would be 10,000 * 10% * $50 = $50,000.

It would appear with a negative sign on the pro-forma since it represents a deduction.

Effective Gross Income (EGI)

This is similar to "Net Sales" or "Net Revenue" for a normal company, but it's on a cash basis.

EGI represents the cash revenue that a property generates after the adjustments for temporary and permanent vacancy and free months of rent.

It might also include adjustments for above or below-market leases, percentage rent for retail tenants, and other items.

Real Estate Pro-Forma – Property Expenses

That takes us to the end of the Revenue section, which ends with Effective Gross Income.

Most of the property's expenses are easier to explain:

Property Management Fees

Property owners rarely manage their properties directly.

Instead, they hire 3rd party management companies to deal with tenants, collect rent, resolve problems, and set up repairs and maintenance. The fees paid to these management companies are usually a percentage of EGI.

Simple Calculation: If the management fees are 3% of EGI and EGI is $1 million, then these fees are $30,000.

Operating Expenses

These include insurance, maintenance and repairs, utilities, and other miscellaneous items.

If the property is big enough, this category might also include staff payroll, sales & marketing, janitorial, landscaping, and security services. These expenses are usually based on per-square-foot or per-square-meter amounts.

Simple Calculation: If the Operating Expenses are $10.00 per Rentable Square Foot per Year and there are 50,000 rentable square feet, then the annual Operating Expenses are $500,000.

Real Estate & Property Taxes

Nearly all local governments charge a tax on properties to fund school systems, police, and infrastructure.

Often, this tax is a small percentage (1-4%) of the property's assessed value, and it increases each year as the assessed value goes up.

In places like Australia and the U.K., there's also a "Stamp Duty" on property sales, which may exist along with or in place of annual property taxes.

Simple Calculation: A property's most recent assessed value was $20 million. The state and city charge property taxes for a total of 3% of the property's value, so the taxes here are $600K.

Capital Costs on the Real Estate Pro-Forma: Capital Expenditures, Tenant Improvements, and Leasing Commissions

"Capital costs" refer to spending on items that will provide a benefit for many years to come.

The main categories are Capital Expenditures, Tenant Improvements, and Leasing Commissions.

In the simple real estate pro-forma above, we group these items into a single line ("CapEx, TIs, and LCs"), but we calculate them separately and show them on separate lines in more complex models.

Capital Expenditures (CapEx) represent spending on items that are not specific to individual tenants. For example, CapEx might include spending on a new roof, elevator, or on the air conditioning or heating system in the building.

CapEx may vary greatly from year to year, depending on the property's age and condition.

Simple Calculation: If CapEx per Gross Square Foot is $5 and there are 50,000 Gross Square Feet in the property, CapEx is $250K.

Tenant Improvements (TIs) represent spending on items that are specific to individual tenants. Owners often pay for these items to customize the space and make it easier for tenants to move in. Examples might include additional walls and doors in an office suite.

Simple Calculation: If the TIs per Rentable Square Foot for a tenant are $50 and the tenant is renting 10,000 square feet, the TIs will be $500,000 upon initial move-in.

Those TIs will not recur each year – only when a tenant renews a lease, or a new lease begins.

Leasing Commissions (LCs) are payments made to commercial real estate brokerage and leasing companies to find new tenants and to get existing tenants to renew their leases (as some negotiation may be required).

LCs are almost always based on a percentage of the total lease value over the term of the lease.

Simple Calculation: A tenant signs a 5-year lease for 10,000 square feet that initially starts at $50 per rentable square foot and then increases to $52, $54, $56, and $58 over the term.

The Leasing Commissions will be 5% of the total lease value over these 5 years.

Therefore, the LCs will be 5% * ($50 + $52 + $54 + $56 + $58) * 10,000 = $135,000.

As with TIs, LCs are incurred only when a new tenant moves in, or an existing one renews.

NOTE: We simplified this calculation in the Excel example here and didn't account for lease escalations, but in a more complex version, we would.

CapEx, TI, and LC Reserves (AKA "Replacement Reserves" or "Capital Reserves")

From the descriptions above, you can immediately detect one big issue with these capital costs: they're often lumpy.

A property might have $200K in capital costs one year, then $0 the next year, then $800K, then $300K, and then $200K.

This pattern makes the property's cash flows fluctuate significantly, and it makes it difficult for owners to plan their long-term strategy.

So, nearly all properties use "Reserves" that aim to smooth out these fluctuations.

In the example directly above, the owners might estimate $1.5 million in capital costs over 5 years.

Based on that, they might then allocate $300K per year into these Reserves.

When capital costs are owed, the owners draw on the Reserves to pay for them.

If the Reserves cannot cover everything in one year, the remaining required funding will come out of the property's cash flow for that year.

Here's how it works over the first 3 years in the example above:

Year 1: Allocate $300K; spend $200K; Ending Reserves of $100K.
Year 2: Allocate $300K; spend $0; Ending Reserves of $400K.
Year 3: Allocate $300K; spend $800K (with $100K funded from cash flows since the Reserves are only up to $700K); Ending Reserves of $0.

There is significant disagreement over where the Reserve Allocations should be shown.

Some people argue that they should not affect the property's Net Operating Income (see below) because they are not true "cash costs" when they are allocated.

Therefore, this group argues, the Reserves should be deducted after the NOI line item.

Other people argue that you must deduct the Reserve Allocations before the NOI line item to reflect the true cost of owning the property.

We agree more with the second line of reasoning, so in this article and the case studies in our Real Estate Course, we assume that the Reserve Allocations reduce NOI.

Net Operating Income (NOI)

This item is similar to EBITDA for normal companies – a capital structure-neutral measure of core-business cash flow – but it's not the same.

For example, if you deduct the Reserve Allocations, as we do, then NOI partially reflects capital costs.

EBITDA, by contrast, never reflects capital costs because it excludes CapEx and D&A.

NOI is critical because properties are often valued based on their projected NOI divided by a selected "Capitalization Rate" (Cap Rate) or "Yield."

For example, if a property's projected NOI is $5 million and Cap Rates for similar properties in the area are 5%, this property might be worth $5 million / 5% = $100 million.

If your NOI figures are off, then your valuation will be off as well.

Capital Costs Paid from Reserves

This line item represents the capital costs that are covered by the Reserves in a given year.

If the Reserves are insufficient, this amount will not completely offset the capital costs – and that will reduce the property's cash flow:

Adjusted Net Operating Income (Adjusted NOI)

This is the item shown above: the property's NOI minus the capital costs not covered by the Reserves.

Even if you disagree with our treatment of the Reserves in NOI, you'd still have to deduct the Reserve Allocations in this section – so Adjusted NOI ends up being the same.

Adjusted NOI is similar to Unlevered Free Cash Flow for normal companies because it represents the cash flow generated by core-business operations after capital costs and re-investment, ignoring capital structure (i.e., interest expense).

But it's not quite the same because Adjusted NOI excludes income taxes, the Change in Working Capital, and several other items that go into Unlevered FCF.

Interest Expense on Debt

Nearly all property acquisitions and developments are funded partially by Debt, so you will almost always see the Interest Expense on that Debt on the pro-forma.

You could use the IPMT function in Excel to calculate the Interest Expense, or you could calculate it manually based on Interest Rate * Debt Balance.

Debt Principal Repayment

This one is the other component of Debt Service: repaying the principal over time.

You could use the PPMT function, the counterpart of IPMT, to calculate this one, or you could do it manually based on percentages; it just depends on the terms of the Debt and your model.

Cash Flow to Equity Investors

This is the final line on the typical real estate pro-forma, and it represents the amount available for distribution to the equity investors (the "owners") in the property.

Companies often avoid dividends and accumulate large cash balances, but properties tend to distribute their annual cash flow unless they need it for something major.

Additional Items and the Real Estate Pro-Forma for Multifamily and Hotel Properties

In this article, we've focused on a real estate pro-forma for a mixedoffice/retailproperty.

This same pro-forma also applies to industrial properties because lease terms, revenue, and expenses are similar.

However, it would be slightly different for a multifamily (apartment) property, and it would be significantly different for a hotel.

Multifamily leases are shorter, simpler, and more similar to each other, and Tenant Improvements and Leasing Commissions tend to be far less significant as a result.

Tenants are often responsible for Utilities, but not much else, so Expense Reimbursements might be labeled just "Utility Reimbursements."

And since individuals rather than companies are the tenants, there is a substantial risk of non-payment. That explains why you'll see "Bad Debt" as a deduction in the Revenue section.

Hotels are so different that we'd probably need a separate article to explain the hotel pro-forma fully.

But in short, it's more like the Income Statement of a real company, with revenue in categories such as Rooms and Food & Beverage, and Fixed vs. Variable Expenses.

Hotels require far more staff to operate since they're more like "real companies," so expense categories like Sales & Marketing and General & Administrative are much bigger.

A few other, more advanced items on the real estate pro-forma include Loss to Lease and Percentage Rent.

Loss to Lease represents the difference between in-place rents and market rents for tenants who are on below-market leases.

For example, if the market rent is $50 per square foot per year, but a tenant occupying 5,000 square feet is paying $45 per square foot per year, the Loss to Lease would be ($50 – $45) * 5,000, or $25,000 (and shown with a negative sign on the pro-forma).

You can determine what the proper rates might be by conducting comparative market analysis.

Percentage Rent is for retail tenants who pay a percentage of their monthly gross sales to the property owners, in addition to the traditional fixed rent.

Retailers might agree to this term in exchange for lower base rent.

With Debt Service, there is non-cash interest for properties, just as there is for companies (it's also called "Accrued" or "Paid-in-Kind" or "PIK" interest).

And in some cases, the full loan proceeds will not be distributed upfront, but rather "held back" and distributed when certain milestones are met.

That creates differences in the Interest and Principal Repayment calculations and the returns for lenders.

What's NOT on the Real Estate Pro-Forma: Taxes and Depreciation

Before wrapping up, I want to address two topics that generate a lot of questions:

"1) Why does your real estate pro-forma not include income taxes?

2) And what about Depreciation? Isn't it huge in real estate?"

You typically exclude income taxes because, as mentioned above, properties are often owned by pass-through entities.

If you form a partnership to acquire apartment buildings, each partner will pay taxes individually, but the partnership as a whole will not pay corporate-level taxes.

Each partner's taxes and rates will vary greatly based on their circumstances, so there's no point in including these on the pro-forma.

Depreciation does not appear on the pro-forma because it is a non-cash expense that exists to "smooth out" CapEx and, partially, for tax purposes.

But since the pro-forma excludes taxes, there's no point in including an item like Depreciation that is relevant mostly for tax purposes.

If we were modeling a taxable entity, such as a C corporation that owned properties, then we might worry about income taxes and Depreciation in the pro-forma.

The Real Estate Pro-Forma: What Next?

Like any financial model, the pro-forma is simply a tool.

If you use it correctly and come up with reasonable assumptions, it can tell you if a deal is potentially viable or completely implausible.

But it cannot tell you whether or not you'll earn an out-sized return on a property acquisition or development.

"Real life" comes into play in a major way there; a deal might look great on paper, but if the property manager is incompetent and toxic rats invade the property, all bets are off.

However, a properly constructed pro-forma can help you evaluate the risks of a deal and see what might happen if something goes wrong, and how you might reduce those risks.

Coming up next, we'll go through a series of real estate case studies that demonstrate how to use the pro-forma to make investment decisions, as you might in real estate private equity.

Yes, we've released real estate case studies in the past, but the ones coming up are newer, better, and more representative of real-life exercises.

And if you want more before then, there's the full Real Estate Financial Modeling course.

kellnerfloace.blogspot.com

Source: https://mergersandinquisitions.com/real-estate-pro-forma/