How to build a 13-week cash flow forecast in a spreadsheet

Your bank statement tells you where your cash went. A cash flow forecast tells you where it's going. The difference between the two is huge, and, in my opinion, the forecast is essential.

By the time a problem shows up in the bank, your options are limited: chase customers in a hurry, delay suppliers, or ring the bank asking for help at short notice. Spot the same problem eight weeks ahead and you have time to plan.

A 13-week forecast is the simplest tool for doing that. Thirteen weeks is one quarter. It is long enough to catch a VAT return, a payroll run, potentially a corporation tax bill and a quiet patch in trading, and short enough that you can predict it with reasonable confidence.

Here's how to build one in a spreadsheet in about an hour, and keep it up to date in fifteen minutes a week.

What a 13-week forecast looks like

The layout is simple: one column per week, and rows for the cash you expect to come in and go out.

Each week's closing balance becomes the next week's opening balance. That's the only formula linking the columns.

Step 1: Start with today's real bank balance

Use the actual balance in the bank today, not the figure in your accounting software. If you have more than one business account, add them together. Include any overdraft as a negative.

This is week 1's opening balance.

Step 2: Forecast receipts by when the money will arrive

This is where most forecasts go wrong. Don't enter sales when you invoice them. Enter them in the week you realistically expect the cash.

1. List your unpaid invoices and put each one in the week you expect it to be paid, based on how that customer actually behaves, not your payment terms.
2. Add sales you expect to make but haven't invoiced yet, allowing for your normal payment delay.
3. Add any other receipts: grants, asset sales, VAT refunds, money you're putting in.

If a customer usually pays 45 days after invoice, forecast 45 days, even if your terms say 30.

Step 3: List every payment, with its real due date

Work through your bank statements for the last three months, so you don't miss anything. Then enter each type of payment in the week it actually leaves the account:

• Suppliers: by the date you'll pay them, not the invoice date
• Wages: net pay on payday
• PAYE and NIC: usually by the 22nd of the following month
• VAT: in the week the return is paid, which is usually one month and seven days after the end of the VAT quarter
• Corporation tax: for most companies, nine months and one day after the year end
• Rent, rates, insurance, subscriptions and loans: on their direct debit or standing order dates
• Owner's drawings or dividends: what you actually plan to take

Annual and quarterly bills are the ones that cause surprises, so make sure they're in.

Step 4: Let the formulas do the rest

In the spreadsheet:

1. Total in = sum of the receipts rows
2. Total out = sum of the payments rows
3. Closing balance = opening balance + total in − total out
4. Next week's opening balance = this week's closing balance

Then add one more row underneath: your core cash target, meaning all the tax you currently owe plus two months of overheads. (We explain why in the 7 key numbers every business should check monthly.) Use conditional formatting to turn the closing balance red in any week it falls below the target, and darker red if it goes below zero.

A worked example

Here are the first six weeks of a forecast for a business turning over around £850,000 a year:

In week 3 the bank shows £69,000, and it would be easy to feel comfortable. But the forecast shows a VAT payment and a corporation tax bill landing within three weeks of each other, taking the balance down to £44,500 by week 6.

Now try a "what if". Suppose your largest customer pays the week 5 and week 6 receipts two weeks late. That is £36,000 that doesn't arrive, and the week 6 balance falls to £8,500. That's a very different conversation, and the forecast has given you six weeks' warning of it rather than six days.

Step 5: Roll it forward every week

A forecast is only useful if it's kept up to date. Once a week, ideally on the same day:

1. Replace last week's forecast with actuals. Put in what really came in and went out.
2. Check the gaps. Where receipts or payments were different from the forecast, ask why, and adjust future weeks if the reason will repeat.
3. Update the opening balance to the real bank balance.
4. Add a new week 13 at the end, so you always see a full quarter ahead.

It takes around fifteen minutes once it's set up. Over time, the comparison between forecast and actual is where you learn the most about how your business really behaves.

Five common mistakes

1. Forecasting invoices, not cash. Sales only help when the money arrives.
2. Forgetting the lumpy payments. VAT, corporation tax, insurance renewals and annual subscriptions cause most surprises. Remember, VAT isn’t your money.
3. Being optimistic about customers. Forecast what customers do, not what they should do.
4. Leaving out what you take. Drawings and dividends are cash going out, just like any other payment.
5. Building it once and forgetting it. A forecast from two months ago is a history lesson, not a forecast.

For seasonal businesses

If your trade has a strong season, as many hospitality, leisure and golf businesses do, a 13-week forecast is the starting point, not the whole picture. Membership renewals, event deposits and summer takings often have to fund a quieter period months later. Keep a simpler monthly forecast for the full twelve months alongside your weekly one, so the strong weeks are planned against the lean ones.

Your forecast is a decision tool

The point of a forecast isn't to predict the future perfectly. It's to give you time to act. When it shows a pinch point, you can chase a debt, move a purchase, agree a payment plan or arrange a facility before you need it, from a position of strength.

If you'd like a second pair of eyes on your cash, our Clarity diagnostic includes a full cash flow check alongside your seven key numbers and a peer benchmark. Over six weeks we work out where your cash is really going and where the gaps will appear, and you leave with a written plan you can act on straight away.