Financial model template
A financial model turns a handful of assumptions about the next few years (sales, margins, overheads, investment, borrowing and dividends) into forecast profit and cash, so you can see what a plan does to the business before you commit to it. This free Excel template is a simple three-year model: you fill in one Assumptions sheet, and a P&L summary and a cash summary calculate from it.
Running a company of 30 to 200 people? Documents like this are now worth money.
Every figure on the two summaries traces back to a yellow cell on the Assumptions sheet. Change one assumption, such as a bigger dividend or customers paying more slowly, and you see the effect on profit and on the bank balance straight away.
What's in the template
Seven sheets: a read-me; Assumptions, the only sheet you type into, with one row per year; P&L summary and Cash summary, which calculate from it; and an example of each, filled in for a fictional company. The example's main assumptions:
| Assumption | Year 1 | Year 2 | Year 3 |
|---|---|---|---|
| Revenue | 4,200,000 | 4,700,000 | 5,150,000 |
| Gross margin | 38.0% | 38.5% | 39.0% |
| Overheads (excluding depreciation) | 1,150,000 | 1,290,000 | 1,390,000 |
| Debtor days | 52 | 50 | 48 |
| Capital spending | 120,000 | 250,000 | 90,000 |
| New loans less repayments | -40,000 | 110,000 | -70,000 |
| Dividends | 180,000 | 170,000 | 200,000 |
What the two summary sheets make of them:
| From the summaries | Year 1 | Year 2 | Year 3 |
|---|---|---|---|
| Revenue | 4,200,000 | 4,700,000 | 5,150,000 |
| Gross profit | 1,596,000 | 1,809,500 | 2,008,500 |
| EBITDA | 446,000 | 519,500 | 618,500 |
| Profit before tax | 343,000 | 400,500 | 487,500 |
| Corporation tax (25%) | 85,750 | 100,125 | 121,875 |
| Profit after tax | 257,250 | 300,375 | 365,625 |
| Cash from trading | 407,644 | 474,020 | 585,076 |
| Net cash flow | -30,356 | 54,270 | 103,951 |
| Closing cash | 279,644 | 333,914 | 437,865 |
The Assumptions sheet also takes interest, a corporation tax rate and, on the Year 1 row only, the opening position: cash in the bank, what customers owe and any corporation tax still to pay.
How to build the model
- Start from last year's actual figures. Take revenue, gross margin and overheads from your latest accounts; Year 1 should look like a believable step on from them.
- Build each year's revenue from something you can explain, such as customers multiplied by average spend, or contracts you hold plus a realistic win rate. The P&L summary shows the growth each year implies, a quick test of whether it's plausible.
- Set gross margin and overheads. Gross margin is gross profit as a percentage of revenue. Overheads are running costs that don't rise with each sale, such as office salaries, rent, software and insurance. Add planned hires from the year they start.
- Take depreciation from your depreciation schedule, including the equipment in your capital spending column.
- Enter interest and a tax rate for each year; the model applies one flat rate to profit before tax.
- Enter debtor days, the average time customers take to pay. The model turns them into what customers owe at each year end and treats any increase as cash tied up.
- Add the items that move cash but aren't costs: capital spending, new loans, loan repayments and dividends.
- Fill in the opening position on the Year 1 row from your latest balance sheet, then read the cash summary. If cash goes below zero, the last column turns red.
How it fits with the budget, cash forecast and P&L
The model is the long view. Three other finance templates cover what it leaves out:
- The profit and loss template records what actually happened, month by month. Its year totals are where the model starts.
- The budget template sets next year's targets month by month. Year 1 of the model and the budget should agree; if they don't, one of them is out of date.
- The cash flow forecast shows timing within the year. An annual model can look healthy at every year end while a quarter in between runs short, so switch to the forecast whenever the model's cash looks thin.
Use the model for decisions that play out over years: whether to borrow, how big a dividend the business can afford, when to replace equipment or add a team.
Example: a fictional servicing company's three years
The Example sheets model a fictional 45-person company that services commercial refrigeration. It starts Year 1 with £310,000 in the bank, plans to grow revenue from £4.2 million to £5.15 million, and wants to renew its van fleet in Year 2 with a £150,000 loan.
Profit after tax rises from £257,250 to £365,625, but Year 1 is tight. Last year's £80,000 corporation tax bill, £120,000 of equipment, £40,000 of loan repayments and a £180,000 dividend all leave in the same year, so the bank balance falls by £30,356 even though the year is profitable. Years 2 and 3 rebuild it to £437,865.
That's the trade-off the model exists to show. Cut the Year 1 dividend to £150,000 and the year ends with £309,644, almost exactly where it started.
Common mistakes
- Sales growth with flat overheads, so margins widen every year without anyone deciding how.
- Forgetting the cash that growth ties up. At 50 debtor days, every extra £1 million of annual revenue leaves about £137,000 waiting to be collected at the year end.
- Putting equipment purchases in overheads. They go in capital spending; only the depreciation reaches the P&L.
- Paying tax in the wrong year. UK companies with taxable profits up to £1.5 million pay corporation tax 9 months and 1 day after the end of the accounting period, so each year's bill is paid the following year. Larger companies pay in instalments.
- Typing over formulas on the summary sheets. Change the assumption instead, or the model stops adding up.
- Only one version. Save a copy with cautious assumptions (slower growth, slower payers) and compare.
Frequently asked questions
What's the difference between a financial model and a budget?
A budget is next year's plan, month by month, kept fixed so you can measure results against it. A financial model looks further ahead, a year at a time, and you change it as often as you like to test decisions: borrow or not, hire or not, a bigger dividend or a smaller one.
Which corporation tax rate should I enter?
In the UK the main rate is 25%. Companies with profits of £50,000 or less pay the small profits rate of 19%, and marginal relief can apply between £50,000 and £250,000; both limits are reduced for short accounting periods and for associated companies. The model uses one flat rate, so if your profits fall between the limits, ask your accountant for an effective rate. In the US, use the combined federal and state rate your accountant gives you.
Why is there no balance sheet?
To keep the model small enough to follow. It carries only the balance sheet items that move cash most in a typical service business: cash, what customers owe and corporation tax due. If stock, supplier credit or several loans matter to you, a fuller three-statement model that links a balance sheet to the P&L and cash flow is worth building.