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
When a local property developer approached us to assess the financial feasibility of a 12-townhouse development in Sydney, they needed a comprehensive model to evaluate potential profitability and financing requirements. Using Excel, we developed a robust financial model tailored to their needs. This case study walks you through the process, highlighting key techniques and the value delivered.
Project Overview
The developer planned to construct 12 high-quality townhouses on a 2,500 sqm site. Key challenges included managing rising construction costs, securing adequate financing, and projecting rental yields in a competitive market.
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.
Approach
We structured the financial model in Excel, focusing on clarity, flexibility, and actionable insights. Below is a breakdown of the components we developed.
1. Cost Breakdown Schedule
To account for all expenditures, we created 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, we developed 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: Automated the timing of debt drawdowns using
IF()formulas based on negative cash flow.
3. Depreciation and Tax Modelling
We modelled 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 calculated annual tax savings by applying the developer's marginal tax rate to depreciation expenses.
4. Debt-Equity Financing Analysis
To manage the funding needs, we developed a debt-equity financing plan that balanced 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:
We modelled 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 included 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: Applied
=FV()to model potential appreciation in property values.
6. Scenario and Sensitivity Analysis
To evaluate risks, we performed 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: Automated scenario comparisons using Excel’s
What-If Analysis.
Results and Insights
- Profitability Confirmed: The project showed a base-case profit margin of 25%, exceeding the developer's benchmark of 20%.
- Funding Plan Finalised: A 60:40 debt-equity ratio was deemed optimal, reducing interest costs while maintaining flexibility.
- Actionable Insights Delivered:
- Allocating 10% of the budget to contingencies mitigated risks from material cost escalations.
- Staggered sales strategies minimised holding costs in a softening market.
Conclusion
This project demonstrates how Excel modelling can empower property developers to make informed decisions. From detailed cost breakdowns to scenario analysis, the model provided clarity and actionable insights that enhanced project planning. If you’re a property developer looking to streamline your financial planning, adopting robust Excel models is an invaluable step toward success.