How the work is checked

THE FAILURE MODE

A wrong spreadsheet does not crash

Software that breaks tends to say so. A wrong formula in a workbook says nothing. It returns a number, formatted like every other number on the sheet, and somebody acts on it. A payment goes out. A rate is quoted. A figure lands in a board pack and is repeated for the rest of the year.

This is why a spreadsheet that decides money cannot be validated by reading it. It looks right by construction: the totals foot, the columns line up, the formatting is consistent. None of that has any bearing on whether the number matches the document it is meant to match. The only test that means anything is comparing what a cell produces against what the source states it should produce, and writing the comparison down.

A spreadsheet fails silently. Nothing goes red. A number just comes out wrong.

ANATOMY

What a substantiation log line contains

A log line is not a tick. It is a small piece of evidence, written so that a stranger can repeat it without asking me anything. Each line carries six things.

1. The output being checked: sheet, cell address, and what that figure represents.
2. The value the workbook produces.
3. The value the source document states.
4. The source itself, named precisely: document title, the page or table reference, and the date of the edition used.
5. The result: match, or mismatch with the difference stated.
6. Where the figure is derived rather than quoted, the arithmetic connecting source to output, written out.

COVERAGE

Sampling twenty rows is not checking the file

Spot-checking is the normal way spreadsheets get reviewed. Someone picks a handful of rows, works them by hand, finds they agree, and signs off. The arithmetic of that is worth stating plainly: if twenty rows out of 4,350 are checked, then 4,330 rows are unchecked. A clean sample tells you about the rows you looked at. It says nothing about the rest.

It would say something if errors were spread evenly, and they are not. Errors in workbooks cluster at boundaries: the first bracket, the last bracket, the row where a rate changes, the point where a rounding rule turns over, the row added after the range was written. Those are exactly the rows a small sample is least likely to contain and most likely to get wrong.

So coverage is the definition, not the ambition. Either every decision-bearing figure has a log line, or the file has been sampled. Both are legitimate pieces of work. Only one of them can be described as reconciled, and the log makes clear which one you were sold.

MISMATCHES

What happens when a figure does not match

Mismatches are the point of the exercise, so they are handled as a procedure rather than a surprise.

1. The mismatch is recorded before it is explained: value produced, value stated, difference.
2. The source is re-read. Edition, page and effective date are confirmed. A large share of mismatches turn out to be a stale source rather than a broken formula.
3. The formula is traced back through its precedents to the first cell that disagrees with the source. That cell is the defect, not the one that displayed the wrong total.
4. The cause is written in plain language: rounding rule, bracket boundary off by one row, transcription slip, hard-coded value overwriting a formula, a figure that was never in the source at all.
5. The fix is applied and the row is re-run. The log keeps both states, so the correction is visible rather than quiet.

Nothing is silently repaired. If a figure moved, the log says it moved and why.

AI

AI is used in the build, which is the reason to reconcile

I use AI tooling to build and inspect these files, and I would rather state it on this page than have it discovered. It writes formulas quickly, it drafts structure well, and it reads a large workbook faster than I can.

It also produces arithmetic that is plausible and wrong. A bracket that looks like the published one. A rate close to the real one. A rounding rule invented because it fits the pattern of the surrounding rows. Plausible is the failure mode, because plausible is what survives review.

That is precisely why the reconciliation exists in this form. Every figure a machine produced is checked against a document an institution published, and the check is written down with a page number and a date. The reconciliation is not an apology for using AI. It is the control that makes the output usable whatever produced it, and I apply the same control to figures I typed by hand.

AI writes plausible arithmetic. Plausible is not a standard anyone should pay for.

WORKED EXAMPLE

The payroll engine

The method has one public specimen: a US federal payroll withholding engine built in Excel. Nine tabs, 13,815 formulas, reproducing 4,350 figures published by the IRS in Publication 15-T to the dollar.

It ships with a 4,375-line substantiation log that names the source, page and date of every number in it. The log goes to the buyer with the workbook, and how it is built — what each line holds, and how to pick a row and check it against the publication yourself — is written out on the payroll engine page. Being checkable is the whole product.

COPY THIS

How to run the method on your own file

Nothing here is proprietary and there is no benefit to me in keeping it quiet. If you want to reconcile a workbook without hiring anybody, this is the procedure.

1. Fix the source. Name the exact document, edition and date every figure must agree with. If two people would reach for different versions, the reconciliation is not yet defined.
2. List every figure the workbook produces that a decision depends on. That list is your denominator, and writing it usually reveals outputs nobody had thought about.
3. Give each one a row in a log with the six fields above, including the page reference. A row that says only ‘checked’ is not evidence.
4. Work the list to the end rather than to a sample. Stop when every row is marked, not when the file starts looking fine.
5. Record mismatches as you meet them and do not fix as you go. Fixing mid-pass changes figures behind you.
6. Re-run the whole log after fixes. A repair in one place moves numbers in another.
7. Ship the log with the file. A reconciliation nobody outside the room can read is a claim, not evidence.

SCOPE

What this method does not do

This is engineering. Reconciliation proves that a workbook reproduces the source document it was built from. It does not say whether that source is the right one for your circumstances, what you should be filing, paying or reporting, or what any of the numbers mean for your business. Those questions belong to your accountant, your lawyer, or whoever holds the relevant licence.

What the method gives you is narrower and more useful than reassurance: a file in which every figure has an address in a document you can open, and a log that lets you audit the builder rather than trust him.