From spreadsheet to validated production system

The client’s day-to-day operation depended on a calculation that had grown up inside a spreadsheet. The spreadsheet worked, in the sense that people used it every day. But it had become difficult to trust, and difficult to change without risk.

The problem

A client-facing calculation was performed through a spreadsheet workflow that was ambiguous in places. Different people interpreted parts of it differently. The rules that mattered most — the calculations that determined the result the client received — were not stated anywhere as rules. They lived in formulas, in cell references, and in the memory of the people who operated the spreadsheet.

When a calculation is done this way, the risk is not that the spreadsheet stops working. The risk is that nobody can prove what “correct” means.

The operational context

The workflow was business-critical. It ran repeatedly, it produced a result the client relied on, and it had to reconcile with documents and figures produced elsewhere in the operation. It also had to interoperate with the spreadsheets the client’s own people already used, because those were not going away.

The constraints

• The result had to be deterministic and repeatable: the same inputs had to produce the same output.
• The rules had to be validated against the business and regulatory meaning they carried.
• The system had to fit how the client already worked, including Excel and Google Sheets.
• The system had to reach Production without destabilizing a process people depended on.

Understanding the workflow

The first step was to reconstruct the workflow from the spreadsheet and from how it was actually used. This meant turning an ambiguous, partly manual process into an explicit model: what the inputs are, what the rules are, what the calculations are, and what the output must be.

This is the part that cannot be skipped. Automating an ambiguous process simply automates the ambiguity.

Engineering decisions

• Rebuild the calculation as a deterministic engine with explicit rules, rather than porting the spreadsheet formulas as-is.
• Keep business-rule and regulatory validation close to the calculation logic, so a rule can be checked before it is trusted.
• Provide a web UI, an API, and XLSX import/export with manual entry, so the system works for people and for other software.
• Handle workbooks as OOXML documents in a controlled way, rather than treating spreadsheet files as trusted input.
• Run the application in a containerized runtime so the environment is reproducible.

The system delivered

The result was a validated production system: a deterministic calculation engine with a web interface, an API, and spreadsheet interoperability. The same workflow that had lived in an ambiguous spreadsheet now runs against explicit, validated rules.

Reliability mechanisms

• Automated regression tests guard the rules and the calculations.
• Regression fixtures preserve known inputs and expected outputs so behavior cannot silently change.
• Controlled releases move through a Preview environment before Production.
• Rollback is available for every release.
• Production smoke testing confirms the system works before a release is accepted.

The test suite grew to 233 automated tests. That number is supporting engineering evidence, not the point of the project: the point is that the rules the client depends on are now protected by automated checks.

Production status

The system is in Production and is used to perform the client-facing calculation. The engagement continues under a Reliability & Evolution arrangement.

A note on confidentiality

This case study is anonymized. The client’s name, industry, and identifying details are omitted, and no formulas, proprietary logic, or private documents are shown. The description above is intentionally limited to what can be said publicly without identifying the client.