What To Expect in a Real Estate Financial Modeling Case Study

Today, more than ever before, commercial real estate investment, brokerage, and lending firms are testing job candidates applying for analytical roles by including an Excel modeling case study as part of the interview process.

And even though it’s tough to tell exactly what a case study is going to look like from company to company, I’ve seen enough of these over the years to know that most commercial real estate Excel exams consist of a pretty similar structure.

So to help you get clear on what you should be focusing on to prepare for an upcoming Excel modeling exam, this article walks through the most common components of a real estate financial modeling case study, and the things you’ll need to be ready for to pass with flying colors.

If video is more your thing, you can watch the video version of this article here:

Real Estate Financial Modeling Case Study Overview

To make sure job candidates are required to prove out the technical skills they’ve added to their resumes, many commercial real estate investment, brokerage, and lending firms will give their analyst candidates an Excel modeling exam during the interview process.

And often, these exams are going to be designed in a case study format, including the analysis of a sample acquisition or development opportunity, and a modeling task with associated questions.

Pro Forma Cash Flow Projections

The first thing that’s going to be tested in almost all real estate financial modeling case studies is your ability to create a pro forma cash flow model from scratch in Excel, given information on current property operations and assumptions about future performance.

Companies will often provide candidates with an assumed purchase price, in-place revenue and expense metrics, projected capital costs, loan terms, and sale assumptions, with candidates being required to build out cash flow projections over a 5-10 year hold period.

Companies also expect these models to be built out dynamically, meaning that you’ll need to create formulas that drive the cash flow projections in your model, and allow these values to change automatically if changes to assumptions are made.

Key Operating & Return Metrics

The second part of an Excel modeling case study involves using the cash flows you’ve built to calculate key operating and return metrics on the deal.

In my experience, the most common metrics you’ll see tested on a case study exam are the following:

• IRR
• Equity Multiple
• Cash-on-Cash Return
• Debt Service Coverage Ratio (DSCR)
• Debt Yield

Companies will also often ask candidates to calculate key return metrics on both an unlevered and levered basis to see the impact of debt on investment performance, with unlevered metrics measuring returns before factoring in loan proceeds, loan payments, or the ultimate payoff at the end of the loan term.

Sensitivity Analysis & Property Valuation

In many cases, companies will also require candidates to run an additional layer of analysis using their cash flow projections, often through building out a sensitivity analysis and solving for a valuation based on targeted investor returns.

For the sensitivity analysis, this is usually going to involve looking at the impact of changes to things like the assumed exit cap rate, hold period, annual revenue growth rate, or even different loan amounts on key investor return metrics like the IRR, equity multiple, or cash-on-cash return.

In addition to this, you may also be asked to value the property based on a target return metric (usually an IRR or equity multiple value), testing your ability to back-solve for a maximum offer price based on required investor returns.

Real Estate Equity Waterfall Structures

Once you’ve built your project-level cash flows, many firms (especially in the private equity space) will test your ability to model JV equity partnership structures, including a waterfall analysis within an exam prompt.

This tends to be one of the most advanced modeling topics in all of commercial real estate, and while this isn’t usually going to be at the core of what you’re evaluated on at most CRE firms, this is still going to be used as a comparison tool between you and other candidates.

When a waterfall structure is included on a test, this is often going to include an IRR-based preferred return, and you’ll need to calculate cash flow distributions to equity partners both up to and over that preferred return target.

In a case study setting, most waterfall structures aren’t going to include multiple layers of additional hurdle rates above the preferred return, so when preparing for a test, I would focus most on understanding how to calculate a preferred rate of return using IRR-based hurdles, and how to calculate cash flows over that preferred return based on an assumed promoted interest percentage.

If you’re not familiar with the term promoted interest, this represents the percentage of cash flows over the preferred return that are allocated directly to the general partner (GP) on a deal, in addition to the percentage of cash flows they would have otherwise been allocated purely based on their initial equity interest. And with that, you’ll want to make sure you can calculate the cash flow split between two partners on a deal, given a promoted interest percentage and each partner’s initial ownership interest in the project.

Qualitative Analysis

Once you’ve modeled out project-level cash flows and partnership-level cash flows, an interview case study exam is often going to wrap up with a qualitative analysis that tests your ability to interpret and explain everything you’ve built.

Along with providing general operating assumptions, some firms will also provide additional documentation at the start of an exam, including things like an offering memorandum, a rent roll, and/or T-12 financial statements, which are all intended to give a candidate a deeper level of insight into the property overall.

When this is the case, the final requirement within a case study exam is often to tie the analysis together by doing things like listing out potential downsides of the deal, describing how these risks can be mitigated, deciding on an appropriate valuation for the property, and ultimately providing an investment recommendation for the team.

The goal here is for the company to be able to see that you can think critically about your own work in order to spot patterns, identify risks, and uncover opportunities.

Where To Find Practice Excel Modeling Case Study Exams

If you’re preparing for an upcoming Excel modeling case study, or you just want to get ahead of the process and make sure you’re ready when the time comes, make sure to check out our all-in-one membership training platform, Break Into CRE Academy.

A membership to the Academy will give you instant access to over 120 hours of video training on real estate financial modeling and analysis, you’ll get access to hundreds of practice Excel interview exam questions, sample acquisition case studies, and you’ll also get access to the Break Into CRE Analyst Certification Exam. This exam covers topics like real estate pro forma and development modeling, commercial real estate lease modeling, equity waterfall modeling, and many other real estate financial analysis concepts that will help you prove to employers that you have what it takes to tackle the responsibilities of an analyst or associate at a top real estate firm.

As always, thanks so much for reading, and make sure to check out the Break Into CRE YouTube channel for more content that can help you take the next step in your real estate career.