Cash Flow Forecast Spreadsheet: How to Build One for a Small Business

Profit and cash are not the same number, and the gap between them is where small businesses actually fail. You can invoice $18,000 this quarter, show a healthy profit on paper, and still bounce a payroll transfer in week seven because none of that $18,000 has landed in the bank yet. A profit and loss statement tells you how the business is doing. A cash flow forecast tells you whether you can pay the bills on the 15th. Both matter; only one gives you advance warning.

Here's how to build a rolling cash flow forecast in a spreadsheet, what rows it needs, and how the formulas should actually work.

What a cash flow forecast is (and isn't)

A cash flow forecast projects your bank balance forward, month by month, based on money you expect to actually receive and pay — not money you've earned on paper. It isn't a budget (a budget is what you plan to spend) and it isn't a P&L (a P&L is revenue and expenses matched to the period they relate to, regardless of when cash moves). A forecast cares about one thing: when does cash actually cross into or out of the account.

That distinction is the whole point. An invoice you sent last week is revenue on your books today. It's cash in three or six or eleven weeks — whenever the client actually pays — and a forecast has to place it there, not today.

The five rows every forecast needs

Whatever else you add, a working monthly cash flow forecast comes down to five lines, repeated across a row of months:

Run this across 12 rolling months — this month plus the next eleven, not a fixed calendar year that goes stale every January — and you have a forecast. Everything else is refinement.

Step 1 — Set the opening balance and let it carry forward

Type your actual current bank balance into month one's opening balance cell. Every month after that should reference the previous month's closing balance, not be typed in by hand. This one link is what turns twelve separate months into a single rolling projection — change an assumption in month three and months four through twelve should all move with it.

Step 2 — Split expected income into confirmed and uncertain

This is where most DIY forecasts go wrong: someone writes an optimistic revenue number for each future month based on a target, and the forecast quietly turns into a wish list. A forecast that's worth trusting separates the two:

• Confirmed income — unpaid invoices you've already sent, placed in the month they're due. If you're already tracking invoices with a due date column, this can pull automatically: sum the invoice amounts where the due date falls in that month and the status isn't yet "Paid."
• Manual override / new business — a separate row for income you expect but haven't invoiced yet. Keep it separate so you can always see how much of next quarter's number is real versus hoped-for.

If you're already keeping an invoice tracker with due dates and statuses, this is also exactly the data an accounts receivable aging report is built from — the two sheets should be reading from the same invoice list, not two separate ones that drift apart.

Step 3 — Split expenses into recurring and one-off

Recurring costs — rent, software subscriptions, insurance, loan repayments — are the easy part; they're roughly the same every month, so one row referencing a monthly total does most of the work. The harder part is remembering the irregular ones: the annual insurance renewal, the quarterly tax payment, the once-a-year conference ticket. These are exactly the expenses that blindside a forecast, because they don't show up until the month they hit. Keep a running list of anything that happens less often than monthly, with the month it's due, and add it as a one-off line in that specific month.

Step 4 — Net movement and closing balance

Net movement is simple subtraction: total expected income minus total expected expenses for the month. Closing balance is opening balance plus net movement. The only thing worth adding here is conditional formatting: flag any month where the closing balance goes negative. That's the entire point of doing this — catching the shortfall two months before it happens instead of the week it happens.

Step 5 — Add a runway figure

Runway is a single number: at your current burn rate, how many months until the money runs out. It's the number to look at first, before the twelve-column grid, because it answers the question in one glance. If your closing balance is trending down and runway reads four months, you have four months to fix it — not a vague sense that things feel tight.

Step 6 — Test more than one scenario

A single-line forecast tells you what happens if your assumptions are exactly right, which they won't be. Build (or at least sketch) three versions: a conservative case where a slow-paying client stays slow and no new business lands, a base case using your trailing actuals, and a growth case where the pipeline converts. The gap between conservative and base is usually the real number to plan around — it's the cushion you need, not the average.

Common mistakes

• Forecasting revenue instead of cash. An invoice due in 45 days is not money you have in 5.
• Re-typing the opening balance each month instead of linking it to the prior month's closing balance — breaks the roll-forward and hides errors.
• Forgetting annual and quarterly costs because the monthly view never surfaces them until the month they land.
• One optimistic scenario only. A forecast with no downside case isn't a forecast, it's a hope.
• Not updating it. A forecast built once in January and never touched again is a historical document by March. Rebuild the "expected income" row from your actual invoice list monthly.
• Ignoring the runway number. The 12-column grid is easy to skim past; the single "months of runway" figure is the one that should trigger action.

Google Sheets vs Excel for this

Either works. The forecast leans on SUMIFS-style formulas — summing invoice amounts where the due date falls within a given month and the status isn't "Paid" — which both handle fine. Excel is the steadier choice for a large invoice list and heavier formulas; Google Sheets wins if you want an accountant or business partner to glance at the live numbers without emailing a file back and forth. If you're forecasting off a live invoice tracker, keep it in the same workbook as your income and expense records so the two never fall out of sync — see our guide on tracking income and expenses in a spreadsheet for how to structure that base layer.

What good looks like

A forecast you'll actually trust has: an opening balance that rolls forward automatically, expected income split into confirmed-from-invoices and speculative, every recurring and known one-off expense accounted for by month, a closing balance that flags red the moment it goes negative, and a single runway number you check first. Build that once and updating it monthly is a ten-minute task, not a rebuild.