Work orders vs spreadsheets: where the spreadsheet fails

The production spreadsheet is not a bad idea. It is the right tool for a shop with one product, one builder and one job at a time, and most manufacturers started with one. It fails at five specific points, each of them the day the shop grows past that shape, and each of them quietly. A work order exists to hold at exactly those five points.

Reading this for your own shop? Start a free 14-day trial — no credit card, and your data is yours to export at any time.

1.The short answer

A spreadsheet row describes a job. A work order changes stock: on release it reserves the components, on issue it moves them into work in process, on completion it brings finished goods into stock at their cost and consumes what built them. Everything the spreadsheet cannot do follows from that one difference — the sheet says what should happen, and someone still has to make the inventory agree, and in a busy week nobody does.

2.Two jobs, one bin of parts

The sheet says Job 41 needs 200 brackets and Job 42 needs 150. The rack holds 300. Both jobs look fine, because each row was checked against the same 300 on its own, and the second person to reach the rack finds 100.

A work order allocates its components when it is released. Available stock is what is on hand minus what released work orders and confirmed sales orders have already claimed, so Job 42's release sees 100 brackets available, not 300 — and shows up as a task, "needs purchase orders for components", before anyone walks to the rack. The allocation is released as each work order consumes it, so it never double-counts against the job that holds it.

3.A batch that finishes in halves

The sheet has one row for 100 units, with a "done" column. Reality is that 40 were finished Tuesday and the truck for the customer who wanted 40 left Wednesday morning; the other 60 are Thursday's. The row cannot say that, so either the 40 ship against a job that is still "open" and stock is wrong all week, or someone splits the row and the cost of the batch is now in two places.

A work order takes partial completions: 40 today, 60 Thursday. Each completion brings that many finished units into stock at the job's per-unit cost, consumes their share of components, and adds to the completed quantity; the order closes when the total is reached. The 40 are sellable stock on Tuesday afternoon, and the batch's cost is one number.

4.Consumption that never gets recorded

This is the failure that costs the most and shows up last. Finished goods get added to stock when a job is done, because there is a sale waiting. The components that made them rarely get subtracted, because nothing is waiting on that, and in a week the numbers look plausible anyway. Six months later the annual count is short by exactly the components of everything built since the last one, and the write-off has no explanation.

A work order backflushes: when units are reported complete, the components the bill of materials says they took — scrap allowance included — are consumed at that moment, at the cost the job was released with. Components that were issued deliberately earlier are not consumed twice; anything used beyond the bill is recorded as an extra issue, which is exactly the number the job costing report wants to see. Completion is never blocked for want of a component record, because blocking it is how people learn to bypass the system; a shortfall becomes a visible deficit to resolve, not a reason to stop reporting. And a cancelled job returns whatever it had drawn into work in process back to stock, so a job that did not happen leaves no hole.

5.The bill of materials changed under the job

Engineering swaps the bracket for a heavier one on Wednesday. The sheet references the BOM tab, which now says the heavy bracket, so Monday's half-built job is costed and consumed against a part it never used — and the record of what last month's units were built to is gone, because the tab has only one version.

A work order snapshots the active bill of materials at release: components, quantities, scrap rates, routing steps, instructions and the rolled cost, scaled to the planned quantity. The job works from its snapshot to the end; the next job released picks up the new version. That is what makes a BOM version meaningful: it is the line between jobs, not a note on a tab.

6.Nobody can say what was built from what

A customer reports a failure in a unit shipped in March and asks which lot of steel it came from and who else got units from that lot. The sheet has "Job 38, 100 units, March". It does not have which sheets, which brackets, or which serial numbers went to which customer.

A work order records its issues by lot, its completions by serial where the item is serialized, and the genealogy of what went into what — so the question can be answered forward to every customer who received a unit from that lot and backward to the supplier the lot came from. That is a subject of its own, covered in the lot traceability guide.

7.What the person on the floor sees

None of this survives if it makes the builder's day harder. On the floor, a released work order opens as a step-by-step view: one routing step at a time, with the instructions and the reference image the BOM carries, big buttons to clock on and off, log progress against the step, and scan the components being used. Work orders are assigned to a person, and one person's assignments cannot overlap, so the schedule on the wall and the list on the tablet are the same list. After the last unit is complete, the actual time is confirmed once, and the job's real labor and overhead replace the estimate — the cost rollup guide follows that through.

8.And the books

Because a work order moves stock, it produces the entries the accountant needs without a month-end reconstruction: material issued leaves inventory for work in process, and completed units come back into inventory at cost. If QuickBooks is the ledger, the bill of materials guide covers where those movements go and what each QuickBooks edition can hold of them.

9.When the spreadsheet is still right

Honestly: one product at a time, one person building it, parts bought for the job, builds that finish the day they start, no one asking what a batch cost, no customer who will ever ask what went into a unit. That is a real shop and it does not need work orders yet. The moment two jobs are open at once, a batch finishes in halves, or somebody has to answer for stock or traceability, it does — and the sheet will not say so, because a sheet is right about whatever was typed into it.

10.Questions people ask

Do I have to issue components by hand before I can complete a job?

No. Issue deliberately where you want the control — expensive parts, kitted jobs — and let backflush handle the rest at completion. The two coexist on the same order without double-counting.

What if we build without a bill of materials sometimes?

An ad-hoc work order with no BOM releases with no components and no rolled cost; it tracks the job and the completions, and consumes nothing automatically. Useful for rework and one-offs, and a sign, if it is happening often, that the product needs a BOM.

Can a work order be cancelled once material has been issued?

Yes, from any state short of completed; the issued material is returned from work in process to stock so the cancelled job leaves the inventory as it found it.

How is this different from the sales order?

The sales order is the promise to a customer; the work order is the instruction to the floor. A sales order for a product you make and do not have in stock raises a task to create the work order, and the two stay linked so the unit that was built for that order is the one that ships against it.