The workbook is the specification
Every company of any size has one. A distributor's rebate accrual model. A manufacturer's allocation sheet. A clinic's capacity plan. A commission calculation. A grant tracker. It has forty tabs, a file name ending in a version number and someone's initials, a tab labelled do not delete, and it is load-bearing: lose it on a Tuesday and the department cannot work on Wednesday. It is also, whether anyone intended it or not, the most complete written description of how that part of the business actually operates.
That is why replacing it is not primarily a technical job. The formulas are easy. The hard part is that the workbook encodes decisions nobody wrote down, serves three audiences who want different things, and belongs to a person whose standing in the company is partly built on understanding it.
You are probably here because
- One person can run the file and they are about to retire, or already left
- Two people edited it the same afternoon and one version won silently
- A number went out wrong and nobody can reconstruct how it was produced
- Someone tried to replace it last year and the department went back to the workbook
The last one is the most instructive. A rejected replacement almost always failed on the exceptions, not on the main calculation.
Inventory the file before anyone designs anything
Open it and count seven things. Tabs, and how many are actually used. Distinct formulas, ignoring copies down a column. Links to other files, especially files on a shared drive one laptop can reach. Cells that should hold a formula and hold a typed number instead. Rows on any tab named exceptions, adjustments or manual. Macros, and whether anyone can explain them. And how many people edit it versus read it.
That takes a day and it converts an argument about opinions into a list. It also tends to reveal a workbook smaller than feared and stranger than expected: eleven of forty tabs genuinely in use, three feeding nothing, and one everybody assumed was archived quietly driving a lookup.
The typed-over cells are the business rules
The single highest-value hour of the project is going through every cell where a formula was replaced with a typed value and asking why. Each one is a business rule nobody wrote down. This customer gets the old rate because of a conversation in 2023. This branch is excluded from the allocation because its inventory count is unreliable. This product line calculates differently because the acquisition never got integrated. None of it is in a policy document; all of it is in the file. A replacement that handles the main calculation beautifully and none of these will be rejected in the first cycle, and the department will be right to reject it.
The exceptions tab is the same material, organised. Go through it row by row with the person who maintains it, and sort every row into three piles: a rule we should encode, a data problem we should fix upstream, and a genuine one-off that needs a manual override path in the new system. The third pile is not a failure of design. Every real operational system needs a way for a competent human to override it, with their name and a reason recorded.
One file, three audiences
A departmental workbook is usually serving three groups at once, and a replacement that satisfies only one gets abandoned.
The people doing the work want speed and familiarity. They know where to click and can sort a column when something looks odd. They lose the most in a migration and complain first.
The person who reconciles it, usually in finance, wants to trace a number back to its inputs and prove it: audit trail, period locking, and confidence that last month cannot silently change.
The executive who reads one tab wants a summary that has not moved since they last looked, and one follow-up question answered without a two-day turnaround.
Design for all three explicitly. The common failure is a beautiful data-entry application for the first group and nothing for the second — at which point finance keeps the workbook alive to reconcile, and now you have two systems.
| Kind of tab | What it really is | Where it goes |
|---|---|---|
| Pasted export from another system | A missing integration | A scheduled data feed. This is usually the cheapest large win |
| Reference or lookup table | Master data with no owner | Managed records with an owner, an effective date and a history |
| Calculation engine | The rules, mixed with the layout | Application logic, with the rules tested independently |
| Exceptions or adjustments | Undocumented policy plus genuine one-offs | Split: encoded rules, upstream fixes, and an audited override path |
| Output or summary | A report, and a very specific layout somebody depends on | A report — and export it in the same shape for at least a year |
| Working area nobody claims | Usually dead, occasionally load-bearing | Trace it before deleting. There is always one surprise |
The person who owns it decides whether this works
There is almost always one person who understands the whole file. They built it, or inherited it and rebuilt it, and their standing in the company is partly a function of being the only one who can run it. A project that treats them as a source of requirements to be extracted will fail, and it will fail slowly and politely.
Bring them in as the person whose knowledge the system exists to preserve. Give them explicit time — a day a week through discovery, agreed with their manager, not squeezed around their real job. Put their name on the design. And be honest early about what happens to their role afterwards, because they have already thought about it and are waiting to see whether anyone else will say it out loud. The version that goes wrong is not sabotage. It is a person who answers every question accurately and never volunteers what you did not know to ask.
Where these projects actually fail — our ranking by frequency
Our ordering by how often we have seen each cause a rejected replacement. Judgement, not a survey.
The replacement will be worse at something. Choose what
A spreadsheet is an extraordinarily good tool. It gives one person total freedom: add a column, sort by anything, try a scenario, paste from an email, in twenty seconds with no permission. Structured software buys correctness, concurrency and audit by taking exactly that freedom away.
Say so during design rather than at launch, then give the freedom back where it costs nothing: a good export in the layout people already use, a scenario view where numbers can be tried without saving, and a route for "I need a new column" that does not take a quarter. Departments do not abandon a new system for being slower. They abandon it because one thing they could do is now impossible and nobody has an answer.
Look for the packaged option first, honestly
Before building, check whether eighty percent of the workbook is a solved category: commission calculation, demand planning, rebate management, grant administration, workforce scheduling, close management. If it is, buying is almost always cheaper and faster, and this is worth genuine effort even though it is the less interesting answer.
The reason companies build anyway is the remaining twenty percent — and quite often that twenty percent is the actual business. The pricing logic that makes the distributor competitive. The allocation rule that keeps the best customers supplied in a shortage. If that is where the value is, the sensible shape is usually to buy the commodity part and build the differentiated part around it, rather than either extreme.
Parallel running is what buys the trust
Run both systems, on the same inputs, for at least two full cycles — two month-ends, two commission runs, two allocation rounds. Reconcile every difference to the cent, and write down the cause of each.
Two things will happen, and the second one is why this section exists. Some differences will be the new system being wrong, and you will fix them. And some differences will be the spreadsheet being wrong — a stale lookup, a range that stopped one row short, a rate updated on one tab and not another. In our experience finding at least one real error in a long-lived operational workbook is the norm rather than the exception, and it needs handling carefully: the goal is a corrected number, not a person embarrassed in front of their department.
The reconciliation document is the actual deliverable of this phase. It is what a finance lead reads before agreeing to switch, and it is worth more than any demonstration.
Cut over at a cycle boundary, never in the middle
If the department works in months, switch on the first day of a month. If it works in pay periods or quarters, use those. A mid-cycle cutover means half a period in each system and a reconciliation nobody can perform, which is exactly the moment when trust in the new system is at its most fragile. Also avoid the busiest cycle of the year entirely — a year-end close or a peak allocation season is not when anyone should be learning a new interface.
Cost, duration, and what drives both
A single well-bounded workflow — one calculation, its inputs, its outputs and its exceptions — is commonly eight to sixteen weeks, often in the sixty to one hundred and eighty thousand dollar range depending on how many systems it must read. A department's whole operating system, several interlocking workflows and multiple audiences, is more often four to nine months. What moves those numbers is rarely the calculation. It is the count of upstream systems, the number of exception rules recovered, and whether the master data has an owner. A workbook that pastes from three systems and reconciles against a fourth is a data integration project with a calculation attached, and should be priced as one.
After launch: governance, and the signal that it failed
Decide before go-live who may change a rate or a rule, and how that change reaches production. In a spreadsheet the answer was whoever had the file open, which is the risk you are buying out. Replace it with something workable rather than heavy: a named owner per rule set, effective dates on every rate, a visible change history, and an override path that records who and why.
Then watch for the one signal that matters. If, within ninety days, someone in the department has built a new spreadsheet that pulls exports out of the new system, the project did not fully land. That workbook is not disobedience — it is a defect report written in the only language available. Go and read it. It will tell you precisely which job the replacement failed to do, and it is usually a small job that nobody asked about.
When to leave it alone
Some workbooks should stay workbooks, and saying so has saved clients more money than anything we have built for them. Leave it alone when one person runs it monthly in under an hour and someone else reviews the output. When it is a model rather than a process — scenario planning, budgeting, deal analysis — because spreadsheets are excellent at that and structured software is worse. When the rules are still changing every quarter. And when the real problem is upstream data quality, because new software will produce the same wrong answer faster and with better formatting.
What we get called in to fix
- A replacement built from an interview instead of from the file
- Exceptions discovered in the first live cycle, after go-live
- No parallel run, so nobody in finance would sign off on the numbers
- The workbook owner consulted twice and never given real time
- No export in the old layout, so three people kept the old file alive
- Master data with no owner, moved into a system that now argues with itself
- Cutover in the middle of a close, in the busiest month of the year
- No override path, so operations lost the ability to handle a real one-off
A sequence that works
From workbook to system, in order
Keeping the old workbook readable but not editable for one final cycle costs nothing and removes most of the fear in the room. It also gives you a reference when someone asks why a number differs from last quarter.
Before you approve the build
- The file has been inventoried, not just described in a meeting
- Every typed-over cell and exception row has been explained by a person
- The workbook owner has scheduled time and is named on the design
- All three audiences have something in scope, finance included
- Packaged options were checked against the common eighty percent
- An export in the existing layout is in the first release
- Two full parallel cycles are in the plan, with a written reconciliation
- Cutover lands on a cycle boundary and outside the busiest season
- Rule ownership, effective dates and an audited override path are designed
Bottom line
Read the workbook, because it is the only complete specification that exists. Treat the typed-over cells and the exceptions tab as the requirements they are. Bring the person who maintains it in as a partner with real time, not as a source to be mined. Buy the commodity part if there is one, build the part that is genuinely your business, and prove it with two parallel cycles and a reconciliation finance will sign. Then watch for the new spreadsheet appearing in month three, and treat it as the most useful bug report you will get.
Frequently asked questions
Go through every cell where a formula was replaced by a typed value, and every row on the exceptions tab, and ask why. Each one is a decision somebody made and nobody recorded. Sort the answers into rules to encode, upstream data problems to fix, and genuine one-offs that need an audited override path in the new system.
At least two complete cycles, whatever a cycle is for that department — two month-ends, two commission runs, two allocation rounds. Reconcile every difference to the cent and record the cause of each. Expect some of the differences to be the spreadsheet being wrong; that written reconciliation is what a finance lead needs before agreeing to switch.
A single well-bounded workflow commonly runs eight to sixteen weeks and somewhere in the sixty to one hundred and eighty thousand dollar range. A department's whole operating system is more often four to nine months. The cost is driven by how many upstream systems must be read and how many exception rules exist, far more than by the difficulty of the calculation.
When one person runs it monthly in under an hour and someone else reviews the output. When it is a model rather than a process, since spreadsheets are excellent at scenarios and structured software is worse at them. When the rules are still changing every quarter. And when the real problem is upstream data quality, because new software will produce the same wrong answer faster.
Almost always one of two things: the exceptions were not captured, so the first real cycle produced answers people knew were wrong, or one specific job the workbook did — usually an export in a particular layout, or a quick what-if — had no replacement. Find the new spreadsheet somebody built and read it. It names the missing capability precisely.