What you will be able to do
Choose the few drivers that generate a forecast and state each in business terms.
Build a linked income statement, balance sheet and cash flow statement for one future year.
Explain why debt or cash is the balancing item, and solve the interest circularity it creates.
Run the checks that prove a model is internally consistent, and say what they do not prove.
Turn one forecast into scenarios and read the result in terms of leverage and cover.
Tell a budget, a business plan and a rolling forecast apart.
1A model is a few assumptions pushed through three linked statements
Before building anything, you need to see why the three statements must agree and which inputs deserve to be called drivers.
Three statements, one set of events
The three statements describe the same year from three angles
In a year, a company sells things, pays people, buys machines, borrows and repays. Accountants record those events once and then present them three ways. The income statement says what was earned over the year. The balance sheet says what the company owns and owes on the last day. The cash flow statement says where the cash came from and where it went.
Because they describe the same events, they are tied together. Net income from the income statement ends up in equity on the balance sheet. A machine bought appears in the cash flow statement as an outflow and on the balance sheet as an asset. Depreciation reduces profit, reduces the asset, and is added back in cash flow because no cash left.
A forecast must keep these ties, or it forecasts nothing
Closing balance sheet = opening balance sheet + the year's flows
A handful of drivers generates the whole forecast
Drivers are the assumptions a manager can defend
A driver is an input that a person in the business can explain and revisit: sales growth, margin, days of customer credit, capex per euro of sales. Everything else in the model is calculated from drivers and from last year's balance sheet. If a number in the model is typed in by hand and is neither a driver nor history, it is a hidden assumption, and the model has a weak spot.
Harmony's year N+1 rests on six drivers
Claire starts from year N: sales 120.0, EBITDA 18.0, depreciation 6.0, net interest 2.0, fixed assets 60.0, working capital 24.0, gross debt 40.0 and cash 10.0. Sales have grown 16.7% then 14.3% in the last two years. She expects a slowdown to 10%, because the dealer network in Germany is nearly saturated.
| Driver | Year N | Year N+1 assumption | Why |
|---|---|---|---|
| Sales growth | 14.3% | 10.0% | Dealer network maturing |
| EBITDA margin | 15.0% | 15.0% | Purchasing gains offset wage rises |
| Depreciation | 6.0 | 10% of opening fixed assets | Existing assets wear out at a steady rate |
| Capex | 9.0 | 8% of sales | Capacity must follow volume |
| Working capital | 20% of sales | 20% of sales | Stock and credit terms unchanged |
| Interest | 5.0% of gross debt | 5.0% on average gross debt; cash earns nothing | Banque du Rhône pricing, unchanged |
Harmony's drivers. Cash is held at 10.0, above the 4.0 policy minimum; the term loan repays its scheduled 4.0 and the revolving facility is the plug; there is no dividend before the IPO.
Hugo manages product design at Harmony and has never read a balance sheet. He asks why the finance team cannot simply forecast next year's debt directly instead of building three statements. Explain to him, in everyday words, why debt has to come out of the model and not go in.
Debt is not something Harmony chooses on its own in a vacuum. It is whatever is left after the business has done its thing. If the business earns and spends so that there is 2 million too little cash, borrowing fills the gap. If there is 2 million too much, it repays. So debt is the answer to a question about sales, costs and investment. Forecast the business first, and the borrowing falls out.
1.2 Why does net income appear in the balance sheet as well as in the income statement?
Net income is what the owners earned over the year. If it is not paid out as a dividend, it stays in the company and belongs to the owners, so it is added to equity on the balance sheet. The income statement shows how the figure was earned. The balance sheet shows where it went.
1.3 Name two drivers from the Harmony table and say which statement each one first affects.
Sales growth first affects the income statement (sales, then costs and margin), and then working capital on the balance sheet. Capex at 8% of sales first affects the cash flow statement as an outflow and then fixed assets on the balance sheet. Depreciation as 10% of opening fixed assets first affects the income statement.
1.4 A forecaster types next year's receivables as 22.0 with no formula. Why is this a problem even if 22.0 looks reasonable?
A typed number does not respond when sales change. If sales fall or rise in a scenario, receivables stay at 22.0, so working capital and cash are wrong and the model gives false answers in every scenario except the one the figure was chosen for. It is a hidden assumption: receivables should come from sales and a days-of-credit driver.
1
Why must the income statement, balance sheet and cash flow statement of a forecast agree?
Right. Net income ends up in equity, capex in fixed assets and cash flow.
Every year must open where the last one closed.
Debt is the result, not the starting point.
Auditors do not sign forecasts; the ties come from the events themselves.
A forecaster types next year's receivables as 22.0 with no formula. What is the problem?
They are, with inventories and payables.
Right. Every scenario except the one it was chosen for is wrong.
The figure may look reasonable; the problem is that it cannot respond.
A plug can make it balance while the number is wrong.
Which of these is a driver in Harmony's N+1 model?
Calculated from sales, margin, depreciation, interest and tax.
That is a result: the plug.
Opening equity plus net income.
Right. A manager can explain it and revisit it.
2Building Harmony's N+1 forecast step by step
Now the build. Each step uses only what the previous step produced, which is how the ties between the statements stay intact.
Operating lines first, funding last
Step 1: sales and operating costs come first
Everything starts with sales. Year N sales of 120.0 grow 10%, so year N+1 sales are 132.0. An EBITDA margin of 15% gives EBITDA of 19.8. Depreciation is 10% of the opening fixed assets of 60.0, so 6.0. Operating profit (EBIT) is 19.8 minus 6.0, or 13.8.
Step 2: the balance sheet items that follow the drivers
Some balance sheet lines can be forecast at once. Working capital is 20% of 132.0, so 26.4, which is 2.4 more than the 24.0 at the end of year N. That increase is cash absorbed by growth. Capex is 8% of 132.0, so 10.56. Fixed assets close at 60.0 plus 10.56 minus 6.0 depreciation, which is 64.56.
Step 3: the cash that is left decides the debt
Now the cash flow statement. Start from EBITDA, take away the tax paid, the working capital increase, capex, and interest. What remains is cash available to repay debt, or a shortfall that must be borrowed. With no dividend and cash held at its year-end level of 10.0, well above the policy minimum of 4.0, this figure is exactly the change in gross debt.
This is why debt is the balancing item, often called the plug. Every other line is driven by the business. The funding line absorbs whatever difference is left so that the balance sheet balances. A model could instead keep debt fixed and let cash move. Companies with a revolving credit facility usually do the opposite, since a treasurer prefers to hold steady cash and let the facility flex. Harmony does this: the term loan repays its scheduled 4.0, the 10.0 of notes stay in place, and the revolving credit facility absorbs the rest.
The circle between interest and debt, and the finished forecast
Interest depends on debt, and debt depends on interest
There are two honest ways to handle it. The simple way charges interest on the opening debt, which breaks the circle but ignores repayments during the year. The better way charges interest on the average of opening and closing debt, and finds the solution by repeating the calculation until the number stops moving.
The finished year N+1 shows debt barely falling
| € million | Year N | Year N+1 |
|---|---|---|
| Sales | 120.00 | 132.00 |
| EBITDA | 18.00 | 19.80 |
| Depreciation | 6.00 | 6.00 |
| EBIT | 12.00 | 13.80 |
| Net interest | 2.00 | 1.95 |
| Profit before tax | 10.00 | 11.85 |
| Tax at 25% | 2.50 | 2.96 |
| Net income | 7.50 | 8.89 |
| Fixed assets | 60.00 | 64.56 |
| Working capital | 24.00 | 26.40 |
| Equity | 54.00 | 62.89 |
| Net debt (gross debt less cash of 10.00) | 30.00 | 28.07 |
Harmony, year N+1 base case. Equity grows by net income because there is no dividend.
Inside the 38.07 of gross debt, the term loan falls to 16.0 and the notes stay at 10.0, so the revolving facility rises from 10.0 to 12.07. Harmony draws 2.07 because the 1.93 of cash flow does not cover the 4.0 repayment. Net debt falls by only 1.93 although Harmony earns 8.89. Most of the profit is swallowed by growth: 2.4 into working capital and 10.56 into equipment, against 6.0 of depreciation that is not cash. Net debt over EBITDA improves from 1.67 to 1.42. EBITDA over interest rises from 9 to 10.1.
2.1 Squeezing working capital
Claire asks what happens if she tightens credit and stock control and lets working capital fall to 18% of sales instead of 20%. With sales of 132.0, what is the new working capital, how does the change in working capital compare with the base case, and how much extra cash does it release? Ignore the small interest effect.
18% of 132.0 is 23.76. That is 0.24 below the opening 24.0, so working capital releases 0.24 of cash instead of absorbing 2.40. The difference with the base case is 26.40 − 23.76 = 2.64 of extra cash, more than the whole 1.93 left after interest in the base case.
Five checks must hold in every model
A forecast that does not pass these checks is broken, whatever its numbers look like.
The balance sheet balances. Assets equal liabilities plus equity. Here, equity of 62.89 plus net debt of 28.07 equals capital employed of 90.96.
The cash flow statement reconciles. Net income 8.89 plus depreciation 6.0 gives cash earnings of 14.89. Subtract 2.40 of working capital and 10.56 of capex, and 1.93 remains: exactly the fall in net debt.
Equity moves only by net income, dividends and share issues. No other line may change it.
Cash never goes negative and debt never goes below zero. Otherwise the plug is hiding a financing gap or surplus that nobody has decided how to handle.
Each year opens where the last one closed. A single wrong link from one column to the next is the commonest silent fault.
a small live model with Harmony's six drivers as sliders and the three statements updating beside them, with a green or red light for each of the five checks. A second tab has a deliberately broken version, with a wrong link, a hard-coded receivables figure and an unreconciled cash flow. The learner must find the faults using the checks, and discovers that one fault can pass four checks and fail the fifth.
Camille runs a bike shop and keeps her books in a notebook. She says: my profit this year was 40,000 but my bank balance went down. Using Harmony's year N+1 as the example, explain to her in everyday words where the profit went.
Profit is not cash. Her 40,000 includes sales not yet paid by customers, and money she spent on stock that is still on the shelf and tools that will last years. In Harmony's year N+1, net income is 8.89 million, but growth swallows 2.4 into customer credit and stock, and 10.56 into equipment, against only 6.0 of depreciation that costs no cash. Only 1.93 is left to reduce debt. Her bank balance fell because she spent her profit, and more, on growth.
2.3 Why does Harmony's net debt fall by only 1.93 when net income is 8.89?
Net income plus depreciation gives 14.89 of cash earnings. Growth then takes 2.40 for working capital and 10.56 for capex. That leaves 14.89 − 2.40 − 10.56 = 1.93.
2.4 Why is interest on the average debt better than interest on the opening debt, and what price is paid for using it?
Debt changes during the year, so the average is closer to what the company actually carries than the opening figure. The price is circularity: interest depends on closing debt, which depends on interest. It must be solved by repetition or algebra, and a circular spreadsheet can be fragile.
2.5 The model shows closing cash of −3.0 and debt unchanged. Which check has been broken, and what real-world decision does it point to?
The check that cash and debt stay within sensible bounds has failed. In real life it points to a financing gap: Harmony would need to draw on a credit line, borrow, or issue equity, or else cut capex or dividends. The model must be given a rule for that, so that the plug takes up the gap openly.
2
Harmony earns 8.89 in N+1. Why does net debt fall by only 1.93?
Interest is already deducted in net income.
There is no dividend before the IPO.
Right. 14.89 − 2.40 − 10.56 = 1.93.
Tax is already deducted in net income.
Why is interest on average debt better than interest on opening debt, and what is the price?
It can be higher if debt rises during the year.
Right. Solve it by repetition or in closed form.
The rate is unchanged; the price is circularity.
The plug is still needed; interest now depends on it.
In Harmony's model, which line is the plug?
It follows its schedule of 4.0 a year.
Equity moves only by net income, dividends and share issues.
Right. The term loan repays 4.0, the notes stay, the facility absorbs the rest: 12.07.
Harmony holds cash at 10.0 and lets the facility flex.
3Scenarios turn one forecast into a range
A single forecast will be wrong. The useful question is how wrong it can be before something breaks.
Scenarios reveal what a single case hides
One forecast is a single point in a cloud of possible futures
The base case is Claire's most likely view. Boards and lenders also want to know what happens when things go worse, and what Harmony gains when things go better. A scenario is a coherent set of driver values that tell one story. It changes several drivers together, since sales and margins tend to move together.
Claire defines three. In the downside, the German network stalls, sales grow only 4% and discounts cut the EBITDA margin to 14%. In the upside, e-bike demand stays strong: sales grow 12% and the margin reaches 16%. Capex stays 8% of sales and working capital 20% of sales in all three.
In the downside, net debt over EBITDA rises to 1.59 and cover falls to 9.0, close to year N. Harmony stays well inside its bank covenants, net debt at most 3.0 times EBITDA and EBITDA at least 4.0 times net interest (CF12 and CF33). The revolving facility draws 1.80, still leaving 8.2 of the 20.0 line unused.
Look at free cash flow. The downside gives 3.66, more than the base case, because growth consumes cash: each extra euro of sales needs 20 cents of working capital and 8 cents of capex. A company can grow itself into a funding need, which is why cover ratios and funding plans tell more than profit.
a scenario selector with three buttons and a free mode with two sliders, sales growth and EBITDA margin. A chart plots net debt over EBITDA and interest cover, with a shaded zone for a covenant of 3.0 times. The learner explores which combination of the two drivers breaks the covenant, and learns that the margin does more damage than the growth rate.
3.1 A stress case
Claire builds a stress case: sales flat at 120.0, EBITDA margin 13%, depreciation 6.0, capex 10.0 because several orders are already placed, no change in working capital. Use interest on opening debt of 40.0 at 5% and tax of 25%. Compute EBITDA, net income, the cash flow available, closing net debt and net debt over EBITDA. Is Harmony in difficulty?
EBITDA is 13% of 120.0 = 15.6. EBIT is 15.6 − 6.0 = 9.6. Interest is 2.0, so profit before tax is 7.6, tax is 1.9 and net income is 5.70. Cash flow available is 15.6 − 1.9 − 0 − 10.0 − 2.0 = 1.70, so closing gross debt is 38.30 (the facility draws 4.0 − 1.70 = 2.30 to fund the term-loan repayment) and net debt is 28.30. Net debt over EBITDA is 28.3 / 15.6 = 1.81. Leverage is higher than in year N (1.67) but still moderate, and interest cover is 7.8. Harmony is not in difficulty.
Sensitivities rank the drivers and the model feeds other decisions
Sensitivities tell you which driver to worry about
A scenario moves several drivers at once. A sensitivity moves one at a time and records the effect on a chosen result, such as year N+1 net debt or EBITDA. Doing this for each driver ranks them by influence. For Harmony, one percentage point of margin is worth 1.32 of EBITDA. One percentage point of sales growth is worth 1.2 of sales and about 0.18 of EBITDA at a 15% margin.
The same forecast feeds valuation and funding decisions
Free cash flow before interest feeds three other jobs. Discounted over several years it gives the DCF value (CF27). Set against the debt table it supports a covenant review (CF33). Set against the project list it shows whether growth can be funded without new equity (CF32).
3
In the downside (growth 4%, margin 14%), free cash flow before interest is 3.66 against 3.39 in the base case. Why?
Right. Each extra euro of sales needs 20 cents of working capital and 8 of capex.
It is the cost of growth showing up.
Tax falls a little, but not enough to explain it.
It is lower: 14% against 15%.
What is the difference between a scenario and a sensitivity?
Either can go either way.
Right.
Both use the same model.
They answer different questions.
One point of EBITDA margin is worth 1.32 of EBITDA for Harmony in N+1. One point of sales growth is worth about…
1.2 is the extra sales, not EBITDA.
Too high: only 15% of the extra sales becomes EBITDA.
That is the margin point.
Right. 1.2 of sales at a 15% margin.
4Discipline makes a model usable, and planning cycles put it to work
A correct model that nobody can read or update is soon abandoned. Habits and routines decide whether the work survives.
Hygiene: six habits that prevent most model errors
Most model errors come from structure, not arithmetic: an input buried in a formula, a column that copies the wrong way, a total that skips a row. A few habits remove most of the risk.
Separate inputs, calculations and outputs. Drivers sit in one place, in a distinct colour. Calculations never contain typed numbers.
One formula per row. The formula in the first forecast year is copied across, so a reader checks one cell and trusts the row.
Build the checks in. The five checks of part 2 sit on the front page, with a single cell that turns red if any fails.
Label units and signs. Millions or thousands, cash in as positive, cash out as negative, stated once and followed everywhere.
Keep a log. A short sheet records who changed what, and when. Without it, two people soon hold two versions of the truth.
Test with extreme inputs. Set sales to zero, or margin to 100%. A sound model shows absurd but consistent results; a broken one shows errors.
From budget to business plan to rolling forecast
Budget, business plan and rolling forecast answer three different questions
| Budget | Business plan | Rolling forecast | |
|---|---|---|---|
| Horizon | Next financial year | Three to five years | Next four to six quarters, always |
| Question | What are we committing to? | Where is the company going and how will it be funded? | What now looks likely? |
| Detail | High: by department and month | Medium: by business line and year | Medium: key drivers and results |
| Used for | Targets, bonuses, cost control | Board strategy, lenders, investors | Early warning, funding timing |
Three forecasting documents and what each is for.
A budget is a commitment, so it is partly a negotiation. A business plan is a story told to lenders and investors, and must show that the funding closes: how much new money is needed, when, and from whom. A rolling forecast is refreshed every quarter and always looks the same distance ahead. One model with clean drivers serves all three, and the gap between budget and actual, the variance, is explained driver by driver: volume, price, mix, cost.
a variance bridge game. The learner is given Harmony's budget and a set of actual results for the first quarter and must split the gap into volume, price and cost effects by dragging amounts into the right bars. The tool shows the learner's bridge against the correct one and discovers that the largest variance is often not where the headline gap suggests.
4.1 Why should typed numbers never sit inside a calculation formula?
A number typed inside a formula is invisible and cannot be changed in one place. Someone updates the input cell and the buried number stays, so the model gives a wrong answer without any sign of error. Inputs belong in one labelled area.
4.2 How does a rolling forecast differ from a budget, and why would a CFO want both?
A budget fixes targets for a year and is rarely changed; it is a commitment. A rolling forecast is updated each quarter and always looks ahead the same distance, so it shows what now looks likely. A CFO wants the budget to hold managers to targets and the rolling forecast to see problems and funding needs early.
4.3 Why is a three-statement model, not just a profit forecast, required for a financing plan?
A profit forecast does not show cash, debt, or the funding needed to support growth. A financing plan is about exactly those: how much money is needed and when, and whether leverage stays acceptable. Only a model that links profit to cash and to the balance sheet can show it.
4
Which habit stops a typed number from hiding inside a formula?
Useful, but it does not stop the error.
Helps reading, but a typed number can still sit in that formula.
Right. Drivers sit in one coloured area; calculations never contain typed numbers.
Rounding hides nothing and fixes nothing.
How does a rolling forecast differ from a budget?
That is the business plan.
That describes the budget.
Bonuses follow the budget.
Right. The budget is a commitment for one year.
Why does a business plan for lenders need a three-statement model and not only a profit forecast?
The reason is economic, not legal.
They do, but profit alone does not show funding.
The issue is what they leave out, not their bias.
Right. The plan is judged on whether the funding closes.
Summary and cheat sheet
The module’s formulas
Tap a term to see what it means and its value at Harmony.
The skeleton of every model
= +
If a column does not open on the previous closing figure, the model forecasts nothing.
The cash that decides the debt
= − − − −
With no dividend and cash held at 10.0, it is exactly the fall in debt.
Interest on average debt (closed form)
= × (2 × − ) / 2 / (1 − r × (1 − ) / 2)
Three passes of repetition give the same 1.95: the closed form avoids the circular reference.
The leverage the bank watches
= /
Banque du Rhône covenant: at most 3.0×; and EBITDA / interest at least 4.0×.
| Idea | What to remember |
|---|---|
| Three statements | Income statement, balance sheet and cash flow describe the same events and must agree. |
| Driver | An input a manager can explain and revisit. Everything else is calculated. |
| Skeleton | Closing balance = opening balance + the year's flows. |
| Working capital | Grows with sales. At 20% of sales, Harmony's 12.0 of extra sales absorbs 2.4 of cash. |
| Capex and depreciation | Capex follows growth; depreciation follows the assets already owned. |
| Plug | Debt (or cash) is the balancing item. Treat it as a result and check it is plausible. |
| Circularity | Interest on average debt makes interest depend on itself. Solve by repetition or closed form. |
| Five checks | Balance sheet balances, cash flow reconciles, equity moves only by known items, no negative cash or debt, years link. |
| Harmony N+1 | Sales 132.0, EBITDA 19.8, net income 8.89, net debt 28.07 (revolving facility 12.07), leverage 1.42×, cover 10.1×. |
| Scenario vs sensitivity | Scenario: several drivers tell a story. Sensitivity: one driver, to rank influence. |
The one-sentence version
Pick a few drivers you can defend, push them through three statements built from opening balances plus flows, let debt or cash balance the sheet, check that everything agrees, and then change the drivers to see how far the answer can move.
Key terms
| Term | Plain-language meaning |
|---|---|
| Driver | An input assumption, such as sales growth, that a person can explain and change. |
| Three-statement model | A forecast that links the income statement, balance sheet and cash flow statement. |
| Plug | The balancing line, usually debt or cash, that makes the balance sheet balance. |
| Circular reference | A calculation that depends on its own result, such as interest on average debt. |
| Scenario | A coherent set of driver values that tells one story about the future. |
| Sensitivity | The effect on a result of changing one driver at a time. |
Going further
Vernimmen, Quiry, Le Fur — Finance d'entreprise (Dalloz), chapters on financial analysis, cash flows and company valuation, which supply the statements and the forecasting framework used here.
Benninga — Financial Modeling (MIT Press), a practical guide to building spreadsheet models of corporate finance problems.
Koller, Goedhart, Wessels — Valuation: Measuring and Managing the Value of Companies (McKinsey and Company, Wiley), chapters on forecasting performance.
Independent educational material. Not investment, legal or tax advice. All companies and figures in the running case are fictional.