Cash flow spreadsheet: how to set one up, with the formulas and a worked example

Short answer: a cash flow spreadsheet has one column per month and one row per kind of money coming in or going out. Each month starts with the opening balance, adds total cash in, takes off total cash out, and ends with the closing balance, which becomes next month’s opening balance. Five formulas do all the work: two SUMs for the totals, one subtraction for net cash flow, one addition for the closing balance, and one cell that copies the closing balance across. The row to read is the closing balance: the month where it is lowest is the one to plan for. If you would rather not build it, the free cash flow forecast template does the same sums as you type and downloads a spreadsheet with the formulas already in it.

The layout

Put the labels in column A and one month in each column from B. A six-month sheet uses B to G; a year uses B to M.

Row	Column A	What goes in it
1	Month	Nov, Dec, Jan…
2	Opening balance	The bank balance at the start of the month
3	Customer receipts	Money customers actually pay in that month, not what you invoice
4	Other cash in	Loans received, grants, money you put in, an asset sold
5	Total cash in	Formula
6	Rent
7	Wages
8	Stock and supplies
9	Other running costs	Insurance, software, phone, card fees, fuel
10	Tax	VAT or GST, income tax, PAYE, super or pension, in the month you pay it
11	Equipment	Anything bought outright: vans, tools, computers
12	Drawings or loan repayments	What you take out, and repayments of any borrowing
13	Total cash out	Formula
14	Net cash flow	Formula
15	Closing balance	Formula

Add or merge rows to suit the business (a café wants a separate stock row, a consultant may not need one), but keep cash in above cash out and the four formula rows at the bottom.

The formulas

Type these into column B, then copy them across to the other months:

• Total cash in (B5): =SUM(B3:B4)
• Total cash out (B13): =SUM(B6:B12)
• Net cash flow (B14): =B5-B13
• Closing balance (B15): =B2+B14
• Opening balance for the next month (C2): =B15, copied across to D2 onwards. Only B2, the first month, is typed in: it is today’s bank balance.

Two checks worth adding below the table:

• Lowest balance: =MIN(B15:G15) shows the low point of the period, the number to plan around.
• Sum check: =B2+SUM(B14:G14) must equal the last closing balance (G15). If it does not, a formula has been overwritten with a typed number somewhere.

The formulas work the same in Excel, Google Sheets and Numbers. To make a low month jump out, add conditional formatting to row 15 that turns a cell red when it is below the cushion you want to keep (one month of costs is a common choice; see how many months of cash a business should have).

A worked example: a dog groomer in Leeds

These figures are made up. Priya is a sole trader running a dog grooming salon in Leeds with one part-time assistant. Her sales are under the £90,000 VAT registration threshold, so there is no VAT in her figures. On 1 November she has £8,000 in the bank. She knows two big payments are coming: her £3,200 Self Assessment bill on 31 January (the balancing payment for last year plus the first payment on account for this one), and a £1,800 hydraulic grooming table she plans to buy in February.

£	Nov	Dec	Jan	Feb	Mar	Apr
Opening balance	8,000	8,750	10,700	6,750	4,470	4,750
Customer receipts	6,500	7,800	4,900	5,200	6,000	6,400
Total cash in	6,500	7,800	4,900	5,200	6,000	6,400
Rent	900	900	900	900	900	900
Wages	1,600	1,600	1,600	1,600	1,600	1,600
Supplies	450	550	350	380	420	450
Other running costs	300	300	300	300	300	300
Tax (Self Assessment)	0	0	3,200	0	0	0
Equipment	0	0	0	1,800	0	0
Drawings	2,500	2,500	2,500	2,500	2,500	2,500
Total cash out	5,750	5,850	8,850	7,480	5,720	5,750
Net cash flow	+750	+1,950	−3,950	−2,280	+280	+650
Closing balance	8,750	10,700	6,750	4,470	4,750	5,400

What the sheet tells her

1. December’s £10,700 is not spare money. The Christmas rush leaves her with the highest balance of the six months, but £3,200 of it is already owed to HMRC at the end of January. Without the tax row, December looks like a good time to buy the table.
2. The low point is £4,470, at the end of February. January is quiet (£4,900 in, against £7,800 in December) and has the tax bill; February has the table. Together they take the balance below one month of her normal running costs and drawings (about £5,750), the cushion she wants to keep.
3. Moving the table to April lifts the low point. With the £1,800 in April instead of February, February’s net cash flow is −480 and the balances run 6,750, 6,270, 6,550, then 5,400 at the end of April. The six-month total does not change (she still ends April on £5,400), but the low point rises from £4,470 to £5,400, and it falls in April, when she can see whether spring bookings have picked up before she spends.
4. The sum check passes. £8,000 opening + (750 + 1,950 − 3,950 − 2,280 + 280 + 650 = −£2,600) = £5,400, the April closing balance.

Filling it in from Xero

Xero does not have a cash flow spreadsheet or a cash budget of its own, but it holds most of the figures:

1. The opening balance: the bank balance in Xero on the start date, once the bank feed is reconciled. If you have more than one account, add them together.
2. Past months, to see the pattern: Accounting › Reports › Bank Summary gives cash received and cash spent for each account over any date range; Account Transactions on a single account shows what made up each line. Last year’s quiet and busy months are the best starting point for this year’s receipts.
3. Money customers already owe: the Aged Receivables report shows unpaid invoices and how late they are. Put each one in the month you realistically expect it, not the due date (see who owes me money in Xero).
4. Bills already entered: Aged Payables, or the bills awaiting payment list, gives the near-term cash out.
5. The next 30 days: Xero’s short-term cash flow projects the balance from invoices and bills already in Xero. It is a good check on the first month of the sheet, not a replacement for it.

Menu names move between Xero releases and regions, so search Xero Central for the report name if your screen differs.

Common mistakes

• Typing sales instead of receipts. A spreadsheet that uses the month you invoice, rather than the month you are paid, can show cash that has not arrived. If customers take 30 days to pay, November’s work is December’s cash in.
• Leaving out the lumpy payments. Tax bills, annual insurance, equipment and loan repayments are not on a monthly Profit and Loss in the same way, and they are what make a month go negative. Priya’s sheet without the tax row would have hidden her low point.
• Overwriting a formula. Typing a number over a closing balance breaks every month after it. The sum check catches it.
• Mixing up profit and cash. A cash flow spreadsheet includes drawings, loan repayments, equipment and VAT or GST, which the Profit and Loss treats differently. A profitable year can still have a month like Priya’s February (see profit but no cash).
• Building it once and never updating it. Replace each forecast month with the actual figures when it ends, and add a new month at the end. A sheet that is three months old is a history, not a forecast.

Spreadsheet, budget or forecast?

They are the same layout used for different jobs. A cash flow budget is the plan you set for the year (see cash flow budget). A cash flow forecast is your best current estimate, updated as things change (cash flow forecast example). A cash flow spreadsheet can be either. For a tight few weeks, the same rows by week rather than by month give a 13-week cash flow forecast. And if you want to see how much cash one project uses before it pays back, add its net cash flows up in a running total: cumulative cash flow.

Common questions

How do I create a cash flow spreadsheet? Put the months across the top and the kinds of cash in and cash out down the side. Each month, add up total cash in and total cash out, take one from the other to get net cash flow, and add that to the opening balance to get the closing balance. The closing balance becomes next month’s opening balance.

What should a cash flow spreadsheet include? An opening balance, every source of cash in (customer receipts, loans, money you put in), every payment out (rent, wages, stock, running costs, tax, equipment, drawings, loan repayments), and the totals, net cash flow and closing balance for each month.

What is the formula for closing balance in a cash flow spreadsheet? Closing balance = opening balance + total cash in − total cash out. With the opening balance in B2 and net cash flow in B14, the closing balance is =B2+B14, and the next month’s opening balance is =B15.

Is there a free cash flow spreadsheet template? Yes. The free ReportPilot cash flow forecast template lets you type 12 months of figures in your browser, shows each closing balance and the lowest month, and downloads a CSV that opens in Excel, Google Sheets or Numbers with the formulas included.

Can Xero make a cash flow spreadsheet? Not directly. Xero’s budgets cover Profit and Loss accounts, and its short-term cash flow looks 30 days ahead from invoices and bills already entered. Use Xero’s Bank Summary, Aged Receivables and Aged Payables reports for the figures, and put them into a spreadsheet for the months ahead.

How often should I update a cash flow spreadsheet? Monthly at least: replace the month that just ended with what actually happened and add a new month at the end. When cash is tight, update it weekly.

See your cash every Monday

ReportPilot reads your Xero figures and sends a short plain-English email every Monday, with a 24-month dashboard behind it. The email covers the cash in the bank against the end of last month, sales and profit against last month and the same month last year, money owed to you that is past due and by whom, and bills due in the next two weeks against your cash. It asks Xero for read-only access, and there is a 14-day free trial with no card.