Watch Me Expand the Home Construction Pro Forma
I’m regularly asked to expand my single family home construction pro forma to analyze the construction of more than one home. I’ve contemplated adding that functionality to the model, but it is such a custom task that no template could really cover even a majority of multi-home scenarios. So instead, I thought I’d show you how to expand the model yourself.
If you’re not familiar with my Single Family Home Construction Model, it’s a back-of-the-envelope model meant to be used by home builders to assess the viability of a single family home construction project. It’s simple but quite robust, allowing one to analyze a potential build in a matter of minutes.
This Watch Me Build video takes that model and adds three more homes to the analysis.
Are you an Accelerator member? See the ‘Modeling Development Cash Flow course in the core curriculum. Additionally, if you’re an Accelerator Advanced Member check out course 4 of the ‘Advanced Modeling – Development’ endorsement for additional techniques for modeling construction cash flows. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.
Roll-Up Property-Level Cash Flows to Project-Level Cash Flows
This exercise relies on a basic framework whereby we first model property-level cash flows for each of the home builds, and then roll up those cash flows to project-level cash flows. Returns are calculated both at the individual property-level as well as on the aggregate. This same framework is used to model portfolio-level and mixed-use projects.
In the case of this exercise, I use the following worksheets:
Property-level worksheets. I use one worksheet for each home we intend to build. It’s much easier to analyze multiple properties in one project when the inputs, calculation modules, and outputs for each property are housed on one worksheet each. In fact, I intentionally kept the Single Family Home Construction Pro Forma to one worksheet to make expanding it to include more homes that much easier.
Summary worksheet. I then create a Summary worksheet where the project-level details are summarized and the cash flows for each of the properties are added together to arrive at project-level aggregate cash flows. Returns are then summarized at the project-level on this worksheet.
Using Multi-Sheet Ranges in Excel
This exercise relies on a technique in Excel whereby the same cells across a range of Worksheets are added together. By using this technique, we avoid having to manually add together each cell in each worksheet that we want to include on the Summary tab.
Additionally, using this technique allows us to quickly add new homes to the analysis by simply duplicating an existing property-level worksheet and placing it inside of the range. Thus, modeling a 10, 20, or even 100 home project is as simple as copying new worksheets and adding them to the range.
The one caveat to this technique is that the cells being added together must be in the exact same location in each worksheet. Or in other words, if you add or delete a column or row in any of the property worksheets, that property will not roll up properly to the summary worksheet.
The Watch Me Build Video – Pro Forma for Multiple Home Building Projects
Before watching the video, download the template and completed file for this Watch Me Build. Then follow along as I expand the model.
As I walk through expanding the model, do your best to follow along. Once you’ve completed the video, attempt to complete the exercise a second time. Try expanding the model to include elements not included in the Watch Me Build, such as additional property worksheets and report tabs.
As always, if you have any questions or comments, please don’t hesitate to reach out.
Download the Source Files for this Watch Me Build Exercise
To make these files accessible to everyone, they are offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – similar real estate training exercises sell for $100 – $300+). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
Version Notes
v1.0
- Initial release
