Cash Flow Forecast in Excel — Free Template + 12-Month Guide (2026)

More businesses fail from running out of cash than from being unprofitable — and most of those failures were visible months in advance to anyone with a working forecast. In 45 minutes you’ll build a complete 12-month cash flow forecast in Excel: a clean inflow/outflow structure, a running closing balance that rolls itself forward, realistic receivables timing, seasonality, best/base/worst scenarios, a 13-week version for when cash gets tight, and a chart with a minimum-cash alert. Works in Excel 2010 through Microsoft 365.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is a Cash Flow Forecast?

A cash flow forecast is a forward-looking model of your bank balance. It lists the cash you expect to receive and the cash you expect to pay out, period by period, and rolls the resulting balance forward so you can see — today — what your account will look like in three, six, or twelve months. It is the single most important financial model a small or growing business can maintain, because it answers the one question accounting reports dodge: will we have enough money in the bank when the bills land?

It is critically different from a profit forecast. Profit recognises revenue when it is earned and costs when they are incurred; cash flow recognises money only when it actually moves. A consultancy that invoices £50,000 in March but gets paid in May is £50,000 more profitable in March — and not a penny richer until May. Meanwhile payroll, rent, and the VAT bill leave the account on their own schedule. That gap between profit and cash is where healthy-looking businesses quietly die. A cash flow forecast exists precisely to expose it.

The mechanics are refreshingly simple. Every period (usually a month) has four lines: the opening balance (what you start with), total inflows (customer receipts, loan drawdowns, tax refunds), total outflows (payroll, suppliers, rent, tax, loan repayments), and the closing balance, which becomes next period’s opening balance. Chain twelve of those together and you have a 12-month forecast. Everything else in this guide — payment-terms lags, seasonality, scenarios — is about making those inflow and outflow estimates honest rather than hopeful.

A good forecast earns its keep three ways. It gives you early warning: a cash dip in month seven, spotted today, can be fixed with a chase on debtors, a delayed purchase, or an overdraft arranged calmly rather than begged for in a panic. It supports decisions: can we afford the new hire in Q3? What happens if the big client pays 30 days late? And it builds credibility: banks and investors expect to see one, and a business that can show twelve months of forecast-versus-actual discipline borrows on far better terms than one that cannot.

Before You Start

Gather three things before you open Excel — the model is only as good as these inputs.

Your true opening cash position

Start from your cleared bank balance today, across all accounts, minus any cheques or payments already issued but not yet debited. If you have a balance on a credit facility or an overdraft in use, decide whether to model gross cash plus a separate facility line, or net cash — and stay consistent. Most small businesses are fine starting with a single net figure.

A realistic list of inflows and outflows

Pull the last six to twelve months of bank statements and categorise every line. This does two jobs: it produces the category list for your forecast rows, and it gives you base monthly figures grounded in reality rather than optimism. The categories that catch people out are the lumpy ones — quarterly VAT, annual insurance, corporation tax, loan balloon payments. Hunt them down now; they are exactly the items a forecast exists to catch.

Your real payment terms — both directions

Note when customers actually pay (not what the invoice says: if terms are 30 days but the average is 47, use 47) and when you actually pay suppliers. These lags drive Step 4, and getting them roughly right matters far more than precision anywhere else in the model.

1.Step 1 — Structure the Inputs

Lay out the skeleton before any numbers. In column A, build this row structure:

Keep the category list short enough to maintain — six to ten rows per section is plenty. A forecast with forty micro-categories gets abandoned by month two; a forecast with eight honest categories gets updated every month for years. You can always split a category later when it grows large enough to deserve its own line.

Resist the urge to copy your profit-and-loss categories wholesale. Cash categories follow the bank statement, not the P&L: depreciation does not appear at all (no cash moves), while loan repayments and VAT — invisible on the P&L — get prominent rows. If you also maintain a P&L model, our profit & loss statement in Excel guide pairs naturally with this one.

2.Step 2 — Build the 12-Month Grid

Now stretch the structure across time. In B1, enter the first day of your starting month (e.g. 01/07/2026). In C1, generate the next month automatically:

Drag it across to column M and you have 12 month headers that update themselves if you ever change the start date. Format the row with a custom number format of mmm-yy (Ctrl+1 → Custom) so the headers read “Jul-26”, “Aug-26”, and so on while remaining real dates underneath — you will use MONTH() against them in Step 5.

Populate the input rows with your base estimates from the prep work: average monthly receipts in the receipts row, payroll in the payroll row, and so on. Do not agonise over precision yet — flat averages are fine for a first pass, because Steps 4 and 5 will reshape them. Do place the lumpy items in their correct months now: VAT in its quarterly payment months, the insurance renewal in its month, corporation tax in its month. Lumpy items in the wrong month are the most common reason a forecast misses a crunch.

Finally, freeze panes so the labels stay visible: click cell B2, then View → Freeze Panes. Column A and row 1 now stay put while you scroll the months.

3.Step 3 — Net Movement & the Running Closing Balance

This is the engine of the whole model — three formulas. First, in B18, net movement for the month:

Second, in B20, the closing balance:

Third — the one that makes the model “roll” — in C2, link the new month’s opening balance to the previous month’s closing balance:

Now select C2:C20... actually, select the formula cells (C2, C9, C17, C18, C20 once you’ve copied B9/B17 across) and fill the whole block right to column M. Every month’s opening balance now equals the prior month’s closing balance, so a change to any single number anywhere in the grid ripples instantly through every later month. Type a bigger payroll figure in September and watch the December closing balance fall — that immediate cause-and-effect is what makes the model useful in meetings.

Only B2 — the very first opening balance — is a hard-typed number. Everything downstream is formula. If you ever find yourself typing over a closing balance to “correct” it, stop: fix the inflow or outflow that is wrong instead, or the chain silently breaks.

4.Step 4 — Receivables & Payables Timing: the Cash-vs-Revenue Gap

Here is where a forecast becomes honest. If you invoice on 30-day terms, the cash from March invoices arrives in April — putting March sales in March’s receipts row overstates your cash by a full month, permanently. The fix is to model invoiced sales and cash received as separate rows.

Add a row (say row 25, below the model) labelled “Invoiced sales” with your sales forecast by month. Then make the Customer receipts row (row 4) pull from it with a lag. For uniform 30-day terms:

…entered in C4 (April receipts = March invoices) and dragged across. For the realistic case where collections spread over several months, split each month’s invoices into buckets. Suppose 60% pays the following month, 30% two months on, and 10% three months on. Then April’s receipts (D4, with invoices in row 25 starting at B25) are:

Put the percentages in cells ($B$27:$B$29) rather than hard-coding, so you can test “what if collections slip by 10%” in one keystroke. Apply exactly the same logic in reverse to supplier payments: if you pay on 30-day terms, your outflow for March purchases lands in April. Paying suppliers on terms while collecting promptly is free working capital; the spreadsheet makes the size of that effect visible.

5.Step 5 — Layer In Seasonality

Almost no business earns evenly across the year — retail peaks in December, B2B dies in August, accountancy spikes around filing deadlines. A flat-average forecast hides exactly the dips a forecast exists to find. Encode the shape with a seasonal index.

From last year’s sales, compute each month’s share of an average month: divide each month’s sales by the 12-month mean. A December that does 40% better than average gets an index of 1.40; a dead January gets 0.70. Store the 12 multipliers in a vertical range, say $P$2:$P$13 (P2 = January’s index … P13 = December’s). Then build the invoiced-sales row from a single base figure (in $B$24) shaped by the right index:

Because row 1 holds real dates, MONTH(B1) returns 1–12 and INDEX fetches the matching multiplier. Change the base figure once and the whole seasonal year reshapes. Apply indices to receipts and to genuinely volume-linked costs (stock purchases, shipping, card fees); leave fixed costs (rent, salaries, insurance) flat.

One subtlety that catches even experienced modellers: the cash effect lags the sales effect. With 30-day terms, a blockbuster December produces blockbuster January cash — while December itself may be a heavy cash-out month as you stock up. Because you built the lag in Step 4, your model now handles this automatically: seasonality shapes the invoiced row, and the lag formulas shift the cash. This interaction — strong sales months being weak cash months — is the insight that makes people gasp at the chart in Step 8.

6.Step 6 — Best, Base & Worst Scenario Columns

A single forecast line is a single guess. Decision-grade forecasts show a range. Build a small control block, say in R1:U5:

Then add three closing-balance rows below the model — one per scenario — each computed from the base model’s flows scaled by its assumptions. The simplest robust pattern: a scenario’s net movement equals base inflows times its receipts factor minus base outflows times its cost factor:

…where B30 is the scenario’s prior-month closing balance and S$2/S$4 are that scenario’s assumption cells. Fill right for 12 months, repeat for each scenario column of assumptions, and you have three balance trajectories you can chart together. (For the collection-slip assumption, the cleanest implementation is to shift the receipts lag from Step 4 by one extra month in the worst-case receipts calculation.)

Read the result two ways. The spread between best and worst at any month is your uncertainty — if it is wider than your cash buffer, you need contingency plans, not just a forecast. And the worst-case minimum is your funding requirement: if worst-case cash bottoms at −£18,000 in October, you need a facility of at least that, arranged now while you do not need it. Banks lend umbrellas in sunshine.

7.Step 7 — The 13-Week Forecast for Tight Cash

Monthly granularity has a blind spot: it cannot see inside the month. You can open July with £20,000, close it with £15,000 — and have gone £8,000 overdrawn in week two when payroll left before the big receipt arrived. When cash headroom drops below roughly three months of outgoings, the professional tool is the 13-week cash flow forecast: the same structure, weekly columns, one quarter ahead. It is the format turnaround specialists and lenders use, for good reason.

Set up week-ending dates across 13 columns, generated from the first Friday:

The crucial discipline change: at weekly granularity you stop forecasting averages and start scheduling actual items. Each receipt is a specific invoice with a due date; each payment is a specific payroll run, rent debit, or supplier payment run. Pull your open invoices into a list with due dates and let Excel place them in the right week:

…which sums every invoice due in the seven days ending on the column’s week-ending date. (Add a few days’ grace to due dates to reflect reality.) Do the same for supplier bills. The opening/net/closing chain from Step 3 works identically — just weekly.

Run it as a rolling forecast: every Monday, replace last week’s forecast column with actuals, add a new week 13 at the far end, and note the variance. Within four or five weeks of this routine your near-term forecasting accuracy improves dramatically, because every miss teaches you which assumption was wrong.

8.Step 8 — Chart the Forecast & Add a Minimum-Cash Alert

Numbers persuade analysts; lines persuade everyone else. Select the month headers (row 1) and the closing-balance row (row 20) — Ctrl-click to select both — and insert a Line chart (Insert → Charts → Line). You now have the trajectory of your bank balance for the next year, and the question “where is the low point?” answers itself visually.

Now add the alert. Decide your minimum acceptable cash — a sensible default is one full month of outgoings (payroll + rent + the typical supplier run) — and put it in a cell, say $B$22. Fill a row with that same value across all 12 months and add it to the chart as a second series: it draws as a flat horizontal threshold line. Any month where the closing-balance line dips below the threshold is a funding gap, visible months before it happens.

Back in the grid, make breaches impossible to miss with conditional formatting. Select the closing-balance row, then Home → Conditional Formatting → New Rule → Use a formula, and enter:

…with a red fill. Add a second rule, =B20<0, with a darker red for outright negative months. Now the model audits itself: open the file, and any month at risk is already glowing. If you went on to build the three scenario rows in Step 6, add all three to the chart — the fan of lines against the threshold is the single most persuasive slide you will ever show a bank manager.

Try It: 12-Month Cash Flow Projector

This is the whole model in miniature. Enter an opening balance, monthly cash in and out, and a monthly growth rate for each. The chart projects the closing balance for 12 months exactly as your Step 3 formulas would — opening + in − out, rolled forward — and highlights the lowest point and your months of runway. Try giving costs a faster growth rate than receipts and watch how early the trouble becomes visible: that early warning is the entire point of the exercise.

Advanced Cash Flow Forecasting Tips

Reconcile to actuals every month — and keep score

Add an “Actual” row beneath each forecast row and a variance row beneath that (=Actual−Forecast). Patterns in the variance are gold: if receipts consistently land 8% under forecast, your collection assumption is wrong — fix the assumption, not the month. A forecast that is reconciled monthly compounds in accuracy; one that is not decays into fiction by quarter two.

Model the overdraft properly

If you have a facility, add a row for facility drawn/repaid and let it absorb negative months: =MAX(0,-(B2+B18)) computes the draw needed to keep cash at zero. Charting “facility utilisation” alongside cash shows headroom on the resource that actually constrains you.

Direct vs indirect method

Everything in this guide is the direct method — forecasting actual receipts and payments — which is right for operational horizons up to a year. The indirect method (start from forecast profit, adjust for working-capital movements, depreciation, and capex) suits multi-year strategic models and is what investors expect in a fundraising deck. Many businesses maintain both: direct for the next 12 months, indirect for years two and three.

From spreadsheet to live dashboard

Once the forecast drives weekly decisions, the spreadsheet’s manual refresh becomes the bottleneck. Upload your cash flow workbook to DataHub Pro and it becomes an interactive, always-current dashboard — closing-balance trajectory, threshold alerts, and scenario lines — shareable as a link with your co-founder, FD, or bank instead of a Friday-night email attachment.

Worked Example: a Studio With a Hidden October Problem

Meet a design studio with £25,000 in the bank on 1 July. It invoices £18,000 a month on 30-day terms, growing 4% a month off the back of a new retainer. Outgoings are £21,000 a month — £14,000 payroll, £3,000 rent and software, £4,000 contractors — growing 1%. On a profit basis the picture is rosy: revenue overtakes costs around month five and accelerates away.

Now run the cash model. July’s receipts are June’s invoices — the old, smaller number — while July’s payroll is paid in full at the new level. Net movement runs roughly £3,000 negative for the first several months: opening £25,000 becomes ~£22,000 by end-July, ~£19,200 by end-August, ~£16,600 by end-September. The trough arrives around October–November at roughly £12,000–£14,000 — uncomfortably close to one month of payroll — before the compounding 4% receipt growth finally outpaces costs and the balance climbs back through £25,000 around month ten and ends the year near £40,000.

The decision value is concrete. Knowing in July that October is the squeeze, the studio can: invoice the retainer monthly-in-advance instead of in-arrears (pulling a month of cash forward and lifting the trough by ~£18,000); delay a planned £6,000 equipment purchase from September to December; or arrange a £10,000 facility now, when the bank sees a growing business, rather than in October when it sees a draining one. Run the same numbers through the projector widget above — the defaults are this example — and watch the yellow low-point marker land exactly where the trouble is.

And the worst case? Re-run with receipts −15% and a one-month collection slip: the trough drops below zero around September. That single red dip below the axis is the difference between “we should keep an eye on cash” and “we arrange a facility this week” — which is precisely the conversation a forecast exists to start.

Common Errors & Fixes

The closing balance doesn’t change when I edit an input

A balance cell has been typed over with a number, breaking the formula chain. Click along the opening and closing rows: every cell except the very first opening balance must show a formula in the formula bar. Re-enter =B20-style links where numbers have crept in.

My forecast says I’m fine, but the bank account disagrees

Almost always a timing error: revenue booked as cash in the invoice month (fix with Step 4’s lag), VAT forecast net instead of gross, or a lumpy annual cost (insurance, corporation tax) missing entirely. Reconcile last month’s forecast against the bank statement line by line — the culprit will be obvious.

Circular reference warning

You have pointed a month’s opening balance at its own closing balance (e.g. C2=C20) instead of the previous month’s (C2=B20). Excel flags it in the status bar; fix the reference and the warning clears. Interest-on-overdraft formulas can also create deliberate circularity — avoid them, or enable iterative calculation knowingly.

#REF! errors after deleting a category row

The SUM ranges in Total inflows/outflows referenced the deleted row. Make the SUMs span from the first to the last category row inclusive, and when adding categories, insert rows inside the range so the SUM expands automatically.

The chart’s months show as serial numbers

The header row lost its date formatting. Select row 1, Ctrl+1, Custom, mmm-yy. If headers were typed as text (“July”), MONTH() in Step 5 will also fail — re-enter them as real dates.