In the final months of my time in IT, the department had stabilised. A SageX3 senior had joined as team lead, and the previous lead had returned after recovering from illness. Work had shifted from purely reactive to something more structured. In my last month in IT, the Industrial Director approached me to help build a planning tool for one of the group's production societies.
The engineer responsible for planning at the time had a working concept — a matrix in Excel: item code, description, then columns per site covering stock, open orders, and monthly sales forecasts for three months, with a final column for total to produce. Simple in principle.
The execution was the problem.
The data feeding the matrix came from SageX3 queries running through Syracuse — the browser-based SageX3 interface — built and maintained by the IT department. Processing times were long, query precision was low, and one user making a heavier-than-expected request was enough to block all other requests.
After pulling the data, the engineer ran a series of manual verification steps in Excel to understand which orders were already in production, what still needed to be produced, and what the forecasts looked like. The forecasts themselves were based on an outdated cardex. Stock figures in SageX3 were unreliable.
This for 4 colour cycles, 350 SKUs, and production quantities ranging from 1,500 to 20,000 units per run.
When the engineer was transferred to support other group challenges, I took over the role. By that point at least one improvement was in place: stock could be updated via CSV import once a day. Not automated, not real-time, but at least a daily sync.
The starting point was her matrix. The idea was sound — the implementation needed to be rebuilt from the data layer up. I configured an ODBC connection from the SageX3 server directly to Excel, rewrote the SQL queries, stripped out everything that wasn't needed, and began building sequential versions — each one a new patch, a new fix, a new capability added.
Throughout this project — and others that followed — the approach was the same: Excel with an ODBC connection as a prototype layer. It is fast to build, fast to test, and fast to put in front of real users in production. The limitation is also real: at some point the prototype needs to be replaced with something built properly — in C, Java, or a dedicated data stack. That transition requires either time or budget, and usually both. Here, neither materialised.
The tool grew incrementally. The first version resolved only pending orders. Then sales forecast calculations were added — first for one month, then two, then three. Having a live connection to SageX3 data meant I could ask the factory floor supervisor what worked best given available raw materials and adjust accordingly.
In the first year alone, 23 versions were released. Three structural problems emerged along the way.
Problem 1 — POH vs unit-level forecasts
The sales societies started issuing POH (purchase orders to the factory), ordered at pallet level. The warehouse staff picking and separating material weren't checking pending orders first — material was scattered across warehouses, customers going unserved. The conflict: POHs were at pallet level, forecasts were at unit level. Solution: MAX(POH, sales forecast) — take whichever was larger as the production signal.
Problem 2 — Two production speeds
The factory operated at two speeds: standard runs above 1,000 units per SKU, and occasional shortage runs for orders that had already come in. I built a shortage plan view — checking only stock and open orders. The problem: stock is dynamic, and not all customers place orders on the same days. This remained unsolved when I left.
Problem 3 — Excel as the processing layer
As scope grew — 5 sites plus dedicated columns for the two largest clients and the Spanish market — Excel VBA was added to handle calculations. In hindsight, a mistake. Queries via ODBC were fast. But the data was loaded into Excel first, and only then did Excel execute its functions. The result: 15 minutes to fully refresh. By the time I left, a new approach was underway — moving the calculation logic into SQL, loading only the final result into Excel. 40 versions in total.
Planning time dropped from 3 days to 15 minutes. A working daily planning tool covering 350 SKUs across 4 colour cycles, 5 sites, and key client visibility. The factory supervisor had a usable production signal every morning. The tool ran for two years and went through 40 iterations.
The architecture was constrained by what was available and what I knew at the time. Excel as a processing layer was the wrong choice — something I only fully understood after starting a Data Engineering course later. Stock data was never real-time, only daily. The shortage planning problem — dynamic stock, asynchronous customer orders — was never cleanly solved.
The broader limitation is one that repeats across several projects: the prototype worked well enough to stay a prototype. A proper implementation in a compiled language or a dedicated data stack would have solved the performance ceiling, but that investment was never made.
The last version in progress when I left was moving calculation logic from SQL, keeping Excel only as a display layer. The performance gain would have been significant. That work was not completed.