Most ERP content starts by telling you your spreadsheets are the problem. Start from the opposite position: the workbook your operations team fights with every day is the most accurate requirements document your company owns. Nobody wrote it as a spec, which is exactly why it works as one. Every formula, override, and color code was added because reality demanded it, usually right after something went wrong. Before you sign an ERP contract or scope a custom build, run the forensic audit below. Treat the spreadsheet as evidence, and the migration largely scopes itself.
Why is the spreadsheet a better spec than your requirements workshop?
Because nobody performs for a spreadsheet. Requirements workshops capture what managers believe the process is; the workbook captures what it actually is, because it had to produce correct numbers every week for years. Every exception, workaround, and negotiated edge case is already encoded in a formula or a pasted value, waiting to be read.
Interview-driven specs fail in a predictable way: people describe the happy path and forget the exceptions, and the exceptions are where ERP projects die. The workbook has no such bias, it is the fossil record of every edge case that ever mattered.
- ▸Interviews give you intentions; artifacts give you behavior. When the two disagree, trust the artifact.
- ▸The workbook survived contact with reality. A wrong rule gets noticed at month-end and fixed; a five-year-old formula is a battle-tested requirement.
- ▸It is complete in the way that matters. If a case is handled nowhere in the workbook, it genuinely never happens, a claim no workshop transcript can make.
We covered why spreadsheets drift into becoming production databases in When the Spreadsheet Becomes the Database: An SME Escape Plan; this method is what to do next. Pair the audit with a conventional document, see How to Write a Software Requirements Document That Actually Ships, and the workbook fills in the sections interviews always leave blank.
How do you run formula archaeology on a business-critical workbook?
Extract every formula programmatically, cluster the results by pattern, and interrogate the outliers. Most cells share a handful of templates; the strange ones, a hardcoded 0.94 multiplier, a five-level nested IF, are business rules someone bolted on after an incident. The output you want is a numbered rule inventory with an owner and a reason attached to each entry.
Dump formulas with a script (openpyxl in Python, or Power Query) rather than clicking through sheets: you want a searchable corpus, not impressions.
- ▸Hardcoded constants are negotiated reality. A stray multiplier is usually a rebate, a tax quirk, or a tolerance someone agreed to verbally. Find the person who knows why, and write it down before they leave.
- ▸Nested IF chains are decision tables. Unroll each into rows of condition and outcome; that table drops straight into the new system's logic and, just as importantly, into its test suite.
- ▸IFERROR wrappers are silent failure policy. Each one says: when this breaks, hide it. The new system must decide explicitly whether that case is an error, a default, or a workflow.
- ▸Date arithmetic exposes calendar truth. Fiscal-year offsets, cutoff rules, and month-end-minus-two-working-days logic live in date math nobody ever documented.
Where are the hidden business rules that formulas do not show?
Look where humans overrode the machine. Cells where a literal value was pasted over a formula mark the exact places official logic is known to be wrong. Conditional formatting encodes thresholds, comments hold tribal knowledge, and hidden sheets contain deprecated logic that may still feed live calculations. All of it is specification.
- ▸Run a paste-over census. Diff each column's dominant formula pattern against cells holding literals. Every literal in a formula column is an exception case with a story, and the story is a requirement.
- ▸Conditional formatting is a status workflow. Red at 30 days overdue, escalation highlighting at 45, those thresholds are SLA rules the new system must own explicitly.
- ▸Cell comments are the missing manual. Export all of them; they concentrate around exactly the cases interviews miss.
- ▸Hidden columns and sheets are archaeology sites. Some are dead; some silently feed lookups. Trace references before declaring anything obsolete.
Budget real interview time here. Each override has an author, and the author is carrying the actual requirement in their head.
What does an error census tell you before anyone writes code?
Count every #REF!, #N/A, and #VALUE! error, plus the silently wrong numbers, then classify each one: broken reference, missing master data, type mismatch, or genuine business exception. The census gives you a data-quality baseline, a validation-rule backlog for the new system, and an honest size for the cleanup work every migration must budget.
- ▸#REF! errors map to referential integrity. They mark relationships the workbook could not enforce, the new schema enforces them with foreign keys.
- ▸#N/A clusters expose missing master data. A lookup that fails for one customer in ten means the customer registry itself needs remediation before import day.
- ▸Type mismatches predict import failures. Dates stored as text and numbers with stray spaces will fail in bulk exactly when the cutover clock is running.
- ▸Duplicates and unit inconsistencies are the expensive ones. They produce plausible wrong numbers rather than visible errors, so hunt them deliberately.
Migrations rarely fail on software; they fail on data nobody profiled. The census converts the vague risk of dirty data into a costed line item you can plan around.
How does who-edits-what become your real RBAC spec?
The pattern of who edits which columns, which sheets are password-protected, and who receives which filtered extract by email is your organization's real access-control model, evolved under pressure, not designed. Map editors, approvers, and read-only consumers per region of the workbook, and the matrix translates almost one-to-one into roles in the new system.
- ▸Protected ranges are approval boundaries. The sheet the finance lead locks at month-end is a closing workflow with a named approver, model it as one.
- ▸Per-department file copies are scope boundaries. If sales and operations each keep their own version, that is row-level or module-level scoping, not a shared table.
- ▸Emailed extracts are reporting permissions. Who receives the filtered version tells you precisely what each role is not supposed to see.
- ▸The person who fixes everyone else's mistakes is your admin role. The audit trail they wish they had is a requirement too.
Design this deliberately rather than defaulting to one shared login: loose internal roles are exactly what security auditors keep finding in admin panels.
Which spreadsheet behaviors should the new system refuse to replicate?
Not everything the workbook does is a requirement; some of it is scar tissue. Classify every behavior as rule, workaround, or defect before it fossilizes into software. Free-text cells that should be enums, copy-paste duplication that should be relations, and manual month-end reconciliation that should be a scheduled job all belong in the workaround column.
- ▸Flexibility was compensating for missing process. The cell that accepts anything exists because nobody agreed on the allowed values. The migration is your one chance to force that agreement.
- ▸Some columns exist only because Excel lacks joins. Re-keyed customer names and copied price lists become relations, not fields.
- ▸Some manual steps are controls in disguise. The review that happened while someone retyped numbers was a checkpoint; keep the checkpoint, drop the retyping.
Faithfully reimplementing the workbook, warts included, is the most common failure mode of these projects, you end up paying software prices for Excel's defects.
How do the findings turn into a build, buy, or hybrid decision?
Score the rule inventory against candidate systems in three columns: rules a standard module covers, rules configuration covers, and rules that need custom code. If generic rules dominate, buy an ERP and stay disciplined. If your differentiating rules dominate, a custom core, or a hybrid of both, is usually cheaper over five years.
- ▸The custom-rule count is the honest input to the framework in Build vs Buy: When to Use SaaS and When to Build Custom Software, most build-vs-buy debates are conducted without it.
- ▸If you are leaning toward Odoo or similar, price the future, not just the build. Every custom module you add is recurring rework, as we break down in the Odoo upgrade tax.
- ▸If you are leaning custom, the full migration path, data, cutover order, parallel running, rollback, is in Escaping SaaS: The Complete Guide to Migrating to Custom Software.
Either way, the forensic audit is not wasted: the rule inventory becomes configuration decisions for a bought ERP and the backlog for a built one.
How TuniCyberLabs helps
We run spreadsheet forensics as a fixed-scope engagement: formula extraction, a rule inventory with named owners, the error census, and the RBAC matrix, delivered as a document you own whichever vendor or build path you choose. If the scorecard says build, our EU and North Africa engineering teams do that too.
Ready to find out what your workbook has been trying to tell you? Book a spreadsheet forensics audit with our team.