When to replace a spreadsheet production schedule

An Excel schedule stops working when one change has effects the planner can’t see: a shared fixture runs short, a successor starts before its predecessor ends, or two people edit different copies. Replace it with a finite-capacity scheduler, which checks every machine, tool and dependency each time a job moves. Keep the spreadsheet while one planner owns it and the machine is the only thing that limits what runs next.

This guide is for plant managers and planners who rebuild the week in a spreadsheet every Monday. It works one schedule change through by hand, compares what can replace the sheet, and lays out the move.

What a spreadsheet schedule does well#

A spreadsheet earns its place as a first scheduler. It costs nothing extra, everyone can read it, and the planner can add a column the day a new constraint appears.

The last clause is the one that matters. A row knows its own dates, and nothing else.

A job is an order for a quantity of a part, and a task is one operation of that job on one machine; the glossary entry for jobs and tasks uses the same split. A spreadsheet holds tasks as rows but has no idea which rows depend on each other.

You don’t need to replace the sheet if all of these hold:

• One planner owns it and makes every change.
• The machine is the only constraint: no shared fixtures, molds, tool sets or certified operators.
• The mix repeats week to week, and a moved job rarely moves another.
• Progress arrives at a daily meeting, and that is fast enough.

For example, a 6-machine job shop with one planner, repeat orders and a dedicated fixture on each machine can run on a spreadsheet for years.

Five signs the spreadsheet has stopped working#

Each sign below is a check the planner does in their head because the sheet can’t. One sign is a nuisance. Two or more mean the plan depends on what one person remembers.

A shared resource decides what runs next#

The machine is free, but the fixture, mold, die or tool set it needs is on another machine. A spreadsheet has no column that counts how many are free at 14:00 on Wednesday, so the planner tracks it from memory or a second tab.

One moved job breaks rows you can’t see#

A customer pulls an order forward, and the planner changes its row. Every row typed from the old date (its next operation, the job queued behind it on the same machine) is now wrong, and nothing turns red.

More than one person edits the plan#

The night supervisor swaps two jobs, sales promises a date, and the planner works from Monday’s copy. Files named Schedule_wk42_v7_FINAL are the symptom.

The floor reports after the fact#

Operators write counts on a sheet, and someone types them in the next morning. By then the plan and the plant have drifted for a whole shift, so every date downstream is a guess.

Planning takes hours every week#

The planner rebuilds the week each Monday instead of adjusting it. When the person who understands the sheet is on vacation, the schedule stops changing.

Formula errors make every sign worse. Powell, Baker and Lawson audited 50 operational spreadsheets and found errors in 47 of them; 43, or 86%, held at least one error that produced a wrong result.^[1]

The most common type was hard-coding, a number typed into a formula instead of referenced, at 37.7% of error instances.^[1] Most hard-coded numbers gave no wrong result on the day of the audit; the authors class them as a risky practice that can lead to errors in later use.^[1]

In a schedule, an end date written as =B4+30, with the 30 hours typed into the formula, is hard-coding. Double the job’s quantity and the end date still sits 30 hours after the start. The study covered business spreadsheets in general, not schedules, and a sheet built from typed dates and durations is exposed to the same errors.

Work one schedule change through by hand#

A pulled-in order shows how a spreadsheet fails. The shop below has three mills and one fixture that two of them share.

The planner can catch the shortage, but only by checking every BR-210 row by eye. Then the fix itself breaks two rows nobody touched. A spreadsheet stores each row’s dates on their own, while a scheduler stores the links between rows, so it re-checks them when one row moves.

What replaces a spreadsheet schedule?#

Two tools replace a spreadsheet schedule: the ERP’s planning run and a finite-capacity scheduler, which can also read live data from the machines. They answer different questions, and a plant can run both side by side.

The ERP plans at a different level. ISA-95 places ERP at Level 4, “all the activities related to running a business”, and manufacturing execution systems at Level 3.^[3]

The ERP’s planning run is material requirements planning (MRP), and standard MRP “lacks capacity constraints by decoupling fixed planned lead times from time-dependent department workloads”, which can produce plans the floor can’t run.^[2] The comparison of machine monitoring, MES and CMMS maps the same levels onto plant software.

The ERP also needs data back from the floor: the hours and counts each job took. Connecting machine data to your ERP covers that direction.

A scheduler is only as good as its durations. If your planned times come from ideal cycle times, the plan will still run late; plan on measured run rates to size each job from what the machine has done.

Make the spreadsheet safer while you decide#

If you keep the sheet for now, remove the error type the audit found most often, numbers typed into formulas, along with its schedule form: dates typed where a formula belongs.

• Calculate every end date. End = start + the duration cell, as a formula. Never type an end date or a number into the formula.
• Chain successors. The next operation’s start is a formula on the previous operation’s end, not a typed date.
• Give shared tools a row each. Add a tab that counts each fixture in use per shift, and highlight any count above what the shop owns.
• One owner, one file. Store it in one place, let one person edit, and give everyone else view access.
• Freeze the next 48 hours. Changes inside the frozen window need the planner’s sign-off.

These rules close the knock-on gap in the example: with OP20’s start chained to OP10’s end, moving 1052 moves OP20 too. They don’t close the double booking on Mill 3, and the sheet still can’t see the floor.

Move your open jobs off the spreadsheet#

The move is mostly data. A scheduler needs routings, shared resources and open jobs, and most of that already sits in the sheet or in the planner’s head.

1. List what every column means

   Write down each column and the unwritten rules beside it: which parts share a fixture, which machine never runs part BR-210, which jobs a customer has been promised. The rules in the planner’s head are the constraints a scheduler has to model.

2. Build a routing for each part

   Give every part its operations in order, each with a machine and a cycle time. For example, BR-210 is OP10 on a VF-2 mill at 4.5 minutes, then OP20 on Lathe 1 at 2.0 minutes. The guide to parts and operations shows the fields a routing carries.

3. Model each shared resource as a count

   Enter each fixture, mold or tool set with how many the shop owns: 4 sets of BR-210 soft jaws. Attach it to the operations that need it, with the quantity each one holds.

4. Load the open jobs

   Export the sheet as CSV with one row per task and import it. When there are only a few dozen open jobs, create each job by hand instead. Map columns as in the table below.

5. Retire the sheet

   Keep the spreadsheet read-only for one or two weeks to compare dates, then archive it. From then on, every change goes through the schedule.

Plan jobs in one live schedule#

The Production Scheduler holds every open job in one schedule, in list, table, Gantt, Kanban and calendar views. Fixtures and tool sets are reusable items a task reserves. The capacity lane under the Gantt draws how many are free at every moment, in rose when a move would leave them short. Schedule issues lists double-booked machines and tooling with no units free, and the optimizer proposes a plan that writes nothing until you apply it.

The six-step schedule importer reads a CSV or Excel export, a hand-built spreadsheet included. Hugo Benzing built its jobs directly in the scheduler with no ERP import and retired a stack of complex Excel sheets. It went from kickoff to planning live in under two weeks, and its planners save more than 10 hours a week.

Checklist: is it time to switch?#

Score your schedule against each sign. Replace the spreadsheet when two or more are true.

If you switch, start with the routings for the parts that run on your most contested machine. Most knock-on moves start there, so that is where a scheduler shows its first conflict.