Your accountant says the business made a profit. Your bank account says otherwise. Both are right, and that gap is exactly why every small business owner eventually needs to build a cash flow forecast — preferably before the day the payroll transfer bounces.
I still remember the first time I built one properly. I was running a two-person consultancy, we had invoiced a good quarter, and I genuinely thought we were fine. Then a single client paid 60 days late and I spent three weeks watching our balance drop in ways my profit-and-loss statement had never warned me about. That was the moment I stopped treating cash flow forecasting as a spreadsheet chore and started treating it as the single most useful document in the business.
What follows is the process I use now: how to build the forecast, where the numbers come from, how to structure it in Excel, and the mistakes that quietly ruin it.
Key takeaways
- A cash flow forecast tracks when money actually moves, not when you earn or owe it — profit and cash are two different things.
- Start with your opening balance, then list cash in and cash out week by week (or month by month) for at least one full cash cycle.
- The assumptions matter more than the formulas: client payment delays, seasonality, and tax deadlines decide whether your forecast is useful or decorative.
- Build three versions — base case, worst case, best case — because a single-line forecast gives you false confidence.
- Update it every month against reality; an untouched forecast is fiction within six weeks.
What a cash flow forecast actually tracks (and why profit misleads you)
A cash flow forecast is a table showing the money coming into and going out of your bank account over a future period, and the resulting balance at each point. That's the whole idea. What makes it powerful is the timing discipline: an invoice issued in March and paid in June belongs in June, no matter what your accounting software says about March.
Cash versus profit: the distinction that saves businesses
Profit is an accounting measure of what you earned in a period. Cash is a physical fact about what's in the account. You can be profitable and insolvent at the same time — plenty of businesses are, right up until they aren't. A healthy profit margin means nothing if your customers pay in 60 days while your suppliers and your staff need money in 15.
Which is why I always build the forecast on actual payment dates, not on invoicing dates. If a client has historically paid you 12 days after the due date, that's your real assumption, not the 30 days written on the contract.
How far ahead should you project?
Long enough to cover your full cash conversion cycle — the time it takes for money to leave your account and come back. If you buy stock, hold it for six weeks, sell it, and get paid 30 days later, your cycle is roughly ten weeks. A forecast shorter than that will show comfortable numbers that are simply wrong.
My rule: weekly forecasts for three months, then monthly. Weekly granularity near-term catches the crunch points; monthly for the rest gives you a view without drowning you in detail.
How do I create a cash flow forecast?
You build it from four blocks, in this order: opening balance, cash inflows, cash outflows, closing balance. Each period's closing balance becomes the next period's opening balance — that single link is what makes the whole thing a chain of predicted bank balances rather than a scatter of numbers.
Step one: gather the real numbers before opening a spreadsheet
Don't start typing until you've collected these. This is where most people skip ahead and produce a forecast that looks tidy and tells them nothing.
- Aged receivables report — who owes you what, and how late they are
- Aged payables report — what you owe suppliers and when it falls due
- Your recurring fixed costs: rent, salaries, software subscriptions, loan repayments
- The tax and VAT schedule for the period (these dates are fixed, so they're the easiest thing to get right and the most commonly forgotten)
- Any known one-off items: equipment purchase, a big insurance premium, a bonus payment
Step two: set your assumptions explicitly
Put them at the top of a separate tab, in plain language. "Customer A pays 45 days late on average." "Revenue dips 20% in August." "We hire one person in Q3." Written assumptions let you change a variable and watch the effect, and they force you to be honest about what you're guessing.
The optimistic assumption I see most often: everyone pays on time. In my experience, for small businesses dealing with larger corporate clients, a realistic expected delay is somewhere between 45 and 60 days even on standard terms. Build the forecast on that, and a prompt payer feels like a bonus rather than a surprise.
Step three: build the grid
Lay out periods across the top (weeks or months), and these rows down the left side:
- Opening balance
- Inflows: sales receipts, other income, loan drawdowns
- Outflows: suppliers, payroll, rent, tax, loan repayments, capital purchases
- Net movement (inflows minus outflows)
- Closing balance
The closing balance row is your alarm system. If it goes negative in a period, you have a funding gap — and now you know about it months in advance instead of on the day.
How do you create a cash flow forecast in Excel?
Excel is genuinely fine for this. You don't need accounting software, and honestly the manual construction teaches you more than any template. Here's the structure I use.
The layout and the formulas that do the work
Columns B onward are your periods (say, January through December). Column A holds the row labels.
Row for opening balance, first period: your actual bank balance today. Every subsequent period: =B10, pointing at the previous period's closing balance.
Then two summary rows, inflows and outflows, each using =SUM() over the relevant category rows above. Then the closing balance: =B3+B8-B9 (opening plus inflows minus outflows). Repeat across the columns.
The one formula worth adding beyond that is a warning flag. Something like =IF(B10<0,"GAP","") next to the closing balance, colour-coded red. It's crude and it's the fastest way to spot a problem across a year of columns.
Making it survive contact with reality
Keep assumptions on their own sheet and reference them from the main grid. If your main client pays in 45 days rather than 30, you should be able to change one cell and have the whole year shift. Forecasts built with hard-coded numbers are dead within a month because updating them is too painful.
And lock the structure so you don't accidentally break the chain. I once overwrote an opening balance cell with a typed figure and spent 45 minutes confused about why the year-end projected something absurd. Embarrassing, but instructive: the chain of closing-to-opening links is the spine of the whole document.
| Approach | Best for | Effort | Main limitation |
|---|---|---|---|
| Spreadsheet built from scratch | Owners who want to understand the mechanics | Half a day to build, an hour a month to maintain | No automation; manual data entry |
| Excel template | Getting a working structure fast | One to two hours to adapt | Assumptions often buried inside formulas |
| Accounting software module | Businesses with high transaction volume | Low once configured | Its payment-delay assumptions are generic, not yours |
Can ChatGPT make a cash flow statement?
No — and this is worth being blunt about, because people try. A language model can help you structure a forecast: it can suggest row labels, write out the Excel formula logic, or draft the assumptions tab. It cannot produce a cash flow statement for your business, because it has no access to your bank data, your receivables, or your payment history.
What it can do reasonably well is act as a thinking partner. Paste in your list of monthly cash outflows and ask it to sanity-check whether anything obvious is missing — tax deadlines, seasonal costs, equipment replacement. I've found that useful for catching blind spots. What I would not do is ask it to generate numbers. It will, cheerfully, and they will be invented.
Where AI actually earns its keep
The genuinely useful application is scenario building. Once your base forecast exists, asking an assistant to help you reason through "what happens if our largest client leaves in Q2" forces you to articulate the dependency. The value is in the exercise, not the output.
How to generate cash flow in a business
Forecasting tells you where the gaps are. Generating cash is a separate discipline, and in my opinion it's where most owners leave the easiest money on the table.
The fastest levers, ranked by effort
Chasing overdue invoices is unglamorous and effective. A polite, specific email on day one past due — not week three — typically pulls in a meaningful chunk of what's outstanding. In my consultancy example, simply sending reminders on the due date rather than two weeks later cut our average collection delay from around 38 days to 22.
- Shorten payment terms and enforce them
- Ask for deposits or staged payments on larger jobs
- Renegotiate supplier terms from 15 days to 30 — it's free working capital
- Review subscriptions ruthlessly; small recurring charges add up quietly
- Sell slow-moving stock at a discount rather than holding it for full price later
What I got wrong about this
Early on, I treated a large invoice as money. My forecast showed a comfortable balance and I made a hiring decision on the back of it. The client paid four months late. We survived, but only because a supplier accepted a delayed payment — a conversation I never want to have again. Since then, every forecast I build has a line for "expected late receipts" sitting directly under the optimistic one, and I plan on the lower figure.
The mistakes that break a forecast
The biggest one isn't a formula error. It's optimism baked into the assumptions. Beyond that: forgetting tax payments, ignoring seasonality, and — most common of all — building the forecast once and never reopening it.
A forecast's value comes entirely from the comparison with what actually happened. Each month, put real figures next to predicted ones and note the variance. After three or four cycles you'll see a pattern in your own business: which clients always pay late, which cost categories always overrun. That pattern is worth more than any template you can download.
Which brings me to the thing I'd want you to take away. You're not building a document to satisfy an accountant or a lender. You're building a device that lets you see a cash crunch six weeks before it arrives — early enough to call the client, delay the purchase, or arrange a facility. The spreadsheet is just the shape of that foresight. And the first month the closing balance row catches something you'd have otherwise missed, you'll never go back to guessing.
