Raw Material Lot Release and Retest Tracker Worksheet
Material that is not yet released, past its retest date or rejected must never reach a batch. This tracker keeps lot status, retest dates and stock value visible in one place, ready to export.
| Lot number | Material | Supplier | Received date | Retest / expiry date | Status | Qty on hand (kg) | Cost per kg | Stock value | Location | |
|---|---|---|---|---|---|---|---|---|---|---|
$3,680 | ||||||||||
$2,997 | ||||||||||
$1,050 | ||||||||||
$2,016 | ||||||||||
$1,040 |
Illustrative data. Retest periods come from supplier specifications and your own stability data. Keep quarantined and rejected lots physically segregated and mirrored in your ERP lot status.
Retest alerts from ERP lot data
Optional: discuss automatic retest and expiry alerts built from your ERP.
Optional. The tool and every export work without this. No spam.
How to use the tracker
Add each incoming lot as soon as it is received and set its status to quarantine. Update to released after the certificate of analysis has been reviewed and any incoming tests pass. Enter the retest or expiry date from the supplier label or your own policy. The sheet calculates stock value from quantity and unit cost. Export to Excel and sort by retest date to plan retesting before material becomes unusable.
Retest versus expiry
An expiry date means the material should not be used after that date. A retest date means the material may still be usable if it passes testing, after which a new retest date is assigned. Many chemical raw materials have retest dates, not hard expiry. Use the status field to separate the two situations and record the test used for the retest.
- Quarantine: received, not yet approved
- Released: approved for use in production
- Retest pending: date reached, awaiting result
- Rejected: failed, awaiting return or disposal
- Expired: past date and not retested
Reading the totals
The stock value total helps finance understand what is tied up in quarantine, retest pending or rejected status. A growing value in rejected or expired material points to supplier quality problems or over-ordering. Compare the quantity on hand with your ERP on-hand balance during cycle counts to find lots that moved without being recorded.
Common mistakes
Typical failures are using material while its status is still quarantine, missing a retest date because the label was not scanned, mixing lots in a tank without recording both, and not blocking rejected stock in the ERP. Enforce status through the ERP's lot status or inspection lot functions rather than relying on this sheet alone.
Frequently Asked Questions
What is the difference between quarantine and rejected?
Quarantine is a temporary hold pending review or testing. Rejected means the material failed and must be returned, reworked under a deviation or disposed of. Keep them in separate locations.
How do I set a retest date?
Use the supplier's stated retest period, or your own stability data and policy. Count from the manufacture date unless the supplier specifies otherwise, and record the basis.
Can I use expired material?
Generally not without a documented assessment, retest and approval. Many quality systems require a deviation. Never use material past expiry simply because stock is short.
Should this replace ERP lot control?
No. Use it as a quick register or a staging sheet. The authoritative status should live in your ERP or warehouse system so transactions are blocked automatically.
Netray can help build alerts from ERP lot data that warn planners before retest dates are reached.
Related Tools
Chemical Certificate of Analysis (CoA) Template
A certificate of analysis layout with lot data, test results versus specification, method references, shelf life and a release signature.
Industry TemplatesChemical Batch Manufacturing Record Template
A fill-in batch record with raw material charges, process steps, in-process checks, yield and review signatures. Exports to PDF, Word or Excel.
Industry TemplatesChemical Batch Yield and Mass Balance Calculator
Calculates theoretical yield from the limiting reagent, actual yield percent corrected for purity, and the unaccounted mass in a batch.
Quality & InspectionChemical Process Deviation Investigation Report Template
A structured deviation report: what happened, immediate containment, product impact, root cause, corrective actions and closure approvals.
More Supply Chain & Inventory tools
See all- Oracle Fusion Inventory Turns and Carrying Cost Calculator
- Distribution ERP Fill Rate Calculator
- Distributor Rebate Tracking Worksheet
- Branch Transfer Planning Worksheet
- Distributor Slow-Moving Inventory Review Worksheet
- Distributor Vendor Performance Scorecard Worksheet
- Distributor GMROI Calculator
- Distributor Backorder Cost and Fill Rate Calculator
- EOQ with Vendor Minimum Order Calculator
Keep exploring
Free tools
- Chemical Batch Manufacturing Record TemplateA fill-in batch record with raw material charges, process steps, in-process checks, yield and review signatures. Exports to PDF, Word or Excel.
- Epicor BPM Directive Inventory WorksheetInventory your BPM directives, score complexity and retest effort, and see total upgrade retest hours. Export to Excel.
- Raw Material Index Price Adjustment Calculator (China Suppliers)Calculate the new unit price and annual cost impact when copper, steel or resin indexes move, using a deadband and a pass-through share.
ERP fixes and how-tos
- Acumatica Error: Inventory Quantity Will Go NegativeAcumatica says inventory quantity will go negative on an issue or shipment? Find the cause, check lot, location and warehouse, and fix the data or the setting.
- NetSuite Quality Management: Incoming Inspection and QuarantineNetSuite has no full QMS, but inventory status, workflows and custom records give incoming inspection, quarantine and NCR tracking. Setup steps and limits.
- How to release (and cancel) an inspection lot in SAP QMRelease an inspection lot in SAP QM with QA02, understand CRTD and REL status, and learn when cancelling a lot is blocked and what to reverse first.
Guides
- SyteLine Quality Hold and Release Process ConfigurationConfigure SyteLine quality hold and release processes for inventory control. Set up hold reasons, release workflows, and quarantine locations in CloudSuite Industrial.
- 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.
- SyteLine Lot Tracking Configuration GuideConfigure lot tracking in Infor SyteLine with step-by-step guidance on item setup, lot attributes, and traceability workflows for CloudSuite Industrial.
AI on your ERP
- AI for ERP in Chemicals and Batch Process ManufacturingAI on the ERP of a chemicals or batch process plant: formula and yield analysis, SDS and REACH lookups, lot traceability. Private, read-only, on-prem option.
- AI for JD Edwards Process Manufacturing: Formulas, Lots, and Yields in Plain EnglishAI for JD Edwards EnterpriseOne process manufacturing: formulas, lots, yields, co-products and batch costing, answered from live data without leaving your network.