Several times in the last month, I’ve been asked to tackle an interesting real estate financial modeling challenge – pare actual construction draw cash flows with s-curve cash flow forecasts to create an Actuals + Forecast Construction Draw schedule.
The real challenge in this exercise, is to have the actual construction draws transition into the s-curve cash flow forecasts such that the s-curve forecast continues from the point where the actuals end. So I decided to spend some time this weekend taking on this challenge. This blog post, together with the accompanying video tutorial and Excel Workbook, are the results of that effort.
Methodology Behind the Model – Getting to a Revised S-Curve
The model is separated into six sections. The first three sections are color coded blue and contain the inputs and main outputs of the model. The sections are labeled ‘Inputs’, ‘Actual’, and ‘Actual+Forecast’. In most cases, these are the only three sections you’ll look at.
Reminder: Blue font cells are required inputs, black font cells are calculations and outputs. Only edit blue font cells.
- The Inputs section contains the construction line items (e.g. land costs, hard costs, etc), budget amounts, forecast method (e.g. straight-line vs. s-curve), and cash flow timing (start month vs end month).
- The Actual section is where you enter actual cash flows. Enter the Actual Month as of today in cell C6, and then fill out the Actuals section with the monthly actual cash flows by line item through to today.
- With the Input and Actual sections complete, the Actual+Forecast section shows the combination of Actual cash flows through the current month, together with the cash flow forecasts thereafter through to the end of the construction period. Months considered actual are labeled with an ‘A’ in row 8 of the Actual+Forecast section while months considered a forecast are labeled with an ‘E’.
The last three sections are color coded grey, and contain the s-curve logic. They are hidden by default, but are grouped such that to reveal the sections simply click the plus sign at the top of column BQ. These sections are labeled ‘Initial S-Curve Ratios’, ‘Revised S-Curve Ratios’, and ‘Forecast S-Curve’.
- The Initial S-Curve Ratios section calculates the s-curve as a percentage per month, with no actual cash flows figured in.
- The Revised S-Curve Ratios section takes into account the actual cash flows, but does not adjust the s-curve as a percentage by the actual amount spent.
- The Forecast S-Curve adjusts the percentage per month to account for the actual cash flows, and then calculates the new s-curve forecast. These cash flows are then linked back to the ‘Actual+Forecast’ section to properly blend the actual cash flows with the newly revised s-curve forecast.
The trick in the revised s-curve calculation was to account for the variance in initial forecast cash flow versus actual cash flow and then adjust the remaining s-curve forecast to account for the remaining budget. This was done using ‘Remaining Ratio’ and ‘S-Curve Multiple’ concepts, combined with the Revised S-Curve Ratios (i.e. Initial S-Curve Ratios less actual periods).
The result is, a combined Actuals + Forecast draw schedule that blends what has been spent to date with a revised forecast for what is left to be spent.
Using this Actuals + Forecast Construction Draw Schedule in Your Model
First, download the model (link below) and then open up the Workbook. Keep in mind, the Excel Workbook is a module that you would plugin to your existing development model. It is not standalone.
You’ll notice right off the bat when you open the Workbook, that I’ve only include four line items. However, the calculations for each line item are made in the same row on the Budget tab. So to add items is easy. Simply insert a new row between either rows 11 and 12, 12 and 13, or 13 and 14, and copy the complete row from above down.
Then, link the Total row under the ‘Actual+Forecast’ section (row 15) to your DCF as that is the amount spent (actual+forecast) to develop the property (e.g. the negative cash flow in your net unlevered cash flow line).
The Actuals + Forecast Construction Draw Schedule – Video Tutorial
To compliment the above, I’ve created a short video demonstrating the methodology behind the model and how you might implement it into your own development model.
Download The Model
To make this model accessible to everyone, it is 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 - typical real estate models sell for $100 - $300 each). 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.
Note (especially for Gmail users): The model is sent via email and occasionally is blocked by spam filters. If you don't see the email arrive within five minutes, check your spam folder.
Actual + Forecast Construction Draw Schedule with S-Curve (v1.01)
- Microsoft Excel Workbook demonstrating a method for modeling both Actual and Forecast development cash flows in your own real estate development model
- Six sections calculating actual cash flows, forecast cash flows, and the combination of actual and forecast cash flows
- Up to 24 periods, easily expandable
- Includes 4 line items, but built to easily add more if you choose to plug this module into your own model
This Excel file (the "software") is distributed as is, completely without warranty or service support. www.AdventuresinCRE.com and its authors are not liable for the condition or performance of the software. www.AdventuresinCRE.com owns the copyright and grants users a perpetual, irrevocable, worldwide, non-exclusive, royalty-free license with respect to the software as set forth below.
www.AdventuresinCRE.com and its authors hereby disclaim all implied warranties. www.AdventuresinCRE.com grants the users the right to modify, copy, and redistribute the software and documentation, both within the user's organization and externally, subject to the following restrictions:
1. The users agree not to charge others for use of the software, regardless of whether users have altered and/or improved the software in anyway.
2. In any use of the software, the users agree to acknowledge the www.AdventuresinCRE.com authors that developed the software.
3. The users agree to obey all government restrictions governing redistribution or export of the software.
4. The users agree to reproduce any copyright notice which appears on the software and documentation on any copy or modification of such made available to others.
About the Author: Born and raised in the Northwest United States, Spencer Burton has over 15 years of real estate investment and development experience. In his current position, Spencer assesses new acquisition, development, and debt opportunities for a $45bn real estate fund. He resides in Dallas, TX.