Accounting & Finance · Cash-flow forecasting
A cash forecast built on when customers pay, not when invoices fall due
Tested on 98,631 public payment reports from 9,388 large companies, for suppliers whose cash depends on a few big accounts.
Executive summary
A large customer’s last published payment report forecasts when it will pay within 4.5 days on average, against 13.3 days for the due-date assumption most cash forecasts use.
Situation
A supplier with a few large customers forecasts cash on due dates; large customers pay early, late or very late, and the forecast misses in the weeks that matter.
Approach
Each customer’s published payment reports used to forecast its next days to pay with an 80% range, tested on 24,692 later reports from 6,618 companies against the due-date assumption and a boosted model.
Result
The last report is within 4.5 days on average and its ±20% range held 81% of outcomes. The boosted model adds 0.06 days, so the simpler method goes live.
The problem
A supplier whose revenue comes from a few large customers forecasts cash by assuming each invoice is paid on its terms. Large customers often pay later than that. Australia’s Payment Times Reporting Scheme shows large businesses take about four weeks on average to pay small suppliers, and the slowest invoices take more than twice the agreed term. For a business with three or four big accounts, a forecast built on terms is wrong in the weeks that matter.
I built a forecast that places each open invoice in the week the customer is expected to pay, from that customer’s own published payment record, and measured how far it lands from the actual payment date against the due date.
- Team
- Treasury or finance manager, credit control team, financial planning and analysis
- Customers
- Large corporate and government customers carrying most of revenue
- Systems
- Xero or MYOB receivables, the Payment Times Reports Register, a cash-flow tool such as Float or a spreadsheet
- Measure
- Error in forecast days to pay; error in cash expected within 30 days
What I built
A payment-timing table for each large customer, refreshed when the customer files its twice-yearly payment report. It gives the expected days to pay with an 80% range and flags customers likely to pay many invoices late. The cash forecast places each open invoice at its expected payment date instead of its due date. Once the business has a few months of its own receipts from a customer, those take over.
Results
For 24,692 payment reports from 6,618 large companies covering periods ending July 2024 to October 2026, never used to fit anything, the customer’s previous report forecasts its next average days to pay within 4.5 days on average. Assuming 30-day terms is out by 13.3 days, and the customer’s own stated standard terms by 17.1.
| Forecast of days to pay | Average error | Within five days | 80% range held |
|---|---|---|---|
| Assume 30-day terms | 13.3 days | 29.1% | – |
| Customer’s stated standard terms | 17.1 days | – | – |
| Last published report, with ±20% range | 4.5 days | 78.0% | 81.2% |
| Boosted model, with conformal range | 4.5 days | 74.4% | 80.5% |
Ranges that hold
A range of ±20% around the last report’s days to pay, set on 2023 reports, held the actual figure for 81.2% of later reports, close to the 80% it was set for. The median range is 13 days wide. The cash forecast shows a likely and a slow case from it.
What it means for a ledger
For 5,000 random ledgers of 12 large customers each, the median share of invoices actually paid within 30 days is 60%. A forecast that treats every invoice as paid on 30-day terms overstates cash in the first month by 40 points. Using each customer’s last report, the error is 2.3 points on average and under 5 points for nine ledgers in ten.
Late payers stay late
Late payment is persistent. A customer that paid more than 40% of its invoices late in one period did so again in the next 75% of the time. One that paid 20% or fewer late crossed that line 1.7% of the time. These customers get shorter credit limits, deposits or milestone billing written into the contract before work starts.
| Last report said | Next report: more than 40% of invoices paid late |
|---|---|
| More than 40% paid late | 74.8% |
| 20% or fewer paid late | 1.7% |
| All large companies | 17.3% |
Data and method
- Data
- UK Payment Practices and Performance reports, published by the Department for Business and Trade under the Reporting on Payment Practices and Performance Regulations 2017 (Open Government Licence): 98,631 usable reports from 9,388 large companies, each covering six months
- Target
- The company’s reported average days to pay in its next period, and whether more than 40% of invoices were paid after agreed terms
- Split
- Fitted on periods ending before January 2024; ranges set on periods ending in 2023; tested on periods ending July 2024 to October 2026
- Methods
- Flat 30-day terms; the company’s stated shortest standard payment period; its last reported days to pay; gradient boosting on the last three reports, trends and stated terms
- Measures
- Average absolute error in days; share within five days; coverage of 80% ranges; company-level bootstrap 95% range on the model’s gain
- Ledger test
- 5,000 random sets of 12 test companies; share of invoices paid within 30 days, forecast against reported
Limitations
- The reports are averages across all of a company’s suppliers. A supplier can be paid faster or slower than average, so the business’s own receipts replace the public figure once a few months of history exist.
- Reports cover UK companies above the size threshold. In Australia the Payment Times Reporting Scheme publishes similar figures for large businesses, and the same method applies to that register.
- Reported figures are self-assessed and some contain obvious errors; reports with more than 365 average days were excluded.
- Days to pay is measured from invoice receipt. An invoice that sits unapproved because of a missing purchase order number is late for reasons the report cannot show.
How I would take it live
- Export aged receivables and two years of receipts from the ledger, and list the large customers with their published reports
- Build the timing table and compare its forecast against the last six months of actual receipts
- Load expected payment dates into the cash forecast in Anaplan, Fathom or Excel, with a likely and a slow case
- Refresh the table when customers file their reports and review the late-payer flags before each new contract