The 13-week cash-flow forecast.
Free template
If you do one thing after reading about insolvency tests, build this. A weekly forecast covering the next thirteen weeks tells you more about your company’s real position than a set of annual accounts — and it is the first document any adviser, lender or creditor will ask to see.
An Excel workbook with the formulas already built in. Opens in Excel, Numbers or Google Sheets.
Filling it in
Six steps, about an hour the first time
1
Set your week-commencing dates
Start with the Monday of this week and run thirteen columns forward. Use real dates, not “week 1” — it makes the tax and payroll timing obvious.
2
Enter your opening cash — once
Put today’s actual bank balance in the Week 1 opening cash cell. Every week after that fills itself from the previous week’s closing figure, so you only type it once.
3
Split your receipts into three honest categories
Confirmed (the customer has told you the date), invoiced but not confirmed, and forecast sales not yet invoiced. Keeping these apart is the single most important thing in the sheet — most forecasts fail because they are added together.
4
Enter every payment, including the tax you have not paid
Payroll, PAYE, pensions, VAT, corporation tax, rent, rates, utilities, suppliers, finance and lease payments, insurance, fuel, professional fees. VAT and PAYE you are holding are liabilities, not working capital. Put a contingency line in at five to ten per cent of payments.
5
Read the two check figures at the bottom
The sheet calculates the lowest closing cash and the lowest headroom across all thirteen weeks. Those two numbers are the forecast. If either is negative, you know the week it happens and roughly how much is missing.
6
Copy the sheet and build a downside
Duplicate it, cut receipts by twenty per cent, push the biggest customer out two weeks, and look at the lowest headroom again. That version is the one worth planning around.
What is already built in
The formulas you do not have to write
- Opening cash carries forward from last week’s closing figure
- Total receipts and total payments sum their own sections
- Net movement is receipts less payments
- Closing cash is opening plus net movement
- Headroom adds your facility limit to closing cash
- Two check rows find the worst week automatically
You only ever type into the white input rows. Everything in the totals, closing and headroom rows calculates itself.
Where forecasts usually go wrong
- Receipt dates set to when you hope, not when customers actually pay
- Confirmed receipts mixed in with hoped-for sales
- Tax left out, or understated
- Seasonal fluctuations ignored
- Built once and never updated
- Never compared against what actually happened
- No downside scenario at all
Update it every Monday. Then put last week’s forecast next to what actually happened. The gap between the two is usually the most informative number in the business — and it gets smaller as you get better at it.
If the answer is uncomfortable
A forecast that shows a shortfall is doing its job
Finding a negative week eight or nine weeks out is the point of the exercise. It is far better to know now, with two months to act, than to discover it on the day payroll fails. Most of the useful options — negotiating with a supplier, arranging a Time to Pay, refinancing, restructuring a contract — need lead time to work.
If your lowest headroom figure is negative, or you are not confident in the receipt dates you have entered, send it to us. We look at these constantly, and a second pair of eyes on the assumptions usually changes the picture in one direction or the other.
The template is provided free and without obligation. It is a working tool, not personalised financial or insolvency advice, and a forecast is a basis for a conversation rather than a substitute for one. ← Back to Am I insolvent?
Related
The Insolvency tests and warning signs page
This guide sits alongside our main page on the subject, which covers the same ground more briefly and links to the rest of the site.
No obligation, no pressure
Start a confidential conversation.
Speak to us. The initial conversation is free, and speaking to us does not commit you to anything.