# Medical Device Lot Reconciliation and Traceability Worksheet

> Source: https://netray.co/tools/medical-device-lot-reconciliation-and-genealogy-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Row-by-row lot reconciliation with yield and unaccounted-unit columns, plus component lot and sterilization lot links for genealogy. Exports to Excel.

## Category

quality

## Industry

Medical Devices

## Audience

- Quality engineer
- Production supervisor
- Document control
- Regulatory affairs

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Finished lot number

**Key:** lot

**Type:** text

#### Device part number

**Key:** part

**Type:** text

#### Critical component lot(s)

**Key:** comp_lot

**Type:** text

**Help:** Primary lot or the key lot for the highest-risk component.

#### Sterilization lot / load

**Key:** ster_lot

**Type:** text

#### Units started

**Key:** built

**Type:** number

**Format:** integer

#### Good units to stock

**Key:** good

**Type:** number

**Format:** integer

#### Scrapped

**Key:** scrap

**Type:** number

**Format:** integer

#### On rework or hold

**Key:** rework

**Type:** number

**Format:** integer

#### Samples and retains

**Key:** sample

**Type:** number

**Format:** integer

#### Unaccounted units

**Key:** unacc

**Type:** number

**Expr:** built - good - scrap - rework - sample

**Format:** integer

**Help:** Must be zero before DHR sign-off.

#### Investigate flag (1 = yes)

**Key:** flag

**Type:** number

**Expr:** if(eq(built - good - scrap - rework - sample, 0), 0, 1)

**Format:** integer

#### Yield to stock

**Key:** yield

**Type:** number

**Expr:** if(gt(built, 0), good / built * 100, 0)

**Format:** percent

#### Manufacture date

**Key:** mfg_date

**Type:** date

**Rows:**

**Lot:** L260914A

**Part:** CAT-210-S

**Comp lot:** TUB-88231

**Ster lot:** EO-26-0412

**Built:** 1200

**Good:** 1118

**Scrap:** 41

**Rework:** 0

**Sample:** 41

**Mfg date:** 2026-09-14

**Lot:** L260916B

**Part:** CAT-210-S

**Comp lot:** TUB-88231

**Ster lot:** EO-26-0412

**Built:** 1200

**Good:** 1127

**Scrap:** 32

**Rework:** 0

**Sample:** 41

**Mfg date:** 2026-09-16

**Lot:** L260921A

**Part:** CAT-210-M

**Comp lot:** TUB-88240

**Ster lot:** EO-26-0415

**Built:** 900

**Good:** 842

**Scrap:** 28

**Rework:** 0

**Sample:** 30

**Mfg date:** 2026-09-21

**Lot:** L260923C

**Part:** CAT-210-M

**Comp lot:** TUB-88240

**Ster lot:** EO-26-0415

**Built:** 900

**Good:** 820

**Scrap:** 35

**Rework:** 12

**Sample:** 30

**Mfg date:** 2026-09-23

**Lot:** L260928A

**Part:** CAT-230-L

**Comp lot:** TUB-88262

**Ster lot:** EO-26-0419

**Built:** 600

**Good:** 561

**Scrap:** 18

**Rework:** 0

**Sample:** 20

**Mfg date:** 2026-09-28

**Lot:** L260930B

**Part:** CAT-230-L

**Comp lot:** TUB-88262

**Ster lot:** EO-26-0419

**Built:** 600

**Good:** 558

**Scrap:** 20

**Rework:** 0

**Sample:** 20

**Mfg date:** 2026-09-30

**Totals:**

#### Total units started

**Key:** built

**Agg:** sum

**Format:** integer

#### Total good units

**Key:** good

**Agg:** sum

**Format:** integer

#### Total scrapped

**Key:** scrap

**Agg:** sum

**Format:** integer

#### Total unaccounted units

**Key:** unacc

**Agg:** sum

**Format:** integer

#### Lots to investigate

**Key:** flag

**Agg:** sum

**Format:** integer

**Summary:**

#### Overall yield to stock

**Key:** overall_yield

**Expr:** if(gt(sum_built, 0), sum_good / sum_built * 100, 0)

**Format:** percent

**Primary:** true

#### Overall scrap rate

**Key:** scrap_rate

**Expr:** if(gt(sum_built, 0), sum_scrap / sum_built * 100, 0)

**Format:** percent

#### Unaccounted rate

**Key:** unacc_rate

**Expr:** if(gt(sum_built, 0), sum_unacc / sum_built * 100, 0)

**Format:** percent

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the worksheet

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** If your lot, component and sterilization data live in your ERP or MES, we can generate this reconciliation automatically for every lot at release.

**Faq:**

#### What must a device history record show about quantity?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

lot traceability worksheet, lot reconciliation form medical device, device lot genealogy template excel, material reconciliation batch record, UDI lot traceability log, ISO 13485 traceability record, component lot to finished lot traceability, unit reconciliation yield worksheet

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json