Building a Green/Amber/Red Due-Date Risk System (Before You Have Software)

The Job That Looked Fine on Monday

Monday morning, job 4471 shows eight days of slack against its due date. Nobody looks at it twice. It's sitting third in the queue behind two other jobs, the material is in-house, and the customer isn't due a status update until Friday. By Wednesday, a tool breaks on the job ahead of it, the shift supervisor pulls an operator to cover a sick call, and a hot job from a key account gets slotted in front of everything. Job 4471 hasn't missed anything yet — technically, it's still "on schedule." But its eight days of slack are now two, nobody re-ran the math, and the first person to notice will be the customer, Thursday afternoon, asking why their parts aren't on the truck.

This is the failure mode that a due-date risk flagging system job shop teams build to prevent: not the catastrophic single-day slip, but the slow bleed of slack that nobody is tracking until it's gone. A green/amber/red system doesn't require software. It requires a handful of honest numbers, re-checked on a fixed schedule, and the discipline to act on amber before it becomes red. Here's how to build one with what you already have — a spreadsheet, a whiteboard, or a stack of job travelers — and where that manual version eventually stops being enough.

What Green/Amber/Red Actually Means for a Job

Before building the tracker, it's worth being precise about what each color represents, because vague colors produce vague action. A workable definition, in terms every production manager can check without a calculator:

• Green — the job has more scheduled slack between now and its due date than its remaining routing realistically needs, even accounting for one normal-sized disruption (a short delay, a changeover, a queue wait).
• Amber — the job's slack has shrunk to roughly the size of one plausible disruption. It will hit its due date if nothing else goes wrong between now and ship. That's the useful part of the definition: amber means "no more room for bad luck," not "in trouble yet."
• Red — the job cannot hit its due date on the current plan without an active intervention: expediting, resequencing, overtime, or a customer conversation about a new date.

The whole value of the system is in the amber band — green jobs don't need you, and red jobs you already know about. Amber is the warning you'd otherwise miss.

Notice what's absent from this definition: a measured hit rate. On-time delivery benchmarks vary by shop type — job shops commonly run in the 85–95% range depending on routing complexity and customer mix (User Solutions, 50 Manufacturing KPIs, 2026) — but your flagging thresholds aren't about matching an industry number. They're about catching slippage on your jobs early enough to do something about it.

The Three Signals That Actually Decide a Job's Color

Most shops that try to build a due-date risk flagging system job shop teams can actually run day to day fail for one reason: they try to track too much. Three signals are enough to assign a color reliably.

1. Slack days remaining. Due date minus today's date minus remaining routing time (including queue time at each remaining operation, not just run time). This is the core number. Everything else adjusts it.

2. Operations remaining and their risk profile. A job with two ops left on machines that are running clean is a different risk than a job with five ops left, one of which needs a certified operator who's only on shift three days a week. Weight the slack-day number down for jobs with more remaining handoffs — each operation change is a point where a job can sit in a queue longer than planned.

3. What's standing between the job and its next operation. Is the machine it needs currently running something else with its own slack problem? Is the right operator scheduled on the right shift? A job can show six days of slack on paper and still be amber if its next machine is already double-booked.

A simple scoring approach: start with slack days, subtract one day of "buffer consumed" for each remaining operation beyond two, and flag amber if adjusted slack falls to roughly one to two days, red if it's at or below zero. The exact cutoffs should be calibrated to your shop's own routing lengths and historical slippage — a five-operation aerospace part and a two-operation bracket don't deserve the same amber threshold. Treat the numbers above as a starting framework, not a fixed rule, and tune them against a few months of your own job history.

Building the Tracker by Hand

You don't need a scheduling tool to run this system for the first few months — a spreadsheet with one row per open job does the job. The columns that matter:

• Job number / customer / due date
• Remaining operations (count and description)
• Estimated remaining routing time, including expected queue time
• Slack days (due date − today − remaining routing time)
• Adjusted slack (slack days minus the operation-count penalty above)
• Color (set by a simple IF formula off adjusted slack)
• Next operation and the machine/operator it needs
• Last reviewed date

The last column is the one shops skip and later regret. A color that was assigned Monday and never rechecked is worse than no color at all, because it creates false confidence. If you're going to run this manually, the review cadence matters as much as the math — more on that below.

For shops that want a structured starting point rather than building the spreadsheet from scratch, a dedicated on-time delivery tracker with the slack-day math and color logic pre-built is one way to skip the setup step and get straight to using it.

The Daily/Weekly Ritual That Makes It Work

A risk-flagging system is only as good as the habit wrapped around it. The shops that get real value from green/amber/red tracking run it on a fixed rhythm, not "when someone remembers":

• Daily, 10 minutes: re-scan every amber and red job only — not the whole list. Has anything moved? Did a amber job's next machine just get a new job queued ahead of it?
• Weekly, 20–30 minutes: recompute the full list, including green jobs, since a job that looked safe seven days ago may have quietly lost slack.
• At every schedule change: a rush order inserted, a machine going down, an operator calling in sick — any of these can silently shift several jobs from green to amber in one event. This is the trigger most manual systems miss, because the schedule changed but the tracker didn't get re-touched until the next scheduled review. If rush work is a regular feature of your mix, it's worth reading how rush order scheduling cascades through a week before assuming a one-off hot job is contained.

The habit, not the spreadsheet, is what prevents the job 4471 scenario from the opening of this article. The math was fine on Monday. Nobody reran it after Wednesday's disruption.

Where Manual Flagging Runs Out of Road

A hand-built tracker works well up to a point, and it's worth being honest about where that point is, because the failure mode isn't dramatic — it's a slow loss of trust in the tool.

It depends on someone remembering to update it after every disruption. The math behind slack days is simple, but it has to be rerun every time the schedule actually changes — a new rush job, a machine down, an operator absence — and in a busy week, that update is the first thing to get skipped.

It doesn't catch operator-certification conflicts automatically. A job can show adjusted slack of three days and still be effectively red if its next operation needs a certified operator who isn't on the schedule for that window. A spreadsheet doesn't know who's qualified on what machine unless someone manually cross-references it every time — and most shops don't, until the floor finds out the hard way.

It breaks down once concurrent job count climbs. Tracking 15 jobs by hand, reviewed daily, is manageable. Tracking 40 jobs across multiple machines with overlapping amber windows is where manual reconciliation starts producing stale colors faster than anyone can refresh them — the same ceiling that shows up in production scheduling metrics generally: past a certain job count, memory-based tracking stops being a process and starts being a guess.

It doesn't give the owner or the customer a clean view without extra work. If you need to hand a red/amber list to leadership or explain a status to a customer, someone has to translate the spreadsheet into a conversation. For shops that report delivery performance externally, it's worth pairing this internal system with a repeatable approach to customer-facing OTD reporting so the numbers you're tracking internally match what you're telling the account.

None of this means the manual version isn't worth building — it's the right starting point, and it will catch real problems in the first week you run it. It just means the system has a ceiling, and it's useful to know where that ceiling is before you hit it. If visibility into "what's actually at risk right now" is a recurring gap for whoever runs your floor, it's also worth a look at how that visibility problem shows up more broadly for shop owners trying to see the whole schedule at once, not just one job at a time.

Keeping the System Honest

The discipline that makes green/amber/red work isn't the color-coding — it's the willingness to look at amber jobs before they turn red, on a schedule, even in a week when nothing feels urgent. That's the same discipline behind improving on-time delivery generally: small, early corrections instead of large, late ones. If you want the fuller picture of how shops move their OTD number over time rather than just catching individual slips, the companion piece on on-time delivery improvement walks through that process end to end.

To skip building the tracker from a blank sheet, the On-Time Delivery Performance Tracker has the slack-day formulas, the 30/60/90-day rolling view, and the color logic already built — a starting point you can adapt to your own routing lengths and thresholds rather than a one-size-fits-all rule.