How-To· 8 min read

How to Build a 12-Month Cash Flow Forecast in Excel

A cash flow forecast answers one question: will there be enough money in the bank each month? In Excel it is twelve rows, one formula that carries the balance forward, and three checks that warn you about the bad months early.

Short on time? Our Cash Flow Forecast template carries every balance forward and flags deficit months for you. See the Cash Flow Forecast template →

Cash flow is not profit

A business can be profitable and still run out of cash, because customers pay late, stock is bought before it is sold, and tax bills arrive in lumps. A forecast tracks when money actually arrives and leaves, not when a sale is made.

Step 1: The layout

Put the months down the rows, one per row, and these columns across: Month, Opening balance, Cash in, Cash out, Net, Closing balance. Twelve rows, from row 2 to row 13.

Months in rows rather than columns makes the formulas easier to read and copy. If you prefer months across the top, the same logic works sideways.

Step 2: The formulas

  • Net (E2): =C2-D2
  • Closing balance (F2): =B2+E2
  • Opening balance for every month after the first (B3): =F2, the previous month's closing balance.

Type the real opening balance only in B2. Copy B3 down to B13 and E2:F2 down to row 13. Now changing any month's cash in or cash out re-forecasts the rest of the year.

Step 3: What goes in cash in and cash out

Keep it simple at first with one figure per month, then break it down if you need to:

  • Cash in: customer payments (when you expect to be paid, not when you invoice), loans received, owner investment, tax refunds.
  • Cash out: rent, wages, suppliers, loan repayments, tax and VAT/GST payments, one-off purchases.

If you want more detail, put each line item on its own sheet and pull the monthly totals into this one with SUM.

Step 4: The three checks that matter

  • Lowest closing balance: =MIN(F2:F13). This is the number to watch. If it goes negative, you need an overdraft, a delayed purchase or faster collections.
  • Months spending more than coming in: =COUNTIF(E2:E13,"<0")
  • Months below zero: =COUNTIF(F2:F13,"<0")

Add conditional formatting to column F (Highlight Cells Rules → Less Than → 0) so a shortfall month turns red the moment it appears.

Step 5: Test a bad scenario

Copy the sheet and, on the copy, push the biggest customer's payments back by a month or cut cash in by 20%. Watch what happens to the lowest balance. If one late payer makes you go negative, that is worth knowing now.

Keep it current

Each month, replace the forecast figures for the month just ended with what actually happened, and roll the forecast forward a month. A forecast that is updated monthly gets more accurate; one built once and left alone is wrong by spring.

Frequently Asked Questions

What is the formula for closing balance in a cash flow forecast?

Closing balance = opening balance + cash in - cash out. Each month's opening balance is the previous month's closing balance.

What is the difference between a cash flow forecast and a budget?

A budget plans income and spending for the year. A cash flow forecast shows when the money actually moves, month by month, so you can see when you would run short.

How far ahead should I forecast?

Twelve months is standard. Update it every month, replacing the month just ended with actual figures.

Can I use a cash flow forecast in Google Sheets?

Yes. The formulas are simple arithmetic plus MIN and COUNTIF, which work the same in Google Sheets.

Get the ready-made 12-month cash flow forecast

Opening balance once, then every month carries forward. It shows the lowest balance, deficit months and months below zero. Works in Excel and Google Sheets.

See the Cash Flow Forecast template →