The Spreadsheet Was the Acceptance Test: Proving a Pension System Against Audited History

This is the verification-method companion to our group pension administration case study. The old system of record was Excel, and the workbooks were not a joke waiting for software to rescue them. They contained years of operational knowledge, produced figures the department understood and had passed audit. A replacement that began by dismissing them would be asking users to trade working evidence for architectural confidence.

We set a more demanding entry condition: selected historical scheme-years had to be recomputed through GPAS and match the audited workbooks to the cent. Only after clearing that bar could the new system make its stronger argument—immutable history, enforced approvals, as-at balances and rules that do not rewrite earlier periods.

The spreadsheet was evidence before it was a migration source

A working spreadsheet usually combines at least four things: source data, business rules, operator decisions and presentation. Treating it as a flat import file loses the distinction. Treating every formula as an unquestionable specification preserves accidental behaviour. We used the workbook first as observed evidence: for these historical inputs, this controlled process produced these audited outputs.

The business meaning came from a separate set of sources: a meeting with the department, its signed and version-controlled operating procedure, and a real output document used as a worked example. Raw evidence stayed in a different folder from the emerging specification. That separation made it possible to ask whether a workbook formula represented the documented rule, an operational workaround or an unexplained discrepancy.

The goal was not to clone cell mechanics. It was to reproduce the accepted financial result through a model that could show its working. When the procedure and workbook agreed, they reinforced the rule. When they appeared to differ, the discrepancy became a question to resolve rather than a choice hidden inside code.

Four historical years became golden fixtures

We selected four scheme-year examples that exercised real historical combinations of balances, contributions, interest, fees and member classifications. Each fixture contains controlled inputs and the audited expected outputs. The examples are not toy plans invented to make the engine pass. Their value comes from having existed before the implementation and having been checked through the old process.

A fixture is more than a final total. It preserves control totals at useful boundaries so a mismatch can be located. If the fund total agrees but member allocations differ, the defect is not the declared rate. If opening balances match and closing balances do not, the variance can be narrowed to postings inside the year. Good fixtures make failure diagnostic instead of merely red.

We named the fixtures and kept their purpose stable as the code changed. New money-posting work had to leave the historical set intact. That turned the fixtures into a ratchet: the implementation could gain plan shapes and workflow depth, but it could not quietly move a result already accepted as correct.

The final proof had to use the live application path

Unit tests prove individual rules: a vesting boundary, a contribution split, a rounding allocation or a rule-version transition. They cannot prove that the application loaded the right source field, selected the right plan version, posted to the right accounts and queried the resulting balance correctly.

For acceptance, the historical fixtures go through the live database and the same posting path used by the application. Opening positions are staged, calculations run, balanced transactions commit, and the resulting scheme-year positions are compared with the workbook. The comparison crosses the boundaries where correct functions are most often wired incorrectly.

This distinction caught more than arithmetic risk. A perfectly correct calculation applied to the employer source instead of the employee source is still wrong. So is a correct rule selected from today's configuration for an earlier value date. Database-level reproduction verifies the composition, while unit tests keep the individual components explainable.

Exact means exact

The acceptance comparison uses fixed-precision amounts and requires equality to the cent. There is no percentage tolerance that allows small rounding differences to accumulate across members. A financial discrepancy may have a harmless explanation, but that explanation must be found; tolerance cannot stand in for understanding.

The engine rounds half-up at posting and uses deterministic remainder allocation when a rounded control total is divided across members. That gives the comparison a stable target. Re-running the same fixture cannot distribute the final cents differently because record order changed.

When a mismatch appears, we compare from the outside in: source totals, opening positions, control postings, source-separated member balances and final outputs. This is faster and safer than adjusting a formula until the bottom-right cell agrees. The investigation has to explain which independent fact moved and why.

Classify the mismatch before changing the calculation

A difference can come from source preparation, rule interpretation, temporal selection, arithmetic, posting composition or presentation. We record which layer first diverges before editing code. If the imported opening balance differs, changing the interest formula can only make the final total agree for the wrong reason. If the ledger agrees and the PDF does not, the calculation is not the defect.

That discipline prevents reconciliation from becoming curve fitting. A patch must correct the identified cause and leave unrelated fixture boundaries unchanged. The before-and-after evidence belongs with the finding, so a future reviewer can see why the expected result remained authoritative or why a source assumption had to be revised.

No posting feature merged ahead of its evidence

The build order put the calculation core and historical fixtures ahead of the full user interface. A feature capable of posting money could not merge until its fixture remained exact through the database. Contribution work had to preserve opening positions. Interest work had to preserve its control totals. Fee and benefit work could not disturb the closed historical results.

This gate changes development behaviour. A historical mismatch is not parked as a known issue while screens continue to accumulate. The feature remains incomplete. The team cannot compensate for uncertain arithmetic with more interface progress because the branch has one unambiguous acceptance condition.

The pure-domain architecture that makes the rule engine independently testable is covered in Building the Financial Calculation Engine Before the UI. Here the important point is sequencing: proof preceded breadth.

The adversarial review challenged the benchmark too

Before implementation, an independent critique argued that the design resembled back-office workflow and spreadsheet mathematics more than a conventional recordkeeping platform. It challenged the declared-rate earnings model and raised fairness and portability concerns. We did not accept the critique wholesale, and we did not dismiss it.

The signed procedure and historical outputs showed that declared-rate deposit administration was the real product, not an accidental spreadsheet approximation. We kept that model. A fairness concern became daily proration behind a plan-level choice. Other recommendations hardened reconciliation controls or entered the explicit out-of-scope list. The benchmark survived because evidence supported it, not because it was familiar.

An external-looking review is useful only when disagreement is recorded. Accepting every recommendation replaces one unexamined authority with another. Rejecting the challenge without evidence turns existing practice into dogma. The written response made clear which observations changed the build and why the core calculation remained.

Fifty decisions turned open questions into testable defaults

The decision log records fifty choices with rationales tied to the meeting, procedure, regulatory requirement or product direction. Its standing rule was that an unanswered business question should not block implementation indefinitely. The question received a buildable default, and a later answer would become configuration rather than a rewrite.

Defaults did not become invisible assumptions. They were named, sourced and testable. That is what made later contact with real plans manageable: new fee shapes, billing patterns and entry rules expanded configuration while effective-dated versions protected historical results. A default can change safely when the system remembers that it was a decision.

A correct total is necessary and still incomplete

Matching the workbook proves numerical continuity. It does not prove authorization, delivery or operational completeness. Late in the build, we found features that existed but failed their stated principle: queued statements without a consumer, beneficiary data ignored by the death-benefit destination, rule changes without dual control, an annual close that one actor could complete, and two compatible-looking settings that produced an invalid economic combination.

None announced itself through a failing golden fixture. The historical money could remain correct while a future workflow did nothing or permitted the wrong actor. We found them by rereading the system against its own decisions and tracing each promise to the place it was enforced. Acceptance therefore has layers: arithmetic reproduction, control enforcement and end-to-end operational effect.

The resulting corrections were recorded as revisions to earlier decisions rather than edited into a spotless history. That makes the log useful for future review: it shows which principles proved strong, where their implementation was incomplete and how the evidence changed the design.

What the new system had to add after matching

• An append-only ledger that reconstructs balances for any earlier date.
• Separate employee and employer sources through interest, vesting, forfeiture and payout.
• Effective-dated plan rules that do not restate a closed period.
• Identity-based maker-checker controls that broad roles cannot bypass.
• Regulatory evidence and exact reconciliation built into state transitions.

Those capabilities justify replacement. The spreadsheet's accepted result remains the floor. GPAS adds the ability to refuse an invalid transition, explain a balance from immutable events and prove which rule and actor produced it. Respecting the old evidence made the argument for the new controls stronger, not weaker.

The final demonstrations therefore begin with continuity, not novelty. A historical year is reproduced, the member and fund positions are shown, and the operator can trace the postings behind them. Only then do we demonstrate the refusal paths, rule history and as-at questions. Users first see that the familiar result survived; after that, the additional controls have something credible to improve upon.

A reusable acceptance pattern

1. Preserve raw operational evidence separately from your interpretation of it.
2. Select historical examples that exercise different rule and data shapes.
3. Record expected totals at enough boundaries to locate a mismatch.
4. Run the fixtures through the production database and posting composition, not only pure functions.
5. Require exact explanation of discrepancies before widening the money-posting surface.
6. Add separate reviews for control enforcement and external side effects.

The takeaway

When the existing system already passes audit, correctness is not a differentiator. It is admission to the conversation. Use the spreadsheet as independent historical evidence, reproduce it through the real application path and make the result a gate rather than a late demonstration. Then show what the spreadsheet could not: an immutable explanation, temporal rules and controls the operator cannot skip.