Sensitivity — ModelxcelPro docs

What it does

For a selected formula output cell, Sensitivity finds the numeric input cells that feed it, perturbs each across a set of percentages, measures how the output moves, and ranks the inputs by impact in a tornado chart. It runs on a background thread behind a progress dialog and goes through five stages:

Pre-flight. The active cell must contain a formula and evaluate to a number — a text/error/blank result, or a hardcoded constant, is rejected up front.
Metric detection. The selected cell is the primary output; the engine also auto-detects additional output metrics from single-cell named ranges matching finance tokens (IRR, NPV, DSCR, LLCR, …) at high confidence, so one run can rank drivers against several outputs at once.
Topology build. A forward-precedent index of the workbook’s formulas is built (parallelized on large models). External-workbook references are skipped.
Driver discovery. A breadth-first walk collects every leaf (non-formula) cell feeding the metric(s) and classifies each as Numeric / Date / Boolean / Text / Empty. Only numeric, non-zero constants are perturbable.
Ranking + perturbation. A quick pre-scan ranks candidates, then the top drivers are perturbed in full and the results assembled.

How the perturbation works — and the safety story

It temporarily changes your live workbook, then restores it

Sensitivity does not work on a copy. For each driver it writes a perturbed value into the real cell (Range.Value2), recalculates, reads the output, then writes the original value back; after the loop it runs a final recalculation to return the workbook to its starting state. The whole run happens with screen updating and events suppressed, so you don’t see the flicker — and because events are off, these writes don’t enter Excel’s undo stack (none are needed, since every value is restored programmatically). Cancel also restores all originals and recalculates.

Practically: don’t edit the workbook while a run is in progress, and expect a recalculation. In the rare event Excel is interrupted mid-run (e.g. a crash), a driver could be left at a perturbed value — re-run, or restore that input.

By default each driver is tested at −10%, −5%, −1%, +1%, +5%, +10%. Output change is measured as a percentage of the original output. For speed, the engine uses a scoped recalculation when the output’s entire precedent chain is on one sheet, and a full (dirty-only) recalculation otherwise.

What you see

A progress dialog (with phase text, the driver being processed, an ETA, and a Cancel button) runs first. If no perturbable numeric inputs are found, an info dialog says so and no results open. Otherwise the results window shows:

An output header (“Output: Sheet!Cell = value”) and a line summarizing how many inputs had measurable impact.
A filter toolbar: a Metric dropdown (when more than one output was detected), Hide zero-impact (on by default), Hide outliers, and a Show Top 10/20/50/All selector.
A tornado chart: horizontal bars centered on a 0% axis — negative impact extends left (red), positive right (blue), widest = most influential. Click a bar to jump to that input cell.
A detailed results grid: one row per driver with the original value, the output change at each percentage, and max impact, color-coded by magnitude. Double-click a row to jump to the input.
A diagnostics line (formulas scanned, drivers, skipped, metrics, recalc strategy, timings).

Output: Copy Data (clipboard, not a file)

The Copy Data button copies the full results as tab-delimited text to the clipboard (paste into a sheet or document); the button briefly reads “Copied!”. Sensitivity does not create a file or add a worksheet. Clicking a bar or double-clicking a grid row navigates Excel to the input cell.

Workflow

Select the output formula cell (it must evaluate to a number).
Press Ctrl+Shift+S (or click Sensitivity). Wait through the progress dialog — large models can take from seconds to a couple of minutes.
Read the tornado chart: the widest bars are the highest-leverage inputs.
If several metrics were detected, switch the Metric dropdown to rank drivers against each output.
Use Hide zero-impact / Hide outliers / Top-N to focus the view; click bars or rows to jump to inputs.
Copy Data to take the table elsewhere; Close — the workbook is fully restored.

Limitations

Formula output required: a hardcoded constant or a text/error result is rejected.
Numeric, non-zero drivers only: date, boolean, text, empty, and currently-zero inputs are not perturbed (a zero input has no percentage response).
Active workbook only: inputs that live in externally linked workbooks are skipped — cross-workbook models are covered incompletely.
Up to 100 drivers in the interactive run; a pre-scan selects the most leveraged from a wider pool, so the biggest movers should still appear.
Volatile/iterative formulas (RAND, NOW, OFFSET, INDIRECT, iterative calc) recalculate on every pass and can add noise to the measured impact; very large models affect speed.

Tracing — see which inputs feed an output before you rank them.
Replay Proof — after the fact, explain and sign a specific output movement.