How to create a cash flow forecast for your business (without drowning in spreadsheets)
A client called me in a mild panic last spring. Her company had just closed its best quarter ever on paper, and yet she couldn't make payroll on the 28th. Profit on the P&L, zero in the bank. That's the moment you understand what a cash flow forecast is really for: it doesn't tell you whether your business is healthy, it tells you whether it will still be alive in six weeks.
Most founders build one after the first scare. I did too. I'll walk you through exactly how to create a cash flow forecast for your business, with the row structure I use, the Excel formulas that actually work, and an honest look at what an AI assistant can and cannot do for you.
Key Takeaways
- A cash flow forecast tracks money in and money out by date, not revenue and expenses by accounting period.
- Build it month by month for 12 months, with weekly detail for the next 8 weeks when cash is tight.
- The single most important assumption is your collection delay: when clients actually pay, not when you invoice.
- Excel works perfectly well. ChatGPT can help you structure it but cannot produce a real statement from your numbers without verified inputs.
- Growing companies run out of cash more often than shrinking ones. The gap between profit and bank balance is your working capital.
What a cash flow forecast actually is (and why profit lies)
Cash flow forecasting means projecting your opening balance, your inflows, your outflows and your closing balance over a defined period. That's it. No accruals, no depreciation, no matching principle.
Here's where people go wrong. They export the P&L from their accounting software, add a growth assumption, and call it a forecast. But the P&L records an invoice the day you issue it. Your bank records the payment the day it lands. Between those two dates, something happens: your customer takes 45 days to pay, you pay your staff on the 25th, and the VAT of the quarter is due on the 15th of the following month.
Why growing businesses run dry
In my first year running a small agency, we doubled our revenue in ten months. We also had the worst cash crunch of our existence. Every new client meant hiring a freelancer before the cash arrived, and every invoice went out with 30-day terms that clients happily stretched to 60.
The math is brutal: if you grow 10% per month and your customers pay in 60 days, you are permanently financing two months of additional cost out of your own pocket. Growth is a cash-hungry machine, and the forecast is the only instrument that shows it before the account turns red.
The row structure of a working cash flow forecast
Strip away the templates you'll find online and you're left with about fifteen rows. Here is the structure I've settled on after several iterations, and it fits both a two-person consultancy and a ten-person product business.
| Line | What goes in it | Pitfall to avoid |
|---|---|---|
| Opening balance | Bank balance at the start of the period | Forgetting pending card settlements |
| Cash sales collected | Money received immediately | Mixing with invoiced revenue |
| Receivables collected | Payments from invoices, dated by expected arrival | Using invoice date instead of payment date |
| Other inflows | Loans, grants, asset sales, tax refunds | Counting a loan before it's signed |
| Supplier payments | Actual payment dates, not invoice dates | Ignoring payment terms you negotiated |
| Payroll and social charges | Net salaries plus employer contributions | Forgetting the payment date shift |
| VAT / sales tax | Due according to your filing regime | Treating collected VAT as your money |
| Rent, subscriptions, insurance | Fixed recurring charges | Missing annual renewals |
| Loan repayments | Principal plus interest | Assuming the bank will reschedule |
| Taxes on profit | Instalments and balance | Ignoring the instalment schedule |
| Capex | Equipment, software licences paid upfront | Hiding it in "miscellaneous" |
| Closing balance | Opening + inflows − outflows | Letting it go negative without noticing |
Two rules make this table usable. First, every row is dated by the day cash moves. Second, any row you cannot predict with reasonable confidence gets an explicit assumption written next to it, not a guess buried in a formula.
A small business cash flow forecast example
Numbers speak louder than methodology. Here's a simplified three-month forecast for a design studio with roughly €40,000 of monthly revenue, two employees, and a mix of retainer clients and project work.
| Month | Opening | Receipts | Payments | Closing |
|---|---|---|---|---|
| April | €18,000 | €34,000 | €37,500 | €14,500 |
| May | €14,500 | €29,000 | €36,000 | €7,500 |
| June | €7,500 | €46,000 | €38,000 | €15,500 |
Look at May. Revenue is fine, the pipeline is full, and yet the closing balance drops by half. Why? One large client paid late, and the quarterly VAT payment landed the same week as payroll. Nobody saw it coming on the P&L. The forecast caught it in April, with three weeks to act: a reminder email to the late payer, and a request to defer one supplier invoice by fifteen days. Crisis avoided with two phone calls.
That's the real output of a forecast—a warning window, not a spreadsheet to admire.
How do I create a cash flow forecast in Excel?
You open a blank sheet, put your months across the top row, and build the rows as above. That's the whole secret. What matters is the formula structure.
- Row for opening balance. April cell = your actual bank balance. May cell = April closing balance. Drag right.
- Receipts block. One line per category, each cell typed manually from your invoice list, dated by expected payment.
- Payments block. Same logic, one line per category.
- Closing balance formula:
=Opening + SUM(Receipts) - SUM(Payments). - Minimum balance line. Add a row with the formula
=MIN(closing balance range)and conditional formatting that turns red below your safety threshold.
Build a second tab for weekly detail covering the next eight weeks. Monthly granularity is fine for planning, but payroll and VAT don't spread evenly across a month.
Scenario planning without rebuilding everything
Duplicate the sheet three times: pessimistic, realistic, optimistic. On the pessimistic version, stretch your collection delay by 30 days and add 10% to supplier costs. If the closing balance stays positive in that scenario, you can sleep. If it doesn't, you now know your exact cash gap and the month it appears, which is precisely what a bank or an investor will ask you for.
Can ChatGPT make a cash flow statement?
No, not a real one. ChatGPT can draft a template, suggest a row structure, write the Excel formulas, and explain the logic behind a forecast. It cannot produce a genuine cash flow statement for your company, because a statement requires your actual transactions, verified balances, and reconciliation against your bank account. An AI model has no access to any of that, and if you feed it invented or partial numbers you'll get a confident-looking document that is simply wrong.
Where it genuinely helps: describing your business model and asking for a plausible list of cash categories you might have forgotten, or asking it to sanity-check whether your collection assumptions look realistic for your sector. Use it as a sparring partner, not as a bookkeeper. The output goes into your sheet only after you've verified every line against a real document.
How to generate cash flow in a business
Forecasting tells you what's coming. Generating cash changes what's coming. Four levers do most of the work, and none of them require new customers.
- Shorten collection. Send invoices the day you deliver, not at month end. A polite reminder at day 20 rather than day 45 typically pulls payment forward by two to three weeks. On €40,000 of monthly receivables, that's real money sitting in your account instead of theirs.
- Ask for deposits. A 30% upfront payment on project work transforms your working capital position overnight. Clients rarely refuse when it's stated in the proposal rather than negotiated later.
Two more, less obvious:
- Negotiate supplier terms. Going from 30 to 45 days on your three biggest suppliers is often a five-minute conversation.
- Watch inventory and work-in-progress. Cash parked in stock or in unfinished projects is cash you cannot spend.
And the lever nobody likes: raise your prices. A 5% increase with unchanged volume flows straight to the bank, whereas a 5% cost cut usually takes months to materialise.
The habit that separates calm founders from stressed ones
Update the forecast every Monday morning. Fifteen minutes, no more. Replace last week's projections with what actually happened, push the horizon out by a week, and look at the minimum balance line.
Do that for three months and something shifts in how you run the business. You start pricing differently, because you can see the cash impact of a 60-day payment term. You start saying no to clients who pay badly, even when the project looks attractive on paper. The forecast stops being a chore and becomes the quiet instrument you check before every significant decision.
Which raises a question worth sitting with: if you had to survive the next six months on the cash already committed, would your current plan hold? If the answer is uncertain, that uncertainty is exactly where your Monday fifteen minutes should start.
