How is on-time delivery calculated?

Shipped work orders with a ship date on or before their due date, divided by all shipped work orders inside the Dashboard filters. If 45 of 55 shipped jobs met their due date, on-time delivery is 81.8 percent. Open jobs do not count until they ship, but an open job past its due date shows Overdue.

How is the cost variance of a job split?

Into hours, rate and material. A job estimated at 42 hours that took 48.5, quoted at 95 per hour and costed at 98, with material of 1,850 estimated and 2,010 actual, has an hours variance of 6.5 x 95 = 617.50, a rate variance of 3 x 48.5 = 145.50 and a material variance of 160, a total of 923.00 over the estimate of 5,840.

Why are there two shop rates on Setup?

Quotes are priced at the rate you estimate with, but actual hours cost what your shop costs today. If your burdened rate has risen from 95 to 98 per hour, every actual hour carries 3 of rate variance that no amount of faster machining would fix. Set both to the same value if you use one rate.

What does Hours used mean on an open job?

Actual hours booked so far divided by the estimated hours. A job estimated at 30 hours with 27 booked is at 90 percent; if it is only at the finishing stage it will almost certainly run over, which is worth raising before it ships rather than after.

How is margin calculated?

Order value minus actual cost, divided by order value, for shipped jobs. A 7,800 job with an actual cost of 6,763 has a margin of 13.3 percent. Over several jobs the Dashboard forms the margin from the sums, so a large job counts more than a small one.

How do I handle partial shipments?

Split the work order into two rows, one per shipment, each with its own quantity, hours and ship date, for example WO-2650-1 and WO-2650-2. The tracker then counts each shipment's on-time result and cost separately.

Production and Throughput

Job Shop Work Order Tracker Excel Template

Track every work order from release to shipment and see on-time delivery, open work by stage, and estimated versus actual hours, cost and margin on one dashboard.

Overview

This work order tracker is a working Excel workbook for job shop owners, production managers and estimators who need one list of every job in the shop and an honest view of how the shop delivers and how its quotes hold up. It ships with 72 example work orders for a fictional CNC machining and fabrication shop with six customers, so the dashboard and charts are populated the moment you open it; replace the example rows with your own and everything recalculates.

Each row follows one job from release to shipment. Against a status date you type, open jobs show Overdue, Due soon or In progress and the stage they sit at, so the meeting starts with the late jobs and where they are stuck. Shipped jobs show days late, lead time and whether they shipped on time, and the Dashboard rolls these up into on-time delivery by ship week and by customer against your target.

On the cost side, estimated cost is estimated hours at your estimating rate plus estimated material; actual cost is booked hours at your costing rate plus actual material. Once a job ships, the workbook gives its cost variance and margin, and the Dashboard splits the variance of all shipped jobs into hours, rate and material parts, the same split as the Job Costing Variance Calculator on this site. Rates, targets and the example prices are yours to set; nothing is benchmarked.

What this template includes

Work Orders log: one row per job with customer, part, quantity, released, due and shipped dates, current stage, order value, and estimated and actual hours and material
Per-job estimated and actual cost at your estimating and costing rates, cost variance and margin once shipped, hours used against the estimate, days late, lead time, a status in words (Complete, Overdue, Due soon, In progress) and an on-time flag
Check column that names problems in words: customer not on the list, due or ship date before release, dates after the status date, open jobs with no stage, shipped jobs with no hours, duplicate WO numbers
Setup with your currency, estimating and costing shop rates, status date, due-soon window, on-time, margin and hours-overrun targets, and the customer and stage lists
Dashboard with customer and ship-date filters and KPI tiles for on-time delivery, open and overdue work orders, open order value, hours versus estimate, cost variance split into hours, rate and material, margin and lead time
Four native charts: on-time delivery by ship week against target, open work orders by stage (overdue, due soon, in progress), estimated versus actual hours by customer, and margin by customer
Weekly, by-stage and by-customer tables, a Method sheet with every formula and a Verify sheet that re-checks them (Self-check: PASS)

Suggested use case

Use it to run the weekly open-order meeting in a machine shop or fabricator: see which jobs are overdue or due soon and where they sit, track on-time delivery by week and customer, and find the customers and jobs whose actual hours run over the quote.

How to use it

On Setup, type your shop name, currency, estimating and costing rates, targets and status date, then replace the example customers and stages.
On Work Orders, add one row per job when you release it: customer, part, quantity, due date, order value, estimated hours and estimated material.
As the job moves, update its stage and actual hours; when it ships, type the ship date and the actual material cost.
Read the Check column and fix anything it names.
Before the open-order meeting, set the status date on Setup and open the Dashboard; use the customer and ship-date filters to look at one customer or one month.

Frequently Asked Questions

How is on-time delivery calculated?: Shipped work orders with a ship date on or before their due date, divided by all shipped work orders inside the Dashboard filters. If 45 of 55 shipped jobs met their due date, on-time delivery is 81.8 percent. Open jobs do not count until they ship, but an open job past its due date shows Overdue.
How is the cost variance of a job split?: Into hours, rate and material. A job estimated at 42 hours that took 48.5, quoted at 95 per hour and costed at 98, with material of 1,850 estimated and 2,010 actual, has an hours variance of 6.5 x 95 = 617.50, a rate variance of 3 x 48.5 = 145.50 and a material variance of 160, a total of 923.00 over the estimate of 5,840.
Why are there two shop rates on Setup?: Quotes are priced at the rate you estimate with, but actual hours cost what your shop costs today. If your burdened rate has risen from 95 to 98 per hour, every actual hour carries 3 of rate variance that no amount of faster machining would fix. Set both to the same value if you use one rate.
What does Hours used mean on an open job?: Actual hours booked so far divided by the estimated hours. A job estimated at 30 hours with 27 booked is at 90 percent; if it is only at the finishing stage it will almost certainly run over, which is worth raising before it ships rather than after.
How is margin calculated?: Order value minus actual cost, divided by order value, for shipped jobs. A 7,800 job with an actual cost of 6,763 has a margin of 13.3 percent. Over several jobs the Dashboard forms the margin from the sums, so a large job counts more than a small one.
How do I handle partial shipments?: Split the work order into two rows, one per shipment, each with its own quantity, hours and ship date, for example WO-2650-1 and WO-2650-2. The tracker then counts each shipment's on-time result and cost separately.