How we traced a broken lot code on a signed shipping record to one empty cell
We read a food maker’s scanned records field by field and followed one order from the workbook to the filed shipping record.
- Excel
Excel
- Client
- North American chilled food manufacturer and co-packer
- Documents
- Shipping records, receiving logs, supplier invoices and a production workbook
- What we built
- Two independent readings of the records, compared field by field
- What they get
- Checked values traced from workbook to shipping record
- Standard
- Certified to a global food safety standard
=TEXT(date, "yyyy-mm-dd")Shipping record signed and filed
- Made18/0319/03
- Packed18/0319/03
- Shipped18/0319/03
- Delivery18/0319/03
- Use by25/0326/03
- Lot code1900-01-00
Found: an empty cell, printed as a lot code
Two ways of reading every page.
We compared the readings field by field and checked each disagreement against the original page.
of 12 disagreements settled in favour of the picture3 of the 12 shown above
- 12scanner text errors caught by reading the page image
- 40findings audited against the originals
- 260fields read and checked on the shipping records
- 20formula problems found in a 3,000-cell workbook
“If we don’t print it, we don’t need to scan it, and we don’t need to save it.”
Read the full story
A chilled food manufacturer ran on one spreadsheet, printouts and pen. We read its shipping, receiving and production records two independent ways, field by field, and found a signed shipping record whose lot code pointed to no batch at all.
An order had to be typed into two systems.
The whole business ran through one Excel workbook and a lot of paper.
Orders arrive several times a day, each with up to 20 lines. Every one is typed into the workbook, then again into the accounts software. Production sheets get printed, filled in by hand, scanned and filed. A 20-item invoice means 20 manual entries.
Tracing a batch meant working back through paper records. When a chicken recall came through, the team had to find which products had used the affected batch.
“We’re printing, entering, printing, scanning and saving. It takes a long time.”
Two ways of reading every page.
We started with the records that matter most in a recall: signed shipping records, receiving paperwork, supplier invoices and the master production workbook.
We read the records two independent ways. First, we read each scanned page as a picture, enlarging handwritten or faint entries. Second, our code extracted the text stored in each PDF and every cell and formula in the spreadsheets.
We compared the readings field by field. Each disagreement was checked against the original page at up to 16 times zoom. We also checked a selection of the values the two readings agreed on.
On the scanned shipping records the two readings disagreed 12 times, and the picture reading was right every time. The scanner’s text had things like 1.2 where the page clearly says 12. Every value we double-checked held up, and on the spreadsheets the two readings matched on every cell.
The lot code that pointed at nothing.
Then we followed a single order from the workbook all the way to the filed shipping record.
On a signed shipping record for an order of over 1,300 units, the lot code read 1900-01-00. Staff had corrected five broken dates on that page in pen. They missed the lot code. The record was sent, signed, scanned and filed.
We traced the cause through the workbook’s formulas. That customer’s production date sits in a different cell from the other customers’, and on that day it was empty. Excel reads an empty date as day zero of 1900, and printed that as the lot code.
A lot code is how you find product in a recall. This one pointed at nothing.
Twenty formula problems in the production workbook.
We found 20 formula problems in the master workbook’s 3,000 cells.
One formula understated how much of each ingredient was needed across the whole production plan.
Before reporting the findings, we audited 40 against the originals. Each came with the page, cell or formula behind it for the manufacturer’s team to check. The team remained responsible for approving any changes to its production and shipping records.
Trace an order through your records.
Send us a day’s worth of your paperwork. We’ll show you what it says, and what it gets wrong.