Profit is an opinion, but cash is a fact. Plenty of healthy UK businesses get into trouble not because they are unprofitable, but because money goes out before it comes in. A cash flow forecast in Excel is the simplest tool to see those squeezes coming. In this guide I will show you how to build a rolling 12-week forecast that tells you your bank balance at the end of every week, so you are never caught out by a payment you knew was coming but had not planned for.
I have sat across the table from too many business owners who only discovered a cash gap when a payment bounced. Almost always, the warning was there weeks earlier, hidden in a pile of invoices and bills. A forecast simply brings that information forward in time. It does not need to be clever or beautiful; it needs to be honest and up to date. The version we build here takes about twenty minutes to set up and five minutes a week to maintain.
Step 1: Lay out your weeks across the top
Put your categories down the left and the weeks across the top. Twelve weeks is a good horizon for a small business: long enough to spot trouble, short enough to stay realistic. Start with your opening bank balance, then split everything into money in and money out.
| Item | Week 1 | Week 2 | Week 3 |
|---|---|---|---|
| Opening balance | 4,200 | 5,150 | 3,900 |
| Customer receipts | 3,000 | 1,200 | 4,500 |
| Supplier payments | 1,400 | 900 | 1,100 |
| Wages | 650 | 650 | 650 |
| VAT / tax | 0 | 900 | 0 |
Step 2: Total your money in and money out
Below the receipts, add a “Total in” row that sums every income line for the week. Do the same for outgoings. If receipts are in rows 3 to 4 of the Week 1 column B, use =SUM(B3:B4), and for outgoings in rows 5 to 7 use =SUM(B5:B7). Keeping the two halves separate makes the next step trivial. The more detail you give your outgoings, the more useful the forecast: separate lines for wages, rent, suppliers, VAT and loan repayments make it obvious which lever to pull when a week looks tight.
Be realistic with your receipts. The temptation is to assume every customer pays the day their invoice falls due, but most pay a little later. If your average customer pays at 40 days rather than the 30 on the invoice, build that delay in. A forecast that assumes the best case is worse than no forecast at all, because it gives false comfort. I would rather be pleasantly surprised by early payment than ambushed by a late one.
Step 3: Calculate the closing balance
The closing balance is the opening balance plus money in minus money out. With the opening balance in B2, total in in B8 and total out in B9, the formula is =B2+B8-B9. This is the number that matters most: it is what your bank account will read at the end of the week. Everything else in the forecast exists to feed this one figure, so it is worth giving it a bold format and a clear label so your eye finds it instantly.
If you keep a minimum buffer in your account, you might prefer to track headroom rather than the raw balance. Subtract your buffer from the closing balance and you get the amount you can actually spend without dipping into your safety margin. A line such as =B10-1000 shows how much genuine slack each week has, which is often a more honest number to plan against than the balance itself, especially if an overdraft tempts you to treat zero as the floor when it is not.
Step 4: Carry the balance forward
Each week’s opening balance is simply last week’s closing balance. So if Week 1’s closing sits in B10, Week 2’s opening balance in C2 is just =B10. Drag that across and your forecast becomes a chain: change one number anywhere and the running balance updates all the way to Week 12.
Step 5: Flag the danger weeks
Use a formula to highlight any week that dips below zero or below your comfort buffer. A quick text flag works well: =IF(B10<0,"OVERDRAWN",IF(B10<1000,"Tight","OK")). Pair this with conditional formatting and your eye goes straight to the weeks that need action, such as chasing an invoice early or delaying a non-urgent purchase. The buffer figure of £1,000 is just an example; set it to whatever level lets you sleep at night, because every business has a different comfort zone.
The point of the flag is to prompt action while you still have options. A week that shows "Tight" three weeks out is easy to fix: chase a slow payer, agree a short extension with a supplier, or move a discretionary purchase. The same gap discovered on the day is a crisis. This is the whole argument for forecasting in a sentence: the earlier you see a problem, the cheaper and calmer it is to solve.
Putting it to work
The real value is in the "what if". Slide a big payment a week later, or assume a customer pays late, and watch the closing balances react. That is how you decide whether you can afford a new hire or a piece of equipment. Because every week links to the last, a single change ripples through the whole forecast instantly, which is something no paper plan can do. If you would rather start from a tested layout, our cash flow forecast template is built exactly along these lines, and it pairs well with a profit and loss template for the bigger picture.
Once the forecast is running, get into the habit of comparing it against what actually happened. Each week, replace the estimate with the real figure and note where you were wrong. Over a couple of months you will learn your own patterns, such as customers who always pay a week late or a quiet month for sales, and your estimates will get sharper. That feedback loop is what turns a forecast from a guess into a genuinely reliable early-warning system for your business.
Common mistakes
- Forecasting profit instead of cash. Record money on the date it actually moves, not when you raise the invoice.
- Forgetting lumpy payments. VAT, Corporation Tax and annual insurance hit hard in single weeks. Put them in.
- Being too optimistic on receipts. Assume customers pay on their usual day, not the day the invoice is due.
- Breaking the carry-forward. If a week's opening balance is a typed number rather than a link to last week's close, the chain stops updating.
Frequently asked questions
How many weeks should a cash flow forecast cover?
For a small business, a rolling 12 to 13 weeks is the sweet spot. It is far enough ahead to act on, yet near enough that your estimates are believable.
What is the difference between cash flow and profit?
Profit is sales minus costs over a period. Cash flow is the actual timing of money in and out of your bank. You can be profitable on paper and still run out of cash if customers pay slowly.
Should I include VAT in my cash flow?
Yes. Show VAT on the week you expect to pay or receive it. It is one of the biggest single movements most small businesses face.
How often should I update the forecast?
Weekly is ideal. Roll the window forward one week each time and replace estimates with what actually happened, so the forecast stays honest.
