Real Estate Development - Hotel Expansion
Detailed financial model for a > $20M real estate project, combining pricing, cost allocation, cash forecasting, and ongoing financial control in one workbook.
- Problem
- The development needed a single model to confirm whether unit sales could fund construction and financing without a capital shortfall.
- What I Built
- A unit-level underwriting and cash management model integrating construction budgeting, cost allocation, financing, and profitability analysis.
- Impact
- Confirmed a funding structure across 10 villas worth $20M+ in projected sale proceeds over a 3-year horizon.
- Stack
- ExcelFinancial ModelingScenario AnalysisCash Flow Planning
Representative aerial view of the villa clusters and beachfront site. The development name and visual have been anonymized.
Project Summary
- Role
- Financial Model Architect and Development Finance Consultant
- Sector
- Real Estate Development, Luxury Hospitality, Hotel Development
- Engagement
- Design, build, and ongoing maintenance of a development underwriting and cash-control model
- Tools
- Excel: structured tables, dynamic formulas, data validation, scenario analysis, financial modelling
- Input
- Vendor budgets, architect assumptions, bank statements, management sale variables, FX data, QS budgets
- Output
- Executive dashboard, core KPIs, villa-level P&L, cash-control workbook
2
Villa clusters
10
Villas
Plus infrastructure upgrade
5
Financial Engine Layers
$20M+
Revenue in villa proceeds
System at a glance
Decision Context
The development involved multiple villas sold individually but delivered through a shared construction program.
Management needed to understand the economics of each unit without losing sight of the project's overall cash requirements. The model was built to answer a recurring set of commercial and financial questions:
- What is the expected cost, profit, and margin of each villa?
- How should shared development costs be allocated across units?
- Are forecast sales proceeds sufficient to fund construction and operating requirements?
- When could additional funding be required?
- How does actual expenditure compare with the approved budget?
- Do the accounting records agree with the underlying bank activity?
- How would changes in pricing, construction cost, timing, or foreign exchange affect the project?
The result needed to function as both a forward-looking underwriting model and an ongoing financial-control system.
The Problem
At the start of the engagement, there was no unified financial model connecting villa pricing, development costs, sales timing, cash flow, and bank activity.
Shared costs were a particular challenge. Land, design, infrastructure, project management, professional fees, and other development expenses benefited more than one villa and needed to be allocated through a consistent and transparent methodology.
Existing situation
Required outcome
Information scattered across vendor budgets, accounting records, bank statements, and ledgers.
One unified model connecting pricing, cost, cash flow, and bank activity.
No consistent method to allocate shared development costs.
Transparent, driver-based cost-allocation methodology.
Difficult to establish reliable unit economics for pricing negotiations.
Villa-level underwriting with clear cost, profit, and margin visibility.
No traceability from summary outputs back to source records.
Traceability preserved from executive outputs to underlying transactions.
Model / System Architecture
The workbook follows a controlled, one-directional calculation flow.
01
Source data
- IDR bank statements
- USD bank statements
- Foreign-exchange data
- Outflow transaction ledger
- Inflow transaction ledger
- Vendor directory and budgets
02
Model inputs
- Core assumptions
- VAT and tax parameters
- Contingency
- Villa dimensions and land areas
- Sales prices and expected sale timing
- Cost-allocation methodologies
03
Calculation layer
- Cost classification and mapping
- Villa-level cost allocation
- Budget-versus-actual calculations
- Estimate-at-completion calculations
- Actual and forecast monthly cash flow
- Unit-level revenue, cost, tax, profit, and margin calculations
04
Reporting outputs
- Executive dashboard
- Villa-level underwriting and P&L
- Development cash flow
- Budget-versus-actual reporting
- Funding and liquidity outlook
05
Controls and reconciliation
- Transaction-to-bank reconciliation
- Accounting-to-bank reconciliation
- Allocation checks
- Cash roll-forward checks
- Missing or unmapped record checks
- Model-integrity warnings
Control checks use the same source and calculation chain as the reporting outputs. They do not rely on a separate parallel calculation path.
Selected Work
Executive Dashboard
A consolidated view of project economics, development costs, sales, liquidity, and key control warnings. The dashboard is designed to show the current position of the development before the user moves into the supporting schedules.
Villa-Level Underwriting
Revenue, land cost, construction cost, allocated shared costs, tax, profit, and margin are calculated separately for each villa and then consolidated at cluster and project level.
Cost Allocation
Shared hard and soft costs are allocated to individual villas using explicit drivers. Built-area share is used where the relationship is appropriate, while other costs can be assigned directly or allocated through a different documented basis.
Monthly Cash Flow and Funding
The cash-flow schedule separates recorded actuals from forecast activity and tracks opening cash, inflows, development expenditure, operating costs, funding movements, and closing liquidity by month.
Budget Control
Budget, actual cost, committed or forecast cost, estimate at completion, and variance are presented by category and by villa. This provides a current view of expected final cost rather than relying only on historical spend.
Transaction and Bank Reconciliation
Receipts and payments are linked to vendors, categories, currencies, and bank activity. Missing, duplicated, or unreconciled items are surfaced as exceptions rather than absorbed into the reporting.
Change Log
Material changes to assumptions, scope, timing, or methodology are recorded with the date, source, and reason for the revision.
Interactive Demonstration
The public demonstration uses a reduced synthetic version of the model. It reproduces the decision logic without exposing the client's original data.
Real Estate Development Finance & Cash Control Model: synthetic demonstration workbook
07Outcome
The workbook became the central financial framework for pricing, profitability, development cost, cash planning, and ongoing control.
It was updated as new budgets, transactions, construction information, and sales assumptions became available. Material changes could then be reviewed with management and reflected in pricing, scope, funding, or timing decisions.
Key outcomes
- Centralized commercial and financial assumptions
- Consistent allocation of shared costs to individual villas
- A clear distinction between actual and forecast cash flow
- Unit-level, cluster-level, and consolidated profitability reporting
- Transaction-level traceability from reporting outputs to source records
- Automated budget, allocation, cash roll-forward, and reconciliation checks
- Earlier visibility into potential liquidity or funding gaps
- Clear identification of unreconciled or pending bank activity
- A maintainable structure for future project updates
08Technical Notes
The model was designed around several core principles:
- One source of truth for each assumption
- Clear separation between inputs, calculations, and outputs
- One-directional formula flow
- Structured transaction and bank-feed tables
- Consistent villa, vendor, and category identifiers
- Formula-driven actual-versus-forecast cut-off
- Transparent cost-allocation drivers
- Bounded calculation ranges
- Dedicated reconciliation and model-integrity checks
- No unexplained hard-coded values inside calculation formulas
Category and month aggregation
=SUMIFS(
tblOutflows[Amount_USD],
tblOutflows[Category], $A12,
tblOutflows[Date], ">="&B$6,
tblOutflows[Date], "<"&EDATE(B$6,1)
)Actual-versus-forecast cut-off
=IF(
Month_End<=Actuals_Cutoff,
Actual_Cash_Flow,
Forecast_Cash_Flow
)Cost-allocation driver
=Shared_Cost *
Unit_Built_Area /
Total_Applicable_Built_AreaThese formulas are simplified and renamed for publication. They are representative of the methodology rather than copied directly from the original workbook.
09Files
10Disclosure
This case study is based on a real financial-modeling engagement.
"Kalindo Cove" is a pseudonym. Company names, villa identifiers, vendors, bank accounts, dates, and commercially sensitive figures have been removed, altered, or aggregated.
Any downloadable workbook, transaction dataset, or interactive demonstration has been independently reconstructed using synthetic information. The public materials illustrate the architecture, modeling techniques, and control framework without reproducing the client's confidential data or exact commercial results.
The model shown publicly should therefore be described as a representative reconstruction, not the original operating workbook.