Sensitivity Analysis
Executive Summary
Key Takeaways
- ✓ Sensitivity analysis measures the change in a model output in response to a defined change in a single variable, holding all others constant.
- ✓ It identifies which assumptions have the greatest impact on key outputs.
- ✓ One-way tables test a single variable; two-way tables test the interaction of two variables.
- ✓ Excel Data Tables are the standard implementation tool.
- ✓ Auditors should verify that sensitivity tables are formula-driven, connected to the correct input cells, and consistent with any presented outputs.
- ✓ Sensitivity analysis is distinct from scenario analysis, which changes multiple assumptions simultaneously.
Definition¶
Sensitivity analysis is the quantitative assessment of how much a financial model's output changes when a single input variable is changed by a defined amount, while all other variables are held at their base case values. It measures the responsiveness — or sensitivity — of outputs to individual assumption changes.
Sensitivity analysis is distinct from scenario analysis, which changes multiple assumptions simultaneously to reflect a coherent alternative state. Sensitivity analysis isolates the effect of individual variables; scenario analysis tests the combined effect of assumption sets.
Why It Matters¶
Every financial model rests on assumptions that are uncertain. Sensitivity analysis identifies which assumptions have the greatest impact on the model's key outputs, which allows decision-makers and auditors to:
- Focus their scrutiny on the assumptions that matter most
- Understand the range of possible outcomes driven by assumption uncertainty
- Identify the assumptions that must be robustly justified versus those that have limited impact on the outcome
- Test whether the investment or transaction remains viable across a range of plausible input values
In a model audit context, sensitivity analysis serves two additional purposes:
First, it is itself a subject of audit. Investment memoranda and lender presentations frequently include sensitivity tables. The auditor must verify that these tables are derived from the financial model and are internally consistent with the model's outputs.
Second, it is a tool for the auditor. By running sensitivity analysis on a model being reviewed, an auditor can identify which assumptions are most likely to produce material errors if incorrectly modelled — and can prioritise their detailed formula review accordingly.
Technical Background¶
One-Way Sensitivity Analysis¶
A one-way sensitivity analysis changes a single variable across a defined range and records the output at each value. For example:
| Revenue Growth Rate | Equity IRR |
|---|---|
| 0% | 11.2% |
| 1% | 12.4% |
| 2% (Base) | 13.8% |
| 3% | 15.3% |
| 4% | 17.1% |
One-way sensitivity analysis is typically implemented using an Excel data table (one-variable version). This is the most common format for presenting sensitivity results in investment models.
Two-Way Sensitivity Analysis¶
A two-way sensitivity analysis changes two variables simultaneously across a defined range and presents the output as a grid. For example:
| | Revenue Growth Rate | |---|---|---|---|---|---| | Capex Overrun | 0% | 1% | 2% (Base) | 3% | 4% | | 0% | 12.1% | 13.3% | 14.8% | 16.4% | 18.3% | | 5% | 11.6% | 12.8% | 14.2% | 15.8% | 17.6% | | 10% (Base) | 11.2% | 12.4% | 13.8% | 15.3% | 17.1% | | 15% | 10.7% | 11.9% | 13.3% | 14.9% | 16.6% | | 20% | 10.3% | 11.4% | 12.8% | 14.4% | 16.1% |
Two-way sensitivity analysis is implemented using an Excel data table (two-variable version). The two-way table is the standard format for key sensitivity presentations in institutional investment and project finance models.
Implementing Sensitivity Analysis: Excel Data Table¶
Excel's Data Table function (Data → What-If Analysis → Data Table) calculates a model output across a range of input values automatically, without requiring the user to manually change the input and record each result.
One-variable data table: 1. Enter the range of input values in a column (or row) 2. Enter a reference to the output cell in the row above the column of inputs (or column to the left of the row of inputs) 3. Select the entire range (including inputs and the output reference) 4. Data → What-If Analysis → Data Table 5. In the dialog, enter the input cell in "Column input cell" (or "Row input cell")
Two-variable data table: 1. Enter one set of input values in a column and the other in a row 2. Place the output cell reference at the intersection of the column and row headers 3. Select the entire table 4. Data → What-If Analysis → Data Table 5. Enter one input in "Row input cell" and the other in "Column input cell"
Data tables recalculate automatically when the model recalculates.
Tornado Chart¶
A tornado chart (or tornado diagram) presents one-way sensitivity results visually, ranking assumptions from most to least impactful. Each assumption is shown as a horizontal bar whose width represents the range of output values produced by changing that assumption across its sensitivity range.
The chart is named for its shape: the most impactful assumptions (widest bars) appear at the top, narrowing toward the least impactful at the bottom — resembling a tornado.
Tornado charts are a standard presentation tool for investment committees because they communicate, at a glance, which assumptions are most important to the outcome.
Sensitivity Range Selection¶
A sensitivity analysis is only meaningful if the range of values tested for each variable is realistic. Common approaches include:
- Percentage range: Test the variable at ±X% from the base case (e.g. ±10%, ±20%)
- Standard deviation: Test across a range based on the historical volatility of the variable
- Contractual range: For contractual variables (offtake price, fuel cost), test across the range permitted under the relevant agreement
- Lender-defined range: For project finance, lenders may specify the sensitivity ranges to be tested in the credit analysis
The choice of sensitivity range affects the apparent significance of each assumption. A small range will show all assumptions as having small impacts; an excessively wide range will exaggerate the impact of every assumption.
Sensitivity vs Materiality¶
Sensitivity analysis is a useful tool for identifying material assumptions — those to which key outputs are most sensitive. However, high sensitivity does not automatically make an assumption material if the assumption is highly constrained or well-supported by evidence. An assumption that cannot realistically deviate from the base case value (because it is fixed by contract, for example) may show high sensitivity in the analysis but carry low actual risk.
Audit Considerations¶
1. Verify Sensitivity Table Sources¶
Confirm that sensitivity tables in the model are actually driven by the financial model — that they use Excel Data Tables or other formula-driven mechanisms rather than manually entered numbers. A sensitivity table with manually entered values may not accurately reflect the model's actual response to assumption changes.
2. Check Data Table Input Cell References¶
Verify that the "Column input cell" (or "Row input cell") in each Data Table is correctly referencing the intended assumption cell. A Data Table connected to the wrong input cell will produce results that do not represent the sensitivity of the output to the stated assumption.
3. Consistency with Presentation Materials¶
Verify that sensitivity outputs presented in investment memoranda or lender presentations match the values in the model. A discrepancy — even a small one — indicates either that the model has been updated since the presentation was prepared, or that the presentation values were not taken directly from the model.
4. Sensitivity Range Appropriateness¶
Assess whether the sensitivity ranges tested are realistic for the specific project. Ranges that are too narrow may understate risk; ranges that are too wide may create alarm about unlikely outcomes.
5. Key Variable Coverage¶
Confirm that sensitivity analysis covers the key risk variables for the specific project type. For a demand-risk transport project, volume sensitivity is critical. For a PPP availability project, cost escalation and lifecycle cost sensitivity are critical. A sensitivity analysis that omits the most important risk variables for the transaction provides an incomplete picture.
6. Circular Reference Interaction¶
Data Tables can interact unpredictably with models containing circular references. If a model uses iterative calculation to resolve a circular reference, the Data Table may not correctly recalculate across all input values. Test the sensitivity outputs for reasonableness and confirm they change appropriately as inputs are varied.
Common Errors¶
| Error | Description | Risk |
|---|---|---|
| Hardcoded sensitivity table | Values in table entered manually rather than formula-driven | Table does not reflect model outputs |
| Wrong input cell | Data Table connected to wrong assumption cell | Sensitivity table tests the wrong variable |
| Presentation values differ from model | Sensitivity values stated in documentation do not match model | Misleading analysis presented to decision-makers |
| Incomplete variable coverage | Key risk assumptions not included in sensitivity analysis | Risk profile understated |
| Unrealistic ranges | Ranges too narrow or too wide for the specific asset | Analysis not informative for decision-making |
| Circular reference interference | Data Table recalculation fails due to iterative calculation conflict | Wrong sensitivity outputs |
Best Practices¶
Build sensitivity analysis into the model from the start rather than adding it as a final step. A model designed with a clean assumption input section makes sensitivity analysis straightforward; a model where assumptions are scattered through calculation sections makes it difficult.
Present a two-way sensitivity table for the two most important variables affecting the key output. For a project finance model, a two-way table of DSCR against revenue and cost is typically the most useful credit analysis tool.
Include a tornado chart in any investment or lender presentation. It communicates sensitivity results more effectively than a numerical table for non-technical readers.
Label every sensitivity table clearly: which output is being tested, what the base case value is, and what each row and column represents.
Continue Reading¶
Prerequisites¶
- What Makes an Excel Financial Model Reliable? — the parent pillar
Related Pillars¶
- What Makes an Excel Financial Model Reliable?
- Investment Analysis and Capital Budgeting — see the Risk Analysis in Investment Appraisal technical guide for sensitivity analysis applied specifically to NPV/IRR capital budgeting decisions
Related Glossary¶
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
What is the difference between sensitivity analysis and scenario analysis?
Sensitivity analysis changes one variable at a time, holding all others at base case values. It tests the model's response to individual assumption changes. Scenario analysis changes multiple assumptions simultaneously to reflect a coherent alternative state of the world. Both are required for a complete model analysis; they provide complementary information.
How many variables should be included in a sensitivity analysis?
There is no fixed number. Include all assumptions that are materially uncertain and that the model shows to be influential on key outputs. For most project finance models, five to ten variables is typical. A tornado chart helps rank variables by impact and identify which deserve detailed analysis.
Can sensitivity analysis be automated in Excel?
Yes. Excel's Data Table function automates the calculation of sensitivity results across a range of input values. For more complex sensitivity analysis across many variables and outputs, VBA macros or third-party tools can extend this capability.
Is sensitivity analysis required by lenders?
Requirements vary by lender, transaction type, and jurisdiction. In project finance, sensitivity analysis is a standard expectation in the information package provided to lenders. The specific variables to be tested and the ranges to be used are often specified by the lender's credit department or set out in the term sheet.
Related Articles
Scenario Analysis
Scenario analysis is the process of recalculating a financial model's outputs under a defined set of alternative assumptions that together represent a coherent possible future state. Each scenario changes multiple assumptions simultaneously to reflect a plausible economic environment or operational outcome — for example, a scenario in which both construction costs are higher than expected and revenue is lower than expected during the ramp-up phase. Scenario analysis is distinct from sensitivity analysis, which changes one variable at a time while holding all others constant. Scenario analysis tests the model under internally consistent combinations of assumptions; sensitivity analysis tests the model's response to changes in individual variables in isolation.
Data Table
In Excel, a data table is a range of cells that performs a series of what-if calculations by substituting a set of input values into one or two designated cells and recording the resulting output from a specified formula. Data tables are the standard mechanism for producing sensitivity matrices in financial models. A one-variable data table varies one input and shows the output for each value; a two-variable data table varies two inputs simultaneously. Data table results are stored as array formulas using the TABLE function and update automatically when the model recalculates, unless the workbook's calculation mode excludes data tables from automatic recalculation.
Base Case
The base case in a financial model is the central scenario that represents the model developer's primary projection of expected outcomes. It uses the most likely or central estimate for each assumption, rather than optimistic or pessimistic values. All other scenarios (upside, downside, stress) are defined in relation to the base case. The base case is the scenario used for investment decisions, credit approvals, and board presentations unless otherwise stated. Its key outputs — typically IRR, NPV, DSCR, and equity returns — are the primary reference metrics for any decision made in reliance on the model.
Downside Case
The downside case in a financial model is a scenario constructed using pessimistic but plausible assumptions to assess the model's projected performance under adverse conditions. It is defined relative to the base case: each assumption in the downside case is set at a level less favourable than the base case, representing conditions that could realistically occur but that the developer does not expect to be the most likely outcome. The downside case is used by lenders and investors to assess whether a project or investment can withstand a realistic adverse scenario while continuing to service debt and meet minimum covenant requirements.
Equity IRR
Equity IRR (Equity Internal Rate of Return) is the discount rate at which the net present value of all equity cash flows — comprising the initial equity investment as a negative cash flow and subsequent distributions and terminal proceeds as positive cash flows — equals zero. It measures the annualised return earned by equity investors on capital contributed to a project or transaction, calculated on post-debt-service cash flows only. Equity IRR is distinct from Project IRR, which is calculated on total project cash flows before financing. Equity IRR is always higher than Project IRR in a positively leveraged transaction because debt amplifies equity returns. It is lower than Project IRR when leverage is negative — that is, when the cost of debt exceeds the unlevered return of the project.
What Makes an Excel Financial Model Reliable?
An Excel financial model is a structured spreadsheet used to represent, calculate, and forecast the financial mechanics of a business, investment, or transaction. Reliability is not a function of how sophisticated a model looks; it is a function of its structure, discipline, and consistency. This page defines what an Excel financial model is, the structural characteristics that separate a reliable model from a fragile one, and the standards and terminology that underpin every other page in the FMAE Knowledge Centre that references a specific modelling concept. This is a crowded educational topic, and most existing content in this space is course marketing rather than a neutral reference. This page is written as the latter: a vendor neutral definition of reliable modelling practice, not a sales page for a training course.
Investment Analysis and Capital Budgeting
Investment analysis and capital budgeting is the discipline of deciding whether a project or investment is expected to create value, using a toolkit of quantitative techniques — net present value, internal rate of return, modified internal rate of return, payback period, and the profitability index — each applied to the same underlying forecast cash flow series but answering a subtly different question. This page is the hub for the Knowledge Centre's investment analysis content: what each technique measures, how the techniques relate to and sometimes conflict with one another, how discount rates and hurdle rates are set, how risk is layered onto the analysis through sensitivity, scenario, and Monte Carlo methods, and — distinctively — how capital-budgeting failure modes map onto FMAE's existing structural audit rule taxonomy.
Risk Analysis in Investment Appraisal
A single-point NPV or IRR calculation, built on one specific set of assumptions, does not on its own convey how a capital budgeting conclusion would change if those assumptions turned out to be wrong. Risk analysis in investment appraisal addresses this by layering a defined set of techniques on top of the base calculation: sensitivity analysis, which tests the effect of changing one input at a time; scenario analysis, which tests coherent alternative sets of assumptions together; and Monte Carlo simulation, which models a full probability distribution of outcomes across many simultaneously varying inputs. This guide sets out what each technique tests, how they complement rather than substitute for one another, and how they apply specifically to a capital budgeting decision.