Build a Simple Cash Flow Forecast in Excel

A cash flow forecast in Excel shows how much money you expect to come in and go out each month, and what your bank balance will be at the end of each one. It warns you early about months where cash runs low, even when the business is profitable. This guide builds a four-month forecast with SUM, a rolling closing balance and one growth assumption you can change.

How a cash flow forecast is laid out#

A forecast has months across the top and cash items down the side. Cash in sits above cash out, and the balances sit at the bottom.

You type the opening balance for January, the expected receipts for January and each cost. Everything in bold, the later opening balances and the receipts from February onwards are formulas.

Step 1: List cash in and cash out#

Cash flow counts money when it actually moves, not when you send an invoice. If customers pay 30 days after you invoice, January's sales show up as February's receipts.

• Cash in: customer receipts, loans received, grants, money from selling equipment.
• Cash out: rent, wages, supplies, loan repayments, tax payments, equipment.

Use one row per item and keep the same items every month, even when a value is 0. Shade input cells in a light color so it's clear what to type and what's calculated.

Step 2: Total each group with SUM#

SUM adds every number in a range. In B5, total cash in:

=SUM(B3:B4)

In B10, total cash out:

=SUM(B6:B9)

January's totals are 12,500 in and 12,300 out. Select B5:E5 and press Ctrl+R (Mac: ⌘R) to fill right, then do the same for row 10. You can also click a total cell and press Alt+= (Mac: ⌘⇧T) to let AutoSum write the formula. Check that it picked the right rows. The AutoSum shortcut guide and the SUM function guide cover both ways.

If you add a cost row later, insert it inside the group, above Equipment for example. The SUM range then grows by itself. A row added below the last item isn't included.

Step 3: Calculate net cash flow and the closing balance#

Net cash flow is cash in minus cash out. In B11:

=B5-B10

The closing balance is the opening balance plus net cash flow. In B12:

=B2+B11

January closes at 8,200. Fill both formulas right to April.

Step 4: Roll the balance forward#

Each month starts with the money the previous month ended with. In C2, link to January's closing balance:

=B12

Fill it right to E2. Now February opens at 8,200, March at 8,100 and April at 4,230. Change any number in January and every later month updates.

This chain is a kind of running total: each balance builds on the one before. The guide to a running total in Excel shows the same idea down a column.

Step 5: Drive the forecast with assumptions and $ references#

An assumption is a number you choose rather than know, like how fast sales will grow. Put each one in its own cell below the forecast, so you can test different scenarios.

In A15, type Monthly growth and in B15, type 5%. Then in C3:

=ROUND(B3*(1+$B$15),0)

Fill it right to E3. Receipts grow from 12,000 to 12,600, 13,230 and 13,892. ROUND keeps them to whole numbers.

The $ signs matter. Without them, the reference to B15 would slide to C15, D15 and E15 as you fill right, and those cells are empty, so growth would drop to 0%. With $B$15, every month uses the same rate. Press F4 (Mac: ⌘T) to add the signs. Practise relative and absolute references if you're unsure which cells to lock.

Try changing B15 to 2% or 8%. The whole forecast updates in a second, which is the point of keeping assumptions in cells.

Step 6: Find the danger months#

The most important number in a cash forecast is the lowest balance. In B17:

=MIN(B12:E12)

The lowest balance is 4,230, in March, when the equipment purchase lands. To flag months below a safety buffer, put the buffer in B16 (say 5,000) and add a check row:

=IF(B12<$B$16,"Low","OK")

March shows Low. Now you can act early: delay the equipment, ask customers to pay sooner, or arrange a short-term loan.

You can also color low balances with Home > Conditional Formatting > Highlight Cells Rules > Less Than, set to 5,000.

Keep the forecast up to date#

A forecast is most useful when you compare it with reality and roll it forward:

• Each month, replace the forecast with actual numbers for the month that just ended.
• Add a new month at the end so you always look the same distance ahead.
• Check big differences between forecast and actual, and adjust your assumptions.

For a personal version, the monthly budget in Excel uses the same in-and-out structure. If you bill by the hour, feed your timesheet totals into the receipts row. And to value future cash flows in today's money, see the NPV function in Excel.

Practise it#

Try the exercise on this page: one closing-balance formula with two SUMs. Then practise SUM and AutoSum in a live spreadsheet, and practise absolute references so your growth assumptions fill across every month correctly.