Preserved six-step workflow: the original synthetic product proof

Step 1 · Excel → structured MD: make the workbook legible to AI

A direct read_excel turns a three-row header into Unnamed: 0. The tomd step parses the header region row by row (excerpt from generated output):

**Row 0** (1 non-empty column): Column 0: `June 2026 Payroll Calculation Details (Demo)` ← Title (merged A1:I1)
**Row 1** (2 non-empty columns): Column 3: `Income Items`  Column 6: `Deductions`             ← Group headers
**Row 2** (9 non-empty columns): Employee ID | Name | Department | Base A | Allowance B | Commission C | Deduction D
                                  | Gross Pay E=A+B+C-D | Performance Coefficient            ← Actual field row

Using data_only=False, the tool extracts original formulas and converts supported formulas into draft expressions:

Step 1b · Spec scaffold: generate a draft without letting incomplete logic proceed

The scaffold sets the header depth and maps keys, dimensions, and fields by column position to machine aliases C0…C8. Translatable Excel formulas become checks directly; unsupported formulas are marked TODO. Below is an unaltered excerpt from generated output (excel_ai.py scaffold web/demo/multi_type.xlsx --sheet <payroll-sheet>):

{
  "sheet": "Payroll Details",  "header_rows": 3,
  "keys":       {"C0": 0, "C1": 1},
  "dimensions": {"C2": 2},
  "fields":     {"C3": 3, "C4": 4, "C5": 5, "C6": 6, "C7": 7, "C8": 8},
  "derived": {},
  "checks": [{"name": "Gross Pay E=A+B+C-D", "target": "C7",
              "expr": "C3+C4+C5-C6", "tolerance": 0.01,
              "_translated": true, "_source_formula": "=D4+E4+F4-G4"}],
  "table_checks": [
    {"type": "unique",   "name": "Primary key is unique", "fields": ["C0"]},
    {"type": "non_null", "name": "Required fields are present", "fields": ["C0", "C1"]}]
}

Only after completion is the spec ready for verify: aliases become business names, and table checks are limited to the rules this period's workbook actually needs:

{
  "keys":   {"ID": "Employee ID", "NAME": "Name"},
  "fields": {"A": "Base A", "B": "Allowance B", "C": "Commission C", "D": "Deduction D",
             "E_x": "Gross Pay E", "COEF": "Performance Coefficient"},
  "checks": [{"name": "Gross Pay E", "target": "E_x",
              "expr": "round(A+B+C-D, 2)", "tolerance": 0.01}],
  "table_checks": [
    {"type": "unique",      "name": "Employee ID is unique", "fields": ["ID"]},
    {"type": "total_row",   "name": "Total-row reconciliation", "field": "E_x"},
    {"type": "range",       "name": "Performance coefficient range", "field": "COEF", "min": 0, "max": 2},
    {"type": "foreign_key", "name": "Department must exist in master data",
     "field": "DEPT", "sheet": "Department Master Data", "key": "Department Name"}]
}

Step 2·3 · Field ontology + formula chain and lineage: the two steps that cannot be automated

A script can extract =D4+E4-G4, but not “Column G is the employee social-insurance contribution, capped by local minimum and maximum bases, and not deducted in the month of departure.” Business meaning is not stored in the file; it lives in what the column label implies. Layered formula chain (demo payroll sheet):

Layer 1 · Inputs       A=Base Salary  B=Allowance  C=Commission  D=Deduction  ← Entered by a person / supplied upstream
Layer 2 · Intermediate INCOME = A + B + C
Layer 3 · Result       Gross Pay E = round(A + B + C − D, 2)                 ← Existing Excel column used for reconciliation
Value caveat           Performance Coefficient `/` = not participating (≠ a score of zero)

Cross-sheet lineage (each record states source → target / join key / explanation):

Step 4 ★ · Full validation: recalculate every row and run table controls

Passing requires 100% row-level agreement and zero table-level exceptions. All three sheets fail because the demo intentionally seeds issues; this is the expected result.

4 seeded issues, 5 detection paths, all hit

Step 4b · Ontology graph: turn encoded columns into business objects

Objects, links, and functions are already encoded in the spec (keys / foreign_key / derived / checks). This step reads those definitions rather than guessing again. Generated counts: 4 objects · 1 link · 8 functions (2 validated) · property DAG with 32 nodes / 35 edges · 1 non-simulatable path

Impact ranking (how many downstream nodes change when this node changes): IN/OUT → 3 downstream nodes; M1..M6 → 3; A/B/C → 2. This answers “if this number is wrong, what else is affected?”

Step 5 · Delivery and What-If: recalculate affected dependencies

The output Excel uses three colors: Blue = original input Green = AI calculation Orange = variance, with four additional sheets for validation summary, table checks, data lineage, and field ontology.

What-If no longer relies on a hand-written recalculation list. It starts from the changed input and propagates automatically through the dependency DAG, while explicitly reporting paths that cannot be calculated (generated output):

## What-If: 10% increase in base salary
**Changed input**: `A`
**Automatically recalculated intermediate values** (topological order): `INCOME`
> ⚠️ The following path cannot be simulated and was excluded rather than forced:
>   `R_net`: Net pay requires cumulative withholding tax; a single-month sheet lacks cross-month cumulative values
Gross Pay E total: 152,517.24 → 165,824.50 (+13,307.26)

Step 6 · Web dashboard: one file colleagues can open directly

KPIs and workflow, ontology graph (dashed = unverified), per-sheet validation tabs, formula breakpoints, What-If propagation, and findings are packaged into one HTML file. The file below is the dashboard generated by the demo workflow:

Open the generated dashboard (new tab)

Final report: name the owner and next action

This review covered 3 business sheets (plus 1 referenced master-data sheet), 35 records, and 58 validation points, identifying 4 issues:

1. Gross pay for csid1003 in the payroll sheet was manually overridden by +120—the row formula was replaced by a fixed value (root-cause cell H7). Confirm whether this was a specially approved supplemental payment; if so, record it through the supplemental-payment process. Owner: payroll specialist.
2. “Strategy Department” is absent from department master data, but 4 employees reference it, so cost allocation cannot find a cost center. Owner: HR master-data manager.
3. SKU-B has a 37-unit inventory gap between the end of month 3 and the beginning of month 4, suggesting an unrecorded inventory gain or transfer. Owner: warehouse operations.
4. The Actual Total formula for Rent Expense in the budget sheet omits June, understating the total by 9,310.24. Owner: budget preparer.

Issues 1 and 4 are calculation errors in the workbook and directly affect reported figures; issues 2 and 3 are data-management process gaps that require an added control step.

Try it with your workbook → Read the complete methodology (PRD)