04 · Financial planning and analysis · Reconcile to a reference, drift gate, read-only AI advisor

Forecast Automation

Reconciled finance planning with AI used as a constrained diagnostician rather than a source of financial truth.

Problem: A monthly forecast cycle depended on a hand-maintained template and source workbooks whose layout shifted between cycles.
My role: Chose the mapping-contract design, wrote the platform specification and governed the AI-assisted build.
Result: Outputs reconcile to the production reference to the cent in testing; layout changes halt the build before they become wrong numbers. structural

The business story

Because source layouts shifted between cycles and the template was maintained by hand, errors were slow to find and easy to miss.

A declarative mapping contract now drives a tested engine. Outputs must tie out to the production reference before they are trusted. A model sits beside the pipeline as a read-only explainer with no tools.

My contribution

Decided on a declarative mapping contract over runtime formula interpretation, and set the scope boundaries (no database, no BI refresh in the primary tool)
Wrote the platform specification, its golden rule and invariants
Governed the AI-assisted build: plan gates, stop triggers, secrets policy, model-tier pinning to control cost
Reviewed merged work through pull requests, with reconciliation results as the stated acceptance criterion

The process

Mapping

The allocation rules written into a declarative mapping file, with hard failure on any unmapped item.
Build

A declarative mapping contract, purpose-built readers, a schema-fingerprint drift gate and automatic post-build checks.
Validation

Golden-value regression against a prior cycle and a cell-level comparison against the production reference, with reconciliation as the merge rule.

How it works

A local, validated toolchain that turns the forecast cycle into a repeatable, reconciled upload: purpose-built readers, a declarative mapping contract driving a tested engine, automatic post-build checks, schema-drift detection, a side-by-side diff against the production reference, and a read-only model diagnostician. A prototype driver-based planning platform sits on top and must reconcile to the existing engine before any merge.

Source data is mapped and calculated deterministically, then reconciled against a reference. A read-only AI lane diagnoses exceptions; it does not calculate the financial result. Schematic workflow, not a product screenshot. — Schematic workflow of Forecast Automation, not a product screenshot or a measured result.

Deterministic stage
AI stage
Gate: human decision point

AI role AI explains. It cannot change a number.

01 Problem

Hand-maintained forecast template

A hand-maintained template and source workbooks whose layout shifted between cycles.
02 Rules + data

Declarative mapping contract

Allocation rules live in a declarative mapping file, not in runtime formula interpretation.
03 Engine

Allocation engine with conservation checks

Purpose-built readers normalise signs and remove duplicates; the engine allocates and runs conservation checks.
04 AI

Read-only diagnostician over build results

A chat panel receives the engine source, mapping, last build result and check output as context. The prompt states it is read-only.
05 Gate

Schema-drift and reconciliation gates

A schema fingerprint is compared to a stored baseline before any calculation; reconciliation must tie out before output is trusted.
06 Workflow

Upload files and side-by-side diff for finance review

The output is an upload file and a general-ledger upload file, with a side-by-side diff against the reference file.
07 Outcome

Upload reconciled to the production reference structural

Upload files that reconcile to the production reference to the cent in testing.

Step by step

Source workbooks (general-ledger export, forecast model, BI working file) are read by purpose-built readers; signs are normalised and duplicates removed at the first layer.
Before any calculation, a schema-fingerprint check compares the source layout with a stored baseline and halts on drift.
A declarative mapping file drives allocation; conservation and reconciliation checks must tie out before output is trusted.
Post-build checks run automatically: cross-reference, coverage gaps, magnitude outliers; drift reports are saved per run.
The output is an upload file and a general-ledger upload, with a side-by-side diff against the reference.
A chat panel sends the engine source, mapping contract, last build and check results to a model as cached context, as a read-only explainer of the build.

AI and engineering judgement

Where AI is used

As a single-turn, read-only diagnostician. The model sees the engine source, the mapping, the last build result, the check results and the drift report, and explains causes. It has no tools, cannot edit, cannot run a build, and is never a source of a figure.

What remains deterministic

Every number: readers, allocation, conservation and reconciliation
Schema-drift detection and the decision to halt
Post-build checks and golden regression tests
The upload file formats

How risk is controlled

Reconciliation to an independent reference is the stated merge rule for the platform prototype
Hard failure on unmapped items in the build; a detected schema drift halts the build
Model key stored locally and kept out of the repository; the installer bundle excludes settings and sweeps for secret names
Procedural gates for AI-assisted development: plan before build, stop and ask on irreversible actions, no automatic commits

Verification and controls

Golden-value regression against a prior cycle and a live cell-level comparison against the production reference
A to-the-cent reconcile suite for the planning platform
Continuous integration across supported language versions with an import smoke test of every package
Reusable skill definitions for the drift guard and the reconcile step

Tech stack

Data

pandas / openpyxl purpose-built readers normalise signs and remove duplicates at the first layer
Declarative mapping contract allocation rules live in a mapping file, not in runtime formula interpretation

Application

Python the allocation engine with conservation and reconciliation checks
Flask local web app a local toolchain with no database and no BI refresh in the primary tool

AI at runtime

Model API for the read-only diagnostician a single-turn explainer with no tools; it cannot edit, run a build or supply a figure

Testing and CI

pytest and golden regression golden-value regression and a to-the-cent reconcile suite
GitHub Actions runs the automated checks

AI-assisted development (not at runtime)

Governed AI-assisted build plan gates, stop triggers, a secrets policy and pinned model choice to control cost
Reusable skills shared skill definitions

Adoption and outcomes

The toolchain turns the forecast cycle into a repeatable, reconciled upload with automatic post-build checks and a side-by-side diff against the production reference. Hours returned to the finance team are expected but have not been measured, so no time figure is claimed. structural

Outcome

To the cent

reconciliation to the production reference

realised
Engineering

Drift gate

halts the build when a source changes shape

structural
Control

Read-only

model has no tools, no edits, no builds

structural

What I learned

Make the oracle the contract. Pin any automation that touches finance numbers to an independent reference, require it to tie out to the cent, and halt the build when inputs change shape rather than letting wrong numbers flow quietly.

Related work

Explore Finance planning