Cash flow forecast template
A cash flow forecast shows how much cash your company will have in the bank each month or week, based on when money actually comes in and goes out. It's how you see a shortfall early enough to do something about it. This free Excel template has a 12-month forecast, a 13-week version for when timing is tight, and a filled-in example.
Running a company of 30 to 200 people? Documents like this are now worth money.
Any period where your closing balance drops below the minimum you set turns red.
What's in the template
Each sheet lists cash in (customer receipts, other income, VAT refunds, loans, asset sales) and cash out (suppliers, net pay, PAYE and payroll taxes, pensions, rent, VAT, corporation tax, loan repayments, capital spending, dividends and more), then works out the net flow and the closing balance for every period.
From the Example sheet (a fictional company starting the year with 125,000 in the bank and a 75,000 minimum), the second half of the year:
| Line | Jul | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|
| Total cash in | 303,100 | 281,900 | 296,100 | 313,100 | 320,000 | 293,100 |
| Total cash out | 291,100 | 291,900 | 354,100 | 292,100 | 323,000 | 284,100 |
| Net cash flow | 12,000 | -10,000 | -58,000 | 21,000 | -3,000 | 9,000 |
| Closing balance | 129,000 | 119,000 | 61,000 | 82,000 | 79,000 | 88,000 |
A profitable company can still drop below its minimum: here it happens in Sep, the month a corporation tax bill and a set of professional fees land together. The forecast shows it months in advance, which is the point.
How to build the forecast
- Start from today's bank balance, not from the accounts.
- Enter receipts when customers will actually pay. An invoice raised in March on 30-day terms usually arrives in April at the earliest; use your customers' real payment habits, not the terms on the invoice.
- Put in the fixed dates first: payroll, rent, loan repayments, and the tax payments below.
- Include VAT. Unlike the P&L, a cash forecast includes the VAT you collect and pay over, because it's real money moving through your account.
- Add the irregular items: annual insurance, bonuses, equipment, dividends.
- Update it every week or month and compare it with what happened. That's how the forecast gets accurate.
UK tax payment dates to put in the forecast
- VAT: the deadline for submitting your return online is usually one calendar month and 7 days after the end of the VAT period, and that is also the deadline for paying HMRC.
- PAYE and National Insurance: if you pay monthly, payment is due by the 22nd of the next tax month when you pay electronically.
- Corporation Tax: for taxable profits up to £1.5 million, it's due 9 months and 1 day after the end of your accounting period. Larger companies pay in instalments.
These are from GOV.UK (links below). Check them for your own company, as the rules can differ, for example under the VAT annual accounting scheme.
Why a 13-week forecast
A monthly forecast can hide a problem inside a month: payroll goes out in week two, the big customer pays in week four, and for two weeks you're overdrawn. A 13-week forecast shows the next quarter week by week, which is why it's the standard tool when cash is tight.
Common mistakes
- Forecasting from invoice dates instead of the dates money arrives.
- Forgetting VAT, tax and annual bills.
- Assuming every customer pays on time.
- Never comparing the forecast with what actually happened.
- Only one version. Run a cautious case too, with your biggest customer paying a month late.
Frequently asked questions
What's the difference between a cash flow forecast and a cash flow statement?
A forecast looks forward at what you expect to happen. A cash flow statement is part of the accounts and reports what did happen.
How far ahead should I forecast?
Twelve months, month by month, for planning; and the next 13 weeks, week by week, whenever cash is tight or a big payment is coming.
Why is my profit different from my cash?
Profit counts revenue when it's earned and costs when they're incurred. Cash counts money when it moves. Late payers, VAT, loan repayments, equipment and dividends all make the two differ.