A direct weekly forecast for one quarter, worked line by line, with the buffer for forecast error that a monthly view never shows.
A 13-week cash flow forecast lists every expected receipt and payment week by week, rolls the opening balance forward to a closing balance for each week, and compares the lowest week with the minimum cash the business must hold plus a buffer for forecast error. On the illustrative company below, cash opens at 6,000 thousand and never falls below the 3,000 minimum, but its trough in week 6 leaves only 300 of headroom, and once a 95 per cent buffer for six weeks of forecast error is added the company needs 1,312 thousand of revolver drawings it would not see on a monthly view.
Worked in full in Treasury Management by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
Thirteen weeks is one quarter: long enough to see the next payroll cycles, the quarterly tax payment and the covenant test date, short enough that receipts and payments can be forecast from known invoices, orders and payment runs rather than from the budget. It is a direct forecast, built from cash lines, not from profit adjusted for working capital. Weekly granularity is the point: payroll, rent and tax fall in particular weeks, and a month-end balance can look healthy while the middle of the month is short.
| Line | Amount | Timing |
|---|---|---|
| Opening cash | 6,000 | Week 1 |
| Customer receipts | 1,950 to 2,650 | Weekly, from the debtor ledger and the order book |
| Supplier payments | 1,100 | Every week |
| Payroll | 1,800 | Weeks 2, 4, 6, 8, 10, 12 |
| Rent | 600 | Weeks 1, 5, 9, 13 |
| Quarterly tax | 1,500 | Week 6 |
| Capital expenditure | 900 | Week 6 |
| Interest | 400 | Week 13 |
| Minimum cash, policy | 3,000 | |
| Standard deviation of one week's net forecast error | 400 | From the error log |
Step 1, roll the balance. Each week's closing balance is the opening balance plus receipts less payments, and becomes the next week's opening balance.
Closingw = Openingw + Receiptsw − Paymentsw; Openingw+1 = Closingw
Headroomw = Closingw − Minimum
Excel, weeks in columns: =C5+C8-C15 for the close, =MIN(C20:O20) for the trough, =MATCH(MIN(C20:O20),C20:O20,0) for its week
| Week | Opening | Receipts | Payments | Net | Closing | Headroom |
|---|---|---|---|---|---|---|
| 1 | 6,000 | 2,300 | 1,700 | 600 | 6,600 | 3,600 |
| 2 | 6,600 | 2,350 | 2,900 | -550 | 6,050 | 3,050 |
| 3 | 6,050 | 2,200 | 1,100 | 1,100 | 7,150 | 4,150 |
| 4 | 7,150 | 2,100 | 2,900 | -800 | 6,350 | 3,350 |
| 5 | 6,350 | 2,000 | 1,700 | 300 | 6,650 | 3,650 |
| 6 | 6,650 | 1,950 | 5,300 | -3,350 | 3,300 | 300 |
| 7 | 3,300 | 2,050 | 1,100 | 950 | 4,250 | 1,250 |
| 8 | 4,250 | 2,200 | 2,900 | -700 | 3,550 | 550 |
| 9 | 3,550 | 2,400 | 1,700 | 700 | 4,250 | 1,250 |
| 10 | 4,250 | 2,500 | 2,900 | -400 | 3,850 | 850 |
| 11 | 3,850 | 2,550 | 1,100 | 1,450 | 5,300 | 2,300 |
| 12 | 5,300 | 2,600 | 2,900 | -300 | 5,000 | 2,000 |
| 13 | 5,000 | 2,650 | 2,100 | 550 | 5,550 | 2,550 |
Over the quarter receipts total 29,850 and payments 30,300, so cash ends 450 lower at 5,550. The trough is week 6, when payroll, the quarterly tax and a capex instalment land together: 5,300 out against 1,950 in.
Step 2, the buffer. A forecast made today is less certain about week 6 than week 1. If weekly errors are independent with a standard deviation of 400, the error on the cumulative balance after w weeks has a standard deviation of 400 × √w: 980 by week 6. At 95 per cent one-sided confidence the buffer is 1.645 × 980 = 1,612.
Step 3, the funding need. Week 6 must hold the minimum plus the buffer, 3,000 + 1,612 = 4,612, against a forecast of 3,300. The shortfall is 1,312. Arrange the draw, or move the capex instalment, before week 6 arrives.
| Receipts | Trough week | Trough balance | Draw to reach the minimum |
|---|---|---|---|
| As forecast | 6 | 3,300 | 0 |
| 5% lower | 6 | 2,655 | 345 |
| 10% lower | 10 | 1,645 | 1,355 |
A uniform shortfall moves the trough later, because the lost receipts accumulate. At 10 per cent below forecast the low point is week 10, not week 6, which is why the whole thirteen weeks has to be scanned rather than the obvious week.
A 13-week forecast is rebuilt every week, not once a quarter. When week 1 closes, its actual receipts and payments replace the forecast, the difference goes into the error log line by line, and a new week 14 is added at the far end so the horizon stays at thirteen weeks. Two habits keep it honest: forecast receipts customer by customer from the ledger rather than as a percentage of sales, and keep the error log by line, because a forecast that is right in total and wrong on every line will fail the first time the lines stop offsetting.
Roll the balance week by week, find the trough, and test it against the minimum plus a buffer that widens with the horizon. Here that turns a forecast that never breaches 3,000 into a funding need of 1,312 in week 6. Put the trough and the headroom on the monthly report, as described in what a monthly treasury report should contain. A thirteen-week forecast error log is among the working documents in the free companion files for this book.
Opening cash, then every receipt and payment by week: customer collections from the debtor ledger, supplier runs, payroll, rent, tax, capex and interest, rolled to a closing balance each week. In the worked case receipts of 29,850 thousand and payments of 30,300 take cash from 6,000 to 5,550 over the quarter.
Measure the standard deviation of weekly forecast errors, then scale it by the square root of the weeks ahead. With 400 thousand a week, the error by week 6 has a standard deviation of 980, and a 95 per cent one-sided buffer is 1.645 times that, 1,612.
Because payments cluster in particular weeks. In the worked case the month-end balances are 6,350, 3,550 and 5,550 thousand, all above a 3,000 minimum, while week 6, with payroll, tax and capex together, falls to 3,300, which needs funding once the error buffer is added.
This article is one calculation from Treasury Management. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: corporate finance, valuation and markets → · All 1340 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.