Cash flow forecast template in Excel: build it right
Build a cash flow forecast template in Excel for a small company on Odoo, and spot when the spreadsheet starts to cost more than it saves.
Setting up a cash flow forecast in a spreadsheet is where almost every small business starts. The tools cost nothing, anyone running the books can wrangle a formula, and done well it flags cash squeezes a week or two before they land. The spreadsheet starts to drag when it grows, when you enter your second VAT quarter with it and when the owner asks “what would it look like if Metro Industrial pays 45 days late?”.
This guide walks through building a cash flow forecast template in Excel for a small company that already runs on Odoo. Formula of closing balance = opening + receipts − payments, separate tabs, locked cells, conditional formatting when the balance dips below your minimum, and a worked example in euros. After that, when the spreadsheet starts costing more than it saves, which signals to watch, and what changes when the data comes straight from your accounting system instead of being retyped.
You will not find a download here. A ready-made template never fits your customers, your payroll shape and your tax calendar, so the useful time goes into listing those yourself. Two or three evenings is enough.
How to build the template
Four separate tabs save you from most future headaches:
- Setup: lists (customers with real payment terms, suppliers with their payment habits, loans, fixed overheads, taxes).
- Receipts: expected inflows per week.
- Payments: expected outflows per week.
- Summary: calculated balances, chart and alerts.
The Setup tab holds no formulas, only lists. Each customer with the actual term (30, 60, 90 days) and, where you know it, the day of the month they usually pay. Each supplier the same. Loans with their monthly instalment and direct debit date. Payroll (gross plus employer taxes) with its pay day. VAT dates for your filing cycle: in the UK it is usually quarterly; in Ireland, usually every two months, by the 19th (the 23rd if you file and pay through ROS).
Rows and columns
Work with a 13-week horizon. Enough columns to see the full quarter ahead, few enough to keep the sheet readable. Vertically, group as follows:
- Opening balance for the week.
- Receipts (collections from invoices, cash sales, VAT refunds, one-offs).
- Total receipts.
- Payments (payroll, employer taxes, suppliers by group, VAT, loans, rent, utilities, other).
- Total payments.
- Closing balance = Opening + Receipts − Payments.
Next week’s opening is, by formula, this week’s closing. That single reference is also the first thing that breaks when someone inserts a column in the middle without thinking.
Formula and locked cells
Protect the sheet. Only the cells where someone enters forecast amounts stay unlocked. Everything else is locked:
- The opening and closing balance rows.
- Totals for receipts and payments.
- Column A (labels).
- The opening balance of week 1, entered once from the reconciled bank statement.
Under Review → Protect Sheet, add a password known to whoever maintains the model. When the team fills in their bit, nobody accidentally overwrites the running balance.
Conditional formatting
Two rules earn their keep:
- Closing balance below your minimum (say €21,000 to cover one month of payroll and employer taxes): red fill.
- Closing balance below zero: dark red fill with the whole row highlighted.
Open the file on Monday and you see at a glance whether any week sits in the red, without reading the numbers.
Worked example
A fictional company, Northfield Joinery Ltd, 12 staff, €1.2 M turnover, quarterly VAT:
| Line | Wk 1 | Wk 2 | Wk 3 | Wk 4 |
|---|---|---|---|---|
| Opening balance | 42,000 | 38,500 | 41,200 | 31,900 |
| Collections | 18,000 | 24,000 | 9,500 | 32,000 |
| Cash sales | 2,500 | 2,500 | 2,500 | 2,500 |
| Total receipts | 20,500 | 26,500 | 12,000 | 34,500 |
| Suppliers | 18,000 | 21,800 | 0 | 22,000 |
| Payroll | 0 | 0 | 15,800 | 0 |
| Employer taxes | 0 | 0 | 5,500 | 0 |
| Rent | 2,000 | 0 | 0 | 0 |
| Loan | 2,000 | 0 | 0 | 2,000 |
| VAT quarterly | 0 | 0 | 0 | 12,500 |
| Utilities | 2,000 | 2,000 | 0 | 0 |
| Total payments | 24,000 | 23,800 | 21,300 | 36,500 |
| Closing balance | 38,500 | 41,200 | 31,900 | 29,900 |
Week 4 is where the warning flashes: VAT lands and the week still closes at €29,900. Set the floor at €25,000 and amber lights up. If Metro Industrial, the €18,000 customer in week 2, slips 30 days, week 4 closes at €11,900. On the 20th of March that single data point changes a decision: hold off on a stock purchase, call the invoice finance line, or ride it out.
To put the first version together, work alongside the step-by-step guide to building a cash flow forecast, and if you want to widen the window read the detail on the 13-week horizon.
What the spreadsheet will not tell you
Spreadsheet templates work until they stop working. It tends to happen one of these ways:
Retyping Odoo data every week. Friday afternoon you open the aged receivables list, export it, paste it, nudge due dates by eye and refresh the terms. Two hours. Repeat it week after week and that is a large block of expensive time retyping something already written down.
Versions by email. “Latest attached”, “this one’s the right one”, “sorry, I missed the insurance payment”. Three weeks in nobody knows which version rules, and the accounts team runs its own copy on the side.
Broken formulas. Someone inserts a row for a new payment, the running balance reference slides one cell down, and the forecast reads positive when it should read negative. You catch it at the reconciliation if you are lucky; if you are not, the decision is already taken.
The spreadsheet only one person understands. When Sarah takes a fortnight off, nobody touches it. When Sarah leaves, she takes it with her, in her head.
Overdues and real due dates. The sheet projects as if everyone paid on the day printed on the invoice. In the real world a share pay late and nobody updates their row while you wait.
The VAT quarter. Keep the VAT return in one tab and the forecast in another, and you are not seeing month four the way the bank sees it. VAT due dates fall on the same tough days each quarter, and the sheet pretends they are not there once you flip to monthly view.
Signals it is time to leave
- You spend more than two hours a week updating it.
- Four-week forecasts miss by more than 20 % three times running.
- Someone has asked again “which version is the right one?”.
- Two people touch the sheet and there are meetings held just to reconcile it.
- You add a second company to the group and the template cannot stretch without breaking.
- The owner asks for a scenario with a late customer and you rebuild the sheet by hand.
Hit three of those and the money paid for a treasury tool pays back in weeks, not months. Prices for small-business tools vary quite a bit by features and company size.
What changes when the data comes from your accounting system
A treasury tool that reads from Odoo gives the spreadsheet a very different job: writing down the business assumptions (expected hires, investments, likely late payers), not retyping data that is already there. Issued and received invoices with their due dates, bank statements, recurring payments and reconciliations flow in on their own. The forecast refreshes when a payment lands or a due date changes. Variances are explained invoice by invoice: “week 3 is €18,000 short because invoice 2026/0431 for Metro Industrial fell due and is still unpaid”.
Kashbi, for example, reads from Odoo without touching its configuration, shows the forecast at 13 weeks and at 3, 6 and 12 months, and emails you before the cash dips below the floor you set. To weigh whether moving from the spreadsheet makes sense for your case, read also whether an Odoo module covers treasury or a separate tool fits better.
Frequently asked questions
Is it worth building the spreadsheet if I plan to switch to a tool soon?
Yes. The exercise forces you to list customers, suppliers, dates and assumptions. That information is exactly what a tool loads in its first few days. Whatever time you spend builds the mental model you need anyway.
13 weeks or 12 months?
The two views complement each other. 13 weeks is operations (will payroll clear?), 12 months is decisions (hire, invest, borrow). If you only run one, 13 weeks spares you more unpleasant surprises.
Where do quarterly taxes go?
On the real pay-out date. If you accrue VAT month by month as it builds up, record it on a “VAT accrual” row with no cash impact and, on the due date, move it to the “VAT paid” row.
What do I use as the opening balance?
The reconciled bank balance at the close of the previous day, not the book balance. If statements are not reconciled in Odoo, the opening number is already off, and the sheet will mislead you every week.