Spreadsheets that patch the gaps between ERP and MES

The order is scheduled, the material is waiting at the workstation, the operator is ready. Yet the order cannot be started in the MES (the manufacturing execution system) because a single process parameter is missing. The operator calls the office. A process engineer opens an Excel file on a network drive, finds the right row, retypes the value into the system, and the process starts. Fifteen minutes of delay, case closed. Nobody wrote down that the gap even exists, so the same scene will repeat next week with another order.

Where the spreadsheets between systems come from

The company has an ERP (the central system for orders, materials and stock), an MES and often a separate warehouse program as well. The systems are integrated, the data is supposed to flow by itself. In practice, the ERP is missing part of the information the MES needs to start a process: a process parameter, a conversion factor, a packaging variant. Adding it to the source system takes alignment, permissions and time, so the fastest possible solution appears: an extra Excel file where the missing values are typed in by hand.

Such a file is always created for a good reason, and at first it really helps. The trouble is that it stays for years. Over time a second file appears next to the first, then a fifth, and more and more processes depend on data that lives in no source system at all. Sometimes automatic exports to CSV or Excel files, running somewhere in the background, quietly serve to pass data from one system to another. On paper the integration works; in reality, a person works between the integrated systems, carrying numbers across by hand. This pattern looks the same in manufacturing companies in Poland and in Germany, Austria and Switzerland.

Why the “crutch” takes over the process over time

Each such file looks like a small thing. Together, however, they form a layer the plant leans on every day, and this layer has its costs.

First, transcription errors. A value copied from one row too far down looks identical to the correct one, and the difference only shows up on the shop floor.

Second, knowledge kept in heads. Which file holds the missing parameter and how to read it is known to two or three people in the plant. One of them going on holiday can hold up the start of an order.

Third, working hours. Filling in and retyping data between the spreadsheet and the systems is constant, repetitive work that nobody ordered and that usually nobody counts.

Fourth, blockages. One missing parameter can stop an operation in the MES even though the machine, the material and the people are ready.

In many plants, spotting such gaps falls to the person who looks after the system. And so they should, because they know the data best. They should not, however, become the owner of all the missing information, because then the knowledge about the gaps sits in a single head again, and the company trades one problem for another. I described this pattern in more detail in my research report “Excel, paper notes and the one person who knows”: in manufacturing companies in Poland as well as in Germany, Austria and Switzerland, spreadsheets live alongside the ERP systems and take over their work. You can download the report at goidea.net/en/knowledge/excel-paper-notes-and-the-one-person-who-knows.php.

One thing deserves to be said honestly: a spreadsheet as a temporary go-between can be fine. The problem starts when the company becomes its “hostage”: when nobody knows any more how many such files exist, and the production process depends on whether someone remembered to type a value in by hand.

The solution in three elements

Element one: a register of go-between files. A list of all the files that live between the systems. For each of them, four pieces of information: which data is missing from the source system, who fills it in, how often and how much time it takes. The register fixes nothing by itself, but it shows the scale and suggests which file to start with. Every “crutch” is in essence a signal: this specific piece of data is missing from the system, and someone should be entering it there.

Element two: a form instead of the spreadsheet. The data from the most heavily used file is taken over by a simple application. Instead of an open table where anyone can type anything, there is a form with validation: a field accepts only values within the allowed range, every piece of data has an owner, and every change stays in the history with a date and a name. The application also shows gaps in advance: a list of orders approaching their start without a complete set of parameters. A gap stops being a surprise at the start of an order and becomes a task for the day before.

Element three: the data returns to the source system. A mechanism passes the completed values to where they should live: to the ERP or to the MES. The person stops being a manual interface between integrated systems, and subsequent orders automatically use data that was entered once. Eventually the spreadsheet disappears, because it is no longer needed.

What this means for the business owner

The calculation is best done with your own numbers. How many go-between files live between the systems in the company today? How many people and how many hours a week does filling in and retyping data take? How many orders a month start late because one parameter was missing, and what does an hour of an idle workstation cost? What did the last transcription error cost: a defective batch, a return, a repeated operation?

Multiply the hours by the rates, add the cost of delays, and it usually turns out that the “free” spreadsheet is one of the more expensive tools in the company. The other side of the calculation is simpler: a form with validation and a mechanism passing data to the systems is a small application, built in a few weeks, because it puts one specific data flow in order instead of replacing the systems the company already has.

Where to start

Pick one spreadsheet: the one that stops orders most often or eats up the most time. For two weeks, write down three things about it: what was missing, who filled it in and how long it took. After two weeks you have a register with real numbers and, at the same time, a ready specification from which a simple application can be built to automate data entry and keep watch over the gaps. The remaining files can wait; each subsequent one is taken over in the same way, in the order the register suggests.

If you would like to talk about the spreadsheets that live between the systems in your company, write to me or book a meeting right away.