At The Center Of It All: The Pro Forma Model

by Kevin Kirkeby

As far as true confessions go, this is rather lightweight: I love spreadsheets and financial models. There’s something about the designing, linking and debugging that is satisfying, even after several decades. There are always new analytic approaches and different formulas, not to mention Excel updates that add even more features.

A review of the pro forma model for an investment program is not simply one of the multiple components in a due diligence review. Yes, it offers us a ballpark return that an investor might expect to earn on the investment. I, however, as a biased (toward the importance of models) analyst, offer that a review of the pro forma is much bigger than that. It is the piece that ties the whole deal together. The model structure and its assumptions lend insight into the sponsor’s business plan and its projected compensation. The model reveals where the manager plans to generate returns for investors, via operational adjustments like renovations and cost savings, or through favorable financing. The model also provides considerable insight as to where risks might lurk. This is all true even as the model is an educated, but huge guess at where asset and market conditions will be over the next several years. The model underpins the entire narrative.

The pro forma models we review come in myriad shapes and sizes, ranging from single-page, basic models to spreadsheets with dozens of linked worksheets and cells with formula strings that are six lines long. The accompanying dialogue with sponsors concerning the level of detail ranges from “it’s proprietary” to “our financial modeling guy is really good.” Though, sometimes it feels like they want to overwhelm the reviewer with unnecessary data and complexity. This, itself, is a potential red flag and prompts us to examine further.

Regardless of where in the spectrum the pro forma model falls, the ultimate end goal is to demonstrate the economics of the deal. Let’s examine how in various ways.

Start with the most foundational inputs

Assume we are looking at a projection model for real estate investment program. As a first step in our review of the model, we look at the operating assumptions to ensure they align with on-the-ground conditions. Notably, are trends in revenue components (rent, occupancy) consistent with data checks for the neighborhood and nearby comparables? In instances where growth is projected to exceed market trends, we dig further to see if there is heavy upfront capital expenditures or cost passthroughs, among other factors that could support outsized growth. We look for corroboration in the offering documents, our own site visit, and always call upon management for additional commentary regarding their business plan.

There’s obviously more to achieving favorable income and cash flow than simply generating revenue. We want to examine what has happened on the expense side, and if anything is expected to change. There is often room to take out costs, or to boost marketing spend. We look at the maintenance and repair budget in relation to property age and the level of related reserves, along with planned capital improvements throughout the anticipated holding period. One thing is certain—operating expenses are not linear. They can and do fluctuate from period to period. The model provides many clues as to how conservatively or aggressively management intends to operate the property.

It’s not just about the asset fundamentals

Once we have an understanding of property dynamics and the expected net operating income, we then examine how the property is financed and how that impacts cash needs. Unlike many of the components of operating expenses, debt payments, if not fixed, should still fluctuate less. That said, decisions about debt levels, floating or fixed interest rates, interest only or principal amortizing all have a bearing on potential risks and returns. For example, interest-only payments free up cash early in the investment, but heighten the importance of disposition price to the return of investor capital. Part of our review process involves ratio analysis, including a review of debt-to-equity and interest coverage, as well as the resulting figures like cash-on-cash yield. While this blog post is centered on the pro forma model, it warrants mention that non-model related considerations are present, too. In the case of debt, this involves examining aspects like terms and covenants, plus guarantees and pre-payment or late penalties.

Don’t forget the reality check

So, suppose the deal pencils out. The math shows, in theory, that if the program acquires properties at a 6% cap rate, places 75% leverage on them, and is able to grow net operating income on average 3% over five years, the fund will achieve targeted returns for investors. But what if the opportunity pipeline the sponsor provides shows that it is evaluating properties to be acquired at cap rates near 5%? What if the level of leverage is not realistically obtainable in the market?

Assumptions not lining up with reality in the market can be a red flag.

Stress-testing for confidence

The speed and severity at which the COVID-19 pandemic impacted the economy highlights the need for sensitivity analysis. With holding periods for many private offerings ranging from 5 to 10 years, stretching past the reasonably foreseeable future, we want to know what level of shock the business model can withstand. The pro forma model is ideal for understanding how (even relatively small) changes to variables like rent, occupancy or operating expenses will affect investment returns. By varying the different inputs we are better able to identify areas of risk and sensitivity. For example, if an aggressive program to raise rents does proceed at the expected pace, is there enough cushion built in to cover debt payments? In the case of a DST, is the Master Tenant capitalization likely to be sufficient? These assessments influence other portions of the due diligence review, like if rent payments can be deferred, or if the asset management fee is subordinated to a certain level of annual return.

This blog post is by no means an exhaustive representation of FactRight’s due diligence process. But it demonstrates how we use pro forma models to cross check the business plan, evaluate the reasonableness of assumptions, and rank the left tail risks. There are a tremendous number of information nuggets to be found in the model. We become more comfortable with an investment story as we see more of these underlying details aligning.


Contact Information:

Kevin Kirkeby


(612) 284-3119

Sign Up for Blog Updates