How to trace the input chain behind any financial model output cell
- Step 1Open the tool on a Pro account — The Dependency Map is Pro-gated; Free returns "Cell Dependency Map requires Pro tier." Sign in on Pro or higher (50 MB / 100,000 rows on Pro).
- Step 2Load the model (.xlsx) — Drop the workbook on the upload area. XLSX only — a CSV export of a model loses the formulas and cannot be traced. Parsing happens locally.
- Step 3Pick the output cell to audit — Enter the final-output address into Target cell, e.g. the valuation cell
B12. The field auto-uppercases and ignores$, so a copied$B$12works as-is. - Step 4Name the summary sheet — Type the tab that holds the output into Sheet name (e.g.
Summary). Leave blank only if the output is on the first tab. - Step 5Run and scan for (hardcoded) — Process the model. Read the tree top-down; every
(hardcoded)leaf is an assumption or a plug. Confirm each is intentional and documented. - Step 6Capture the trail — Copy the text tree into your audit working papers. Re-run from a deeper cell if a chain is cut at the 8-level cap.
Every control the tool actually exposes
The whole UI is two text boxes — there is no depth slider, no chart toggle, no export-format picker, no range expansion. Anything not in this table does not exist in the tool.
| Field | Type | Required | Default | What it does |
|---|---|---|---|---|
| Target cell | Text box (auto-uppercased) | Yes | (none) | The cell whose precedents you trace, e.g. B12. $ signs are stripped, so $B$12 and B12 behave identically. Lower-case is forced to upper-case as you type. |
| Sheet name | Text box | No | First sheet in the workbook | Which sheet contains the target cell. Leave blank to use the first sheet. Must match the tab name exactly (case-sensitive in the lookup). |
How the text tree marks each cell
Output is a plain-text report (not a diagram). Every node renders as one line; the marker tells you what kind of cell it is.
| Node renders as | Meaning |
|---|---|
Sheet1!B12 = =SUM(C1:C10) | A formula cell. The formula is shown verbatim after =. Its precedents are traced on the lines below. |
Sheet1!A1 = "1250" (hardcoded) | A leaf input — no formula. The value shown is the cell's displayed text; (hardcoded) flags it as a model assumption. |
Sheet1!A1 = (empty) | The referenced cell is blank. Common when a formula points at a yet-to-be-filled input. |
Sheet1!B12 (already traced ↑) | This cell was reached earlier in the trace. It is NOT re-expanded — scroll up to its first occurrence for the full sub-tree. |
Cell Sheet1!B12 not found or has no dependencies. | The target address is empty or absent on that sheet, so there was nothing to trace. |
What the reference parser does and does not follow
Precedents are pulled from each formula string by a regular expression. Knowing its limits prevents surprises when a branch looks shorter than you expected.
| Reference style in a formula | Followed? | Behaviour |
|---|---|---|
Plain cell A1, B12 | Yes | Followed and expanded on the current sheet. |
Range C1:C10 | Partly | Only the FIRST cell (C1) is walked; the rest of the range is not individually traced. |
Cross-sheet Sheet2!B5 | Yes | Followed; the node is labelled with its sheet name. |
Quoted sheet 'P&L'!B5, 'Q1-Actuals'!A3 | No | Sheet names with spaces are fine, but &, -, . or quotes are not matched, so the link is dropped or mis-attributed to the current sheet. |
Named range Revenue, table Table1[Amount] | No | Names and structured table references are not resolved to addresses, so they are skipped. |
Absolute $B$12 | Yes | $ is ignored; treated identically to B12. |
Cookbook
Traces an auditor actually runs against a model. Numbers are illustrative; the tree on the right is the exact format the tool prints.
Equity value back to its drivers
A summary valuation that multiplies a calc-tab EBITDA by a hardcoded multiple. The multiple is the assumption you want flagged.
Cells: Summary!B12: =Calc!E20*Summary!B13 Calc!E20: =Calc!E18-Calc!E19 Summary!B13: 8.5 (exit multiple) Calc!E18: =Inputs!B2 Calc!E19: 1200000 Target cell: B12 Sheet name: Summary Output: Dependency tree for Summary!B12: Summary!B12 = =Calc!E20*Summary!B13 └─ Calc!E20 = =Calc!E18-Calc!E19 └─ └─ Calc!E18 = =Inputs!B2 └─ └─ └─ Inputs!B2 = "5400000" (hardcoded) └─ └─ Calc!E19 = "1200000" (hardcoded) └─ Summary!B13 = "8.5" (hardcoded)
Spotting a plug overriding a formula
An analyst typed a number on top of what should be a formula. The tree shows it as hardcoded where you expected a calculation.
Cells (Calc): E5: =E3*E4 (you expect this) ...but the live cell is: E5: 980000 (someone overrode it) Target cell: E5 Sheet name: Calc Output: Dependency tree for Calc!E5: Calc!E5 = "980000" (hardcoded) (No precedents — the formula was replaced by a plug.)
A shared discount-rate cell
WACC in one cell feeds several NPV lines. It is expanded once, then collapsed wherever it recurs.
Cells (Model): B30: =NPV(B5,C10:C14) B31: =NPV(B5,D10:D14) B5: =B3+B4 (WACC = risk-free + premium) B3: 0.04 B4: 0.055 Target cell: B30 Sheet name: Model Output: Dependency tree for Model!B30: Model!B30 = =NPV(B5,C10:C14) └─ Model!B5 = =B3+B4 └─ └─ Model!B3 = "0.04" (hardcoded) └─ └─ Model!B4 = "0.055" (hardcoded) └─ Model!C10 = "..." (hardcoded) (Run B31 separately — its B5 branch shows as already traced.)
Range in a forecast collapses to first cell
A revenue total that sums a forecast row only walks the first period — note this when auditing horizons.
Cells (Model): G5: =SUM(C5:F5) (4-year revenue) C5: 1000000 D5: =C5*1.1 E5: =D5*1.1 F5: =E5*1.1 Target cell: G5 Sheet name: Model Output: Dependency tree for Model!G5: Model!G5 = =SUM(C5:F5) └─ Model!C5 = "1000000" (hardcoded) (Only C5 is traced; D5:F5 are inside the range and not listed.)
Named-range assumption is invisible
If WACC is a named range rather than a cell address, the trace cannot resolve it — a real gap to know about.
Cells (Model): B30: =NPV(WACC, C10:C14) (WACC = named range -> B5) Target cell: B30 Sheet name: Model Output: Dependency tree for Model!B30: Model!B30 = =NPV(WACC, C10:C14) └─ Model!C10 = "..." (hardcoded) (WACC is a name, not A1-style, so it is NOT followed to B5.)
Edge cases and what actually happens
Buried plug overriding a formula
By designWhen an analyst overtypes a formula with a number, the tool shows the cell as (hardcoded) with no precedents below it. This is the single most useful audit signal — it instantly reveals a manual override where calculation was expected.
Named range for WACC / growth
SkippedAssumptions referenced by name (=NPV(WACC,...)) are not resolved to their cell, so they never appear in the tree. This is a real coverage gap for models that lean on named ranges — convert the name to its address, or trace that cell directly.
Forecast range summed
First cell onlyA SUM(C5:F5) over a forecast row traces only C5. The other periods are inside the range and are not listed, so a horizon check still needs you to inspect each period cell.
Chain longer than 8 levels
Truncated silentlyDeeply layered models (raw inputs -> drivers -> sub-calcs -> schedules -> summary) can exceed 8 levels. The trace stops at 8 with no marker; re-run from a level-8 cell to continue the audit downstream.
Cross-tab name with & (P&L)
Not followedA reference into a tab named P&L or Cap-Ex is not matched by the parser, so that branch is dropped. Rename financial tabs to plain alphanumerics/underscores/spaces before auditing.
Free tier
Pro requiredModel audits need Pro or higher. Free returns the Pro-required message and a 5 MB / 10,000-row cap; Pro raises that to 50 MB / 100,000 rows, enough for most deal models.
Empty assumption cell
By designA driver cell left blank shows as (empty) rather than being skipped, flagging an assumption that was never filled in — a common cause of a zeroed-out valuation.
Output cell on a hidden sheet
SupportedHidden sheets are still parsed, so a target on a hidden calc tab traces normally. If you also need to know what is hidden, see the Hidden Sheet Destroyer.
Frequently asked questions
Can it show which cells are hardcoded assumptions?
Yes — every cell with no formula is tagged (hardcoded) and its displayed value is shown. Scanning for (hardcoded) in the tree is the fastest way to inventory a model's inputs and spot any plugs that overrode a formula.
What if the model has more than 8 nested levels?
The trace stops at 8 levels — that is a hardcoded cap with no UI control and no "depth limit reached" marker; the branch just ends. For a deeper chain, take a cell at level 8 and run the trace again with it as the target.
Does it follow assumptions across the Inputs, Calc, and Summary tabs?
Yes, for normally named tabs. Cross-sheet references such as Inputs!B2 are followed and labelled with the sheet. Tabs whose names contain &, -, or . (e.g. P&L) are the exception and are not followed.
Will it expand a SUM over a forecast range?
Only the first cell of the range. SUM(C5:F5) traces C5 and ignores the later periods. Trace each period cell individually if you need the full horizon audited.
Does it resolve named ranges like WACC or GrowthRate?
No. Named ranges and structured table references are not resolved to addresses, so they are skipped. This is a known gap for name-heavy models — convert the name to its cell or trace that cell directly.
Can I audit several output cells at once?
Run the tool once per output (equity value, EBITDA, IRR, etc.); each run prints one tree. There is no multi-target batch mode.
Is the value shown the live calculated number?
For formula cells the formula text is shown, not its result. For leaf cells the displayed value is shown. To compute what input drives a specific target output, pair this with the Goal Seek Simulator.
How do I also check for circular references in the model?
Use the Circular Reference Finder; it scans the whole workbook for cycles. The Dependency Map will not loop on a cycle (it collapses revisited cells) but it does not enumerate every cycle.
Can I get a plain-English read of one complex formula in the chain?
Yes — copy that formula into the Formula Explainer for a natural-language breakdown of nested IFs, lookups, and so on.
Does the workbook leave my machine?
No. The model is parsed and traced entirely in your browser with SheetJS — nothing is uploaded, which matters for confidential deal files.
What file types can I trace?
XLSX workbooks only — that is the single accepted format for this tool. A standalone CSV has no formulas, so there is nothing to trace; if your data is in CSV, open it in Excel/Sheets, build or paste the formulas, and save as .xlsx first.
Does my workbook get uploaded anywhere?
No. The whole trace runs in your browser using SheetJS — the workbook is parsed locally and never sent to a server. That matters for confidential models: you can run the trace offline once the page has loaded.
Why does it require Pro?
The Cell Dependency Map is gated to Pro tier or higher. On the Free tier the tool returns "Cell Dependency Map requires Pro tier." The free excel limits are 5 MB / 10,000 rows / 1 file; Pro raises that to 50 MB / 100,000 rows / 5 files.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.