13.2 Scenario Analysis

Scenario Versus Sensitivity

Quick Answer: Sensitivity analysis changes one input at a time and reads the output from a data table. Scenario analysis changes several inputs together into a coherent case, typically downside / base / upside (or worst / base / best). CFI's core course Scenario & Sensitivity Analysis in Excel, required after the 27 February 2026 refresh, drives scenarios with a scenario code (1 / 2 / 3) plus INDEX or CHOOSE, not with Excel's Scenario Manager. A scenario is not three data tables: a data table can vary one input (one-way) or two inputs (two-way) and cannot apply revenue growth, EBIT margin, and WACC as a joint shock.

Chapter 8 taught the Excel mechanics of Goal Seek and {=TABLE(...)}. This section is the modeling use of the same distinction. Finance is about 10% of estimated FMVA final weight and Financial Modeling about 30%. Valuation (about 30%) is where the output usually sits: enterprise value (EV) or equity value. The final's Excel cases will not give you a ribbon screenshot. They will give you a model with a blue scenario cell and ask what EV, net income, or closing revolver does when that cell moves from 2 to 1.

CFI's published article-level distinction is the one you should quote:

• Sensitivity: change one driver, hold everything else constant, usually with a data table.
• Scenario: change several drivers that belong together economically, usually into base / worst / best.

A recession that cuts revenue growth but somehow leaves EBIT margin and WACC untouched is a sensitivity, not a scenario. In a real downside, volume and price pressure compress the margin, and risk premia lift WACC. Those three moves are one story. Modeling them one at a time understates the damage because the effects compound: lower near-term cash flow and a higher discount rate and a thinner margin on every dollar of sales.

The Scenario Toggle CFI Expects

Excel ships Scenario Manager (Data → What-If Analysis → Scenario Manager). CFI models typically do not use it for the case you will sit. Scenario Manager stores named cases off-sheet, is painful to audit in a timed exam, and does not print a visible driver grid for a reviewer. The CFI pattern is:

1. A blue input cell holds a scenario code: 1 = downside (worst), 2 = base, 3 = upside (best) — or whatever labels the case prints. Keep the code 1-based so it matches CHOOSE.
2. A scenario table lists every shocked driver in rows and the three cases in columns.
3. Live driver cells on the model's assumption block are formulas: INDEX into the table, or CHOOSE of the three values.
4. The rest of the model — income statement, debt schedule, WACC, DCF — reads only those live driver cells. Nothing in the forecast hardcodes a 10% growth rate next to the table.

That architecture means a reviewer (and you, at minute 40 of a case) can change one blue cell and watch every statement, the revolver, and EV move together. It is also how you avoid the integrity error of shocking growth on the income statement while leaving the DCF WACC stuck at the base rate.

Building the Driver Table

Apex Components is a simple unlevered DCF used to isolate the scenario engine. Year-0 revenue is $40 million. Tax is 25%. For this illustration, depreciation equals capex and change in net working capital is $0, so unlevered free cash flow (UFCF / FCFF) = EBIT × (1 − tax rate). A full FMVA case still has the Chapter 13.1 debt schedule, working capital, and capex; those would also read the live drivers. Terminal value uses Gordon growth: TV = FCFF₃ × (1 + g) / (WACC − g).

Park this table on an Inputs (or Scenarios) tab in the CFI color convention: blue for the scenario code, black for the INDEX/CHOOSE formulas, and the three case columns either as blue hardcodes (they are assumptions) or as a referenced case pack. The live growth cell is not a blue 10%. It is a formula pointing at this table.

INDEX and CHOOSE

Two formulas CFI expects you to type without hunting the ribbon:

CHOOSE is 1-based and lists values in order:

=CHOOSE($C$3, 0.02, 0.10, 0.15)

When $C$3 is 2, the live growth rate is 10%. When $C$3 is 1, it is 2%. CHOOSE is transparent when there are three cases and a handful of drivers. It becomes clumsy when the table has ten drivers: you must keep the argument order identical in every row.

INDEX reads the table:

=INDEX($E$6:$G$6, $C$3)

If row 6 is the growth row and columns E/F/G are downside/base/upside, a code of 3 returns the upside 15%. INDEX scales. Add a fourth case in column H, extend the range, and every live driver updates. INDEX also survives a column insert better than a handwritten CHOOSE list.

Either function is acceptable on the exam. OFFSET can do the same job and is harder to audit; CFI's modeling guidelines prefer INDEX. Do not write nested IF($C$3=1, 0.02, IF($C$3=2, 0.10, 0.15)) for every driver — it works, it is slow to audit, and a fourth case becomes a nest you will mis-edit under time pressure.

Named ranges help: =INDEX(tbl_growth, 1, scenario_code) is readable. If you name the code cell scenario_code, lock it as an absolute reference in every live driver so a copy-right does not pick up a blank cell.

Worked Example: Apex Components Three Cases

Base (code 2). Revenue grows 10% for three years from $40 million: Year 1 $44.0 million, Year 2 $48.4 million, Year 3 $53.24 million. EBIT margin 16% → EBIT $7.04m / $7.744m / $8.5184m. After 25% tax, FCFF = $5.28m / $5.808m / $6.3888m. Terminal value at g = 2.5% and WACC = 10%:

TV = 6.3888 × 1.025 / (0.10 − 0.025) = 6.54852 / 0.075 = $87.3136 million

Discount factors at 10%: 1.10, 1.21, 1.331.

EV = 5.28/1.10 + 5.808/1.21 + (6.3888 + 87.3136)/1.331 = 4.80 + 4.80 + 93.7024/1.331 = 4.80 + 4.80 + 70.40 = $80.0 million

That round $80 million is the base you will shock in Section 13.3. Remember it.

Downside (code 1). Growth 2%, margin 10%, WACC 12%, g 1.5%. Year-1 revenue = 40 × 1.02 = $40.8 million. EBIT = 40.8 × 0.10 = $4.08 million. FCFF = 4.08 × 0.75 = $3.06 million. Year 2 revenue = 40.8 × 1.02 = $41.616 million, FCFF = $3.1212 million. Year 3 revenue = $42.448 million, FCFF = $3.1836 million. TV = 3.1836 × 1.015 / (0.12 − 0.015) = 3.2314 / 0.105 = $30.78 million.

EV = 3.06/1.12 + 3.1212/1.2544 + (3.1836 + 30.78)/1.404928 ≈ 2.73 + 2.49 + 24.17 ≈ $29.4 million

The joint downside does not shave 10% off the $80 million base. It destroys more than 60% of EV, because cash flows are thinner and they are discounted harder and Gordon's denominator (WACC − g) is wider.

Upside (code 3). Growth 15%, margin 20%, WACC 8%, g 3.0%. Year-1 revenue = $46.0 million, EBIT = $9.20 million, FCFF = $6.90 million. Year 2 revenue = $52.9 million, FCFF = $7.935 million. Year 3 revenue = $60.835 million, FCFF = $9.125 million. TV = 9.125 × 1.03 / (0.08 − 0.03) = 9.399 / 0.05 = $187.98 million.

EV = 6.90/1.08 + 7.935/1.1664 + (9.125 + 187.98)/1.259712 ≈ 6.39 + 6.80 + 156.47 ≈ $169.7 million

The spread is the point. Joint shocks are not additive. You cannot take "growth sensitivity −$15 million plus margin sensitivity −$30 million plus WACC sensitivity −$17 million" and call it the downside. Those one-at-a-time bars (Section 13.3) each hold the other drivers at base. The scenario turns all of them at once.

Why This Is Not Three Data Tables

A one-way data table varies one input across a column or row and records one or more outputs. A two-way data table varies two inputs and records exactly one output. Neither tool can apply four drivers as a set.

If you built three one-way tables — EV versus growth, EV versus margin, EV versus WACC — you would still be reading ceteris paribus results. The growth table would keep margin at 16% and WACC at 10%. That is useful ranking (the tornado in 13.3). It is not the downside case. A two-way table of growth versus WACC would still freeze margin and g. There is no native three-way data table in Excel.

Scenario Manager could store three named cases, but it would hide the driver grid, would not sit on the Inputs tab for a screenshot, and is easy to leave stale when you add a fourth driver (for example, a capex-to-sales ratio). INDEX/CHOOSE keeps every driver visible, printable, and auditable — the same standard CFI applies to the Circ Switch and to blue/black font.

A related trap: copying the whole workbook three times ("Downside.xlsx", "Base.xlsx", "Upside.xlsx"). That is three models, not a scenario engine. Formulas diverge, a debt-schedule fix lands in only one file, and you cannot toggle during the case. One model, one code cell.

Using Scenarios on FMVA Case Studies

CFI budgets about 45 minutes per Excel case (60 maximum) inside the 3-hour exam. Use the scenario toggle as an audit tool, not only as a valuation toy:

1. Find the code cell first. It should be blue, on Inputs, and documented (1 / 2 / 3). If the case already has INDEX/CHOOSE wired, do not rebuild Scenario Manager on top of it.
2. Set the code to 2 (base) and confirm the three-statement Assets = Liabilities + Equity check is $0, BS cash equals CFS ending cash, and the debt rollforward ties. Record base NI, closing revolver, and EV.
3. Toggle to 1 (downside). Growth, margin, and WACC (and any other listed drivers) must all change. If EV changes but WACC did not, the DCF is not reading the live WACC cell. If the income statement moved but the revolver did not, the cash plug is hardcoded.
4. Watch the debt schedule. Lower EBIT can trip a leverage or interest-coverage flag even if the term-loan amortization is unchanged. The revolver should draw if cash would otherwise breach minimum cash. That is Section 13.1 working under a joint shock.
5. Toggle to 3 (upside) and confirm you did not hardcode a sign that only works in the base (for example, an optional prepay of $1 million that drives debt negative when cash is huge — cap prepay at remaining principal).
6. Return the code to whatever the case asks you to submit. Leaving the file on downside when the question wants base EV is a fully preventable miss.

Interview-style items on the 50-question exam will quote the CFI distinction almost verbatim: several inputs together versus one input at a time. If a stem says "worst / base / best" or "downside / base / upside," the answer is scenario. If the stem says "data table" or "holding all else constant," the answer is sensitivity.

Exam Traps on Scenarios

• Off-by-one in CHOOSE. CHOOSE is 1-based. A code of 0 returns #VALUE!. A code of 4 with only three arguments also errors. Restrict the blue cell with data validation 1–3.
• INDEX range excludes a column. INDEX($E$6:$F$6, 3) is out of range. Include all case columns.
• Live driver is still a blue 10%. Then the table is decoration. EV will not move when the code changes.
• Shocking only the income statement. WACC and g must be live too, or the DCF is a partial scenario.
• Treating three one-way tables as the downside case. That is a tornado, not a scenario.
• Gordon with WACC ≤ g in the upside. If an upside WACC of 8% is paired with a g of 8% or more, TV explodes or divides by zero. Cap g below WACC in every case, or the toggle itself is a bomb.
• Forgetting tax, D&A, capex, or ΔNWC in a full case. The Apex simplification (FCFF = EBIT × (1 − t)) is a teaching cut. The case will not be.