From Spreadsheet Cells to Dependency Graphs: Tracing What Changes When an Assumption Moves

A financial feasibility model may contain hundreds or even thousands of formulas, spread across multiple worksheets and connected through a network of assumptions, intermediate calculations, and final outputs. Although an analyst sees these calculations arranged in rows and columns, the underlying logic is much closer to a software dependency system than a conventional table.

Consider a relatively straightforward development feasibility model. An analyst changes the construction cost rate because updated project information suggests that the original assumption is no longer appropriate. The change does not remain confined to one input cell. It affects the base construction cost, which may influence contingency, total development costs, financing requirements, residual land value, and ultimately the financial indicators used to evaluate the development scenario.

The difficult part is not identifying the cell that has changed. The real challenge is understanding the complete chain of calculations that depends on that cell, determining which outputs need to be recalculated, and explaining the resulting impact without losing the connection to the original workbook.

This becomes particularly important when artificial intelligence is introduced into spreadsheet workflows. A language model may understand a user's question and explain financial relationships in natural language, but that does not automatically mean it can reliably reconstruct every formula dependency in a complex workbook.

A more robust engineering approach is to represent the workbook's calculation relationships as a directed dependency graph. This provides a structured representation of how inputs, formulas, and outputs connect, allowing the system to trace the potential consequences of an assumption change before attempting to explain them.

1. Understanding the Dependency Structure Behind a Financial Model

An Excel workbook is usually designed around the way an analyst wants to organise and review information. Different worksheets may contain development assumptions, construction costs, revenue calculations, financing schedules, cash flows, and investment metrics. This arrangement is convenient for human review, but it does not necessarily communicate the complete calculation structure of the model.

A formula can reference a cell located several worksheets away, while another formula may depend on a named range, a table reference, or an intermediate calculation that is not immediately visible to the person reviewing the workbook. As a result, the physical arrangement of cells is not a reliable representation of the order in which calculations depend on one another.

For example, suppose a development model calculates base construction cost using gross floor area and a construction cost rate. Contingency is then calculated as a percentage of base construction cost, and total construction cost is derived by combining the two values. The resulting amount may feed into a broader development cost calculation, which subsequently affects the residual land value.

The logical relationships can be represented as follows:

Construction Cost Rate | v Base Construction Cost | v Contingency | v Total Construction Cost | v Residual Land Value | v Development Metrics 

This representation is useful because it shifts attention away from the location of individual cells and towards the relationships between calculations. Instead of asking where a formula is located, the system can ask which inputs determine its value and which other calculations depend on its result.

Microsoft describes the related behaviour through Excel's calculation and dependency mechanisms, including the way formula dependencies influence recalculation.

2. Representing Spreadsheet Formulas as a Directed Graph

A directed graph provides a natural way to represent these relationships. Each node represents a cell, range, or logical model variable, while each directed edge represents a dependency between two elements.

Suppose a simplified development model contains the following calculations:

The corresponding graph would contain an edge from each precedent to the formula that references it. For example, both B2 and B3 would point towards C4 because the base construction cost depends on both the construction cost rate and gross floor area.

The graph also makes it possible to distinguish between direct and indirect dependencies. A direct dependency exists when one formula references another cell or input directly. A transitive dependency exists when a calculation is affected through one or more intermediate formulas.

This distinction becomes important when an analyst wants to understand the full impact of changing an assumption. If the construction cost rate changes, the system needs to identify not only the immediate calculation affected by the change but also every downstream calculation that depends on it.

Microsoft's Excel JavaScript API provides methods for retrieving formula precedents and dependents, making it possible to inspect these relationships through supported Excel interfaces.

3. Extracting Dependencies from an Excel Workbook

The first engineering challenge is converting workbook formulas into a structured representation of their dependencies.

A practical implementation would begin by identifying worksheets and their formula-containing cells. It would then extract formula expressions, parse their references, resolve those references against the workbook structure, and construct the graph.

A simplified processing pipeline looks like this:

Excel Workbook | v Read Worksheets | v Identify Formula Cells | v Extract Formula Expressions | v Parse Cell References | v Resolve Named Ranges and Worksheet References | v Construct Dependency Graph | v Validate Graph Structure 

For example, consider the following Excel formula:

=C4*B5 

The formula cell depends directly on C4 and B5. The dependency graph therefore needs two incoming relationships:

C4 ─────┐ ├────> C5 B5 ─────┘ 

This is relatively easy to process because the formula contains straightforward cell references. However, a real financial model may contain formulas such as SUM(B2:B20), references to other worksheets, named ranges, structured table references, and functions that construct references dynamically.

These cases require more sophisticated parsing and resolution logic. A simple regular expression that searches formula text for cell addresses may identify some references correctly, but it cannot be treated as a complete formula interpretation system.

The dependency extraction layer should therefore distinguish between identifying formula syntax, resolving workbook references, and determining the relationships that need to be represented in the graph. Keeping these responsibilities separate makes the system easier to test and reduces the risk of silently overlooking dependencies.

4. Identifying the Complete Impact of an Assumption Change

Once the dependency graph has been constructed, the next task is determining which nodes are affected when an input changes.

This can be handled through graph traversal. Starting from the changed input node, the system follows outgoing edges and records every downstream node that depends directly or indirectly on that input.

Consider a simplified dependency map:

from collections import defaultdict, deque dependencies = { "BaseCost": {"CostRate", "GFA"}, "Contingency": {"BaseCost", "ContingencyRate"}, "TotalCost": {"BaseCost", "Contingency"}, "ResidualLandValue": { "Revenue", "TotalCost", "Finance", "TargetProfit" } } def build_dependents(dependencies): graph = defaultdict(set) for formula, precedents in dependencies.items(): for precedent in precedents: graph[precedent].add(formula) return graph def affected_nodes(changed_node, dependencies): graph = build_dependents(dependencies) visited = set() queue = deque([changed_node]) while queue: current = queue.popleft() for dependent in graph[current]: if dependent not in visited: visited.add(dependent) queue.append(dependent) return visited result = affected_nodes("CostRate", dependencies) print(sorted(result)) 

The expected output is:

[ 'BaseCost', 'Contingency', 'ResidualLandValue', 'TotalCost' ] 

The example demonstrates how a graph traversal can identify the downstream calculations affected by a change to the construction cost rate. The result is an impact map, not a set of recalculated financial values.

That distinction is important because dependency analysis and numerical calculation are two separate engineering responsibilities. The graph identifies which calculations may be affected, while an appropriate calculation engine evaluates the actual formulas and determines the resulting values.

For financial modelling, combining these responsibilities without clearly separating them can create problems. A system might correctly identify every affected formula but still produce an incorrect numerical result if it does not reproduce the workbook's calculation behaviour.

5. Determining the Correct Calculation Sequence

Identifying affected cells is only part of the problem. When the system needs to evaluate formulas in a particular sequence, it must also respect the dependency order.

For a directed acyclic graph, this can be achieved through topological sorting. The algorithm produces an ordering in which every precedent appears before the formula that depends on it.

Python provides a standard-library implementation through graphlib.TopologicalSorter.

from graphlib import TopologicalSorter dependencies = { "BaseCost": {"CostRate", "GFA"}, "Contingency": {"BaseCost", "ContingencyRate"}, "TotalCost": {"BaseCost", "Contingency"}, "ResidualLandValue": { "Revenue", "TotalCost", "Finance", "TargetProfit" } } order = TopologicalSorter( dependencies ).static_order() for node in order: print(node) 

The resulting sequence respects the relationships defined in the dependency map. Independent inputs may appear in different valid positions, but a dependent calculation will not be placed ahead of its required precedents.

This is particularly useful when building a system that needs to understand calculation order or determine which intermediate results must be available before evaluating a downstream formula.

However, topological sorting should not be confused with spreadsheet recalculation itself. It does not interpret Excel formulas, reproduce the complete behaviour of Excel functions, or handle every workbook-specific calculation feature. It provides a valid ordering for an acyclic dependency structure, while the actual numerical evaluation remains the responsibility of a calculation engine.

6. Handling Circular References and Iterative Calculations

A more difficult situation arises when formulas depend on one another in a circular relationship.

Development finance models provide a practical example. Interest expense may depend on the outstanding debt balance, while the debt balance may depend on project cash flow. If project cash flow also depends on interest expense, the model contains a circular calculation relationship.

A simple topological ordering cannot resolve this structure because there is no valid sequence in which every dependency can be evaluated only after its precedents.

Excel supports iterative calculations for workbooks that deliberately use circular references. These calculations are governed by workbook settings, including iteration limits and convergence-related parameters.

A dependency analysis system must therefore distinguish between an accidental circular reference and an intentional iterative calculation. Treating both conditions as ordinary dependencies would make the resulting graph misleading and could produce an invalid calculation sequence.

A more reliable implementation should identify strongly connected components, report the formulas involved in each cycle, and preserve the workbook's intended calculation semantics when iterative calculations are required.

The objective should not be to eliminate every cycle automatically. Some financial models are deliberately structured around iterative relationships, and replacing those relationships with an arbitrary calculation sequence would change the model's behaviour.

7. Why Excel's Calculation Chain Is Not a Dependency Graph

There is an important distinction in the underlying structure of Excel workbooks that is relevant when developing tools for spreadsheet inspection.

The calculation chain records the order in which formula cells were last calculated. It should not be treated as a complete description of the relationships between formulas and their precedents.

The dependency structure must be understood separately from the calculation sequence. A system that relies only on the calculation chain may therefore obtain information about calculation ordering without reconstructing the complete network of formula dependencies.

This distinction matters when processing .xlsx files programmatically, particularly when the objective is to understand how a change to one input affects the rest of the workbook.

The engineering process should derive the dependency relationships from formula references and workbook metadata rather than assuming that the calculation chain contains everything needed for impact analysis.

8. Integrating Dependency Analysis into an AI-Assisted Spreadsheet Workflow

Once the workbook has been converted into a structured dependency representation, it becomes possible to introduce a language model into the workflow without making the model responsible for reconstructing the entire calculation system independently.

Consider an analyst asking:

"What happens to the residual land value if construction costs increase by 8%?"

A reliable system should interpret this as a request to modify a specific assumption and investigate the consequences of that change. The language model can help understand the question, but the workbook processing layer must determine which input the analyst is referring to and how that input connects to the remaining calculations.

The first stage is request interpretation. The system identifies the proposed change and determines whether the user intends to modify an existing assumption, examine a hypothetical scenario, or simply understand the relationship between two calculations.

The second stage is input resolution. The requested assumption must be matched to a specific workbook cell, named range, or structured model variable. This step should not depend on approximate language-model interpretation alone because two different cells may have similar descriptions but very different financial meanings.

The third stage is dependency analysis. The graph identifies all direct and indirect calculations connected to the selected assumption. This provides the system with a structured understanding of which intermediate calculations and final outputs may be affected.

The fourth stage is numerical recalculation. The relevant calculation engine evaluates the workbook using the changed assumption and applies the workbook's calculation rules. The system should also preserve the original scenario so that the impact of the proposed change can be compared against the starting position.

The final stage is explanation. The language model can use the verified inputs, formula relationships, and recalculated results to describe what changed and why. The explanation should retain references to the relevant cells and formulas so that an analyst can independently inspect the underlying calculations.

This separation of responsibilities is important. The language model interprets the request and communicates the result, while deterministic workbook operations handle reference resolution, dependency analysis, and numerical calculations.

It is one possible architecture for combining AI-assisted interaction with conventional spreadsheet engineering, rather than a description of any particular proprietary product implementation.

9. Technical Limitations That Need to Be Addressed

Although dependency graphs provide a useful foundation, a production implementation must account for spreadsheet features that cannot always be resolved through straightforward formula parsing.

Dynamic references are one example. Functions such as INDIRECT can construct references from text values, which means the actual dependency may not be obvious from the formula's static structure. A parser may identify the function but still be unable to determine the referenced cell without evaluating the relevant expression.

Named ranges create a different challenge. A formula may refer to a defined name rather than a direct cell address, and the system must resolve that name against the workbook's defined-name scope. The distinction between workbook-level and worksheet-level names also needs to be preserved.

External workbook references introduce another complication. A formula may depend on a cell in a separate workbook that is unavailable during analysis. The system should retain the external dependency and identify its resolution status rather than silently treating the missing reference as an ordinary blank value.

Volatile functions require additional consideration because their values may change during recalculation even when conventional input dependencies have not changed. Similarly, dynamic arrays and structured table references can introduce relationships that require more sophisticated workbook interpretation.

Finally, different spreadsheet engines may support different formula functions and calculation behaviours. A dependency graph constructed from an Excel workbook does not automatically guarantee that another calculation engine will reproduce exactly the same results.

These limitations should be recorded as part of the analysis rather than hidden behind an apparently complete dependency map.

10. Establishing a Validation Framework

Before relying on dependency analysis in a financial modelling workflow, the implementation should be tested against a range of workbook structures and calculation scenarios.

The validation process should cover simple direct references as well as more complicated relationships involving multiple worksheets, named ranges, circular references, and external dependencies.

The final validation area deserves particular attention because a structurally correct graph does not guarantee numerically correct results. A workbook may contain stale cached values, unsupported formulas, incomplete external references, or calculation settings that influence the final output.

For that reason, dependency validation and numerical validation should be treated as separate testing responsibilities. The first establishes whether the system understands the workbook's calculation relationships, while the second establishes whether the calculated results are consistent with the workbook's intended behaviour.

Conclusion: Making Spreadsheet Logic More Transparent

Financial models are often reviewed through their visible outputs, but the reliability of those outputs depends on the assumptions and calculations connected beneath them. A change to one input can influence several intermediate formulas and ultimately alter the financial indicators used to assess a development scenario.

Representing these relationships as a directed dependency graph provides a structured way to understand how that influence travels through the workbook. It also creates a useful foundation for impact analysis, calculation sequencing, validation, and explanation in AI-assisted spreadsheet workflows.

The broader engineering principle is that language models should not be expected to reconstruct complex financial calculations from unstructured spreadsheet content and then independently produce numerical conclusions. Formula interpretation, dependency resolution, and numerical calculation require explicit, testable mechanisms that preserve the relationship between the original workbook and the resulting outputs.

AI can make these systems easier to interact with and can help communicate complicated calculation relationships in a more accessible way. However, the underlying calculation logic must remain inspectable, and the results must remain open to independent verification.

For real estate development professionals, this is more than a technical design consideration. When an assumption changes, the important question is not simply whether a final number has moved, but whether the analyst can trace that movement back through the calculations and understand which assumptions and relationships produced the result.

A financial model becomes more useful when every material output can be traced back to the assumptions, formulas, and dependencies that determine it.