A free 12-month cash flow forecast template in Excel, for a small business
Sales by month collected in the month, the next month and the month after, suppliers paid the month after, payroll, rent, fixed costs, loan repayments, capex and tax, the month-end balance against a minimum buffer, and the credit line it implies: three sheets, no macros.
The file forecasts a year of cash for a small business with 2.77 million of seasonal sales. Customers pay 20% in the month of sale, 55% the next month and 25% the month after; suppliers are paid the month after. Opening with 60,000 in the bank, the business is profitable over the year and ends with 113,250, but a 60,000 capex in April takes cash down to 21,250, below the 30,000 buffer: it needs an 8,750 credit line for one month, and 307,500 is still owed by customers at year end.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Cash_Flow_Forecast_12_Months.xlsx (14 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This model goes with Treasury Management, which explains the method behind it, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Three sheets. Inputs are blue cells on a pale blue fill on the Inputs sheet.
Inputs
Opening cash, collection shares, last year's November and December sales, cost of sales and supplier timing, payroll, rent, other fixed costs, loan repayments, the buffer, and monthly sales, capex and tax.
Forecast
Twelve months of opening cash, collections, payments, net cash flow and closing cash, a flag below the buffer and the credit line needed; then the low point, its month and the year-end position.
Checks
Collection shares add to 100%, the cash bridge, the receivables identity and the worked case: all OK.
What the worked case returns
Measure
Value
Annual sales
2,765,000
Opening cash
60,000
Lowest month-end cash
21,250 (April)
Months below the 30,000 buffer
1
Credit line needed
8,750
Closing cash
113,250
Receivables at year end
307,500
Profit and cash are different months. Sales in December are mostly collected in January and February, so a growing or seasonal business can be profitable and still run short of cash, and the low point usually coincides with a payment that does not move: capex, tax or a loan instalment.
How to use it on your own business
Enter opening cash and how your customers pay on Inputs.
Enter last year's November and December sales, still being collected.
Enter monthly sales, capex and tax for the year.
Read the low point and the credit line needed on Forecast.
Questions people ask about it
Is this template really free?
Yes. The file costs nothing: you enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference.
How are collections forecast?
Each month's sales are collected in three parts: in the month, the next month and the month after, in the shares you set. Here 20%, 55% and 25%, which is roughly 45 days of receivables.
Can I use it for my own business?
Yes, by replacing the inputs. The business is fictional.
Go further
For liquidity, working capital and the treasury decisions behind a forecast like this, Treasury Management and its companion workbooks go further.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.