How to build a cash flow forecast
A cash flow forecast is one row of arithmetic repeated across the months: opening balance + money actually received − money actually paid = closing balance, and that closing balance becomes the next month's opening balance. It forecasts cash, not profit, which is why a business can post a quarterly profit of £4,000 and still come within £320 of an empty account — as the worked example below does.
Profit is an opinion; cash is a date
Your profit and loss account records a sale when you raise the invoice. Your bank account records it when the money lands. Those are usually different months, and the gap is where businesses die.
Take an invoice for £12,000 raised on 28 March on 30-day terms, paid on 19 May. The P&L books £12,000 of revenue in March. The bank sees nothing until May. Meanwhile the wages, the rent and the subcontractor who did the work were all paid in March and April. A cash flow forecast is simply the same business written down by payment date instead of by invoice date.
The five rows every forecast needs
A forecast that works has five bands, in this order, one column per period:
- Opening balance — what is in the bank on day one of the period. Only the first one is typed; the rest are formulas.
- Receipts — customer payments, by the date you expect them to clear, plus anything else that arrives: a loan drawdown, a VAT refund, a grant.
- Payments — payroll, rent, suppliers, subscriptions, loan repayments, tax and VAT, equipment. By the date the money leaves.
- Net movement — receipts minus payments.
- Closing balance — opening plus net movement, which is the next column's opening balance.
In a spreadsheet with months running across from column C, that is four formulas: =SUM(receipts range), =SUM(payments range), =receipts−payments, =opening+net, and then =C_closing in the next month's opening cell. Nothing else in a cash flow forecast is a formula, and anything more elaborate is usually hiding an assumption.
A worked quarter
A six-person design studio. Opening bank balance on 1 April: £8,400. It invoices steadily, its customers pay slowly, and its VAT quarter falls in May.
| April | May | June | |
|---|---|---|---|
| Opening balance | £8,400 | £8,510 | £320 |
| Customer receipts | £22,000 | £18,500 | £26,400 |
| Salaries | −£14,200 | −£14,200 | −£14,200 |
| Rent | −£1,850 | −£1,850 | −£1,850 |
| Software | −£640 | −£640 | −£640 |
| Freelancers | −£3,900 | −£2,400 | −£4,600 |
| Everything else | −£1,300 | −£1,300 | −£1,300 |
| VAT payment | — | −£6,300 | — |
| Net movement | +£110 | −£8,190 | +£3,810 |
| Closing balance | £8,510 | £320 | £4,130 |
Read May. Nothing went wrong that month: no customer defaulted, no cost overran, the studio was profitable across the quarter. A single scheduled payment that the P&L does not treat as a cost at all took the account to £320. Had one £6,000 invoice slipped by three weeks — which is an ordinary event — the balance would have gone below zero, and that is the thing the forecast exists to show you four weeks early rather than on the day.
Thirteen weeks, not twelve months, when it is tight
A monthly forecast hides everything that happens inside a month. If payroll leaves on the 28th and your biggest customer pays on the 5th, a month that closes at £4,130 can still have spent a fortnight overdrawn.
When cash is tight, rebuild the same five bands at weekly granularity for thirteen weeks — one quarter. It is the same arithmetic, thirteen columns wide, and it is the only version that answers "can I make payroll on the 28th". Keep the monthly version for the year ahead and the weekly version for the quarter you are in; they are the same sheet at two zoom levels, not two different forecasts.
Getting the receipts line honest
The single biggest error in a first forecast is putting customer receipts in the month you invoiced. Use your own history instead. Take the last twelve months: total invoiced, and the average number of days between invoice date and payment date.
Say the studio invoices £276,000 a year and customers take 52 days on average. The cash tied up in unpaid invoices at any moment is:
£276,000 ÷ 365 = £756.16 a day × 52 days = £39,320
Collect in 30 days instead and the same sum gives £756.16 × 30 = £22,685. The difference, £16,635, is cash that arrives once and then stays in the business — more than the studio's entire VAT bill, and it requires no new customers. In the forecast, that improvement shows up simply as moving each receipt three weeks to the left.
The lines people forget
Forecasts break on payments that are irregular rather than large. The usual offenders: VAT or sales tax quarters; corporation or income tax on account; annual insurance renewals; software billed yearly; equipment purchases; loan capital repayments, where only the interest appears in the P&L; dividends or owner drawings; and, if you employ people, whatever your payroll report shows as employer on-costs — take the figure from the report rather than a rate you half-remember.
A practical test: open last year's bank statements and list every payment over £500 that did not happen monthly. That list is the set of rows your forecast is currently missing.
Running it: three columns, not one
Once the forecast exists, keep three versions of the receipts line and leave the payments alone: expected, slow (every customer takes two weeks longer) and lost (your largest single expected receipt does not arrive at all). Copy the closing balance row of each into one small summary.
For the studio above, "slow" pushes roughly £4,600 of the June receipts into July and takes the June close from £4,130 to under £0. That is a real answer to a real question, and it takes ten minutes once the sheet exists.
Then update it weekly with what actually happened. A forecast is only worth the last time someone typed a real number into it.
Arithmetic and general information only — not financial, tax, legal or investment advice. Your own figures, and your own accountant, decide what any of this means for you.
Do this sum for free
free break-even calculator — it answers the other half of the question: how many sales a month it takes to cover your fixed costs in the first place. It runs in your browser and nothing you type is sent anywhere.
If you would rather not build it yourself
The Small Business Finance Tracker ($39) is a 12-month cash flow and revenue forecast, a profit and loss summary, a break-even calculator, a tax-reserve sheet and a KPI dashboard, all with working formulas and a written setup guide. One-time purchase, instant download.
Frequently asked questions
What is the cash flow forecast formula?
Closing balance = opening balance + receipts − payments, and each period's closing balance becomes the next period's opening balance. Receipts and payments are dated by when the money actually moves, not by when the invoice was raised.
What is the difference between a cash flow forecast and a profit and loss forecast?
A P&L records a sale when the invoice is raised and spreads costs like equipment over years as depreciation. A cash flow forecast records money on the day it moves, and includes things the P&L never shows: VAT payments, loan capital repayments, tax on account and owner drawings. A profitable business can still run out of cash.
How far ahead should a cash flow forecast go?
Twelve months monthly for planning, and thirteen weeks weekly for the quarter you are in. The weekly version is the one that answers whether you can make a specific payment on a specific date; the monthly version is the one that shows a seasonal dip coming.
How do I work out when customers will actually pay?
Take the last twelve months of invoices and calculate the average days between invoice date and payment date. Use that figure, not your stated terms. You can also size the cash it is costing you: annual invoiced value ÷ 365 × average days paid gives the amount permanently tied up in unpaid invoices.
Should VAT be in a cash flow forecast?
If you are registered, yes — as a payment on the date the money leaves your account, which for quarterly filers is one large payment every three months rather than a smooth monthly cost. It is one of the most common reasons a forecast that looked fine turns out to be wrong.
Can I build a cash flow forecast in Excel or Google Sheets?
Yes. It needs four formulas in total: sum the receipts, sum the payments, subtract one from the other, and add the result to the opening balance. Everything else is typed assumptions, which is why the sheet is easy and getting the assumptions right is the hard part.