The Coordinate Trap
You have just joined the corporate finance team at a mid-sized technology company. Your predecessor left three weeks ago. On your desk is a USB drive containing the operating model — the spreadsheet that drives the quarterly board deck, the annual budget, and every variance analysis the CFO has seen for the past four years. Nobody else on the team built it. Nobody else fully understands it.
You open the file. Forty-seven tabs. Thousands of formulas. You click on the Gross Profit cell for Year 3 and read:
=B14-(C14*$F$8+D$3)
Somewhere in that formula is the answer to how this company calculates Gross Profit. But to find it, you need to click on B14, then C14, then F8, then D3, reconstruct the logic in your head, and hope that the column headers accurately describe what those cells actually contain. You will do this hundreds of times today. You will do it thousands of times this quarter. And every time, you will be reverse-engineering the same business rules that the original analyst understood intuitively — but encoded as grid coordinates instead of business language.
This is the Coordinate Trap. It is not a sign of a badly built model. It is the inevitable result of how spreadsheets have been designed for forty years: business logic encoded as cell addresses, where the meaning lives in the analyst's head and the formula records only the location.
The Four Symptoms of Formula Rot
The Coordinate Trap produces a specific pattern of degradation that compounds over time. This pattern has a name.
Formula Rot is the progressive degradation of a financial model's integrity and comprehensibility as it ages, is modified by multiple people, and is adapted for purposes beyond its original design.
Formula Rot is not caused by careless analysts. It is caused by the coordinate-first design of spreadsheets — the structural decision to encode business rules as cell addresses. Even an expertly built model exhibits all four symptoms, because the medium itself does not carry meaning.
Symptom 1 — Silent Breakage. When someone inserts a row above row 14, the formula =B14-(C14*$F$8+D$3) may continue to calculate — but it now references a different row than intended. Excel will not warn you. The model will produce a wrong answer and display it with the same confidence as a correct one.
Symptom 2 — Logic Diffusion. A COGS assumption that should be encoded once appears in seven different cells across four tabs. When the assumption changes, six cells get updated. The seventh produces a silent discrepancy that may not surface until the board questions a number three months later.
Symptom 3 — Audit Burden. Every new user of the model must independently reconstruct the business logic by clicking through cell references. In a complex model, this takes hours. In a model inherited from a departed colleague, it may be impossible to complete with full confidence.
Symptom 4 — AI Opacity. When an AI agent reads a coordinate-based model, it faces the same reverse-engineering burden a human analyst does — amplified. The agent can read all cells simultaneously, but it cannot infer intent from coordinates. It can tell you the formula. It cannot tell you the business rule the formula was meant to encode.
These four symptoms are not independent problems. They compound. Silent breakage goes undetected because the audit burden makes verification impractical. Logic diffusion creates inconsistencies that AI agents cannot flag because they cannot distinguish intentional variation from accidental drift. The model degrades, and nobody has the tools to notice.
Feel the Problem: Claude Reads a Coordinate Formula
The best way to understand AI opacity is to see it happen. Open Claude in Excel (or your preferred AI assistant) and give it this formula:
=B14-(C14*$F$8+D$3)
Ask Claude to explain what this formula calculates and what business logic it represents.
Watch what happens. Claude will describe the arithmetic accurately: "This formula takes the value in B14, subtracts the product of C14 and F8, and then subtracts D3." It may guess that B14 is revenue based on common spreadsheet layouts. But it cannot tell you with confidence what the formula means — whether this is a gross profit calculation, a margin adjustment, or something else entirely. The business logic is not in the formula. It is in the context that coordinates cannot carry.
Now imagine this at scale. A model with five hundred formulas, each one a coordinate puzzle. The agent can read every cell. It can trace every dependency. But it cannot answer the question the CFO actually asks: "Why did gross margin decline by two points this quarter?" — because the answer requires understanding business rules that are encoded in positions, not in language.
This is not a limitation of AI. It is a limitation of the model architecture.
The Business Cost of Formula Rot
Formula Rot is not an aesthetic problem. It carries measurable costs that compound across every finance team that relies on coordinate-based models.
| Cost Category | What Happens | Business Impact |
|---|---|---|
| Audit hours | Every formula must be manually traced through cell references to verify correctness | External audit fees increase; internal reviews take days instead of hours |
| Restatement risk | A silent breakage goes undetected through the review cycle and produces an incorrect board number | Restatements damage credibility with boards, investors, and regulators |
| Handover failure | The analyst who built the model leaves; the replacement cannot reconstruct the logic with confidence | Critical models become unusable within months of the original builder's departure |
| AI underperformance | Agents operate on models they cannot fully interpret, producing explanations that describe arithmetic instead of business rules | The investment in AI tools delivers a fraction of its potential value |
The common response to these costs is "better documentation" — adding comment blocks, creating model maps, writing user guides. But documentation that exists outside the formula drifts from the formula over time. The formula changes; the documentation does not. Within six months, the documentation describes a model that no longer exists.
The structural solution is not better documentation alongside the formula. It is a formula that documents itself — where the business rule is visible in the formula text, not in an external reference. That is the direction this chapter takes, starting in Lesson 2.
Exercise: Measure Formula Rot in a Real Model
Open any financial model you have access to — one you built, one you inherited, or a sample model from an online source. Pick three formulas from different parts of the model and complete this exercise.
For each formula:
- Copy the formula text (e.g.,
=B14-(C14*$F$8+D$3)) - Ask Claude in Excel (or your preferred AI assistant) to explain what the formula calculates and what business rule it represents
- Time how long the explanation takes to arrive
- Rate the explanation: Did Claude explain the business rule confidently, or did it hedge with phrases like "this appears to be" or "this likely represents"?
What to look for: Formulas where Claude explains the arithmetic confidently but hedges on the business meaning are exhibiting AI opacity. Formulas where Claude is fully confident likely have some contextual clues (descriptive headers, adjacent labels) that partially compensate for the coordinate design — but that compensation is fragile and disappears when the model layout changes.
Document your findings. You will return to them in Lesson 2, where you will see the same formulas rewritten in a format that eliminates every hedge.
Try With AI
Use these prompts in Claude in Excel or your preferred AI assistant to explore this lesson's concepts.
Prompt 1: Explain a Coordinate Formula
Here is a formula from a financial model:
=B14-(C14*$F$8+D$3)
Without any additional context about this spreadsheet:
1. Describe exactly what arithmetic this formula performs
2. Explain what business rule this formula might represent
3. Rate your confidence in your business rule explanation
on a scale of 1-10, and explain why
Be honest about what you can and cannot determine from
the formula alone.
What you are learning: This prompt makes AI opacity tangible. The agent will describe the arithmetic with high confidence and the business logic with low confidence — because coordinate references carry position but not meaning. The gap between the two confidence levels is the cost of the Coordinate Trap.
Prompt 2: Identify Formula Rot Symptoms
I am going to paste several formulas from a financial model.
For each formula, identify which symptoms of Formula Rot
it exhibits:
- Silent breakage risk (would inserting a row break it?)
- Logic diffusion (does it reference values that might be
duplicated elsewhere?)
- Audit burden (how many cells must you trace to understand it?)
- AI opacity (can you explain the business rule from the
formula alone?)
Formulas:
1. =SUM(B4:B15)
2. =B14-(C14*$F$8+D$3)
3. =IF(D22>0.15, D22*E22, D22*E22*0.85)
4. =VLOOKUP(A5, Sheet2!$A$1:$D$50, 3, FALSE)
For each formula, rate each symptom as High / Medium / Low
and explain your reasoning.
What you are learning: Not all coordinate formulas carry equal risk. A simple SUM range has lower Formula Rot exposure than a multi-reference calculation with mixed absolute and relative references. By rating each symptom per formula, you build an intuition for which formulas in a model are the most dangerous — and which ones an IDFA retrofit (covered in Lesson 8) should prioritise.
Prompt 3: Trace a Calculation's Full Dependency Chain
I have a financial model where cell G28 contains this formula:
=F28*(1+$C$5)-H28*$C$7+VLOOKUP(A28,Assumptions!$A:$D,4,FALSE)
Walk me through the full audit process for this formula:
1. List every cell this formula depends on
2. For each dependency, explain what you would need to check
3. Estimate how many clicks/navigations an auditor would need
to verify this single formula
4. If any of these referenced cells also contain formulas,
describe how the audit burden compounds
Then explain: if this model has 200 formulas of similar
complexity, what does the total audit burden look like?
What you are learning: Audit burden is not linear — it compounds. A single complex formula might require tracing five dependencies, but each dependency may itself have dependencies. By walking through the full chain for one formula and then extrapolating to a model with hundreds, you develop an intuition for why audit costs in coordinate-based models grow faster than model complexity.
Flashcards Study Aid
Continue to Lesson 2: What Changes When AI Reads the Model →