13.3 Sensitivity Analysis
Key Takeaways
- Sensitivity analysis changes one input at a time; a one-way data table varies one input, a two-way table varies two inputs and returns only one output, and both are created with Data → What-If Analysis → Data Table, which writes {=TABLE(...)}.
- The corner formula cell must be a live output such as NPV or equity value, and the row and column input cells must be the model's actual input cells on the same sheet — not copies or labels.
- Apex Components base EV is $80.0 million at 10% WACC; a one-way WACC table holding growth, margin, and g at base gives about $109 million at 8% WACC and about $63 million at 12% WACC.
- A tornado chart ranks one-at-a-time sensitivities by the output swing from low to high; on Apex, EBIT margin is the widest bar, then WACC, then revenue growth, then terminal growth.
- A flat data table in which every result equals the base case means the table is not pointed at the live input — a copy of the WACC cell, a label, or a hardcoded NPV in the corner — not that the model has zero sensitivity.
Sensitivity Means One Input at a Time
Quick Answer: Sensitivity analysis changes one input at a time and records the output. In Excel that engine is the data table: one-way varies one input; two-way varies two inputs and returns only one output. Create it with Data → What-If Analysis → Data Table, which writes a
{=TABLE(...)}array — you cannot type TABLE as a worksheet function. The corner cell must be a live formula (NPV, EV, equity value). The row input and column input cells must be the actual model inputs, on the same sheet. Tornado charts rank those one-at-a-time swings. Data tables are what power tornado and flex displays in CFI's Scenario & Sensitivity Analysis in Excel course.
Section 13.2 built a joint downside that crushed Apex's EV from $80 million to about $29 million. That is the coherent story. Sensitivity asks a different question: which single driver is doing the damage? If you can only hedge, contract, or argue about one assumption in a pitchbook, you want the driver with the widest bar, not a joint scenario that mixes four effects.
Excel is about 10% of CFI's published FMVA topic-weight graphic. After the 2026 refresh, Scenario & Sensitivity Analysis in Excel is required core. The final will score whether you can read a two-way grid of equity value versus WACC and terminal growth, or versus volume and price, and whether you can explain why a table came back flat.
One-Way Data Tables
A one-way data table lists alternative values of one input down a column (or across a row) and records the resulting output. Layout:
- Column of WACC rates (8.0%, 8.5%, …, 12.0%) down the left.
- Live EV formula in the cell one row above and one column to the right of the first rate — the corner of the table.
- Select the whole rectangle including the corner and the rates.
- Data → What-If Analysis → Data Table → Column input cell = the model's actual WACC cell (the blue input that the DCF already reads). Leave Row input cell blank for a column-oriented one-way table.
Excel writes {=TABLE(, WACC_cell)} across the output column. Every WACC in the list is substituted into the live model, EV is recorded, then the original WACC is restored. That substitution is why the table must sit on the same worksheet as the shocked input. A table on a Outputs tab pointing at an Inputs!WACC cell is a common reason a grid prints the base EV in every row.
Worked one-way: Apex EV versus WACC, holding growth at 10%, EBIT margin at 16%, and g at 2.5% (the base scenario). FCFF remains $5.28m / $5.808m / $6.3888m. Only the discount rate and the Gordon denominator change.
| WACC | TV = 6.3888 × 1.025 / (WACC − 2.5%) | EV |
|---|---|---|
| 8.0% | $119.1 million | ~$109 million |
| 9.0% | $100.7 million | ~$92 million |
| 10.0% | $87.3 million | $80.0 million |
| 11.0% | $77.0 million | ~$70 million |
| 12.0% | $68.9 million | ~$63 million |
Walk the 12% row far enough to see the mechanics. TV = 6.3888 × 1.025 / (0.12 − 0.025) = 6.54852 / 0.095 = $68.93 million. PV of FCFF₁ = 5.28 / 1.12 = $4.71 million. PV of FCFF₂ = 5.808 / 1.2544 = $4.63 million. PV of (FCFF₃ + TV) = 75.32 / 1.404928 = $53.61 million. Sum ≈ $63 million.
At 8% WACC the same cash flows are worth about $109 million. A two-point move in WACC, holding everything else at base, swings EV by roughly $46 million. That is a sensitivity, not a scenario: growth, margin, and g never left the base case.
You may place several output formulas across the top of a one-way table (EV, equity value, Year-1 NI, closing revolver) and vary the same WACC down the side. Each extra output is still a one-way table on one input. That is allowed. A two-way table cannot do this — two-way is one output only.
Two-Way Data Tables
A two-way data table varies two inputs and returns one output. Classic FMVA grids:
- EV or equity value versus WACC (column) and terminal growth g (row)
- NPV versus volume (column) and price (row)
Layout:
- Row of g values across the top (2.0%, 2.5%, 3.0%).
- Column of WACC values down the left (8%, 10%, 12%).
- Live EV formula in the corner (the intersection cell, top-left of the grid).
- Data Table → Row input cell = the model's live g cell; Column input cell = the model's live WACC cell.
Excel writes {=TABLE(g_cell, WACC_cell)}. Every pair (g, WACC) is substituted, EV is recorded, originals are restored. The table still returns one output. If you need EV and equity value, you need two two-way tables, each with its own live corner formula.
Worked two-way: Apex EV ($ millions), growth and margin at base.
| WACC \ g | 2.0% | 2.5% | 3.0% |
|---|---|---|---|
| 8% | ~101 | ~109 | ~119 |
| 10% | ~$76 | $80 | ~$85 |
| 12% | ~60 | ~63 | ~66 |
The 10% / 2.5% cell must equal the live EV of $80 million. If it does not, the corner formula is not the same cell the DCF prints, or you pointed at copies of WACC and g. At 10% WACC and g = 2.0%: TV = 6.3888 × 1.02 / 0.08 = $81.46 million; EV = 4.80 + 4.80 + 87.85/1.331 = $75.6 million. At 10% WACC and g = 3.0%: TV = 6.3888 × 1.03 / 0.07 = $94.01 million; EV ≈ $85 million. Terminal growth is a real driver, but at these gaps it moves Apex less than WACC does — a fact the tornado will rank.
Keep g < WACC in every cell of the grid. A 3.0% g column next to an 8.0% WACC is fine (spread 5.0%). A 3.0% g column next to a 3.0% WACC is a divide-by-zero. Design the axes so the Gordon formula stays defined, or wrap TV with a check that flags WACC <= g.
Volume and Price: The Operating Two-Way
Valuation cases pair WACC with g. Operating and project cases pair volume with price (or volume with unit cost). Same Excel object, different inputs.
Ridgeline Product NPV. Time-0 capex $2,000,000, three-year life, straight-line depreciation $666,667 per year, no salvage, 25% tax, 10% WACC. Base volume 100,000 units, price $40, unit cost $22, fixed cash opex $800,000. Base contribution = 100,000 × ($40 − $22) = $1,800,000. EBIT = 1,800,000 − 800,000 − 666,667 = $333,333. NOPAT = $250,000. FCFF = 250,000 + 666,667 = $916,667 per year. NPV = −2,000,000 + 916,667 × 2.48685 ≈ $280,000.
A two-way of volume (80,000 / 100,000 / 120,000) versus price ($36 / $40 / $44), with unit cost, fixed cost, tax, and WACC held at base, is the operating analog of the WACC–g grid. The corner cell is the live NPV formula. The row input is the live price cell. The column input is the live volume cell. If you point the row input at a label that says "Price" or at a copy of price used only for display, every NPV in the grid will print $280,000 and you will think the project is insensitive to price. It is not. Your table is disconnected.
Tornado Charts Rank Sensitivities
A tornado chart is a horizontal bar chart of one-at-a-time output swings, sorted so the widest bar is at the top. Each bar is usually the EV (or NPV) at the driver's low case versus its high case, with every other driver held at base. That is why a tornado is sensitivity, not scenario, even though the low and high values may be borrowed from the scenario table.
CFI's course uses data tables (or a one-way flex table) to feed the tornado. The flex view is the same math in a table: each driver shocked to −10% / +10%, or to the downside / upside values, one row per driver.
Apex tornado, using the Section 13.2 low/high values, one driver at a time, base EV = $80.0 million.
| Driver shocked alone | Low value → EV | High value → EV | Swing |
|---|---|---|---|
| EBIT margin (10% / 20%) | $50 million | $100 million | $50 million |
| WACC (12% / 8%) | ~$63 million | ~$109 million | ~$46 million |
| Revenue growth (2% / 15%) | ~$65 million | ~$91 million | ~$26 million |
| Terminal g (1.5% / 3.0%) | ~$72 million | ~$85 million | ~$13 million |
Margin is linear in this simplified FCFF = EBIT × (1 − t) model: 10/16 × $80 million = $50 million, 20/16 × $80 million = $100 million. WACC is not linear — Gordon's 1/(WACC − g) bends — which is why the 8% and 12% results are not symmetric around $80 million. Growth compounds through three explicit years and the terminal year, so it outranks g but not margin or WACC on this case.
Sort the bars: margin, WACC, growth, g. That ranking is the tornado. It is also the talking sequence for a pitchbook (Chapter 18): spend the page on margin sustainability and on the WACC build, not on whether terminal g is 2.4% or 2.6%.
Do not title a tornado "scenario analysis" because three colored bars appear. The bars are one-at-a-time. The $29 million joint downside from Section 13.2 is not a bar on this tornado. It is smaller than any single low case except a stacked combination, and that is the pedagogical punch line: joint scenarios are harsher than the worst single-driver bar when the drivers are correlated.
The Live-Formula Trap
CFI exam stems on data tables cluster around four mechanical failures. All of them produce a flat table or a #REF! / #N/A rather than a useful grid.
- Corner cell is a hardcoded number. If you paste 80000000 in the corner because that is base EV, Excel has nothing to recalculate. Every output equals 80,000,000. The corner must be
=EVor the actual NPV formula, live. - Row or column input cell is not the model's input. Pointing at a label, a text copy, a formatted display of WACC (
TEXT(WACC,"0.0%")), or a duplicate WACC on the Data Table sheet that the DCF does not read, means the live model never sees the shocked value. Flat table. Point at the same cell the DCF formula already uses. - Table is on a different sheet from the input cells. Excel's data table requires the shocked inputs to be on the same worksheet. If WACC lives on Inputs, either put the table on Inputs or (better for audit) put a live WACC input on the Valuation sheet that the DCF reads, and shock that cell. Do not shock a cross-sheet clone.
- Typing
=TABLE(...)by hand. TABLE is not a function you enter. The Data Table command writes the array. Typing it returns a name error. Chapter 8 covered this; the case will still catch people who try it.
Related failures: converting the table to values (it dies on the next toggle); leaving calculation on Automatic except data tables and wondering why the grid is stale; and shocking a formula WACC (for example =Re*E/V + Rd*(1-T)*D/V) instead of the blue inputs that feed WACC. If you need a WACC sensitivity, shock Rd, Re, or the weights, or replace the WACC formula with a blue override the table is allowed to write into. Data Table overwrites the input cell during the run; a formula in that cell gets replaced and may not come back cleanly.
Reading the Grid on the Timed Case
CFI's published pace is about 45 minutes per case, 60 maximum. When a stem asks "at what WACC does NPV change sign" you do not need Goal Seek if a one-way NPV-versus-WACC table already crosses zero. When a stem asks whether EV is more sensitive to WACC or to g, read the two-way: the larger change along the WACC axis versus the g axis at the base pair is the answer — and it should match the tornado ranking.
If the case already includes a tornado, rebuild one driver by hand as a check: set margin to 10%, record EV, restore 16%. If your hand shock does not match the tornado's low-margin bar, the tornado is stale or is actually a scenario in disguise (someone toggled the code to 1 and pasted values). On a scored case, trust a live data table over a pasted chart.
Pair the tools rather than choosing one. Scenarios (13.2) answer "what does the coherent recession look like?" Sensitivities (13.3) answer "if I can fight about only one assumption, which one?" Debt schedules (13.1) answer "does that recession trip the revolver and the covenants?" The FMVA case is all three at once: toggle the code, read the two-way, and confirm the term loan still amortizes $4 million without going negative.
What must be true of an Excel two-way data table that shocks WACC down the side and terminal growth across the top to record enterprise value?
How do a one-way data table and a two-way data table differ?
On the Apex Components tornado, each driver is shocked to its low and high values while every other driver stays at base. What does that chart rank, and which driver is widest in the worked example?