Sensitivity Table Integrity in Financial Models
Executive Summary
Key Takeaways
- ✓ Sensitivity table integrity failures occur when the results displayed in an Excel data table do not reflect the current state of the model.
- ✓ The three primary causes are: automatic calculation excluded for data tables (results not updated when model changes), incorrectly specified input cells (the varied input does not affect the measured output), and data table results converted to static values.
- ✓ Stale sensitivity results are a high-risk finding because they provide decision-makers with a false picture of how robust the model's outputs are to changes in key assumptions.
- ✓ Integrity verification requires: checking calculation settings, verifying formula presence in result cells, confirming input cell connectivity, and performing a manual cross-check calculation.
- ✓ Sensitivity tables should be re-verified after every material model revision and as part of the pre-submission review process.
Institutional Definition¶
Sensitivity analysis is a standard component of any financial model submitted for investment committee review, lender approval, or transaction decision-making. It shows how key output metrics (IRR, DSCR, equity return, NPV) change as specific input assumptions are varied. Decision-makers rely on sensitivity outputs to understand the range of possible outcomes and to assess how robust the projected returns are to changes in critical assumptions.
How Excel Data Tables Work¶
An Excel data table is a range of cells that performs a series of what-if calculations automatically. A one-variable data table varies one input and shows the resulting output for each value. A two-variable data table varies two inputs simultaneously and shows the resulting output for each combination.
Construction¶
A one-variable data table is constructed as follows: the range of input values is listed in a column (or row); the formula whose output is to be displayed is placed in the cell at the top of an adjacent column (or the left of an adjacent row); the entire range (input values and output formula cells) is then selected and the data table is created (Data > What-If Analysis > Data Table), specifying the column input cell (the model cell that will receive each input value in turn).
When the data table is created, Excel fills the result cells with {=TABLE(,B5)} (or {=TABLE(B4,B5)} for two-variable tables), where B5 is the column input cell. The curly braces indicate an array formula entered across the entire result range.
Calculation Behaviour¶
When the model recalculates (due to an input change or a manual recalculation), Excel recalculates all data tables that reference cells that have changed. The data table reruns each of its input scenarios, records the output from the specified formula, and updates the result matrix.
Sources of Sensitivity Table Integrity Failure¶
Automatic Calculation Disabled for Data Tables¶
Excel provides a calculation setting that performs normal automatic recalculation for the model but excludes data tables from automatic recalculation (Formulas > Calculation Options > Automatic Except for Data Tables). This setting is frequently used in large models where data tables recalculate slowly, because each table iteration requires the entire model to recalculate.
When this setting is active, changing an input assumption causes the model's calculations to update immediately, but the data table results are not updated. A decision-maker who looks at the sensitivity matrix after changing an assumption sees results that correspond to the previous set of assumptions, not the current ones.
This is the most common and most dangerous form of sensitivity table integrity failure: the model and the sensitivity table are showing inconsistent information simultaneously, with no visual indicator that the sensitivity results are stale.
Detection. The presence of the "Automatic Except for Data Tables" calculation setting should be flagged in any model audit. The specific flag in Excel's calculation options is detectable by automated audit tools that examine the workbook's calculation settings.
Incorrectly Specified Input Cells¶
A data table's column input cell (or row input cell) specifies which model cell will receive the varying input values when the table runs. If this cell is incorrectly specified — for example, if it references a cell that is not actually connected to the output being measured — the data table will run but the output will not change across the input range. All result cells in the data table will show the same value, equal to the base case output.
This is a silent error: the table appears to function (it shows results for each input value) but all results are identical because the input is being varied in a cell that does not affect the specified output.
Detection. Dependency analysis from the data table's output formula back to the input cells will reveal whether the specified column input cell is actually in the dependency chain of the output formula. If it is not, the data table's results are meaningless.
Data Table Results Converted to Static Values¶
If a model's data table results have been converted from array formulas to static values (using Paste Special > Values to overwrite the {=TABLE(...)} array formulas), the result cells will display values but will not update when the model recalculates. The sensitivity matrix becomes a static display of historical results.
This conversion may be intentional (as a way of preserving a historical sensitivity snapshot) or unintentional (if a user overwrote the formulas accidentally). In either case, the static values will diverge from the live model over time as assumptions are updated.
Detection. Cells in a live data table contain array formulas beginning with {=TABLE(. Static value cells contain plain numbers. An audit tool can verify whether data table result cells contain live array formulas or static values.
Input Values Referenced from Stale Sources¶
If the data table's input row or column values are referenced from cells that contain stale or incorrect values (for example, if the sensitivity range is drawn from a broken external link), the table will run calculations using the wrong input values, producing results that do not correspond to the intended sensitivity range.
Output Formula Reference Error¶
A data table must reference the correct formula cell as its output. If the formula cell referenced by the data table does not contain the intended output (for example, if the model has been revised and the output has moved to a different cell while the data table still references the original location), the data table will display an incorrect or irrelevant output.
Why Sensitivity Table Integrity Is Material¶
The materiality of a sensitivity table integrity failure depends on how the sensitivity output is used.
Investment committee presentations. A sensitivity matrix showing that the IRR ranges from 14% to 22% across the stated input range is a primary input to the committee's assessment of deal risk. If the matrix reflects the prior iteration of the model rather than the current one, the committee's risk assessment is based on incorrect data.
Lender stress testing. A lender who relies on a project finance model's DSCR sensitivity table to assess debt serviceability under stress conditions is making a credit decision based on those results. If the sensitivity table does not reflect the current model, the lender's risk assessment may be materially incorrect.
Negotiation contexts. In a negotiation where sensitivity outputs are shared between parties to demonstrate how outcomes vary under different assumptions, sensitivity tables that do not reflect the current model state are potentially misleading to the counterparty.
How Sensitivity Table Integrity Is Verified¶
Step 1 — Check Calculation Settings¶
Verify that the workbook's calculation settings do not exclude data tables from automatic recalculation. If they do, force a full recalculation of all data tables (Formulas > Calculate Now or Ctrl+Alt+F9) and verify that the results change as expected in response to changes in the input values.
Step 2 — Verify Formula Presence¶
Confirm that each data table's result cells contain live {=TABLE(...)} array formulas rather than static values.
Step 3 — Verify Input Cell Connectivity¶
Trace the dependency chain from the data table's specified input cell to the output formula cell, confirming that the input cell is within the dependency chain of the output. If the input cell is not a precedent of the output formula, the data table is not measuring the sensitivity of the output to the specified input.
Step 4 — Perform a Cross-Check Calculation¶
For each sensitivity table, manually calculate one off-diagonal result by entering the specified input value directly into the model's input cell and reading the output. The manually calculated result should match the corresponding cell in the sensitivity table (within rounding). A mismatch confirms that the sensitivity table is not reflecting the current model.
Step 5 — Verify Input Range Coverage¶
Confirm that the range of input values in the data table's header row or column is appropriate for the analysis being conducted. Input ranges that do not extend to relevant stress scenarios do not provide adequate sensitivity coverage.
Common Mistakes¶
| Common Mistake | Why It Matters |
|---|---|
| Setting automatic calculation to exclude data tables and not resetting it before submission | Models are frequently set to exclude data tables from automatic calculation during development to improve model speed. This setting must be removed, or a documented manual recalculation step must be performed, before the model is submitted for review. |
| Not testing sensitivity tables after model revisions | Any revision that changes the model's calculation logic or the location of key cells may affect the validity of existing sensitivity tables. Sensitivity tables should be retested after any material model revision. |
| Presenting sensitivity output from a model version that differs from the base case shown | In a presentation context, the sensitivity matrix must be drawn from the same model version and calculation state as the base case outputs shown alongside it. |
| Converting data table results to values for performance reasons without documenting the static date | If data table results are intentionally converted to static values (for example, to capture a point-in-time sensitivity snapshot), this should be documented clearly, with the date of the snapshot and the model version it reflects. |
Best Practices¶
| Best Practice | Why It Matters |
|---|---|
| Include a data table integrity check in the pre-submission review process | Before any model is submitted externally, perform a manual cross-check of at least two sensitivity table results (one high input value and one low input value) against the live model calculation, to confirm that the table is correctly connected to the current model. |
| Keep sensitivity tables on dedicated worksheets separate from calculation worksheets | This makes them easier to identify, locate, and check during an audit. |
| Use a convergence-check approach for critical sensitivity tables | In the cell adjacent to the sensitivity table, include a formula that calculates the base case result from the live model and compares it to the base case result shown in the table. If they differ, this provides an in-model alert. |
| Document the data table construction for each sensitivity analysis | The model documentation should identify each sensitivity table, state its input cell, output cell, input range, and the date it was last verified against the live model. |
Further Reading¶
- ICAEW, Financial Modelling Code, Institute of Chartered Accountants in England and Wales
- Microsoft Documentation, Calculate multiple results by using a data table, Microsoft Support
Continue Reading¶
Prerequisites¶
- Financial Model Auditing — the parent pillar
Related Technical Guides¶
- Audit Methodologies — the guide to audit approaches within which sensitivity table integrity is a standard check
- Formula Error Types — the broader taxonomy of error types including data table failures
- Dependency Analysis — used to verify that data table input cells are correctly connected to output formulas
- Hardcoded Formulas — a related risk when static values replace live data table formulas
Related Glossary¶
- Data Table — the glossary definition of an Excel data table
Related Products¶
- Financial Model Audit Engine (FMAE) — deterministic structural auditing referenced throughout this guide
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
How can I tell if a data table has been converted to static values?
Click on a cell in the data table's result area. If the formula bar shows a number (or is blank), the cell contains a static value. If it shows `{=TABLE(,B5)}` or similar, the cell contains a live data table array formula.
Why do data tables recalculate slowly in large models?
A data table with N input values requires the entire model to recalculate N times (for a one-variable table). A two-variable table with M by N input combinations requires M × N recalculations. In a large, complex model, each recalculation may take several seconds, making a large data table very slow to update.
Is it acceptable to use the "Automatic Except for Data Tables" setting if the table is manually refreshed before submission?
Yes, provided the manual refresh is performed reliably as the final step before submission and the refresh is documented. The risk is the absence of a built-in assurance mechanism: if the manual refresh step is forgotten, the submitted model will contain stale sensitivity results.
Can sensitivity analysis be implemented without Excel data tables?
Yes. An alternative approach is to run scenarios using a macro that varies the input, captures the output, and records results in a separate results range. This approach avoids data tables entirely and may be more robust for complex sensitivity analyses. However, it requires VBA, which introduces its own audit considerations.
What should I do if I find that a sensitivity table in a received model is returning stale results?
Report the finding clearly. Do not rely on the sensitivity results shown in the model. Force a full recalculation (Ctrl+Alt+F9) and verify the results change. If they do not, investigate the input cell specification and verify that the data table is live rather than static.
Related Articles
Audit Methodologies for Financial Models
Financial model audit methodologies fall into three primary categories: manual line-by-line review, automated structural analysis, and deterministic rule-based checking. Each methodology differs in scope, speed, consistency, and the types of errors it is designed to detect. The appropriate methodology depends on transaction complexity, time constraints, and institutional risk appetite.
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.
Formula Error Types in Financial Models
Formula errors in financial models fall into four principal categories: visible error values (including #REF!, #VALUE!, #DIV/0!, #NAME?, #N/A, #NULL!, and #NUM!), which display in cells and are immediately apparent; silent formula errors, which produce plausible-looking values but incorrect results; structural formula errors, which arise from incorrect model construction rather than incorrect values; and logic errors, which occur when a formula correctly implements an incorrect financial relationship. Each category requires different detection methods and carries different risk implications.
Dependency Analysis in Financial Models
Dependency analysis in financial models is the process of mapping the relationships between input cells and output cells to determine which inputs drive which outputs and by how much. A dependency map shows, for any given cell, which cells it depends upon (its precedents) and which cells depend upon it (its dependents). In a model audit or risk assessment context, dependency analysis is used to identify the inputs that have the greatest influence on key outputs, to verify that the dependency structure matches the model's intended design, and to detect structural anomalies such as outputs that are unexpectedly disconnected from their intended inputs.
Hardcoded Formulas in Financial Models
A hardcoded value in a financial model is a fixed numeric value embedded directly within a formula cell, rather than being referenced from a dedicated input or assumption cell. Hardcoded values in formula cells are a structural risk because they do not update when the model's assumptions change, they are invisible during normal model navigation, and they cannot be changed consistently through the model's standard input interface. The ICAEW Financial Modelling Code and the FAST Standard both explicitly prohibit hardcoded values within formulas, requiring that all input values be entered in a dedicated input cell and referenced by formulas rather than embedded within them.