Medical Device Lot Reconciliation and Traceability Worksheet
Every device lot should reconcile: units started equal units shipped, scrapped, held, consumed in testing or retained. If the numbers do not close, you either have a counting error or a missing unit, and either one is a finding. This worksheet does the arithmetic and links each lot to the component and sterilization lots that make recalls and investigations fast.
| Finished lot number | Device part number | Critical component lot(s) | Sterilization lot / load | Units started | Good units to stock | Scrapped | On rework or hold | Samples and retains | Unaccounted units | Investigate flag (1 = yes) | Yield to stock | Manufacture date | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 93.2% | |||||||||||
0 | 0 | 93.9% | |||||||||||
0 | 0 | 93.6% | |||||||||||
3 | 1 | 91.1% | |||||||||||
1 | 1 | 93.5% | |||||||||||
2 | 1 | 93% |
Example rows are illustrative. Two lots in the sample data have unaccounted units to show how the flag works. Reconcile to zero or document the explanation in the DHR before release. Keep the sheet under document control if it forms part of a quality record.
Generate lot reconciliation from your ERP
Optional: we can build a lot reconciliation and genealogy report straight from your work orders and inventory transactions.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Add one row per finished lot. Record the units started, good units moved to stock, scrapped, on rework or hold, and samples or retains. The unaccounted column calculates automatically, and the flag column shows 1 when it is not zero. Record the key component lot and the sterilization load so you can trace in both directions. Export to Excel for attachment to the device history record, or keep the sheet as a working log during production.
- Close every lot to zero unaccounted units before release
- Record rework quantities even if they return to stock later
- Use the same lot format as your labels and UDI production identifier
- Add rows for multiple component lots if the genealogy needs them
Why reconciliation and traceability matter
ISO 13485 clause 7.5.9 requires documented procedures for traceability, and for implantable devices requires records of components, materials and the work environment conditions that could cause the device to not meet its requirements. Clause 7.5.1 requires a record for each medical device or batch that identifies the quantity manufactured and approved for distribution. Reconciliation is the control that proves those two numbers are credible. UDI regulation also expects the production identifier, usually lot or batch, on the label and in the records.
Interpreting the flags and yield
A non-zero unaccounted count means units were miscounted, mislabeled, lost, or moved without a transaction. Investigate before the lot is released, because a missing unit in a sterile device lot may be a unit in the wrong package. A yield that drifts down lot over lot is an early warning for a tooling or material issue, and should feed your trending. Compare the overall yield with the planning factor used in your ERP. If the sheet shows 93 percent and your planning factor assumes 97, expect short shipments.
Common mistakes
Teams often record sample quantities only on the test record, so the lot never closes. Another is to adjust the good count to force reconciliation, which hides the real loss and defeats the purpose. Rework quantities that return to stock should be added back through a documented transaction with a new inspection. Finally, link the sterilization load and the component lots on the same row, or a recall scope question will take days rather than minutes.
Frequently Asked Questions
What must a device history record show about quantity?
Under 21 CFR 820.184 and ISO 13485 clause 7.5.1, the record identifies the dates of manufacture, quantity manufactured and quantity released for distribution, along with acceptance records and labeling. A reconciliation like this one supports those quantities.
How exact does the reconciliation need to be?
For finished devices, it should close to the unit. Tolerances, if allowed at all, should be defined in a procedure and justified by risk. Bulk materials such as resin or adhesive can be reconciled by weight with a stated tolerance.
Do I need component lot numbers for every device?
Not necessarily. The required depth depends on the device risk class and your procedure. Implantables require more extensive traceability. Record the lots of components that affect safety or performance, and any your customer or regulator specifies.
Can I use this as the DHR?
It supports the DHR but does not replace it. The DHR also includes acceptance records, the primary identification label and labeling, and device identification and control numbers. Attach the reconciliation to the record.
If your lot, component and sterilization data live in your ERP or MES, we can generate this reconciliation automatically for every lot at release.
Related Tools
Device History Record Review and Lot Release Checklist
A pre-release checklist for the person who signs off a device lot: verifies identification, quantities, materials, process records, acceptance activities, sterilization, labeling and open deviations.
MES & Shop FloorMedical Device Lot Start Quantity and Rolled Yield Calculator
Multiplies step yields and sterilization loss into a rolled yield, then solves for the start quantity needed to ship the order plus retains and an overage cushion.
Quality & InspectionDevice History Record (DHR) Template
Build a device history record for one lot or serial number: quantities, dates, labeling, acceptance and release sign-off. Exports to PDF or Word.
Developer ToolsDevice Lot Genealogy and Traceability SQL Generator
Build recursive lot genealogy queries for backward trace (finished device to components) and forward trace (component lot to shipped devices) using your own table and column names.
Compliance & ContractsUDI Label Data Field Reference
A searchable table of UDI label elements: device identifier, production identifier, GS1 application identifiers, formats and label notes.
More Quality & Inspection tools
See all- CAPA Corrective and Preventive Action Form Template
- ISO 13485 Internal Audit Checklist
- Process Validation IQ OQ PQ Template
- Medical Device Complaint Handling Form Template
- ISO 14971 Risk Management Worksheet
- Sterilization Load Record Template
- Medical Device Design Verification Protocol Template
- Medical Device Supplier Evaluation Scoring Worksheet
- Medical Device Cleanroom Environmental Monitoring Log
Keep exploring
Free tools
- Device Lot Genealogy and Traceability SQL GeneratorBuild recursive lot genealogy queries for backward trace (finished device to components) and forward trace (component lot to shipped devices) using your own table and column names.
- Medical Device Lot Start Quantity and Rolled Yield CalculatorMultiplies step yields and sterilization loss into a rolled yield, then solves for the start quantity needed to ship the order plus retains and an overage cushion.
- Device History Record Review and Lot Release ChecklistA pre-release checklist for the person who signs off a device lot: verifies identification, quantities, materials, process records, acceptance activities, sterilization, labeling and open deviations.
ERP fixes and how-tos
- How to Link a CAPA to ERP Lots and Serial Numbers for Containment and EffectivenessUse ERP lot and serial genealogy to scope containment for a CAPA, record affected jobs and shipments, and verify effectiveness with real data instead of opinion.
- How to Set Up Lot and Serial Number Tracking in SyteLineTurn on lot or serial tracking for a SyteLine item, set the numbering mask, and assign lot/serial numbers correctly on receipts, job moves, and shipments.
Guides
- Lot Traceability for a Medical Device Contract ManufacturerSet up lot traceability in your ERP for medical device work: material certs, forward and backward genealogy, recall drills, and what auditors ask to see.
- Using AI to Review Device History Records Before Lot ReleaseHow a medical device contract manufacturer can use private AI to pre-check device history records for missing entries, cert mismatches and out-of-spec data.
- How to Quote Medical Device Contract Manufacturing WorkHow medical device contract manufacturers build a quote: materials with certs, cleanroom labor, validation, inspection, sterilization handoffs and NRE.
AI on your ERP
- AI on Infor SyteLine for Medical Device Manufacturers: Genealogy, DHR and Audit EvidencePrivate AI on Infor SyteLine for medical device makers: lot and serial genealogy, DHR assembly, UDI and QMSR evidence, with human approval on every write.
- AI for Critical Manufacturing MES in Semiconductor, Electronics and Medical Device PlantsPrivate AI for Critical Manufacturing MES: lots, equipment, material and genealogy questions answered in plain English, with ERP context and on-prem models.