Sensitivity tables are not Excel decorations. They are decision tools. Built correctly, they show where value truly comes from. Built poorly, they create false confidence and break under review. This tutorial explains how to design sensitivity tables that are structurally sound, economically meaningful, and defensible.
This is not about formatting.
It is about model logic, variable selection, and interpretation discipline.
Why most sensitivity tables are misleading
In practice, sensitivity tables often fail for three reasons:
- the wrong variables are stressed,
- the stressed ranges are unrealistic,
- or the table is disconnected from the valuation logic.
The result looks sophisticated, but answers the wrong question.
Professional standard: A sensitivity table should reduce uncertainty — not multiply it.
Step 1: Start with the decision, not the variable
Before opening Excel, clarify one point:
What decision is this sensitivity meant to inform?
Examples:
- Is the deal robust to changes in operating performance?
- Is valuation driven more by assumptions or by the exit multiple?
- Where does downside risk actually originate?
Only after this is clear should variables be selected.
Step 2: Choose variables that are economically independent
A common modeling error is stressing variables that are mechanically linked.
Poor examples
- Revenue growth and EBITDA margin when margin is revenue-driven
- WACC and discount rate adjustments elsewhere in the model
Better practice
- One operating driver + one valuation driver
- One cash flow driver + one exit assumption
Sensitivity tables should isolate uncertainty, not double-count it.
Step 3: Define realistic ranges (this is where credibility is won)
Ranges should reflect:
- historical volatility,
- industry context,
- and transaction specifics.
Avoid:
- arbitrary ±5 % ranges,
- round numbers without justification,
- extreme scenarios disguised as “base sensitivities”.
Rule of thumb: If you cannot verbally defend a range, it does not belong in the table.
Step 4: Anchor sensitivities to a clean base case
Every sensitivity table must clearly reference:
- a single, well-defined base case,
- unchanged logic elsewhere in the model.
If base assumptions shift silently when the table recalculates, the output becomes unreliable.
Professional reviewers will always ask:
“What exactly is held constant here?”
Your model must already answer that.
Step 5: Build the table so Excel cannot break it
From a technical standpoint:
- link sensitivities to final outputs only (e.g. equity value per share),
- avoid intermediate calculation cells,
- lock formulas where possible.
The Data Table tool is powerful, but unforgiving.
A single volatile reference can invalidate the entire table.
Step 6: Interpret, do not just present
A sensitivity table without interpretation is unfinished work.
A proper takeaway answers:
- which variable dominates value,
- where risk is asymmetric,
- and which assumptions deserve the most scrutiny.
This is where modeling turns into analysis.
In practice: what experienced reviewers actually look for
When reviewing sensitivity tables, professionals check:
- variable selection logic,
- consistency with the valuation framework,
- and whether the conclusions match the numbers.
Formatting rarely matters. Structure always does.
Primary takeaway
Sensitivity tables are not about showing variability.
They are about revealing what truly drives outcomes.
When built correctly, they:
- strengthen decision-making,
- survive scrutiny,
- and elevate the credibility of the entire model.
FAQ
What is the purpose of a sensitivity table in financial modeling?
To show how changes in key assumptions affect valuation outcomes while holding the underlying model logic constant.
Which variables should be used in sensitivity tables?
Only economically independent variables that directly influence value and decision-making.
Why do many sensitivity tables fail in practice?
Because they stress the wrong variables, use unrealistic ranges, or are disconnected from the valuation framework.
How many variables should a sensitivity table include?
Typically one or two. More reduces clarity and interpretability.
Do you have an inquiry? Schedule a free initial consultation