Bridgesheet

Financial modelling7 min read

Sensitivity Table in Excel: Stress-Test a Financial Model

A sensitivity table in Excel recalculates a model's output for several values of one or two assumptions, without copying the model. Here is how to build it with the Data Table tool, check it and read it, using a DCF whose value ranges from €12.7m to €19.9m.

Old book open at a table of logarithms, with columns of printed figures
Photo: ArnoldReinhold · CC BY-SA · Wikimedia

What a sensitivity table in Excel is for

A financial model rests on a handful of assumptions that nobody knows with certainty: the cost of capital, long-term growth, a margin, an exit multiple. A sensitivity table in Excel shows how the model's output (enterprise value, IRR, price per share) changes when those assumptions move. It answers the question an investment committee, a buyer or a banker will ask: "what if you are a point out?"

Excel produces it with a built-in tool, the Data Table (Data tab, then What-If Analysis). The tool substitutes each value from a row or a column, one at a time, for an assumption cell, recalculates the whole model and writes the result into the grid. That saves you from copying the model five or twenty-five times.

The example: a DCF showing €15.4m

Take Laboratoires Vasseur, a fictional industrial testing company. Its DCF (discounted cash flow) uses:

  • revenue of €15.0m, growing 6%, 5%, 4%, 3% and then 2% over five years;
  • an EBITDA margin (operating profit before depreciation and amortisation) of 14%;
  • depreciation of 3% of revenue, capital expenditure of 3.5% and working capital (the cash tied up in inventory and trade receivables) of 15% of revenue;
  • a tax rate of 25%, a WACC (weighted average cost of capital) of 10% and perpetual growth of 2%.

Free cash flow rises from €1.10m in year one to €1.36m in year five. The five discounted cash flows are worth €4.64m; the terminal value, 1.36 × 1.02 ÷ (10% − 2%) = €17.34m, is worth €10.77m once discounted. Enterprise value comes out at €15.4m, of which 70% comes from the terminal value. That dependence is what makes sensitivity analysis indispensable: the terminal value reacts strongly to the WACC and to growth, as our article on the terminal value of a DCF explains.

One-variable sensitivity table

Start with a single assumption, here the EBITDA margin.

  1. On the sheet that holds the margin cell (14%), write the values to test in a column: 12%, 13%, 14%, 15%, 16%.
  2. In the cell one row above and one column to the right of the first value, enter a formula that points to the result: =C40 if enterprise value is in C40.
  3. Select the whole block, values and formula included.
  4. Data > What-If Analysis > Data Table. Leave "Row input cell" empty and enter the margin cell in "Column input cell".

The result:

EBITDA margin Enterprise value
12% €12.3m
13% €13.8m
14% €15.4m
15% €17.0m
16% €18.5m

One point of margin is worth about €1.6m, or 10% of the value. For a company whose margin has varied from 12% to 15% over the last three years, this is the assumption to justify first.

Two-variable sensitivity table: WACC × growth

The most common DCF table crosses the WACC with perpetual growth.

  1. Write the WACC values across a row (9% to 11%, in steps of 0.5 points) and the growth rates down a column (1% to 3%).
  2. In the corner cell, at the intersection of the row and the column, enter the formula =C40.
  3. Select the whole block, then Data Table: the WACC cell as "Row input cell", the growth cell as "Column input cell".

Excel fills the grid with an array formula, {=TABLE(C12,C13)}. Enterprise value of Laboratoires Vasseur, in €m:

g \ WACC 9.0% 9.5% 10.0% 10.5% 11.0%
1.0% 15.9 15.0 14.1 13.4 12.7
1.5% 16.7 15.7 14.7 13.9 13.1
2.0% 17.6 16.5 15.4 14.5 13.7
2.5% 18.7 17.4 16.2 15.2 14.3
3.0% 19.9 18.4 17.1 15.9 14.9

The value ranges from €12.7m to €19.9m. A one-point change in WACC, up or down, moves it by €1.7m to €2.2m; half a point of growth moves it by about €0.8m. With €4.0m of net debt, equity value ranges from €8.7m to €15.9m: the relative spread is even wider for the shareholder, because the debt does not move.

Construction rules that prevent errors

The Data Table has little-known constraints. Ignoring them produces a wrong grid with no error message.

  • The input cells must be on the same sheet as the table. If the WACC is calculated on a separate tab, create an assumption cell on the DCF sheet that feeds it, and point the table at that cell.
  • The input cell must contain a value, not a formula. Excel substitutes each test value into it; if it overwrites a formula, the link to the rest of the model is lost during the calculation.
  • Do not swap row and column. This is the most frequent mistake: the WACC is tested in place of growth, and the table still looks plausible. The check is simple: the central cell must equal the base-case result to the cent (€15.4m here).
  • An odd number of steps, base case in the middle. Two values either side of the chosen assumption make the table easy to read. Bold the base-case cell or colour it with conditional formatting.
  • Steps consistent with real uncertainty. 0.5 points for the WACC and growth, 1 point for a margin, 0.5x for a multiple. Steps that are too wide produce extreme values that nobody will defend.
  • Rule out absurd combinations. Perpetual growth equal to or above the WACC makes the Gordon growth formula diverge. Keep a gap of at least 5 points, or display "n/m" with an IF formula.

Calculation, speed and circular models

Every cell in the table triggers a full recalculation of the model. A 5 × 5 grid therefore runs 25 calculations each time you change anything, and a heavy model becomes slow. Two settings help:

  • File > Options > Formulas > "Automatic except for data tables". Tables then recalculate only when you press F9. Remember to press F9 before printing or sending the file, otherwise the grid shows stale values.
  • Limit the number of tables. Three well-chosen tables are worth more than ten.

LBO models pose a particular problem: interest, calculated on the year's average debt, depends on the cash flow that repays that debt. This circular reference, solved by Excel's iterative calculation, sometimes makes data tables unstable. The usual fix is a switch that calculates interest on opening debt while the sensitivity runs, or a small macro that pastes values one by one. In an LBO model, the standard table crosses the entry multiple with the exit multiple, with the IRR in the centre; the paper LBO shows the order of magnitude of this sensitivity.

Which assumptions to test, by model

Model Output tracked Most useful two-variable table
DCF Enterprise value, price per share WACC × perpetual growth, or WACC × exit multiple
LBO Investors' IRR and MOIC Entry multiple × exit multiple, or EBITDA growth × leverage
Merger model Accretion or dilution of earnings per share Price paid × share of consideration paid in stock
Cash flow plan Minimum cash balance Customer payment terms × revenue growth

Choose assumptions that are both uncertain and influential. The tax rate is influential but known; fifth-year growth is uncertain but matters little. To rank the assumptions, one single-variable table per assumption, with the same amplitude (± 10%, for example), is enough: the one that moves the result most deserves the two-variable table.

Reading and presenting a sensitivity table

A sensitivity table is not a valuation range. Its corners combine two extreme assumptions at the same time, which is rarely realistic. To present a value, keep to the central zone instead (here €14.5m to €16.5m, half a point of WACC either side) and explain why.

Before circulating the table, check three points: the central cell equals the base case; values move in the right direction (value falls as the WACC rises); the table has been recalculated (F9). These checks are part of the five checks for a DCF model to run before relying on a result.

Our DCF Excel model includes a ready-built WACC × growth sensitivity table, with the base case in the centre and the share of terminal value displayed. It is a calculation tool: the result remains an estimate that depends on your assumptions, and is neither a certified valuation nor investment advice.