A rolling cash flow the finance lead stopped dreading
A 16-week rolling cash flow rebuilt by hand every week, breaking whenever a formula got edited.
The situation
Every week, the group rebuilt its 16-week rolling cash flow forecast by hand in Excel: a workbook of 18 worksheets and 26,000; XLOOKUPs, SUMIFS, COUNTIFS. Each update meant exporting payables, phasing cost of goods against the sales forecast, scheduling VAT and PAYE payments, keying in net pay from payroll summaries rather than the underlying records, and then reconciling the whole thing back to the bank. Over time, many formulas had been overwritten with hard-coded values, the model became impossible to audit and produced unreliable data.
What it was costing
The model was fragile: one edited formula and the numbers silently stopped tying. Nobody fully trusted the output, so the conversation each week started with checking the model rather than acting on it.
What changed
It was rebuilt as an input-driven model — the weekly sales forecast, payables export and fixed costs each land in a defined place, and the forecast derives from them. Payment phasing, VAT scheduling and date overrides are handled by rules in one table rather than scattered across 18 worksheets.
Where it landed
The forecast now takes around 30 minutes, following a structured import schedule. The finance lead spends that time understanding the variances rather than doing the arithmetic. Key assumptions can be changed easily to test different scenarios, and the output is a clean, well-formatted PDF that is easy to read on a phone.