Cash flow model: how to build one for a small business, with an example

Short answer: a cash flow model is a cash flow forecast built from a small set of assumptions, called drivers: sales each month, gross margin, how many days customers take to pay, how many days you take to pay suppliers, overheads, loan repayments and tax dates. The model works out every month’s receipts, payments and closing bank balance from those drivers, so changing one number (customers paying 15 days later, a lower margin, no growth) shows at once what happens to the bank. In the example below, a coffee roaster’s model shows the bank ending June at £43,600, with a low point of £37,800 in May. If customers take 60 days instead of 45 and the margin slips three points, the same model shows the account £2,760 overdrawn in May, four months before it would happen.

Cash flow model vs cash flow forecast

	Cash flow forecast	Cash flow model
What you type in	Each month’s receipts and payments	A few drivers (sales, margin, payment days, overheads, tax dates)
How a change works	Retype every affected month	Change one driver; every month recalculates
What-ifs	A copy of the sheet per scenario	A scenario is a different set of drivers
Checks	The bank balance carries forward	Also ties profit to cash, so a missing item shows up
Best for	The next few weeks or a stable year	Growth, new contracts, slower payers, borrowing decisions

A cash flow forecast example is the output; the model is the machine that produces it. Most owners need a simple one: one sheet of drivers, one sheet that calculates, and a short results table.

The parts of a cash flow model

1. Opening position. The bank balance, what customers owe you (debtors) and what you owe suppliers (creditors) on the first day. The money owed in both directions is cash that will move in the first months, so a model that starts from the bank alone is wrong from month one.
2. Drivers. Each assumption on its own row, in one place, never typed into the calculations. Sales by month (or a starting figure and a growth rate), gross margin, customer payment days, supplier payment days, overheads, wages, loan repayments, VAT or GST, tax due dates, drawings or dividends, equipment.
3. Timing rules. These turn profit into cash. Sales invoiced in March and paid in 45 days arrive half in April and half in May. Stock bought in March and paid in 30 days leaves in April. Tax goes out on its due date.
4. The calculation. Receipts, payments, net cash flow and closing balance for each month, all formulas.
5. Results. The lowest closing balance and its month, the closing balance at the end, and the same two figures for each scenario.
6. Checks. Closing bank = opening bank + profit − increase in debtors + increase in creditors − loan repayments − tax paid − other non-profit items. If the check does not agree, something is missing.

A worked example: a coffee roaster in Leeds

These figures are made up. A limited company roasts coffee and sells it to cafés on account. It models January to June 2027. All figures are in pounds without VAT, to keep the example short (in a real model, VAT has its own row with the quarterly payment).

The drivers

Driver	Value
Sales	£60,000 a month, rising to £66,000 from March (new cafés) and £72,000 from May (a café group)
Gross margin	40%, so beans and packaging cost 60% of sales
Customers pay	Half the month after the invoice, half the month after that (about 45 days)
Suppliers paid	The month after delivery (30 days)
Overheads, including wages	£18,000 a month, paid in the month
Loan repayments	£2,000 a month
Corporation tax	£24,000 for last year, due on 1 May

Opening position on 1 January: £40,000 in the bank, £90,000 owed by customers (all of December’s sales and half of November’s) and £36,000 owed to suppliers for December’s beans.

What the model calculates

£	Jan	Feb	Mar	Apr	May	Jun	Total
Sales invoiced	60,000	60,000	66,000	66,000	72,000	72,000	396,000
Receipts from customers	60,000	60,000	60,000	63,000	66,000	69,000	378,000
Paid to suppliers	(36,000)	(36,000)	(36,000)	(39,600)	(39,600)	(43,200)	(230,400)
Overheads	(18,000)	(18,000)	(18,000)	(18,000)	(18,000)	(18,000)	(108,000)
Loan repayments	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(12,000)
Corporation tax					(24,000)		(24,000)
Net cash flow	4,000	4,000	4,000	3,400	(17,600)	5,800	3,600
Closing bank	44,000	48,000	52,000	55,400	37,800	43,600

Brackets are money going out. April’s receipts are half of February’s sales (30,000) and half of March’s (33,000): every figure in that row comes from the sales row and the payment-days driver, not from typing.

The check: profit against cash

£
Gross profit (396,000 × 40%)	158,400
less overheads	(108,000)
Profit for the six months (before tax; the loan repayments here are all capital)	50,400
less increase in debtors (90,000 to 108,000)	(18,000)
add increase in creditors (36,000 to 43,200)	7,200
less loan repayments	(12,000)
less corporation tax paid	(24,000)
Increase in the bank (40,000 to 43,600)	3,600

£50,400 of profit turns into £3,600 of cash. The model agrees with itself, and it shows where the rest went: mostly tax, the loan and the cafés’ unpaid invoices.

Four what-ifs

Each line changes one or two drivers and leaves everything else as it is.

Scenario	Lowest closing balance	Closing bank, June
Base, as above	37,800 (May)	43,600
No growth: £60,000 every month	36,000 (May)	40,000
Margin falls to 37% from January	30,240 (May)	33,880
Customers take 60 days on invoices from January	4,800 (May)	7,600
Slower payers and the lower margin together	(2,760) (May)	(2,120)

What the owner learns:

1. Growth earns profit but uses cash. The two new customer groups add £14,400 of profit over the six months (50,400 against 36,000 with no growth), but only £3,600 of cash by June, because £18,000 more is sitting in unpaid invoices and only £7,200 more is owed to suppliers. Growth that pays later than it costs needs cash to carry it.
2. Payment days matter more than margin here. Fifteen extra days of customer credit costs £36,000 of cash by June, half a month of sales. Three points of margin cost £9,720, about £3,240 a point.
3. May is the month to watch in every scenario, because the corporation tax lands then. With slower payers and a weaker margin, the account is overdrawn. Knowing that in January leaves time to chase the café group for 30-day terms, arrange an overdraft, or set aside the tax money monthly.

How to build one from Xero

1. Opening position. Reconcile the bank, then run the Balance Sheet at the start date for the bank balance, and the Aged Receivables and Aged Payables reports for what is owed each way and when it is due.
2. Sales and margin. Run the Profit and Loss by month for the last 12 months. Use the same months last year as the starting sales, adjusted for what you know is changing, and take gross margin from the same report (gross margin in Xero).
3. Payment days. Work out how long customers really take with the debtor days calculator, and how long you take to pay suppliers from the creditor days in the creditor days guide. Use what happens, not your terms.
4. Fixed payments and tax. Overheads and wages from the Profit and Loss, loan repayments from the loan statements, and VAT or GST, PAYE and income or corporation tax from their due dates. These are rarely bills in Xero, so they are the rows most often left out.
5. Build, then check. Put the drivers on one sheet and the formulas on another, run the profit-to-cash check, and add the scenarios you are actually worried about.
6. Each month, compare. Put the actual month next to the model’s month. Where they differ, fix the driver, not the result.

Xero’s short-term cash flow projects the next 7 or 30 days from invoices and bills already entered, which is a useful near-term check, but it has no drivers to change. For a quick monthly projection without building formulas, the free cash flow forecast template calculates each closing balance as you type, and a simple cash flow spreadsheet needs only five formulas.

When to go further

• A weekly view. For the next three months, when wages, VAT and a big supplier bill can land in the same week, use a 13-week cash flow forecast on top of the monthly model.
• A 3-way model. When a lender or investor wants the forecast Balance Sheet too, link the Profit and Loss, Balance Sheet and cash flow so they must agree: see the 3-way cash flow forecast.
• A valuation model. A discounted cash flow (DCF) model is a different thing: it forecasts free cash flow for years and discounts it to a value today. See discounted cash flow.

Common mistakes

• Starting from the bank balance alone. The £90,000 the cafés owed on 1 January is three months of receipts. Leave it out and the first months look empty; count it twice and they look rich.
• Typing numbers into the calculations. One hard-typed receipt survives every change to the drivers and quietly breaks the what-ifs. Every number in the calculation rows should be a formula.
• Using payment terms instead of payment days. Invoices say 30 days; the cafés took 45. Model what customers do.
• Forgetting the money that is not profit. Loan repayments, tax payments, VAT, drawings and dividends, and equipment do not appear as costs on the Profit and Loss, but they all leave the bank.
• Modelling only the hoped-for case. The point of the model is the bad case. Run the scenario where the big customer pays late and the margin slips, and decide now what you would do.
• Never checking it against what happened. A model nobody compares with the actual month drifts away from reality within a quarter.

Common questions

What is a cash flow model? A cash flow model is a spreadsheet that works out a business’s future receipts, payments and bank balance, month by month, from a small set of assumptions such as sales, gross margin, how long customers take to pay and when tax is due. Changing an assumption updates every month, so you can test what-ifs.

What is the difference between a cash flow model and a cash flow forecast? A forecast is the list of expected receipts and payments and the resulting bank balance. A model is the set of assumptions and formulas that produces it. People often use the words for the same thing, but a model lets you change one assumption and see the effect straight away.

What should a cash flow model include? An opening bank balance and the money owed to and by the business, drivers for sales, margin, customer and supplier payment days, overheads, wages, loan repayments, VAT or GST and tax dates, monthly receipts, payments and closing balance, a few scenarios, and a check that ties profit to the change in cash.

How do I build a cash flow model in Excel? Put every assumption on a drivers sheet. On a second sheet, give each month a column and calculate receipts from sales and payment days, payments from costs and supplier days, then net cash flow and the closing balance, which becomes next month’s opening balance. Keep every calculated cell a formula that points back to the drivers.

How far ahead should a small business cash flow model go? Twelve months by month is common, because it covers every tax payment and seasonal swing in the year. Six months is enough for a decision such as taking on a big new customer, and a 13-week weekly view is better for the next three months.

Is a cash flow model the same as a discounted cash flow model? No. A cash flow model manages the bank balance over the next months. A discounted cash flow model values a business by forecasting free cash flow for several years and discounting it back to today. They share the forecasting step but answer different questions.

Check the model against what really happened

ReportPilot reads your Xero figures and sends a short plain-English email every Monday: sales and profit against last month and the same month last year, the cash in the bank against the end of last month, money owed to you that is past due and by whom, and bills due in the next 14 days against your cash. It does not build a cash flow model, but it shows each week whether customers are paying the way your model assumes. It asks Xero for read-only access, and there is a 14-day free trial with no card.