Aller au contenu
Corporate & Investment Banking Mon parcours English

Accueil / Corporate Finance / Au-delà du manuel / Module 46

46Financial modelling and forecasting

How to turn a handful of assumptions into a forecast that balances

Transformer quelques hypothèses en une prévision qui s’équilibre.

Lecture
20 min
Sections
11
Exercices
2
Questions de rappel
9
Questions de QCM
12
Simulateurs
3

Le texte de ce module est encore en anglais : la version française arrive. Les outils interactifs, eux, sont déjà en français.

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. 1.2 Why does net income appear in the balance sheet as well as in the income statement?

      2. 1.3 Name two drivers from the Harmony table and say which statement each one first affects.

      3. 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?

      1

      1. Why must the income statement, balance sheet and cash flow statement of a forecast agree?

      2. A forecaster types next year's receivables as 22.0 with no formula. What is the problem?

      3. Which of these is a driver in Harmony's N+1 model?

      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.

          1. 2.3 Why does Harmony's net debt fall by only 1.93 when net income is 8.89?

          2. 2.4 Why is interest on the average debt better than interest on the opening debt, and what price is paid for using it?

          3. 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?

          2

          1. Harmony earns 8.89 in N+1. Why does net debt fall by only 1.93?

          2. Why is interest on average debt better than interest on opening debt, and what is the price?

          3. In Harmony's model, which line is the plug?

          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

          1. In the downside (growth 4%, margin 14%), free cash flow before interest is 3.66 against 3.39 in the base case. Why?

          2. What is the difference between a scenario and a sensitivity?

          3. One point of EBITDA margin is worth 1.32 of EBITDA for Harmony in N+1. One point of sales growth is worth about…

          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.

          1. 4.1 Why should typed numbers never sit inside a calculation formula?

          2. 4.2 How does a rolling forecast differ from a budget, and why would a CFO want both?

          3. 4.3 Why is a three-statement model, not just a profit forecast, required for a financing plan?

          4

          1. Which habit stops a typed number from hiding inside a formula?

          2. How does a rolling forecast differ from a budget?

          3. Why does a business plan for lenders need a three-statement model and not only a profit forecast?

          Summary and cheat sheet

          Les formules du module

          Touchez un terme pour voir ce qu’il veut dire et sa valeur chez Harmony.

          Le squelette de tout modèle

          = +

          Si une colonne ne s’ouvre pas sur la clôture de la précédente, le modèle ne prévoit plus rien.

          La trésorerie qui décide la dette

          = − − − −

          Sans dividende et avec une trésorerie tenue à 10,0, c’est exactement la baisse de la dette.

          Les intérêts sur la dette moyenne (forme fermée)

          = × (2 × − ) / 2 / (1 − r × (1 − ) / 2)

          Trois passes de répétition donnent le même 1,95 : la forme fermée évite la référence circulaire.

          Le levier que regarde la banque

          = /

          Covenant de Banque du Rhône : au plus 3,0× ; et EBITDA / intérêts au moins 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.