GMP Batch Material Reconciliation Worksheet
Reconciliation compares what left the warehouse with what was used, returned, rejected or sampled. Any gap is an unaccounted quantity that needs an explanation. This worksheet does the arithmetic per line and shows the lowest reconciliation so reviewers spot the weak line quickly.
| Material | UoM | Issued | Used in batch | Returned to store | Rejected / destroyed | QC / retention samples | Unaccounted | Reconciled | |
|---|---|---|---|---|---|---|---|---|---|
-0 | 100% | ||||||||
-0 | 100% | ||||||||
0 | 100% | ||||||||
0.5 | 99.6% | ||||||||
0 | 100% |
Set the acceptance limits for each material in your approved batch record. Printed materials normally need exact reconciliation (100 percent) with any difference investigated. Bulk materials are compared with a validated yield range. Units differ per line, so do not total different UoMs.
Automate reconciliation from ERP data
Want issue, return and consumption data pulled into this sheet automatically? Ask for an expert review.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Enter one row per material or printed component. Type issued, used, returned, rejected or destroyed, and sampled quantities from the batch record and stores return notes. The sheet computes the unaccounted quantity and the reconciled percent. Anything not at the expected value goes into a deviation. Export to Excel to attach to the batch record review or to paste into your own template.
- Use the same unit of measure for issued and used on each row
- Count rejected and destroyed printed materials with a witness
- Include QC samples and retention samples drawn during processing
The method and the limits
Unaccounted equals issued minus used, returned, rejected and sampled. Reconciled percent equals the accounted sum divided by issued. For labels, cartons and leaflets most quality systems require a full reconciliation because a stray label can cause a mix up, so a single missing unit triggers an investigation. For bulk powders and liquids, yield limits are set from process validation data and are usually a range rather than a single figure.
Reading the result and ERP notes
In the example the leaflet and carton lines each show 100 percent reconciled, so they pass. A shortfall on the foil line would show below 100 and need review. In SAP, process order goods issues and goods receipts, in Oracle process manufacturing batch transactions and in SyteLine job material transactions give you the issued and returned figures, but destroyed and sampled quantities are often still recorded on paper, which is why the reconciliation step is manual.
- Compare against the ERP goods issue before trusting the paper figure
- Do not round unaccounted quantities to zero
Frequently Asked Questions
What reconciliation limit should I use for printed packaging?
Many sites use exact reconciliation, meaning issued equals used plus returned plus destroyed. Any difference is investigated. Your own SOP and regulator expectations set the final rule, so write the limit into the batch record and follow it.
Is reconciliation the same as yield?
No. Yield compares actual output with theoretical output of the process. Reconciliation accounts for every unit of a material that was issued to the batch. Both are normally reviewed, and a good yield does not excuse a failed reconciliation of labels.
Where do the returned quantities come from?
From the warehouse return document signed at the end of the batch, and from the line records for materials returned to store. Check the returned material is still within its status and re-weighed or re-counted as per procedure.
Can I keep this worksheet instead of a paper batch record page?
Only if the electronic record is controlled and meets your data integrity rules, including audit trail and signatures. Otherwise print it, sign it and attach it to the batch documents.
If your reconciliation depends on pulling issue and return transactions out of the ERP by hand, Netray can help automate the extraction and show differences for reviewer approval.
Related Tools
Pharma Line Clearance Record Template
A fill-in line clearance record for the start of a batch or a product changeover, with room for the QA verification signature.
Quality & InspectionGMP Annual Product Quality Review Outline Template
A report outline for the annual product quality review with batch data, deviations, changes, complaints and conclusions.
Quality & InspectionPharma Retention Sample Quantity Calculator
Estimate retention sample units per batch, storage years and total samples held for a product, based on test quantity and shelf life.
Compliance & ContractsGMP SOP Periodic Review Tracker Worksheet
An SOP register with owner, review interval, days since last review and an overdue flag, ready to export to Excel.
More Quality & Inspection tools
See all- Cleanroom Environmental Monitoring Log Worksheet
- Equipment IQ OQ PQ Protocol Template
- SAP QM Process Capability Cp Cpk Calculator
- SAP QM Lot Rejection Rate, PPM and Sigma Level Calculator
- SAP QM Gauge R&R Study Calculator
- SAP QM Cp and Cpk Capability Calculator
- Job Shop Material Certification Log Worksheet
- Job Shop First Piece Inspection Sign-Off Template
- Job Shop Scrap and Rework Cost Calculator