Developer ToolsFree generatorPDF, Excel, Word export

Critical Ratio Dispatch List Python Script Generator

Critical ratio is easy to explain and tedious to keep current in a spreadsheet. This generator writes a short Python script that reads an export of open operations and produces a ranked dispatch list with slack and a late risk flag.

Fill in

Input CSV file

Output CSV file

Job column name

Work center column name

Due date column name (YYYY-MM-DD)

Remaining hours column name

Operations remaining column name

Productive hours per day

Queue plus move days per operation

Generated code

Download:

Script

import csv
from datetime import date, datetime

IN_FILE = 'open_operations.csv'
OUT_FILE = 'dispatch_list.csv'
HOURS_PER_DAY = 7.5
QUEUE_DAYS_PER_OP = 1.0

today = date.today()
rows = []

with open(IN_FILE, newline='', encoding='utf-8-sig') as f:
    for r in csv.DictReader(f):
        due = datetime.strptime(r['due_date'], '%Y-%m-%d').date()
        hours = float(r['remaining_hours'] or 0)
        ops = int(float(r['ops_remaining'] or 1))
        days_left = (due - today).days
        work_days = hours / HOURS_PER_DAY + ops * QUEUE_DAYS_PER_OP
        cr = days_left / work_days if work_days > 0 else 999.0
        slack = days_left - work_days
        rows.append({
            'job': r['job'],
            'work_center': r['work_center'],
            'due_date': due.isoformat(),
            'days_left': days_left,
            'work_days_needed': round(work_days, 2),
            'critical_ratio': round(cr, 2),
            'slack_days': round(slack, 2),
            'flag': 'LATE RISK' if cr < 1 else 'OK',
        })

rows.sort(key=lambda x: (x['critical_ratio'], x['due_date']))

with open(OUT_FILE, 'w', newline='', encoding='utf-8') as f:
    w = csv.DictWriter(f, fieldnames=list(rows[0].keys()) if rows else ['job'])
    w.writeheader()
    w.writerows(rows)

late = sum(1 for r in rows if r['flag'] == 'LATE RISK')
print('Jobs ranked:', len(rows), '| at late risk:', late)

Expected input CSV header

job,work_center,due_date,remaining_hours,ops_remaining
J-10452,MILL-02,2026-11-06,18.5,3
J-10460,LATHE-01,2026-11-02,6.0,2

Uses only the Python standard library. Remaining lead time is a simple estimate (hours divided by productive hours per day plus queue days per operation) and ignores calendars and holidays. Calendar days are used for days left, so keep the productive-day basis consistent. Test on a copy of your data first.

Want a live dispatch list from your ERP?

We can look at your export and suggest a priority approach. Optional, the script is free to use.

Optional. The tool and every export work without this. No spam.

How to use the script

Export open operations from your ERP to CSV with one row per job and the five columns named in the form: job, work center, due date, remaining hours and operations remaining. Type your real column names into the generator, copy the script to a file such as dispatch.py and run it with Python 3. It writes a second CSV sorted from the lowest critical ratio to the highest. Open that file in Excel and filter by work center to produce a supervisor dispatch list.

Due dates must be in YYYY-MM-DD format.
Remaining hours should include setup and run time.
Schedule it with Windows Task Scheduler or cron.

The method behind it

Critical ratio is the time remaining until the due date divided by the work time still needed. The script estimates the work time as remaining hours divided by productive hours per day, plus a queue and move allowance for each remaining operation. A ratio below 1 means the job is expected to finish late, and slack is the signed difference in days. Sorting by ratio puts the most urgent work first even when a long job has a later due date than a short one.

Tuning the estimate

The queue allowance is the sensitive input. In a busy job shop queue time often dominates, so a value of 1 day per operation may be optimistic. Measure your actual average wait between operations from timestamps and replace the default. If operations run on several shifts, raise productive hours per day. Compare the ranking with what experienced supervisors would pick, and adjust until the top ten jobs look right before you rely on it.

Common mistakes

Feeding the job due date for an operation that is not the last one makes early operations look less urgent than they are. Mixing calendar days left with working days of work inflates the ratio over weekends, so convert both to the same basis if precision matters. Forgetting to remove completed jobs produces a ratio of 999 entries that clutter the list. Treat the output as a priority signal, not a finite schedule.

Frequently Asked Questions

Do I need to install anything besides Python?

No. The script uses only the csv and datetime modules from the standard library, so any Python 3 installation will run it. There is no pandas or Excel library to install.

What does the LATE RISK flag mean?

The flag appears when the critical ratio is below 1, which means the estimated work time exceeds the days left until the due date. Review those jobs first, for example for expediting, overtime or moving the promise date.

Can it handle work center calendars?

Not in this version. It uses calendar days and a flat productive-hours value. For shop calendars, holidays or finite capacity you need an APS module or a more detailed scheduling model.

How do I run it every morning?

Schedule the export from your ERP and the script with Windows Task Scheduler or a cron entry, and write the output to a shared folder. Check that the export finishes before the script starts.

If you want this running continuously against live ERP data with an approval step for any change, we can build it as a read-only agent.

Talk to an Expert Explore More Free Tools

Related Tools

MES & Shop Floor

More Developer Tools tools

See all

Back to All Tools