WIP Tracking for a Job Shop: A Spreadsheet With AI Status Updates

Track work in progress with two spreadsheet tabs: one row per job, and one row per operation within each job. Add status, due date and hours columns, export the sheet each morning, and ask AI to draft each customer's update and a list of late or at-risk jobs from that export, with a person approving every message.

Most job shops lose time not on the machines but on the question "where is my order?". Someone walks the floor, finds a traveller in a tray and calls the customer back. A WIP sheet that is true at 8 a.m. answers that question in seconds, and AI turns the answer into a polite paragraph. The sheet does the real work; the AI saves the typing and notices what you might skip when busy.

Follow me on Instagram@sagnikteaches

Why jobs and operations need separate tabs

A job is what the customer ordered: 250 brackets, due on the 14th. An operation is one step to make it: cut, deburr, drill, anodise, inspect, pack. If you track only jobs, the status reads "in progress" for two weeks and tells you nothing. If you track only operations, you lose the customer view. Two tabs, joined by a job number, give both.

Connect on LinkedInSagnik Bhattacharya

Here is the minimum for each tab. Resist adding columns until a real question needs one.

Subscribe on YouTube@sagnikteaches

Jobs tab: Job number, Customer, Part and revision, Quantity, PO number, Date received, Promised date, Priority (A, B or C), Material status (Ordered, Arrived, In stock), Overall status (formula), Notes.

Operations tab: Job number, Operation number, Operation name, Work centre, Planned hours, Actual hours, Status (Not started, In progress, Waiting, Done), Date started, Date finished, Outside process (Yes or No), Notes.

"Waiting" matters more than it looks. It means finished the previous step, but blocked: waiting for material, for an outside process, for a decision, for an inspection. Most late jobs spend more of their life waiting than being worked on, and a status list with only "in progress" and "done" hides that.

Build the sheet in an hour with one rule per column

Set drop-down lists (data validation) on Status, Priority and Work centre, so everyone types the same words. Free-text statuses such as "mostly done" or "w/ nights" break every filter and confuse the AI later. Freeze the top row, turn on filters, and keep the Jobs tab one screen wide.

Five formulas make the sheet useful. With the Jobs columns in the order above, the job number sits in A, Promised date in G, Overall status in J and Notes in K, so the helper columns start at L. On the Operations tab, job numbers are in column A and status in column G:

Operations done (Jobs!L2):   =COUNTIFS(Operations!A:A, A2, Operations!G:G, "Done")
Operations total (Jobs!M2):  =COUNTIFS(Operations!A:A, A2)
Percent done (Jobs!N2):      =IF(M2=0, 0, L2/M2)
Days to promise (Jobs!O2):   =G2 - TODAY()
Overall status (Jobs!J2):    =IF(N2=1, "Done", IF(O2<0, "LATE", IF(AND(O2<=3, N2<0.7), "AT RISK", "OK")))

The AT RISK rule says: within three days of the promised date and less than 70% of operations finished. The numbers are a starting point; with long outside processes, five days may suit better. You can ask an AI assistant to write or fix these formulas, as covered in the guide to ChatGPT in Excel, but test each against two rows where you already know the answer.

A filled-in week from an illustrative shop

Take a six-person machining shop with 38 open jobs on a Monday. The export shows 4 flagged LATE, 6 AT RISK and 28 OK. A slice of it looks like this:

This is the view the owner reads at 8 a.m., and it is also the raw material for the AI. In the same way, the bottleneck is easy to see: if the Operations tab shows the milling centre holding eleven "Not started" operations and the lathe holding two, the next step is a conversation about overtime or outsourcing, not a spreadsheet.

The daily export and what to leave out of it

Each morning, copy the open jobs and their operations into a plain text block, or save a CSV. Before anything goes to an AI assistant, strip what it does not need: unit prices, internal costs, customer contact details, drawing numbers if they identify a confidential product. The status, dates, quantities and blocked-by notes are enough. Replace customer names with the codes (Customer A, Customer B) in the export and keep a private lookup to put names back on messages.

Check what your AI tool does with what you paste. Business plans of the common assistants usually state whether chats are used for training; read the current terms on the vendor's page rather than assuming. The wider picture is in this tutorial on AI security risks for small businesses.

Draft customer updates from the export

This is where the AI earns its keep. The prompt below sets hard rules so the drafts stay accurate and short:

You write customer status updates for a machine shop. Use ONLY the data I paste. Never invent dates, reasons or promises.
For each job below write a message of 40-70 words to the customer: what has been completed, what is next, whether the promised date still stands, and one sentence on any delay in plain words.
If the flag is LATE or AT RISK, say so honestly and offer the next update date I give you. Do not apologise more than once. Do not promise a new delivery date unless the data contains one.
Then list at the end any job where the data is too thin to write a safe message.

DATA:
J-2417 | Customer A | Mounting bracket x250 | promised 14th | 3 of 6 operations done | next: anodising (outside, sent 8th, due back 12th) | flag AT RISK | next update date: 12th
J-2421 | Customer B | Spindle housing x20 | promised 9th | 4 of 7 done | next: finish boring | flag LATE | cause: material arrived 2 days late | revised ship date: NOT SET

An illustrative reply:

J-2417: "Hello [first name], an update on your mounting brackets: cutting, drilling and deburring are complete and the batch went out for anodising on the 8th, due back on the 12th. Final inspection and packing follow. The 14th still stands, but it depends on the finish returning on time. I will confirm on the 12th."

J-2421: "Hello [first name], your spindle housings will miss the 9th. Material reached us two days late and four of seven operations are done. Boring is next. I don't have a firm new date yet and will send one as soon as it is set."

Too thin to write: J-2421 needs a revised ship date before a second message goes.

The second draft shows why the rules matter. A model without the instruction would have invented a date such as "the 12th". Read both drafts against the sheet, fix tone, and send from your own email. Replace [first name] and your own name by hand. If you also want this written once as a reusable template for your shop's voice, how garages send repair updates shows the same idea in a neighbouring trade.

Ask AI for the late-job list and the questions behind it

Every morning after the export, a second prompt turns the sheet into the owner's agenda for the floor meeting:

From the data below, list: (1) jobs flagged LATE with the blocking cause and who must act, (2) jobs AT RISK with the one thing that would rescue each, (3) work centres with more than 8 operations waiting, (4) jobs whose Waiting status has lasted more than 3 days. Keep it to a page. If a cause is missing from the data, say "no cause recorded" instead of guessing.

The line "no cause recorded" is the best feature. It exposes the jobs where nobody wrote down why work stopped, which is a process gap rather than an AI problem. In an illustrative week, 3 of the 4 late jobs had a cause recorded and the fourth had none, so the first agenda item was to ask the cell leader why. Follow the same pattern for building the weekly schedule once you trust the status data.

How to read WIP in dollars as well as hours

Most shops also want to know how much money sits inside unfinished work. Add three columns on the Jobs tab: Material cost, Labour hours booked, and Shop rate per hour. Work in progress value is material plus hours times rate. Illustrative figures: J-2417 has $620 of material, 22 hours booked, and a shop rate of $75 an hour, so WIP value is $620 + (22 × $75) = $2,270. Summing it across 38 open jobs gives a figure your bookkeeper can compare with the stock and work-in-progress line in your accounts. A big jump from one month to the next often means jobs are sitting unfinished or unbilled, which is a cash problem before it is a production one.

Ask the AI to explain a number you do not recognise, but do not let it post entries. Valuation methods vary by shop and by accounting rules, so agree the approach with your accountant, and treat the sheet as a management view. A related tutorial, quote to cash, covers the invoicing end of the same flow.

Outside processes: the status most shops lose track of

Plating, heat treatment, anodising, painting and grinding often leave the building. While a batch is out, the job looks stalled, and the shop's control over the date is lowest. Give outside operations three extra columns on the Operations tab, after Notes: Supplier (L), Date sent (M) and Date due back (N). Then a single formula warns you when a batch is overdue from the supplier:

Outside overdue:  =IF(AND(J2="Yes", G2<>"Done", TODAY()>N2), "CHASE", "")

Here J holds "Yes" for outside work, G the status and N the date due back. A CHASE flag on Monday morning is a phone call to the supplier before the customer asks. You can have the AI draft the chaser from the row, in your own wording, such as "Our batch J-2417 went out on the 8th with a due date of the 12th. Please confirm it ships today and give me the carrier and tracking number." Keep chasers short and factual, and keep every reply in the Notes column so the history survives staff holidays.

A good habit is to note the supplier's quoted turnaround in the sheet at the time of sending and compare it with the real one. After a quarter, the data shows which outside supplier is consistently two days slower than quoted. Add that buffer to your promised dates instead of hoping.

A capacity view from the same data

The Operations tab already holds planned hours per work centre, so a small summary shows where the queue sits. In a new tab, list the work centres and total the planned hours still to run:

Hours waiting at a work centre:  =SUMIFS(Operations!E:E, Operations!D:D, A2, Operations!G:G, "<>Done")
Days of queue:                  =B2 / C2        (C2 = productive hours per day for that centre)

Say the CNC mill has 96 hours of operations not yet finished and runs one machine for six productive hours a day. That is 16 working days of queue. The lathe has 18 hours and three days of queue. A job needing the mill with a promise date nine working days away is late before it starts. Ask the AI to read the summary and say which promised dates cannot be met on the current queue, and show the arithmetic. It will make errors with long lists, so confirm the numbers it quotes against the sheet.

This is a rough picture, as it ignores set-up time, breakdowns and rush jobs that jump the queue. It is still better than a feeling, and it turns an argument about whose job is more urgent into a table. The same data feeds the weekly production schedule, so the queue figures here become that plan's starting point.

Three kinds of customer message, with wording to adapt

Different moments call for different updates. Keep three short templates in the sheet and tell the AI which to use for each job.

The routine progress note, sent on a fixed day for jobs due within ten days: "Hello [first name], quick update on PO [number]: [operations done] are complete, [next operation] is under way, and we are on track for [date]. Nothing needed from you."

The early warning, sent the day an at-risk flag appears, not after the date passes: "Hello [first name], PO [number] is at risk of missing [date] because [one-sentence cause]. We are [action]. I will confirm by [day] whether the date holds, and if you need it sooner for a reason, tell me and we will look at what can move."

The query for the customer, when the shop is the one waiting: "Hello [first name], we are ready to start [operation] on PO [number] but need your answer on [specific question] first. The job's date moves a day for each day we wait."

The third message is the one shops forget, and it protects you: it records that a delay began with the customer. Ask the AI to pick the right template from the flag and the Notes column, and keep a person's approval on each. The honest early warning builds more trust than a polished late apology does.

A quick sum: what the "where is my order" calls cost

Suppose the shop gets eight status calls a week, each taking about 12 minutes by the time someone finds the traveller, checks the machine and calls back. That is 96 minutes a week, about 83 hours a year. At a shop rate of $75 an hour the interruptions cost around $6,200 a year of time that could have been spent cutting metal, and the figure ignores the lost concentration. If routine updates go out on a fixed rhythm and cut the calls by half, the saving is roughly $3,100 a year in time alone, from a spreadsheet and an hour a week of review. These are illustrative numbers; measure your own calls for two weeks with a tally on a notepad before you plan around them.

Where the sheet breaks, and what each break looks like

• Two people edit one file. Saved copies diverge and nobody trusts either. Use a shared online sheet with version history, or give a single person edit rights on the Operations tab.
• Revisions change the routing. A part revision adds a heat-treat step after the job is released. Add the new operation row the same day, or the percent-done formula lies.
• Partial shipments. A job of 250 ships as 100 and 150. Split it into J-2417-A and J-2417-B rather than overwriting the status.
• Scrap and rework. Remakes are separate operations with a reason code. Without them the sheet shows a job on time that is secretly on its second attempt.
• The sheet is right and the floor is wrong. A traveller says "done" but the parts are still on the bench. Sign off operations at the point of hand-off to the next work centre, not at the close of the shift.

A morning when the update went out wrong

One illustrative case: the AI drafted a message saying a job was "on track" because the flag column read OK, while the notes column said the outside plater had called to say they were two days behind. The note had not been in the paste. The rule that followed: the export always includes the Notes column, and the prompt says "if notes contradict the flag, say so at the end". The lesson is that the AI is as complete as its input, and a short checking habit is better than a longer prompt.

Keeping the habit alive

A sheet nobody updates is worse than none, because people trust it. A few small routines keep it true:

1. A five-minute update at each shift change: whoever finishes an operation changes its status, from a list.
2. The owner or planner checks the "Waiting for more than 3 days" list every morning and speaks to the person involved.
3. Updates to customers go out on a fixed rhythm: Tuesdays for jobs with a due date in the next ten days, plus the same day for anything that slips.
4. Once a month, compare promised against actual dates for the last 30 jobs. If more than a quarter slipped, your promised dates are too tight, and no status tool will fix that.

That monthly comparison also tells you which work centre is the true bottleneck: the one whose operations most often sit at Not started or Waiting in the week before a slipped date.

When a spreadsheet is no longer enough

Move on when the same questions keep outrunning the sheet: you need barcode scans so nobody types, several shifts update at once, jobs must tie to purchase orders and invoices automatically, or lead times depend on a real capacity plan. At that point compare an MRP or job-shop system against your actual process, and read whether an MRP add-on or a chat tool over your own data suits you before paying for anything. Even then, the columns you built here are your requirements list, and the weeks of clean data make migration far easier. If you want help deciding which parts to automate first, that is the work of an AI implementation consultation.

WIP tracking questions from small job shops

Is a spreadsheet really enough for WIP tracking?

For a small shop with a few dozen open jobs and one or two people updating it, yes. The limits show when several people edit at once, when you need barcode scans from the floor, or when job costing has to tie to accounting. Until then a disciplined two-tab sheet beats an ERP nobody keeps up to date.

Should customers see the spreadsheet itself?

No. Send them a short written update drafted from it. The sheet holds internal notes, costs and other customers' jobs. An AI-drafted message that contains only the one customer's job, reviewed by a person before sending, keeps both the detail and the privacy right.

What if people on the floor will not update statuses?

Make the update smaller, not the rule stricter. One status change per operation, picked from a list, takes ten seconds at a tablet or a whiteboard photo at shift end. If the data is still late, update twice a day from the traveller paperwork yourself and fix the habit separately.

Can AI predict which jobs will be late?

It can flag jobs that look at risk from your own columns: due date close, operations left, a bottleneck work centre behind. It cannot see a broken machine or a missing material delivery unless you record them. Treat its list as a prompt for a quick floor conversation, not a forecast.

Further reads

• How to Use AI in a Machine Shop: Five Jobs Beyond Quoting — Four more places AI helps in a machine shop.
• Machine Shop Quote Template: AI Turns RFQ Emails Into Draft Quotes — Turn RFQ emails into draft quotes for the same shop.
• AI for Industrial Manufacturing: Read Downtime Logs to Find Repeat Stops — Find repeat stops in your downtime log.
• Outgrowing Spreadsheets: When to Replace Manual Excel With AI — Signs your spreadsheet has reached its limit.
• First Article Inspection Report: AI Formats the FAI, You Measure — Format first-article inspection reports faster.
• AI Email Triage for Shared Inboxes: Sales, Support, and Invoices — Sort status-request emails out of a shared inbox.
• AI Tools for Welding Shops: Quotes, Drawings and Job Scheduling — A welding shop owner's guide to AI on the office side: a drawing-based quote check, cut lists, a schedule stress test and where weld sign-off stays human.
• WIP Report Template for Service Firms: Build One From Timesheets — A WIP report for a service firm built from a timesheet export: ageing buckets, write-offs, roll-forward and lock-up days, with sample figures.
• Six AI Applications in Manufacturing That Need No New Machines — Six office-side AI jobs for a small factory, each with a worked example, a starting step and the check that keeps it honest.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: standard shop-floor practice for routings and travellers; no vendor pricing is used. Job figures in the worked example are illustrative.