Who are you? 

Developer? Development Lender?  Qualified Opportunity Zone Fund (“QOF”)?

Actually, your role doesn’t matter.  If you oversee Excel modeling for any of the above, chances are you end up dealing with not only your own internal development Excel models, but someone else’s as well. Maybe it’s a model you were bequeathed by the person previously in your position, or it is one you picked up off the heap of prior deals that was close enough.  “We’ll just tweak that last one” are famous last words, often of someone who isn’t responsible for the tweaking.

At Realogic, we have been dealing with Excel development models for the better part of our nearly 30-year existence. When we first started the company, it was always hard for us to efficiently develop and use Excel models in a cost-effective manner. Why? Because we had not embraced standardization.  We were doing what you are likely doing:  Need a model for a multi-family project, for example? Then let’s start with the last Excel model used, even though it was for a mixed-use development with multiple phases.  This just isn’t smart or efficient.

One area of commercial real estate where Realogic is seeing a tremendous amount of interest and activity right now is Opportunity Zones. Even in this uncertain economy, developers, investors and fund and asset managers continue to flock to QOZs and QOFs because of the tremendous tax benefits they offer. As a result, QOF development analysts, including our own, are dealing with an almost overwhelming number of deals.

According to the EDA, over $524 million had been invested in QOZ projects as of May 26, 2020, with another $171 million invested in regional or nearby projects supporting Opportunity Zones.* So, if they are going to accurately underwrite deals, whether it be one or hundreds, analysts must embrace the standardization of modeling practices.  A standardized approach is necessary if your analysts are spending more time evaluating a spreadsheet for integrity rather than evaluating the specific deal parameters and returns.  Realogic has been developing our standardized and modular approach for many years, and we have found it offers many significant benefits, including:

  • Accuracy: Do not be dependent upon someone else’s model! Your firm should have a model that has been vetted for accuracy, project after project. Unfortunately, auditing someone else’s formulas instead of vetting their underwriting assumptions is often considered a cost of doing business. However, that cost can be reduced or even eliminated completely.
  • Accuracy Pt II: Tested platforms behave as they should, no unexpected gotchas. We have all heard that the majority of Excel models have errors.  However, these models are often not standardized models, nor are they back tested models confirmed against prior results and specific test cases.
  • Speed: Familiarity with a model allows users to work quickly.  A well organized and flexible model allows users to quickly ascertain if proposed investments are in line with their return expectations.  Development models should be architected with the ability to grow as ideas take shape.  Developments often start as low detail, high expectation projections and quickly evolve into more detailed combinations of reality and projections.
  • Training: Training time is reduced when one institutionalized way of modeling is introduced. Consistent methodologies and best practices lead to institutional knowledge and less time spent training staff.
  • Institutional Knowledge: The combination of each of the above components becomes part of your company’s DNA.

Our approach to standardized Excel development is multi-fold, but simple in concept:  we’ve learned which components of each model are the same for each deal and which components differ.  This will clearly change from organization to organization, but our experience should help give you some ideas.


The first thing to standardize that was obvious to us was the Operating Component. This is the product type under development, such as hotel, multi-family, commercial, student housing, senior living, mixed use, multi-phase, etc.  In our case, it happened to be yes to all of them.  We had to break the operating component into its own worksheet or set of worksheets, if more than one.  There are a few major advantages to this method:

Advantage One: Excel modelers often try to get to a lofty goal of a single page of input. However, we feel this approach is too constraining.  Well-designed worksheets will increase accuracy and speed via a logical layout.  Combining the development or capital structure into one worksheet, along with the future operating components, leads to a tangled mess.  Think of an Excel worksheet as a specific tool designed for a specific job. Data from other worksheets can be brought over easily and efficiently.

Advantage Two: A multi-worksheet approach allows for a clean framework for mixed-use or multi-phased developments.  Need to add another phase? Add another worksheet.  Need to add a retail component to your office park? Simply add another worksheet.

Advantage Three: The worksheet approach is a simple way to achieve sensitivity analysis and turn leasing scenarios or phases on and off with ease.

Once you separate the operating components and turn them into interchangeable modules, the similarities of a development project start to become visible.

Development/Construction should be its own module or worksheet in Excel.  There are common components regardless of product type being developed:

We have contemplated the nature of each and will be addressing best practices about modeling each of these components in future blog posts. Specific project details may change on a project by project basis, but the need to set up a series of events bounded by a time frame such as the Analysis Start, Land Hold, Construction, Lease Up and Operations don’t change.  Can we think of every possible deal term ahead of time?  No.  However, we can create unlimited user defined time frames and uses throughout the model if it’s architected correctly.

We could continue with a discussion about standardized Executive Summaries, Sources and Uses, Returns and Waterfalls.  All of these can be modularized and standardized for re-use.  Analyzing a stream of cash flows will not change from product type to product type.  Some will be smooth, others lumpy, and others will have a set of singular events. Modeling a multi-tiered waterfall, with profit catch-ups based upon monthly, quarterly or annual timing and compounding, along with LP and GP return calculations on both a gross and net basis sounds hard—and it is. But it’s much harder to build a new model and test it every time you need something slightly different.  Improving your team’s performance is incremental, but like a lot of things, you just need to start at the beginning.  As mentioned earlier, we have been working on our standardization process for a long time. We didn’t accomplish it overnight, but now our team is able to accurately and effectively evaluate the dozens of deals our QOF clients are seeing in non-standardized models.

You can learn more about Realogic’s standardized, modular Excel models here. Or, maybe you have a project or set of projects that do not fit neatly into the categories mentioned above. If so, we would love for you to share it with us so we can better understand how we might be able to help you. Development pro-formas have long been the domain of Microsoft Excel. Before you start, mentally run down the modules that you have standardized and ask if a tried and true component can be re-used prior to unleashing your creativity.

*Sources:

Eda.gov/opportunity-zones
Statsamerica.org/opportunity