Project Cash Flow (Milestones)
This use case explains how to model project cashflow for construction and contracting businesses using milestone based billing and cost recognition in Model Reef.
You will use:
Branches to represent projects or sites.
Revenue variables tied to milestones and claims.
Cost variables tied to procurement, labour and subcontractors.
Timing rules for retentions, variations and payment terms.
The aim is to produce a clear view of project level and group level cashflows aligned to how contracts are actually billed and paid.
When to use this pattern
Use this pattern when:
You project revenue based on milestones, claims, certificates or stages.
You want to understand cash in and cash out by project, not just at group level.
You need to plan funding for a project portfolio and understand timing of cash crunches.
If you also want to forecast work in progress and cost to complete, combine this with WIP and Cost-to-Complete Forecasting.
Architecture overview
You will build four layers:
All of this sits inside the standard three statement engine so P&L, Balance Sheet, Cashflow Statement and Cash Waterfall remain consistent.
Set up branches for projects
Create a model where each project is a branch, for example:
GroupProject - Hospital ExtensionProject - School RefurbishmentProject - Retail FitoutOverheads
If you have many projects, group them under regional or business unit branches, such as Region North, Region South, so reporting remains manageable.
Each project branch will hold only that project’s revenue and costs.
Capture contract value and milestone structure
In the Data Library, create drivers per project for:
Contract Value - Project X.Milestone percentages, for example:
Milestone 1 PercentageMilestone 2 PercentagePractical Completion PercentageRetention Percentage.
For each project, also specify:
Expected milestone dates or periods.
Retention release timing, for example:
Fifty percent at practical completion.
Fifty percent at the end of the defects liability period.
If you prefer, you can maintain milestone tables in a spreadsheet and import them as drivers for value by period per project.
Build revenue and claim variables
In each project branch, create Revenue variables that represent claimable revenue per milestone or per period, for example:
Revenue - Milestone Claims - Project X.Revenue - Variations - Project X.
Options for recognition:
Straight line over the expected project duration.
Milestone based, where revenue is recognised when milestones are reached.
A combination of the two if you want to approximate percentage of completion.
Decide whether you want revenue in the model to match accounting recognition or claim timing. For planning, many teams use claim timing, treating each approved claim as both revenue and debtor creation.
Make sure all revenue variables are of type Revenue so they feed P&L correctly.
Model debtor timing and retentions
Use timing and delay rules on revenue variables to reflect:
Typical debtor terms after a claim is approved, for example 30, 45 or 60 days.
Partial payments where retentions are held back.
Implementation patterns:
Split revenue into two variables:
Revenue - Claims Payable(portion not retained).Revenue - Retentions.
Apply different delays:
Shorter delay for normal claims.
Much longer delay for retentions (for example months after completion).
Alternatively, use a single revenue variable and adjust timing with custom drivers that approximate retention behaviour.
The effect should be that:
P&L shows revenue when work is done or claims are approved.
Balance Sheet shows debtors and retention receivable.
Cashflow shows receipts when payments are expected.
Model project cost timing
Within each project branch, create cost variables for:
Materials and procurement.
Labour (site staff and supervision).
Subcontractors.
Plant, equipment and hire.
Site overheads.
Use:
COGS type variables for direct project costs.
Staff variables for employed labour.
Opex variables for project overheads.
For each, define:
Occurrence pattern (when costs are incurred).
Delay or payment terms (for example 30 days for suppliers, 14 days for subcontractors).
This will create Creditors and subcontractor payables in the Balance Sheet and show cash outflows in the Cashflow Statement and Cash Waterfall.
Add variations and risk allowances
Construction projects frequently change scope. For each project, consider:
Variation drivers, for example
Variation Percentage of Contract Valueor explicit variation amounts.Risk allowances, such as contingency percentages on labour or materials.
Implement variations as additional Revenue and COGS variables:
Revenue - Variations - Project X.COGS - Variations - Project X.
You can tie these to scenario specific drivers so that different models represent base, upside and downside views of variation income and cost.
Build project and portfolio cash views
With revenue and costs wired up per project, create:
Project level P&L, Cashflow and Cash Waterfall views.
A portfolio dashboard showing:
Cash in and out per project over time.
Net cash position by project.
Total portfolio cashflow per period.
Retention build up and release over the horizon.
Use this to identify:
Projects that cause large temporary cash deficits.
Timing of funding requirements at portfolio level.
Sensitivity of cash to delays in claims or payments.
Check your work
Contract values and milestone percentages sum correctly to total project value.
Retention assumptions match actual contract terms.
Cost timing approximates procurement, labour and subcontractor payment cycles.
Project level and group cashflows look plausible when compared with known project histories.
Troubleshooting
Related guides
Last updated