Sensitivity analysis is a “what‑if” technique that shows how the output (dependent variable) of a model responds to changes in one or more input (independent) variables, holding other assumptions constant. It helps decision‑makers see which inputs drive results most, quantify upside and downside, and prioritize where to focus research, mitigation, or management attention.
Source: Investopedia (Lara Antal) —
Key takeaways
– Sensitivity analysis reveals how sensitive model outputs are to changes in inputs.
– It is widely used in finance (e.g., net present value), operations, engineering, and policy analysis.
– Common formats: one‑way (single variable), two‑way (two variables), and tornado charts for ranking impact.
– Tools: spreadsheets (Excel What‑If/Data Table), dedicated add‑ins (Monte Carlo simulators), or programming (Python/R).
– Limitations: results depend on model structure and historical inputs (“garbage in, garbage out”); too many variables can obscure insights.
How sensitivity analysis works (conceptual)
1. Build a base model: choose a set of inputs and compute the base output (e.g., sales, NPV, EPS).
2. Select key input(s): identify variables you suspect are important (price, volume growth, discount rate, cost of goods, etc.).
3. Define variation ranges: choose reasonable low/high values or percentage changes for each input.
4. Recompute outputs: change one input at a time (or two at a time) and record resulting outputs.
5. Summarize and visualize: display results in tables, charts, or a tornado diagram to show which inputs cause the largest changes.
Practical example (simple sales model)
Base case:
– Price per unit = $1,000
– Transactions last year = 100
– Base sales = $1,000 × 100 = $100,000
Assumption: a 10% increase in customer traffic increases transactions by 5%.
One‑way sensitivity scenarios:
– If customer traffic +10% → transactions +5% → transactions = 105 → sales = $105,000
– If customer traffic +50% → transactions +25% → transactions = 125 → sales = $125,000
– If customer traffic +100% → transactions +50% → transactions = 150 → sales = $150,000
This demonstrates sales’ sensitivity to customer traffic; you can do the same varying price, conversion rate, or average transaction value.
Sensitivity analysis in NPV
In capital budgeting, sensitivity analysis measures how NPV changes when you vary inputs such as:
– Discount rate (cost of capital)
– Project cash inflows (revenue growth, volume/price)
– Operating expenses or capital expenditure (CapEx)
– Project life or residual value
NPV formula (reminder): NPV = Σ [CFt / (1 + r)^t] where CFt = cash flow at time t and r = discount rate. To test sensitivity to r, compute NPV with r = base, then recompute with alternative discount rates (e.g., 5%, 8%, 10%) while keeping cash flows constant.
How businesses use sensitivity analysis
– Prioritize risk: find which inputs most affect outcomes and focus controls or hedges there.
– Inform strategy: evaluate how much performance must change for a project to be viable (breakeven sensitivity).
– Support decisions: present ranges of outcomes to management rather than a single point estimate.
– Improve models: detect weaknesses or incorrect assumptions in base forecasts.
– Communicate: produce easy‑to‑interpret visuals (tables, charts, tornado diagrams) showing upside/downside.
Fast fact
Sensitivity analysis is often called “what‑if” analysis because it answers questions such as “What if sales drop 20%?” or “What if the discount rate rises 2 percentage points?”
Advantages and disadvantages
Advantages
– Reveals which inputs matter most to outcomes.
– Helps prioritize data collection and risk mitigation.
– Easy to implement for small models (spreadsheets).
– Improves transparency when presenting management options.
Disadvantages
– Still dependent on the accuracy of the base model and input ranges.
– Can be misleading if variables are correlated (varying one at a time may be unrealistic).
– Models with many variables can become hard to interpret.
– Results are estimates—historical-based assumptions may not hold in the future.
How businesses calculate sensitivity analysis — step‑by‑step practical guide
Below is a practical workflow you can follow in Excel or another modeling tool.
Preparation
1. Build your base financial model with clearly defined input cells (label them).
2. Save a copy of the base case values and results.
One‑way sensitivity (single variable)
1. Choose the input cell to test (e.g., volume, price, discount rate).
2. Define the range of values to test (absolute values or % changes, e.g., −20%, −10%, 0%, +10%, +20%).
3. Create a results table: list test values in one column and a formula cell that references your output (NPV or profit).
4. Compute outputs for each input value. In Excel this can be automated with Data → What‑If Analysis → Data Table (one variable).
5. Chart outcomes (line chart or bar chart) to show sensitivity.
Two‑way sensitivity (two variables)
1. Make a grid of values (variable A across top, variable B down side).
2. Use Excel’s two‑variable Data Table to fill the grid with output values (Data → What‑If Analysis → Data Table).
3. Use surface/heat map or contour chart to visualize combined impacts.
Ranking inputs (Tornado chart)
1. For each input, calculate the base output and outputs at low/high variations.
2. Compute the absolute change from base for each input.
3. Sort inputs by impact size (largest to smallest).
4. Plot horizontal bars (Tornado chart) — this highlights the most influential inputs.
Advanced techniques
– Monte Carlo simulation: assign probability distributions to inputs and simulate thousands of runs to produce a probability distribution of outputs (useful when inputs are uncertain and correlated). Tools: @RISK, Crystal Ball, Python (numpy, pandas), R (mc2d, tidyverse).
– Scenario analysis: group multiple inputs into plausible combined states (best case, base case, worst case) — different from sensitivity analysis because scenarios change multiple inputs together based on an overarching storyline.
Difference between sensitivity analysis and scenario analysis
– Sensitivity analysis: vary one (or two) inputs while holding others constant to measure marginal impact. Best for identifying which levers matter most.
– Scenario analysis: define coherent, realistic combinations of inputs that represent specific future states (e.g., recession, regulation change). Best for exploring plausible end‑to‑end outcomes and their implications.
Common pitfalls and how to avoid them
– Ignoring correlations: if price and volume move together, one‑way tests can misstate risk. Use two‑way sensitivity or Monte Carlo to capture joint movements.
– Too many inputs: limit the analysis to a manageable set of high‑impact variables; use a tornado chart to guide selection.
– Unrealistic ranges: choose ranges based on empirical data, industry benchmarks, or management guidance.
– Overreliance on point estimates: present ranges or probability distributions, not just a single “expected” result.
Practical Excel tips (quick how‑to)
– One‑variable Data Table: put test values in a column, set the formula for the output in the cell above the first test value, then use Data → What‑If Analysis → Data Table and set the Column Input Cell to the model input you’re testing.
– Two‑variable Data Table: create a matrix with variable A values in the top row and variable B values in the left column; put the output formula in the upper‑left cell of the matrix and use Data Table with Row/Column Input Cells.
– Tornado chart: calculate outcome at low/high for each input, compute differences, sort by difference, then create horizontal bar chart.
When to use sensitivity analysis vs. Monte Carlo vs. scenario analysis
– Use sensitivity when you want to know which single inputs move outcomes the most.
– Use scenario analysis when you want to stress test coherent, realistic storylines (e.g., regulatory shock).
– Use Monte Carlo simulation when inputs are uncertain with known or estimated distributions and you need probabilistic outcomes.
The bottom line
Sensitivity analysis is a practical, widely used tool that helps managers and analysts understand which inputs drive model outcomes and how much those outcomes change under different assumptions. It is most useful when models are transparent, inputs and ranges are realistic, and results are communicated as ranges or prioritized risk levers rather than single point estimates. Combine sensitivity analysis with scenario planning and probabilistic tools (Monte Carlo) as needed to get a fuller picture of risk and opportunity.
Primary source and further reading
– Investopedia — Sensitivity Analysis (Lara Antal)
Editor’s note: The following topics are reserved for upcoming updates and will be expanded with detailed examples and datasets.