Cash flow forecast template in Excel: 12 months
Cash flow forecast template in Excel: 12 months
Very few small businesses stop trading because sales dry up; they stop because in some month the cash was not there to cover payroll, suppliers and rent. Looking at a month already closed explains the past; forecasting it forward warns you before the problem arrives. This cash flow forecast template in Excel for 12 months puts the whole year on one sheet and answers the question that weighs the most: which months end with a negative balance and how much would be needed to cover them.
The file is free, opens in Excel and in any compatible spreadsheet program, and comes with the twelve months already set up: rows for money in and money out, the total for each month, the flow of the month, the opening and closing balances chained together, the year total and a summary that counts the months with a negative balance. You only write your own assumptions; the workbook does the arithmetic.
⬇ Download the template (Excel .xlsx)What a cash flow forecast is and who it is for
A cash flow forecast is a table where you estimate, month by month, the money expected to come in and the money expected to go out, and from those two figures you work out how much cash is left at the end of each month. The axis is always cash: not profit, not invoiced sales, but the money that really moves through the account. A month can close with excellent sales and still end in the red if those sales are collected in sixty days while payroll is paid on the thirtieth.
It is useful first of all to anticipate. Seeing the whole year on one screen brings out the tight months before you reach them: the month when two large payments land together, the low season month, the month when stock has to be bought for the high season. Second, it helps you decide in time: if March shows a negative balance, February is still there to negotiate a longer term with a supplier, speed up a collection or postpone a purchase. Third, it lets you talk with numbers: a bank, a partner or a relative understands a request for money far better when they can see the exact month it is short and why.
It suits any small business that collects and pays on different dates: shops, workshops, restaurants, distributors, service companies and seasonal work. No accounting background is needed: this is an internal control tool, not an official document, and values are written without a currency symbol.
How it differs from the historical cash flow and the budget
Three tools look alike and answer different questions. The historical cash flow records what already happened, movement by movement, to show how much is in the account today. This template looks forward instead: it does not record facts but assumptions, and its value lies in anticipation.
The annual budget also looks forward, but it works by category and aims at the result: how much you expect to sell and how much you expect to earn. The cash forecast works by collection and payment dates and aims at the balance: when the money comes in and when it goes out. A business can have a budget with a positive result and, at the same time, a forecast with two red months; both statements are true, and the one you actually feel is the second.
What the template includes
The workbook has one main sheet with the twelve months and a summary of the year. This is what you will find when you open it:
| Item | What it gives you |
|---|---|
| Twelve month columns | January to December, each with its own column, so you can compare how the year evolves. |
| Money in rows | Forecast sales, collection of receivables and other income, ready for the assumptions of each month. |
| Money out rows | Purchase of stock, payroll, rent, utilities and other expenses, one row per item. |
| Total in and total out | Automatic sums for every month; they are never typed by hand. |
| Flow of the month | Money in minus money out: what the cash gained or lost that month. |
| Chained closing balance | The balance a month closes with becomes the opening balance of the next, so one bad month carries its effect through the whole year. |
| Year total | A column that adds the twelve months so you see the whole picture without adding by hand. |
| Summary of the year | Counts the months with a negative balance and shows the lowest balance of the year: the figure to watch first. |
The columns of the sheet
Every row of the sheet is an item and every column is a month. The first column names the item and the last one adds up the year:
| Column | What you write |
|---|---|
| Item | The name of the row: forecast sales, collection of receivables, purchase of stock, payroll, rent, utilities and the rest. |
| January to December | Twelve columns, one per month, to write the estimated value of that item in that month. |
| Year total | The sum of the twelve columns; useful to see how much weight each item carries in the whole year. |
The total rows are not edited: total in adds the money in items, total out adds the money out items, the flow of the month subtracts one from the other and the closing balance is the opening balance plus the flow. Write only in the item rows and in the opening balance of the first month.
Worked example: the month that closes at zero
Suppose a business starts the year with an opening balance of 3,000,000 and forecasts sales of 4,000,000 every month. On the way out it estimates stock purchases of 1,800,000, payroll of 1,200,000, rent of 700,000 and utilities of 300,000. The template does the arithmetic and the month looks like this:
| Item | Value |
|---|---|
| Opening balance | 3,000,000 |
| Forecast sales | 4,000,000 |
| Total in | 4,000,000 |
| Purchase of stock | 1,800,000 |
| Payroll | 1,200,000 |
| Rent | 700,000 |
| Utilities | 300,000 |
| Total out | 4,000,000 |
| Flow of the month | 0 |
| Closing balance | 3,000,000 |
With those assumptions the business breaks even: what comes in equals what goes out, the flow of the month is zero and the balance stays at 3,000,000. The format works, but it is not warning you about anything yet.
Now change a single assumption: suppose that in that month stock purchases rise to 2,500,000, because goods have to be bought ahead of the season. Total out becomes 4,700,000, the flow of the month falls to -700,000 and the balance drops to 2,300,000. That is exactly the warning you are after: not an accounting loss, but a month in which the cash is 700,000 short, and a following month that starts from 2,300,000 instead of 3,000,000.
How to read the summary of months with a negative balance
The summary of the year is the part almost nobody looks at and the part that is worth the most. It counts how many months close with a negative balance and which balance is the lowest of the year. A year with two red months needs a plan; a year with seven red months does not need a plan, it needs the assumptions rewritten from the start.
When the summary flags a tight month, the conversation has three concrete ways out: move the collection, by invoicing earlier or asking for a deposit; move the payment, by negotiating a longer term or postponing a purchase that is not urgent; or buy time, if the business is genuinely healthy and the gap is a calendar gap rather than a result gap. What does not work is leaving the month in the red and trusting that something will sort itself out.
Assumptions, besides, get updated. A forecast is not a document written once a year and filed away: it should be revised every month, comparing what you forecast with what really happened and correcting. If January was forecast at 4,000,000 in sales and closed at 3,200,000, the following months have to reflect that reality, because the balance carries over and does not forgive. Eight months of stale assumptions are eight months of decisions taken on numbers that are no longer true.
How to use the template step by step
- Download the file and save it with the year in the name, for example cash-flow-forecast-2027.xlsx, so it is not confused with the historical record.
- Write the opening balance of the first month: what you have available today between the bank account and the cash box.
- Fill the money in rows on a collection basis, not a sales basis: each sale goes in the month you expect to receive the money, not the month you invoice it.
- Fill the money out rows on a payment basis: stock, payroll, rent, utilities and every payment with a known date, including those paid every two or three months.
- Read the balance row of the twelve months in a row, without skipping any, and read the summary of the year: count the negative months and write beside them what you would do to cover them.
- Repeat the review every month: replace the forecast with what actually happened in the month just closed, adjust the remaining assumptions and read the summary again.
Tips and common mistakes
These six oversights are the ones that distort a forecast the most and cost the most:
- Forecasting by sales instead of by collections. A sale on credit reaches the account sixty days later, and the tight month is usually the one that separates the invoice from the payment.
- Forgetting the payments that are not monthly. The annual bonus, period taxes, the yearly insurance policy or a vehicle registration land once or twice a year and they tip a month over if they carry no date.
- Leaving the assumptions untouched all year. A forecast that is never corrected becomes decoration: if real sales come in lower, the following months change too.
- Mixing the cash of the business with the cash of the owner. If money is taken out every week without recording it, no month will ever balance.
- Trusting only the best case. Forecast with prudent assumptions and, when the summary looks comfortable, try the hard case: higher purchases, lower sales and slower collections.
- Looking only at the year total. A year that closes well can still contain two impossible months; the total hides the problem and the summary shows it.
When to move to software with Kardex Tauro
Excel works very well to forecast a year while there is one business, few assumptions and one person filling the file in. The limits appear when there are several locations with separate cash boxes or when three people need to see the same file on the same day. That is when duplicate versions, outdated assumptions and hand-made sums begin.
That is the moment to consider specialist software such as Kardex Tauro, which centralises inventory, sales and receivables and lets you see day-to-day figures without chains of files by email. The work done today is not wasted: the items, the months and the habit of reading the summary are exactly what a system needs to start with clean data.
Download the template, write this year's twelve months of assumptions today and look first at the summary of negative months: that figure, and not the year total, is what tells you what to negotiate and how far ahead.
⬇ Download the template (Excel .xlsx)
