Business Problem
Many organisations discover cash shortfalls only when the bank balance drops unexpectedly. Monthly profit-and-loss reports mask the timing gaps between revenue recognition and cash collection, between invoice receipt and payment, and between planned capital expenditure and actual drawdown. Without a week-by-week liquidity view, treasury decisions are reactive rather than planned.
Objective
Build a rolling 13-week cash flow forecast model that gives business owners and treasury teams a clear, week-by-week picture of liquidity — with scenario toggles for stress testing and a visual cash bridge that makes shortfall timing immediately visible.
Who This Is For
- CFOs and financial controllers managing short-term liquidity
- Treasury analysts responsible for funding and cash positioning
- Small business owners who need to anticipate payroll and supplier payment timing
- Finance teams preparing cash position reports for boards or lenders
Required Data
- Accounts receivable ageing and expected collection schedule
- Accounts payable ageing and committed payment dates
- Payroll schedule with gross amounts and payment dates
- Debt service schedule (loan repayments, interest payments)
- Historical cash movement data (12 months minimum for pattern identification)
- Known one-off inflows or outflows (tax payments, capital expenditure, dividends)
Recommended Tools
Microsoft Excel for the forecast model, scenario toggles, and waterfall visualisation. No macros or VBA required — the entire model runs on standard formulas for maximum compatibility and auditability.
Implementation Steps
- Define the business question: When will our cash position drop below minimum thresholds, and what levers can we pull to prevent it?
- Identify data sources: AR and AP sub-ledgers, payroll system, loan agreements, bank statements.
- Prepare and validate data: Reconcile opening balance to the bank statement. Categorise inflows and outflows. Document payment term assumptions.
- Build the model: Populate known commitments for weeks 1-4, extend with driver-based estimates for weeks 5-13. Build three scenario toggles (base, optimistic, stress).
- Create outputs: 13-week cash forecast with weekly closing balances, waterfall chart showing the cash bridge by category, conditional alerts for breach periods.
- Measure success: Compare forecast to actual weekly. Track forecast accuracy and refine assumptions each period.
Expected Outputs
- Rolling 13-week cash flow forecast workbook
- Three-scenario model (base, optimistic, stress)
- Cash bridge waterfall chart
- Minimum cash threshold alerts with conditional formatting
- One-page board summary for lender or investor presentations
KPIs to Track
- Forecast accuracy (target: within 5% of actual closing balance)
- Cash runway in weeks
- Overdue receivables as a percentage of total AR
- Number of weeks where closing balance breaches minimum threshold
Risks and Assumptions
- Forecast accuracy depends on the quality of AR and AP ageing data — stale sub-ledgers produce unreliable projections
- Assumes payment terms are honoured; late-paying customers introduce variance that must be tracked and corrected over time
- One-off items (tax refunds, legal settlements, capital calls) require manual entry and cannot be modelled from historical patterns alone
- Weekly update discipline is essential — a forecast that is not refreshed reverts to guesswork within two weeks
