Excel Modelling Case Study: Financial Feasibility for a Townhouse Development
A detailed case study showcasing how Excel modelling was used to create a financial feasibility analysis for a townhouse development project.
Introduction
Property developers need robust financial models to assess the feasibility of residential development projects. Excel is the ideal platform for building these models, offering flexibility to handle everything from construction cost schedules to financing structures and scenario analysis. This guide walks through how a financial feasibility model for a 12-townhouse development might be structured.
Project Overview
Consider a hypothetical development scenario: a 12-townhouse project on a 2,500 sqm site, with standard and premium unit types. The developer needs to assess profitability under various scenarios and determine the optimal financing structure.
Key Objectives:
- Assess Financial Feasibility: Determine project profitability under various scenarios.
- Analyse Financing Options: Develop a debt-equity structure aligned with cash flow needs.
- Evaluate Sensitivity: Identify potential risks in cost escalation and market conditions.
Note: The following analysis is an illustrative case for demonstration purposes. It does not represent any specific development or client engagement. All figures are hypothetical.
Approach
The financial model is structured in Excel, focusing on clarity, flexibility, and actionable insights.
1. Cost Breakdown Schedule
To account for all expenditures, create a detailed table categorising costs into labour, materials, approvals, and contingencies.
Example Cost Breakdown:
| Expense Category | Subcategory | Estimated Cost ($) |
|---|---|---|
| Labour Costs | Contractors | 1,200,000 |
| Site Supervisors | 300,000 | |
| Materials | Concrete and Steel | 800,000 |
| Electrical Equipment | 150,000 | |
| Approvals | Council Fees | 50,000 |
| Zoning Compliance | 25,000 | |
| Contingencies | Miscellaneous | 100,000 |
Tools Used:
- Excel Formulas: Applied
=SUM()to aggregate totals for each category. - Dynamic Ranges: Enabled updates as new cost estimates were added.
2. Cash Flow Forecasting
To ensure adequate cash flow, develop a forecast tracking inflows (from sales) and outflows (construction and loan repayments).
Cash Flow Forecast Example:
| Period | Opening Balance ($) | Inflows ($) | Outflows ($) | Closing Balance ($) |
|---|---|---|---|---|
| Q1 | 500,000 | 0 | 1,200,000 | -700,000 |
| Q2 | -700,000 | 2,400,000 | 800,000 | 900,000 |
| Q3 | 900,000 | 1,200,000 | 600,000 | 1,500,000 |
Tools Used:
- Conditional Formatting: Highlighted negative balances to flag funding gaps.
- Loan Drawdown Triggers: Automate the timing of debt drawdowns using
IF()formulas based on negative cash flow.
3. Depreciation and Tax Modelling
Model depreciation for tax purposes, factoring in capital works deductions under Australian tax law.
Depreciation Schedule:
| Asset | Cost ($) | Useful Life (Years) | Annual Depreciation ($) |
|---|---|---|---|
| Building Structure | 5,000,000 | 25 | 200,000 |
| Fixtures & Fittings | 300,000 | 10 | 30,000 |
Tax Benefits:
The model calculates annual tax savings by applying the developer's marginal tax rate to depreciation expenses.
4. Debt-Equity Financing Analysis
To manage the funding needs, develop a debt-equity financing plan that balances risk and returns.
Loan Schedule:
| Period | Opening Balance ($) | Interest ($) | Repayment ($) | Closing Balance ($) |
|---|---|---|---|---|
| Q1 | 1,000,000 | 25,000 | 200,000 | 825,000 |
| Q2 | 825,000 | 20,625 | 200,000 | 645,625 |
Debt-Equity Ratio:
Model multiple scenarios with varying equity contributions to assess the impact on interest costs and return on investment.
5. Revenue and Growth Projections
The revenue forecast includes unit sales and expected growth in property values.
Revenue Projections:
| Unit Type | Units Sold | Sale Price per Unit ($) | Total Revenue ($) |
|---|---|---|---|
| Standard Units | 8 | 900,000 | 7,200,000 |
| Premium Units | 4 | 1,200,000 | 4,800,000 |
Tools Used:
- Growth Rate Projections: Apply
=FV()to model potential appreciation in property values.
6. Scenario and Sensitivity Analysis
To evaluate risks, perform a scenario analysis focusing on:
- Cost Escalations: Material price increases by 10-15%.
- Sales Delays: Extended sales timelines by 6 months.
- Market Conditions: Variability in interest rates.
Scenario Table:
| Scenario | Material Costs ($) | Interest Rate (%) | Total Profit ($) |
|---|---|---|---|
| Best Case | 2,000,000 | 5.0 | 2,500,000 |
| Base Case | 2,200,000 | 5.5 | 2,000,000 |
| Worst Case | 2,500,000 | 6.0 | 1,500,000 |
Tools Used:
- Data Tables: Automate scenario comparisons using Excel's
What-If Analysis.
Financing Structure Sensitivity
The debt-to-equity ratio has a significant impact on project returns. In this scenario, a 60:40 debt-equity split generates an IRR of 18% in the base case. Increasing leverage to 75:25 raises equity returns to 22% but introduces higher risk if sales are delayed. The sensitivity table shows that a 6-month sales delay under 75:25 leverage reduces IRR to just 11% - nearly wiping out the equity return advantage.
Use Excel's data table feature to model the full range of leverage scenarios and find the point where additional risk outweighs the return benefit.
Key Modelling Takeaways
- Sensitivity is driven by construction costs: In this scenario, a 15% cost overrun reduces profit by 25%, making cost control the most important risk management lever
- Staggered sales strategy: Rather than selling all units at completion, a staggered approach (selling 60% during construction, 40% after completion) reduces holding costs and interest exposure
- Contingency allocation: A 10% contingency ($250K in this scenario) is sufficient to absorb typical cost variances in residential development
Conclusion
This case demonstrates how Excel modelling can empower property developers to make informed decisions. From detailed cost breakdowns to scenario analysis, the model provides clarity and actionable insights that enhance project planning. If you're a property developer looking to streamline your financial planning, adopting robust Excel models is an invaluable step toward success.
For more practical guides on financial modelling and property analysis, visit ExcelWiz.com.au.