Sensitivity analysis is the difference between a financial model that informs decisions and a financial model that just exists. The pattern is: vary one or two inputs across a plausible range, show the output, and let the reader see which inputs matter. The four patterns below are the ones we ship on every senior financial model.
The four patterns
One-input Data Table
How does NPV change as growth rate varies from 0% to 20%?
Column A: growth rate (0%, 2%, 4%, ..., 20%). Column B: =TABLE(, B1) where B1 is the input cell.
The one-input Data Table fills Column B with NPV for each growth rate. The senior uses this pattern for tornado charts and single-variable sensitivity.
Two-input Data Table
How does IRR change as growth rate and discount rate vary?
Top row: discount rate (8%, 10%, 12%, 14%, 16%). Left column: growth rate (0%, 5%, 10%, 15%, 20%). Corner cell: IRR formula. Rest: =TABLE(input_cell_1, input_cell_2).
The two-input Data Table fills the grid with IRR for each combination. The senior uses this pattern for break-even matrices and pricing sensitivity.
Tornado chart
Which input has the largest impact on NPV?
One bar per input. Bar length = range of NPV when input varies across its plausible range. Sorted by absolute impact.
The tornado chart is the single most useful communication tool in a financial model. The senior puts the tornado chart on the dashboard and walks the executive team through it in 5 minutes.
Scenario Manager
What does the model look like in Base, Best, and Worst case?
Data → What-If Analysis → Scenario Manager. Define each scenario with a name and a set of input values. Show the scenario with a button click.
Scenario Manager is for discrete scenarios (Base, Best, Worst). Data Tables are for continuous sensitivity. The senior uses both.
The four mistakes to avoid
Forgetting to enter as array formula
In Excel 365 the array entry is automatic. In older versions, the Data Table formula must be entered with Ctrl+Shift+Enter. Without array entry, only the top-left cell of the Data Table returns a value. The fix: enter as array, or upgrade to Excel 365.
Pointing the input cell at the wrong cell
The Data Table formula references the input cell. If the input cell is not the one being varied, the table shows the same value across all rows. The fix: double-check the input cell reference before entering the formula. The senior uses a named cell for the input (e.g. growth_rate) to make the reference obvious.
Trying to vary three inputs in a single Data Table
A Data Table can vary one or two inputs. Three or more inputs requires multiple Data Tables or a tornado chart. The fix: build separate Data Tables for the most important input pairs. The senior uses a tornado chart to identify which inputs matter most before building the tables.
Building the Data Table on the dashboard tab
The Data Table is a working tab. The output goes to the dashboard tab. The fix: keep the Data Table on a hidden tab (or a tab labelled "Sensitivity"), and reference the cells in the dashboard.
From Data Table to tornado chart
The Data Table is the calculation; the tornado chart is the communication. The senior builds one one-input Data Table per key input, reads the impact range from the table, and plots the tornado chart. The chart is the single most useful communication tool in the model - the executive team can see which inputs matter in five seconds, and the discussion moves straight to action.
The pattern is: identify the top 5-7 inputs that drive the output → build a one-input Data Table for each → read the range (max - min) for each → plot the ranges as horizontal bars sorted by length. The senior puts this chart on the dashboard tab and references it in the board pack.
