How to Make a Cash Flow Forecast in Excel (Step by Step)

Excel is still the most common tool for small business cash flow forecasts: it’s flexible, most people know the basics, and accountants and lenders can open the file. This guide builds a 12-month forecast from a blank workbook, explaining each formula, then adds a buffer warning, a summary and a chart. If you’d rather skip the building, the free 12-month template has all of this done.

Step 1: Plan the layout

A forecast runs left to right through time and top to bottom through the categories. Use this structure:

Column A holds the labels, columns B to M the twelve months, and column N a total. Keeping settings at the top in their own cells means formulas can point at them rather than containing typed numbers.

Step 2: Enter the settings

In B5 type the first month as a date, for example 1 October 2026. In B6 type today’s bank balance. In B7 type the minimum balance you want to keep. Give these cells a pale yellow fill so it’s obvious they are inputs.

Optional but helpful: name the cells. Select B6, click the Name Box to the left of the formula bar, type OpeningBalance and press Enter. Do the same for StartDate (B5) and MinBuffer (B7). Formulas such as =OpeningBalance are far easier to read than =$B$6.

Step 3: Create the month headers

In B10 enter =StartDate. In C10 enter =EDATE(B10,1), which adds one month, and copy it across to M10. Format the row as “mmm yy”. Change the start date and every header updates.

Step 4: Add your cash lines

Type the cash in categories in A13 to A18, for example customer payments, invoice collections, owner investment, loans, tax refunds and other income. In A19 type Total cash in.

Type cash out categories from A21, for example payroll, rent, suppliers, loan repayments, insurance, software, marketing, sales tax, income tax, equipment and owner drawings, with Total cash out at the end.

Fill the number cells for each line with the same yellow as the settings, to mark them as inputs.

Step 5: The four formulas that make it work

1. Totals. In B19 enter =SUM(B13:B18) and copy across. Do the same for Total cash out. Because the range spans all the lines, inserting a new row inside it is included automatically.
2. Net cash flow. In B35 enter =B19−B33 (total in minus total out) and copy across.
3. Opening balance. In B11 enter =OpeningBalance. In C11 enter =B36, the previous month’s closing balance, and copy across to M11. This is the link that chains the months together.
4. Closing balance. In B36 enter =B11+B35 and copy across.

Test it: change the opening balance by 1,000 and check every closing balance moves by exactly 1,000. Put a 5,000 payment in March and check every month from March onwards falls by 5,000.

Step 6: Enter your numbers

Enter receipts in the month they’ll reach your bank and payments in the month they’ll leave it. Use your last three months of bank statements to check how quickly customers really pay. Include the lumpy items: quarterly tax, annual insurance and planned purchases. The how to make a cash flow forecast guide covers what to include in detail.

Step 7: Add a buffer warning

Select the closing balance row B36:M36. Choose Home, Conditional Formatting, Highlight Cells Rules, Less Than, and enter =MinBuffer. Pick a light red fill. Any month below your minimum now turns red.

For a clearer signal, add a row underneath with =IF(B36<MinBuffer,"LOW","OK") copied across.

Step 8: Add a summary

Above the grid, add a small summary block:

Format the month result as “mmmm yyyy”. These five numbers are what you’ll look at first every time you open the forecast.

Step 9: Add a chart

Select the month headers, Total cash in, Total cash out and Closing balance rows. Insert a clustered column chart, then right-click the closing balance series, choose Change Series Chart Type and make it a line. You now see money in and out as bars and the balance as a line, with the dips obvious at a glance.

Step 10: Keep it up to date

At each month end, overwrite that month’s forecast figures with actual figures from your bank statement, note any big differences and adjust future months. Some people add a second sheet with the same layout for actuals and a third that subtracts one from the other to show variances.

Time-savers once it’s built

• Freeze panes. Select B11 and choose View, Freeze Panes, so labels and month headers stay visible as you scroll.
• Group rows. Select the individual cash out lines and choose Data, Group, so you can collapse them and see only totals.
• Fill right. Select a formula cell and the empty cells to its right, then press Ctrl+R to copy the formula across.
• Trace precedents. Formulas, Trace Precedents shows which cells feed a total, which is the fastest way to find a missing line.
• Percentages for variable costs. If a cost is always a share of sales, enter the percentage in a settings cell and use a formula such as =B13*CardFeePct, so the cost moves with your sales forecast.
• A scenario cell. Add a “sales adjustment” cell set to 100%, and multiply receipts by it. Changing it to 80% gives you a quick worst case.

Common Excel mistakes

• Typing numbers into formula rows. If a total looks wrong, fix the inputs, not the total.
• Ranges that miss a row. When you add a line below the last row of a section, check the SUM includes it.
• Hard-coded months. Use EDATE from a start date so the whole forecast can roll forward.
• Mixed weeks and months. Keep every column the same length of time.
• No backup. Save a clean copy before making big changes.

Weekly forecasts in Excel

The same structure works for a 13-week forecast: set the first header to a Monday and use =B10+7 for each following week. Weekly detail is worth it when cash is tight or you pay staff weekly; see the 13-week cash flow forecast guide.

Skip the build

Our templates use exactly these formulas, with the layout, colours, summary and chart ready to go, and they work in Excel and Google Sheets.

Premium industry templates add a linked 13-week view, scenarios, actual vs forecast tracking and calculation tabs for your industry.