Excel as a shadow ERP: how to spot it and what it costs

A shadow ERP is the collection of spreadsheets, small databases and side tools a company uses to plan, order and track work beside its ERP, or in place of it. Nobody sets one up on purpose. Each sheet starts as a fix for something the ERP can't do, and the company has a shadow ERP once it runs on the sheet's numbers instead of the system's.

Signs you're running a shadow ERP

You usually hear these in conversation before anyone opens a file.

• People ask a colleague for a figure instead of looking it up, because "the system is never right".
• A spreadsheet has columns that duplicate ERP fields with "real" or "actual" in the header: "real delivery date" or "actual stock".
• The weekly planning meeting starts by agreeing which copy of the list is the current one.
• Data travels from the ERP into an export, through the sheet, and back into the ERP by hand.
• ERP reports exist that nobody runs, since everyone knows the numbers in them are out of date.
• Reorder points sit in a workbook instead of the item master. The reorder point calculation with a worked example takes a few lines, and the result only triggers purchase proposals once it's in the ERP.

Why companies end up with one

Here is an illustrative case. A buyer at a mid-sized machine builder needs to chase suppliers. The ERP stores the date she asked for on each purchase order, but there is no field for the date the supplier actually confirmed. So she keeps an expediting sheet with one row per open order line and a column for the confirmed date. That is a sensible fix for her own work.

Then production planning finds the sheet. It is the only place that shows when parts will really arrive, so the planners start scheduling from it. Sales asks for a copy to answer customer calls. A year later three departments depend on a file that one buyer maintains, with no history of changes, no access rules and no link back to the ERP. Every date in it was typed by hand, and when two people edit at once one of them loses their changes.

None of those steps was a bad decision. The shadow ERP is what a reasonable workaround turns into when nobody closes the gap behind it.

The cost and risk of a shadow ERP

Reconciliation time. Anything held in both places gets entered twice and checked against itself. Take an illustrative plant where a planner spends five hours a week merging copies of the schedule and a buyer spends three hours re-keying confirmed dates. At 46 working weeks and €50 an hour, that is 8 × 46 × 50 = €18,400 a year spent keeping two records in step.

Decisions on stale data. When the sheet and the ERP disagree, someone picks one. If sales quotes a delivery date from the ERP while planning works from the sheet, the customer gets a promise the plant already knows it can't keep.

Key-person dependency. In the example, the expediting sheet runs on one buyer's habits: which colour means "confirmed by phone", which tab holds the cancelled lines. When she is away for two weeks, planning schedules from dates nobody has touched since she left.

No audit trail. Excel's version history, where it exists at all, shows that a file changed. It doesn't show why a confirmed date moved from week 12 to week 15 or who agreed to it. When a customer claims a penalty for a late delivery, that is exactly what you have to answer.

How to get out of a shadow ERP

Telling people to stop using Excel sends the sheets underground. Close the gap each one fills and the sheet stops being needed.

1. Find the sheets other people depend on. A private to-do list is fine. A file that two departments open is a system and should be treated like one.
2. Write down what each sheet decides. For the expediting sheet in the example, that is "which orders will arrive late, and by how much". Next to it, note the field, report or rule the ERP is missing.
3. Pick a home for each one. Often the ERP can do it with a custom field or a saved report. Sometimes a small connected tool with a database, user rights and a change log fits better. Either way, each figure ends up stored in one place.
4. Move the most expensive sheet first. Start with the sheet behind customer promises, because a wrong date there reaches the customer.
5. Switch off the old file. Fix the date the new source goes live, tell everyone who used the sheet, and move the file to a folder they can read but not edit. Otherwise someone keeps typing into it "just this once".

If you want an outside view on which sheets to move and where, our process analysis maps the data flow as it runs today, spreadsheets included, before any software changes.

Where to start

Open the shared drive and sort the spreadsheets by last change. Any file that people from more than one department edited this month belongs on your list. Write next to each one the decision it feeds, and you have the first draft of step 1.