Dependency Analysis in Financial Models
Executive Summary
Key Takeaways
- ✓ Dependency analysis maps the relationships between input cells and output cells in a financial model, showing how information flows from assumptions to results.
- ✓ It identifies high-leverage inputs whose quality has the greatest impact on output accuracy, and disconnected outputs that do not respond correctly to changes in assumptions.
- ✓ Dependency analysis is a component of model risk scoring, pre-audit scoping, change impact assessment, and sensitivity analysis design.
- ✓ Automated dependency mapping is necessary for complex models; manual precedent tracing is insufficient for comprehensive coverage.
- ✓ The results of dependency analysis should inform both the focus of model audits and the selection of inputs for sensitivity analysis.
Institutional Definition¶
Dependency analysis is the systematic mapping of the relationships between cells in a financial model to understand how information flows from inputs through calculations to outputs. For any cell in the model, dependency analysis identifies its precedents (the cells whose values it depends on) and its dependents (the cells whose values depend on it).
What Is Dependency Analysis?¶
Dependency analysis is the process of examining a financial model's formula structure to map the causal relationships between its cells. The analysis produces a directed graph in which each cell is a node and each formula reference is a directed edge from the referenced cell to the referencing cell.
In this graph: - Precedent cells are the cells that a given cell depends on. Cell A's precedents are all cells referenced (directly or indirectly) in A's formula. - Dependent cells are the cells that depend on a given cell. Cell B's dependents are all cells that (directly or indirectly) reference B in their formulas. - Root inputs are cells with no precedents: they contain values rather than formulas, and no formula in the model feeds into them. These are the model's primary inputs. - Terminal outputs are cells with no dependents: no other cell in the model references them. These are the model's final outputs.
Why It Matters¶
Identifying High-Leverage Inputs¶
In most financial models, the distribution of influence across inputs is highly unequal. A small number of inputs drive a large proportion of the variance in key outputs. The revenue growth rate, the discount rate, the debt margin, and the operating cost assumption are typically far more influential than any individual line item in the detailed cost schedule.
Dependency analysis quantifies this distribution by counting the number of output cells that each input cell contributes to (directly or indirectly). An input cell that contributes to every key output in the model is a high-leverage input: errors in it, or sensitivity to changes in it, will affect the entire model. An input cell that contributes to only one minor output is a low-leverage input.
This information is directly relevant to model risk assessment. A model whose key outputs are concentrated in a small number of high-leverage inputs has a concentrated risk profile: the quality of those specific inputs is more important than the quality of the broader model.
Detecting Disconnected Outputs¶
A dependency analysis may reveal output cells that are not connected to any relevant input. This indicates a structural error: the output is either hardcoded, referencing a stale value, or connected to the wrong source. An IRR output cell that is not connected to the revenue or cost input cells is almost certainly wrong, regardless of what value it displays.
This type of disconnection error is difficult to detect through manual review unless the reviewer specifically traces the dependency chain from output back to input. Automated dependency analysis identifies it immediately.
Supporting Change Impact Assessment¶
When a model is revised — for example, when a new scenario is added, a formula is corrected, or an assumption is updated — dependency analysis identifies all cells that will be affected by the change. This allows the reviser to verify that the change has had the intended effect on the outputs it was supposed to affect, and that it has had no unintended effect on outputs it was not supposed to affect.
Verifying Model Structure Against Design¶
Financial models are built to a specification: the IRR should be driven by revenue, cost, and financing assumptions; the DSCR should be driven by operating cash flow and debt service; the equity return should be driven by the exit value and the equity invested. Dependency analysis verifies that the model's actual dependency structure matches its intended structure.
Where Dependency Analysis Is Used¶
Model risk scoring. Dependency analysis is a component of quantitative model risk scoring frameworks that assess a model's risk profile based on its structural characteristics. The number of high-leverage inputs, the depth of the dependency chain from input to output, and the presence of disconnected outputs all contribute to the model's risk score.
Pre-audit scoping. Before conducting a detailed manual audit of a complex model, auditors use dependency analysis to identify the areas of the model that have the greatest influence on the key outputs. This allows the audit effort to be concentrated where it has the most impact.
Change management. When a model is updated, the change manager uses dependency analysis to identify all cells affected by the change and verify that the update has been correctly propagated.
Model handover. When a model is transferred from its developer to a new team, dependency analysis provides the receiving team with a map of the model's structure that supplements (or substitutes for) incomplete documentation.
Sensitivity analysis design. The inputs selected for a model's sensitivity analysis should be the high-leverage inputs identified through dependency analysis. Sensitivity analysis that varies low-leverage inputs produces outputs that are not informative about the model's actual risk exposure.
How Dependency Analysis Works¶
Manual Dependency Tracing¶
Excel provides built-in tools for manual dependency tracing. The Trace Precedents and Trace Dependents commands (Formulas tab > Formula Auditing) display blue arrows from a selected cell to its immediate precedents or dependents. Repeated application of these commands traces the dependency chain one level at a time.
Manual dependency tracing is practical for examining specific relationships within a small model. It is not practical for mapping the complete dependency structure of a complex model with hundreds of interconnected tabs, because the number of cells and relationships involved cannot be inspected exhaustively through manual means in a reasonable timeframe.
Automated Dependency Mapping¶
Automated dependency analysis parses the model's formula contents programmatically, extracts all cell references from each formula, and builds the complete directed dependency graph. This process covers every cell in the model regardless of its location, without omission.
From the complete graph, the analysis can compute:
- Depth: the length of the longest dependency chain from any input to any output
- Breadth: the number of outputs affected by each input
- Concentration: the proportion of all outputs affected by the top N inputs
- Isolation: outputs that are not connected to any input, indicating disconnection errors
- Cycles: dependency cycles that indicate circular references
Key Components of a Dependency Analysis Report¶
Input Classification¶
Inputs are classified by their influence on model outputs. A typical classification is:
- High influence: the input contributes (directly or indirectly) to five or more key output cells, including at least one primary covenant or return metric
- Medium influence: the input contributes to between two and four key output cells
- Low influence: the input contributes to only one key output cell or to non-primary outputs only
Output Connectivity Verification¶
Each key output cell is verified to confirm that it is connected (directly or indirectly) to the appropriate inputs. An output that is not connected to any input is flagged as a disconnection error. An output that is connected to inputs it should not be connected to is flagged as a structural anomaly requiring investigation.
Dependency Depth¶
The dependency chain depth (the number of intermediate calculation steps between the deepest input and a given output) is reported. Very deep dependency chains increase the complexity of the model and the difficulty of tracing errors. A model with a dependency chain depth of more than 15 to 20 levels between an input and an output is generally considered complex from a structural risk perspective.
Circular Reference Identification¶
The dependency graph reveals all circular references as cycles in the directed graph. This provides a complete picture of all circularities in the model, including indirect circularities that span multiple worksheets.
Common Mistakes¶
| Common Mistake | Why It Matters |
|---|---|
| Confusing dependency analysis with sensitivity analysis | Sensitivity analysis varies an input and measures the change in output. Dependency analysis maps the causal relationship between inputs and outputs. They are related but distinct: sensitivity analysis shows the magnitude of influence, dependency analysis shows the structure of the relationship. Both are needed for a complete model risk assessment. |
| Relying on manual precedent tracing for complex models | Manual tracing is subject to human error and omission. In a model with hundreds of interconnected worksheets, a reviewer tracing dependencies manually will follow the most obvious path and is likely to miss less obvious dependency chains. |
| Not verifying disconnected outputs | An output that displays a plausible value but is not connected to the relevant inputs may appear correct in the base case but will not respond correctly to changes in assumptions. This is a latent error that dependency analysis specifically detects. |
| Overlooking hidden worksheets in dependency mapping | Formulas on hidden or very hidden worksheets participate in the model's dependency structure. A dependency analysis that does not examine hidden worksheets produces an incomplete picture of the model's structure. |
Best Practices¶
| Best Practice | Why It Matters |
|---|---|
| Include dependency analysis as a standard component of pre-submission model review | Before submitting any model to an external party, verify that the key outputs are connected to the correct inputs and that no key output is disconnected. |
| Use dependency analysis results to focus audit effort | In a complex model, the highest-leverage inputs are the highest-priority audit targets. Correct values in those inputs have the most impact on output accuracy. |
| Use dependency analysis to inform sensitivity analysis design | The inputs selected for sensitivity analysis should be the high-leverage inputs identified by the dependency map, not an arbitrary selection of round-number assumptions. |
| Document the dependency structure for complex models | A model dependency diagram (a simplified map showing which inputs feed which major calculation blocks, which in turn feed which outputs) is a valuable addition to a model's documentation package, particularly for models that will be operated by teams other than the original developer. |
Further Reading¶
- ICAEW, Financial Modelling Code, Institute of Chartered Accountants in England and Wales
- Federal Reserve, SR 11-7: Guidance on Model Risk Management, Board of Governors of the Federal Reserve System
Continue Reading¶
Prerequisites¶
- Model Risk — the parent pillar covering the broader category of spreadsheet model risk
Related Technical Guides¶
- Audit Methodologies — the guide to audit approaches within which dependency analysis is a component
- Hardcoded Formulas — a specific error type detectable through dependency analysis
- Sensitivity Table Integrity — a related check that verifies output connectivity
- Formula Consistency — a related structural check
Related Glossary¶
- Model Risk Score — the framework that uses dependency analysis results as an input to model risk scoring
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
What is the difference between a precedent and a dependent?
A precedent of cell A is any cell that A's formula depends on, directly or through a chain of references. A dependent of cell A is any cell whose formula depends on A. Precedent tracing goes backward (toward inputs); dependent tracing goes forward (toward outputs).
Can dependency analysis detect hardcoded values?
Yes. Cells containing values rather than formulas are identified as root inputs in the dependency graph. A cell that should be a formula (because it is in the middle of a calculation chain) but is instead a hardcoded value will appear as a root input in an unexpected location, flagging it as a potential hardcoded formula error.
How does dependency analysis relate to model documentation?
Dependency analysis can generate documentation automatically by mapping the model's structure. Where a model lacks documentation, a dependency analysis report provides the receiving party with a structural overview that partially substitutes for the missing documentation.
Is dependency analysis applicable to non-Excel financial models?
The principles of dependency analysis apply to any modelling environment that uses cell references or variable references. The specific implementation differs for Python-based models, database-driven models, and purpose-built financial modelling tools.
How deep should a dependency chain be in a well-structured financial model?
There is no universal standard, but a dependency chain depth of more than 15 to 20 levels from input to key output is generally considered high complexity. Very deep chains increase the difficulty of tracing errors and verifying that changes have been correctly propagated.
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.
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.
Sensitivity Table Integrity in Financial Models
Sensitivity table integrity refers to whether the results displayed in an Excel data table in a financial model reflect the current state of the model's calculations or whether they represent stale values from a previous calculation state. An Excel data table runs a series of calculations by substituting different input values into designated cells and recording the outputs. If automatic calculation is disabled, if the data table's input cells are incorrectly specified, or if the data table has been converted from dynamic to static values, the sensitivity results displayed may not correspond to the model as it currently stands. This is a high-risk structural failure because it provides false assurance about the model's sensitivity to changes in key assumptions.
Formula Consistency in Financial Models
Formula consistency in a financial model means that cells in the same row or column that perform the same calculation use identical or structurally equivalent formulas. In a time-series financial model, the formula in the Year 1 column of a revenue line should be structurally identical to the formula in the Year 5 column of the same line, with references shifting as appropriate across periods. A cell that contains a formula materially different from its neighbours in the same row is either performing a different calculation intentionally (which should be documented) or contains an error introduced by manual editing.