Sensitivity Analysis for Key Drivers

What sensitivity analysis is (and what it is not)

Sensitivity analysis measures how much a model output changes when one input (a “driver”) changes, while other inputs are held constant. In analyst work, it answers questions like: “If price drops by 2%, what happens to EBITDA?” or “How sensitive is NPV to churn?” The goal is not to predict the future; it is to identify which drivers matter most, where risk concentrates, and which assumptions deserve the most scrutiny.

It differs from scenario testing in an important way: scenario testing changes a coherent set of assumptions together (a story), while sensitivity analysis isolates the effect of one driver at a time (or two drivers in a grid). Sensitivity analysis is also different from goal seek/optimization: you are not solving for an input that hits a target; you are mapping the response of outputs across a range of plausible inputs.

When sensitivity analysis is most useful

• Prioritizing diligence: focus validation efforts on the few drivers that move results the most.
• Communicating risk: show stakeholders how outcomes vary under reasonable ranges.
• Testing model behavior: detect non-linearities, discontinuities, or logic errors (e.g., outputs that move the wrong direction).
• Setting guardrails: define thresholds where decisions change (break-even points, covenant headroom, minimum viable volume).

Choosing key drivers and defining ranges

A sensitivity is only as good as the driver selection and the input range. Start by listing candidate drivers that are both uncertain and impactful. Typical categories include:

• Revenue drivers: price, volume, conversion rate, churn, retention, ARPU, mix.
• Cost drivers: COGS %, labor rate, utilization, marketing CAC, logistics cost per unit.
• Timing drivers: ramp-up months, payment terms, project start date.
• Financial drivers: discount rate, tax rate, working capital days, interest rate.

Then define a range for each driver. Use ranges that are plausible and decision-relevant, not extreme for drama. Common approaches:

• Historical variation: e.g., last 12–24 months volatility.
• Management tolerance bands: e.g., ±5% price, ±10% volume.
• Market benchmarks: competitor pricing, industry churn ranges.
• Model breakpoints: include values around thresholds (capacity limits, tiered pricing, minimum order quantities).

Pick step sizes that balance resolution and readability. For a single-driver table, 7–15 points often works well (e.g., -10% to +10% in 2% steps). For two-driver grids, keep each axis to 5–9 points to avoid an unreadable block.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

Audit-friendly range design

Make the range explicit and easy to trace. Put the range values in cells (not embedded in formulas), and label units clearly (%, currency, days). If a driver is a percentage, store it as a true percentage (0.05) and format as percent, rather than storing “5” and dividing by 100 inside formulas.

Single-variable sensitivity with a Data Table (step-by-step)

Excel’s Data Table feature is a fast way to compute an output across many input values. It recalculates the model for each value and records the resulting output(s). Used carefully, it can be audit-friendly and stable.

Example setup

Assume you have a model output cell for NPV in G10. You want to test sensitivity to Price Change % stored in input cell C5. You want to evaluate price change from -10% to +10%.

Step-by-step: one-variable Data Table

• 1) Create a column of test values. In a blank area, list values in a single column. Example: in J5:J15 enter -10%, -8%, …, 10%.
• 2) Link the output at the top of the table. In K4 (one cell above and one cell to the right of the first test value), enter a direct link to the output: =G10.
• 3) Select the full table range. Select J4:K15 (includes the output link cell and the column of test values).
• 4) Create the Data Table. Go to Data → What-If Analysis → Data Table. For a column of inputs, set Column input cell to the driver input cell $C$5. Leave Row input cell blank. Click OK.
• 5) Format and label. Label J4 as “Price Change %” and K4 as “NPV”. Format K5:K15 as currency, and J5:J15 as percent.

Excel will populate K5:K15 with the NPV for each price change value. This gives you a clear mapping from driver to output.

Common pitfalls with Data Tables

• Volatility and performance: Data Tables can slow large models. Consider setting calculation to Automatic except Data Tables (Formulas → Calculation Options) while building, then switch back when needed.
• Hidden dependencies: If the output depends on multiple linked inputs, ensure the driver cell you specify is the only thing changing in the table; otherwise you may misinterpret results.
• Non-deterministic outputs: If the model uses volatile functions (e.g., RAND) the table will be noisy. Replace randomness with fixed seeds or remove it for sensitivity runs.

Two-variable sensitivity grid with a Data Table (step-by-step)

Two-variable sensitivities are useful when outputs depend on interactions, such as price and volume, churn and CAC, or discount rate and terminal multiple. A grid shows how the output changes across combinations.

Example setup

Output: =G10 (NPV). Driver 1: Price Change % in C5. Driver 2: Volume Change % in C6.

Step-by-step: two-variable Data Table

• 1) Create row and column headers of test values. Put price change values across the top row, e.g., K4:Q4 as -10%, -5%, 0%, 5%, 10%. Put volume change values down the first column, e.g., J5:J11 as -10%, -5%, 0%, 5%, 10%.
• 2) Link the output in the top-left corner. In J4 enter =G10.
• 3) Select the full grid. Select J4:Q11.
• 4) Create the Data Table. Data → What-If Analysis → Data Table. Set Row input cell to the input corresponding to the top row driver (Price Change %: $C$5). Set Column input cell to the input corresponding to the first column driver (Volume Change %: $C$6). Click OK.
• 5) Apply conditional formatting. Use a 3-color scale to highlight low-to-high NPV. This makes interaction patterns visible quickly.

Interpretation: if the grid shows steep changes along one axis and mild changes along the other, the steep axis driver is more influential. If the grid shows curvature or diagonal patterns, the drivers interact (non-additive effects).

Measuring and ranking sensitivity (elasticities and deltas)

Tables are visual, but analysts often need a numeric ranking: “Which driver matters most?” Two common measures are delta sensitivity and elasticity.

Delta sensitivity (absolute change)

Delta sensitivity measures how much the output changes when the driver changes by a fixed amount.

Example: “NPV changes by $1.2m when price changes by +1%.” This is intuitive when the driver step is meaningful and consistent across drivers (e.g., all tested at ±1%).

Elasticity (percentage response)

Elasticity normalizes the effect and is useful when drivers have different units.

Elasticity formula conceptually:

Elasticity = (% change in Output) / (% change in Driver)

Example: if a 1% increase in price increases NPV by 2%, elasticity is 2.0. Elasticities help compare drivers like churn (%) versus discount rate (%) versus conversion rate (%).

Practical Excel approach to compute a local sensitivity

Use a small “+step / -step” approach around the base case. For each driver:

• Define a step size (e.g., 0.5% for price, 10 bps for discount rate).
• Calculate output at base, base+step, base-step.
• Approximate slope with a central difference:

Slope ≈ (Output(base+step) - Output(base-step)) / (2*step)

This gives a local sensitivity around the base case, which is often what decision-makers care about.

Building a sensitivity “dashboard” that stays audit-friendly

The challenge is presenting sensitivities without turning the workbook into a fragile web of manual overrides. A robust approach is to separate three elements: (1) driver list and ranges, (2) outputs to track, (3) calculation engine (Data Tables or controlled formulas).

Recommended layout

• Driver block: a table with Driver Name, Input Cell Reference (documented), Base Value, Low, High, Step.
• Output block: a list of outputs (e.g., Revenue, EBITDA, Cash, NPV) with direct cell links.
• Sensitivity tables: one-variable tables for each key driver-output pair, or one driver with multiple outputs.

Keep labels explicit: include units, base case value, and the tested range. If you share the workbook, someone should be able to understand what changed and what was measured without reading formulas.

One driver, multiple outputs in a single Data Table

You can place multiple output links across the top row and run a single Data Table to fill all outputs for each driver value.

Example: driver values in J5:J15. Put output links in K4, L4, M4 such as =G10 (NPV), =G12 (IRR), =G14 (EBITDA). Select J4:M15 and create a one-variable Data Table using $C$5 as the Column input cell. Excel will populate each output column for each driver value.

Interpreting results: patterns that signal model behavior

Sensitivity outputs are not just for ranking drivers; they are also a diagnostic tool. Watch for these patterns:

• Non-monotonic response: output goes up then down as the driver increases. This can be real (e.g., price increases reduce volume) or a sign of a logic issue. Verify the underlying relationships.
• Flat sections: output doesn’t change across a range. This may indicate the driver is not actually connected to the output, or the effect is capped/floored (e.g., minimum margin, capacity constraint).
• Jumps/discontinuities: sudden step changes can come from tiered pricing, tax brackets, bonus thresholds, or IF logic. Ensure these discontinuities are intended and documented.
• Asymmetry: downside sensitivity is larger than upside (or vice versa). This is common with constraints (e.g., you can’t sell below zero, but costs can rise without bound) and is important for risk communication.

Practical visualization: tornado charts (without fragile tricks)

A tornado chart summarizes the impact of multiple drivers on a single output by showing the output at “low” and “high” values for each driver, sorted by impact magnitude. It is one of the clearest ways to communicate “what matters most.”

Step-by-step: compute tornado inputs

For each driver, you need two outputs: Output at Low and Output at High (with all else at base). You can compute these using small two-point Data Tables per driver, or by running a one-variable table and picking the endpoints.

• 1) Create a tornado input table. Columns: Driver, Low Value, High Value, Output@Low, Output@High.
• 2) Fill Output@Low and Output@High. Use Data Tables or controlled recalculation to compute the output for each driver at its low/high.
• 3) Convert to bar components. Create two helper columns: Min = MIN(Output@Low, Output@High) and Max = MAX(Output@Low, Output@High). Then Span = Max - Min.
• 4) Sort by Span descending. Largest impact at top.
• 5) Build a stacked bar chart. Plot Min as the invisible base (no fill) and Span as the visible bar. This creates bars that start at Min and extend to Max.

Label the base case output separately (e.g., a vertical line) if you want to show where the base sits relative to low/high outcomes.

Handling correlated drivers and avoiding misleading sensitivities

Single-driver sensitivity assumes other drivers are fixed. In reality, drivers can be correlated: price changes may affect volume; marketing spend may affect conversion; churn may affect support costs. If you ignore correlation, you can overstate or understate risk.

Practical ways to address correlation

• Use paired sensitivities: run a two-variable grid for the most tightly linked drivers (e.g., price and volume) to show interaction explicitly.
• Use “linked driver” rules: if your model includes a relationship (e.g., volume = base volume * (1 + elasticity * price change)), then a price sensitivity will automatically incorporate volume response. In that case, be clear that you are testing “price including demand response,” not “price holding volume constant.”
• Document what is held constant: label sensitivities as “ceteris paribus” and specify exceptions (e.g., “price change includes mix shift”).

Step-by-step: sensitivity to a driver that is not a single cell

Sometimes a “driver” is a set of inputs (e.g., a whole cost curve by year) or a parameter embedded in multiple places. Sensitivity analysis is easiest when the driver is controlled by a single input cell. If it isn’t, create a single control parameter that scales or shifts the relevant series.

Example: scaling a multi-year cost series

Suppose operating costs are a year-by-year row, and you want to test “Cost Inflation %” as a driver. Create a single input cell C7 for Cost Inflation % and apply it consistently in the cost calculation, e.g., multiply the base cost series by (1 + $C$7) (or compound by year if appropriate). Then run a Data Table on C7. This keeps the sensitivity driver explicit and avoids hunting through multiple cells.

Quality checks for sensitivity outputs

Before sharing sensitivity results, run quick checks to ensure they are credible and reproducible:

• Direction check: does the output move in the expected direction when the driver increases?
• Base case match: does the table include the base input value, and does it reproduce the base output exactly?
• Range sanity: do tested values stay within realistic bounds (no negative churn, no impossible margins)?
• Units check: confirm percent vs basis points vs decimals; confirm currency scaling (thousands vs millions).
• Stability check: recalc twice; outputs should not change unless the model is intentionally stochastic.

Worked example: from sensitivity table to decision threshold

Assume you are evaluating a product launch and tracking a single output: Annual Contribution in H20. Key driver: Conversion Rate in C8. You want to know the conversion rate required to hit a minimum contribution of $500k.

Build a one-variable Data Table with conversion rate values from 0.5% to 3.0% in 0.25% steps, and record Annual Contribution. Then add a helper column to flag whether the threshold is met:

=IF(Contribution>=500000, "Meets", "Below")

Even without solving, you can visually identify the smallest conversion rate where the flag switches to “Meets.” This is a practical way to translate sensitivity into a decision-relevant breakpoint, and it often reveals whether the required performance is realistic.