The spreadsheet that actually runs the plant

Ask how work is scheduled in a plant of a few dozen or a few hundred people and you will be told about the ERP. Ask to see it happen and you will be shown a spreadsheet.

It lives on one PC, or on a shared drive in several versions with dates in the file names. It has colour coding that means something, columns hidden for a reason, and a macro written years ago by somebody who no longer works there. One person understands all of it. When that person is on holiday, the plant schedules more cautiously and nobody quite says why.

Why the spreadsheet exists

It is not there because somebody failed to learn the ERP. It is there because it does a job nothing else in the building does.

The ERP plans in terms the floor does not use. Material requirements, capacity in weeks, orders as commercial objects. The floor runs in shifts, sequences and specific machines, and the translation has to happen somewhere.

The floor changes faster than a system can be told. A machine goes down at ten, a material delivery slips to Friday, the best customer sends a rush order. The spreadsheet can be rearranged in a minute. Most business systems cannot, or not by the person who needs to do it.

It holds what nobody else records. That one tool only fits two of the presses. That one product must not follow another because of cleaning. That the night shift runs this job slower. This is real scheduling knowledge, and the spreadsheet is the only place it is written down at all.

It belongs to the person who uses it. No ticket, no consultant, no change request. That ownership is the reason it works, and the reason it is fragile.

What it costs

The spreadsheet is fast and flexible, and it is also the most concentrated risk in the plant that nobody lists as one.

Knowledge in one head. The rules the file encodes are partly in the formulas and partly in the habits of the person who maintains it. Neither survives intact when that person leaves.

No history. The file shows the current plan. It does not show what the plan was on Tuesday, why it changed or who changed it, so the question why did we promise that date has no answer afterwards.

It is fed by hand. Every figure was typed or pasted from somewhere else, usually a system that was already behind. It is a careful copy of stale data.

Nobody else can see it. Sales asks the planner. The line leader asks the planner. The owner asks the planner. The planner becomes the integration layer between the office and the floor, and spends much of the day answering questions instead of planning.

What a replacement has to keep

Most attempts to retire the spreadsheet fail the same way: the replacement is more rigorous and less useful. Within a month the planner is back in the file, and the new system has become one more place where data has to be entered.

Anything that takes over this role has to keep what made the spreadsheet win.

Speed of change. Moving a job, splitting an order, holding a batch — about as fast as in the file, by the planner, without asking anybody.

Room for the unwritten rules. Tool constraints, forbidden sequences, who runs what. Recorded as rules the system respects, rather than things the planner must remember.

Live inputs instead of pasted ones. Machine states, counts and bookings arriving as they happen, so the plan starts from the floor as it is rather than as it was at the last paste.

A memory. Every change to the plan kept with when, who and why, so a missed date can be explained without reconstructing it from email.

The aim is not to take planning away from the planner. It is to stop the planner being the only place where the plan exists.

A test for Monday morning

Ask somebody other than the planner to produce next week’s schedule using only what is written down, while the planner is still in the building.

Everything they cannot find, cannot interpret or have to phone about is the part of your scheduling system that exists in one person’s head. That list is worth having on paper long before you need it, because the week you need it is the week the planner is not there.