Construction, Financial Model, Real Estate

Budget, Budgeting, Cash Flow Analysis, Cash Flow Projections, Cash on Cash Yield, Cash-on-Cash Multiple, Consolidation, Controlling, Cost Calculation, Cost Projections, Dashboard, DCF Model, Excel, Financial Debt, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Forecast, Forecasting, Interest Accrual, Investment Multiple, Investor Cash Flows, Investors, IRR (Internal Rate of Return), Management, Profitability Analysis, Project Management, Return on Investment (ROI), Startup Financial Models, Variance Analysis

 

Video Tutorial:

One of the hardest things to track is when a developer is building out a project and selling previously completed phases while new construction is still happening. This is the core problem that was addressed when designing this financial model template. Also, the data entry on this is a bit different. Normally there will be some kind of assumptions tab to figure out when various values should fall into various months for costs and sales proceeds. In this template, the data can be entered across any number of months on the actual model tabs.

There are up to 3 phases that have their own sales proceeds (based on units sold / average unit price in each month) and soft/hard cost sections. The idea is to plan out the expected budget of all phases on a single model tab. The same can be done on a separate tab for ‘actuals’ as those happen. On each of those tabs, the line items will be totaled on the far left of the tab so at any given time the user can see the total budgeted amount for each line item as well as the actual for that line item and the % the actual is of the budget. This is included in the ‘equity requirement’ area as well.

There are three ‘phase’ tabs that automatically pull the sub-totals in so that they can be analyzed individually if needed. Then the total soft costs are also pulled into their own tab.

The more advanced logic comes in when figuring out the amount of debt vs. equity that is needed. That is based on a user-defined LTV, which will take any months where the costs are greater than the sales proceeds (most months in a construction project) and split it up between a loan and equity. If there is not going to be debt, the LTV % can be set to 0.

The interest reserve is entered along with the other hard cost items. That is always the last row for each phase section because it is referenced against interest accrued in order to track if any more interest must be paid in the end or if some of the reserves should be credited back when all the debt has been repaid.

There are three summary charts (added after the video was done) that show the budget vs. actual for equity requirement, cash flow, and equity position across 48 months’ time. Also, the summary tab shows the total proceeds, total equity requirement, and the resulting IRR, ROI, equity multiple, and net proceeds from the entire development project.

To use this excel template, the user will only adjust cells with blue text in a light yellow shade.