Production planning tool built on ERP data

Goal

The production planner needed one file that lists every part still waiting to be planned. The list had to have correct dates and stay fresh on its own, without anyone sorting or copying rows by hand.

Results in numbers

What changed

Before ~~9.7 s~~

0.5 s

Excel processing, about 20× faster

Before ~~2,000~~

4600+

rows now reach the planning sheet

Before ~~all~~

0

already-planned parts in the list

Before 1

107

different ways of writing a revision number, all read correctly

Before ~~manual~~

2 min

auto-refresh, and old data turns red

The situation

The planners at a metal fabrication company worked from an Excel file that pulled data from the company’s production software (ERP). Over the years the file had turned into a tangle of SQL queries, API calls, Excel data connections, filters and VBA macros, all depending on each other.

The list included parts that were already planned. Some rows quietly disappeared after a few days. Dates could be off by a day. A big part of the data never reached the planning sheet at all. In the end, nobody fully trusted the file.

Constraints

The production software itself couldn't be changed. Data could only be read.
The most important piece of information, whether a part is already planned, wasn't written down anywhere.
The tool had to stay in Excel, because that's what the planners and the plasma cutting department work in.
Several bugs only showed up after days of real use. One test run proved nothing.

Solution

How I solved it

Data

The list included parts that were already planned, so the planner had to pick them out by hand.

Nothing in the database said where this information lived. I found a field that looked like it marked planned rows and checked it before relying on it: I compared the highlighted rows on the production software’s screen with the database records across many work orders. Only when everything matched did I add the filter. Now only parts that really are unplanned reach Excel.

Data quality

Part codes got the wrong ending, because the database wrote the revision number in 107 different ways (P.2, REV1, R.0 and plenty of typos).

A simple find-and-replace was never going to work. I wrote logic that finds the revision number in any of those forms and tested it against all 107 variants. Every part now gets the right ending.

Logic

Rows disappeared from the list over a few days and never came back.

The macro only checked rows that were currently visible. Once a part was planned and hidden, it was never looked at again, even if it was later taken out of the plan. One test run couldn’t catch this, because hidden rows piled up slowly over days. Now every row is made visible and checked again before each filter pass.

The old copy step also stopped at 2,000 rows when there were over 4,600. It now works out the size of the data by itself.

Dates

The planned start date was sometimes a day off, even though the finish date looked right.

The data was saved in one time zone and read as another. I fixed that and put the columns in the same order as in the production software. I also added the order number column, so you can see when the same part sits on two work orders and only one of them is planned.

Reliability

Nobody could tell whether the numbers on screen were fresh or hours old.

The file refreshes when it opens and then every two minutes. The time of the last refresh is always visible and turns red if the connection drops. I also added protection so a macro that stops halfway can’t leave Excel in a broken state.

Speed

Refreshing was slow, and everyone blamed fetching data from the production software.

I measured before changing anything. All 199 requests together took only about 2.8 seconds. The real slowdown was Excel itself, where data was written one row at a time. Writing it all at once as one array made the Excel part about 20 times faster: from 9.7 seconds to 0.5.

Result

Planners now work from one list that refreshes itself, shows only unplanned parts and warns them when the data is old. The plasma cutting department uses the same file too.

I was also honest about what couldn’t be done. Two values are worked out by the production software only for a moment and never saved: the matching field was empty on all ~1.16 million rows. Instead of making numbers up, I wrote down why and suggested realistic next steps.

What is it worth? If a planner spends 1–2 hours a day putting the list together and checking it, that’s 220–440 hours a year, or roughly €7,000–17,000 of one planner’s time. Fewer mistakes save even more. In metal fabrication, one missed or double-planned part means an idle machine, a rush order or a late delivery, often costing hundreds to thousands of euros each time. (Estimate based on a typical subcontract manufacturer, not the client’s books.)

What's next

The logical next steps are automatic alerts for late orders and missing materials, and a production overview that opens in the browser and reads straight from the production software, with no Excel needed.