Cashflow25 September 20266 min read

How to Build a Rolling 13 Week Cashflow Forecast (Step by Step)

A practical guide to building a rolling 13 week cashflow forecast in a spreadsheet, with a worked example and the four mistakes that make one useless.

Plenty of guides explain what a cashflow forecast is. Far fewer show you how to actually build one. This is the build guide: the grid, the rows, the arithmetic, and the weekly habit that keeps it alive. If you want the concept first, start with cashflow forecasting and come back.

Why 13 weeks

13 weeks is one quarter. It is far enough ahead that you can still do something about a problem, and close enough that you have real evidence for most of the numbers: invoices already raised, bills already received, payroll you already know the size of. Push the horizon out to twelve months and every figure becomes a guess. Pull it in to four weeks and you find out about a shortfall with no time to fix it.

What you need before you start

Four things, all of which you already have.

Your current bank balance, today, from the bank itself rather than from your accounting software.
Your unpaid sales invoices, with the amount and the due date on each.
Your unpaid supplier bills, same detail.
Your regular fixed payments: payroll, rent, loan repayments, subscriptions, and the dates they leave the account.

Step 1: lay out the grid

Open a spreadsheet. Put 13 columns across the top, one per week, labelled with the week commencing date. Down the left, five blocks of rows:

Opening balance, one row.
Money in, one row per source. Customer receipts, VAT refunds, a loan drawdown, owner funds.
Total in, one row.
Money out, one row per category. Payroll, suppliers, rent, VAT, corporation tax, loan repayments, card spend.
Total out, net movement and closing balance, three rows.

Closing balance equals opening plus total in minus total out. Next week's opening balance is a link to this week's closing balance. That link is the whole forecast: get it right and the rest is data entry.

Step 2: put money in the week it actually moves

This is where most forecasts go wrong. A cashflow forecast is not a profit forecast. An invoice belongs in the week you expect the money to land in the bank, not the week you raised it and not the week it is due. If a customer has taken 52 days to pay you for the last six invoices, put their next one in the week that matches their habit, not their payment terms. Your debtor days figure is the honest starting point for that judgement.

The same applies going out. Put payroll on the day it clears, VAT on the day the direct debit is taken, and the quarterly insurance in the one week a year it actually leaves.

A worked example

Take an illustrative business. The figures below are invented to show the mechanics, they are not a claim about any real company.

Opening balance £18,000.

Week 1: £12,000 in, £9,500 out. Closing £20,500.
Week 2: £6,000 in, £21,000 out because payroll falls here. Closing £5,500.
Week 3: £9,000 in, £7,200 out. Closing £7,300.
Week 4: £4,000 in, £11,500 out because the quarterly VAT payment lands. Closing minus £200.

Money comes in every single week. The business still goes overdrawn in week 4, because payroll and VAT land two weeks apart and a slow-paying customer sits in week 5. You find that out in week 1, with three weeks to chase the invoice, move a supplier payment, or call the bank. Without the forecast you find out on the day it bounces.

Step 3: roll it forward every week

The word rolling is the important one. Every Monday, delete the week just gone, add a new week 13 on the end, and replace your estimates for the coming weeks with what actually happened. A forecast built once in January and never touched is worse than no forecast, because it looks like information.

Fifteen minutes a week is enough once the grid exists. Put it in the diary.

The four mistakes that make a forecast useless

Using invoice dates instead of expected payment dates. Your forecast then predicts a bank balance nobody will ever have.
Forgetting the irregular payments. Corporation tax, the annual insurance renewal, the accountant's bill. These are the ones that turn a comfortable week into an overdrawn one.
Forecasting one line and not the other. If you assume sales grow 20% you must assume the costs of delivering those sales grow too, or the forecast quietly invents a margin you have never earned.
Never comparing the forecast to what happened. Each week, look at the gap between what you predicted and what landed. That gap tells you which of your assumptions is wrong, which is the only way the forecast gets better.

Doing it without the spreadsheet

If the weekly maintenance is the part that defeats you, that is the part software removes. CFO Pal projects 13 weeks ahead from your stated cash balance, takes its level for each revenue and cost line from the average of your last four complete months, and lets you override any line where you know something your ledger does not yet. If you would rather stay in a spreadsheet, start from our free 13 week cashflow forecast template, which has the grid already built, or use the free cashflow calculator for a quick first projection.