Batch and Lot Tracking Without the Spreadsheet Chaos

A supplier calls about batch 4471. You need to establish how much arrived, where the remaining stock is and which customers received goods from it.

The receipt is in one spreadsheet. Picking details are in another. The sales record shows the product and quantity, but not the lot that left the warehouse.

You have records of the individual activities. The connection between them is missing.

Batch and lot tracking preserves the identity of a group of stock as it moves through the business. Receipts, storage movements, production use, dispatch and returns need enough detail to follow that group forward and backward.

A spreadsheet can hold those records. The difficulty is maintaining their relationships consistently as work changes and volume grows. A connected inventory process can help, provided the physical identification, transaction rules and checks support the trace.

This guide explains where the chain breaks, how expiry-aware picking differs from receipt-date rotation and what to test before relying on a traceability report.

Traceability depends on preserving the lot identity through the movements that change its location, quantity and status. Illustration from OpsMavix.

Key Takeaways

• Recording a batch at goods-in is only the beginning. The identity needs to remain linked to subsequent movements and shipments.
• Batch tracking groups stock. Identifying a particular individual unit may require serialisation or another unit-level identifier.
• Forward tracing links a lot to its movements and recipients; backward tracing links a relevant item or shipment to its origin.
• FEFO prioritises eligible stock with the earliest expiry. It is different from choosing the oldest receipt.
• Stock holds, returns, damaged goods and manufacturing transformations need explicit handling.
• Connected software supports the chain but does not guarantee complete evidence or establish the scope of a recall by itself.
• Test traceability with a representative exercise and reconcile quantities before relying on the reports.

In this guide:

• What batch and lot tracking means
• Where spreadsheets lose the connection
• Why investigations become slow
• Expiry and FEFO picking
• Tracing backward to the source
• A worked lot reconciliation
• What reliable tracking should include
• Spreadsheets versus connected lot records
• How to prove a first workflow
• Frequently asked questions
• How OpsMavix can help

1. What Batch and Lot Tracking Actually Means

A batch or lot identifier associates stock with a defined group. That group might relate to a production run or a supplier’s lot, depending on the business and product.

The terms are often used interchangeably, but definitions and grouping rules can vary. Agree what each identifier means in your process.

A product code identifies what the goods are. A lot identifies which group of those goods the record concerns.

A lot identifier needs context

Two suppliers may use the same lot number. A code may also repeat across different products.

Preserve the relationship with the relevant product, supplier or producer and supporting receipt information. Do not assume a printed number is globally unique unless the identification scheme establishes that.

Group-level and unit-level tracking are different

A lot-level record can establish that ten units from a particular group were shipped to a customer. It may not distinguish each of those ten units individually.

If the requirement is to identify a specific individual item, assess serial tracking or another suitable identifier.

GS1’s global traceability standard describes identification and the associated event data needed to follow goods through supply-chain activities.

2. The Batch Stops Travelling With the Stock

A common gap starts after receiving. The team records the supplier’s lot, then later picks against a plain product quantity.

The receipt says 100 units of lot 4471 arrived. The shipment says 20 units of the product left. Without the relationship between those records, you cannot reliably determine which lot was dispatched.

Each movement needs the relevant identity

Transfers between shelves, partial picks and shipments should preserve the product, lot and quantity involved.

If a movement includes two lots, record the quantity from each. A single note saying “mixed batch” is not enough to explain the allocation later.

The physical identification also needs to survive handling. Repacking, damaged labels and shared storage locations can separate the goods from the recorded identity.

Spreadsheets can record the detail, but maintaining it takes work

A controlled spreadsheet process may be suitable for a limited, stable workflow. It needs consistent identifiers, complete entries and a way to check the related totals.

As movements multiply, keeping receipt, location and shipment records aligned becomes more demanding. A formula joining files does not establish that the physical lot was recorded correctly at picking.

Assess missed links and reconciliation effort rather than assuming the file format alone determines reliability.

3. Recalls and Quality Investigations Expose the Gaps

When a supplier or customer raises a quality issue, the relevant records need to be retrievable and explainable.

A useful lot report can help establish received quantities, current locations, stock status and shipments. Missing links create uncertainty about which goods the records actually describe.

Prepare the facts the team will need

An investigation may require:

• The product and lot identity under review.
• Supplier, receipt and supporting-document references.
• Quantities received and subsequent movements.
• Stock currently held, including goods on hold or in transit.
• Dispatch dates, shipments and recipients.
• Production use and related output lots where applicable.
• Returns, disposals, adjustments and unresolved differences.

The traceability record supports decisions. It does not by itself determine that only one lot is affected or that other goods are safe.

Show uncertainty instead of hiding it

If a shipment is missing its lot allocation, mark that gap for investigation. Do not fill it with the lot you assume was picked unless evidence supports a controlled correction.

A report that presents inferred information as confirmed can create misplaced confidence.

The response to an actual issue depends on the facts, product and applicable procedures. A connected system should make those facts easier to establish, including where the evidence remains incomplete.

Rehearse retrieval before an urgent event

Run a representative trace exercise. Select a lot, retrieve the movements and recipients, then reconcile the totals with the source records.

Measure how long it takes and which information requires manual reconstruction. Test the team and process as well as the software search.

4. Expiry Dates Need Picking Rules

Lot identity and expiry are related but distinct. A product may require lot tracking without a shelf-life date. A shelf-life product needs the relevant dates and rules recorded accurately.

First-expiry-first-out, or FEFO, prioritises eligible stock with the earliest expiry. FIFO instead prioritises stock according to the defined entry sequence, commonly receipt timing.

The first received lot is not necessarily the first to expire.

A simple FEFO example

Lot B arrived later but expires sooner. A receipt-date rule and an expiry-date rule can therefore select different stock.

Odoo’s FEFO documentation provides a software example of expiry-aware lot selection. Confirm the particular dates and settings used by your chosen platform.

Eligibility comes before rotation

A lot on quality hold should not become available merely because it expires first.

Define how the system handles expired stock, missing dates, holds and customer requirements such as minimum remaining shelf life. A selection rule needs to consider the stock eligible for that order.

An expiry date in a field does not automatically establish a dispatch block. Test whether the application warns, prevents release or follows another configured procedure.

Monitor exposure before stock reaches its limit

Review stock approaching its relevant dates and the commitments against it. Give the team enough information to investigate slow-moving quantities.

Expiry-aware picking can improve rotation. It cannot ensure that demand consumes every unit before expiry.

Rotation needs the lot, dates, physical identity and stock status to remain consistent at picking.

5. You Need to Trace Backward Too

Forward tracing starts with the lot and follows its movements and destinations. Backward tracing starts with an identified shipment or item and seeks its origin.

A customer complaint is easier to investigate when the shipment records show the product and lots supplied.

A returned item needs evidence of identity

A readable lot label, serial reference or linked original shipment may help establish the source.

If the item has no readable identity and the order contained several lots, the source may remain uncertain. Software should preserve that uncertainty rather than promise an instant answer.

Returns should enter the appropriate inspection or hold process. A refund is not evidence that goods have been received or are fit to re-enter sellable stock.

Manufacturing extends the trace

If materials from several lots are consumed to produce an output batch, record the relationship between the input lots, production record and output identity.

Splits, mixing, rework and repacking need defined treatment. A simple finished-goods receipt and dispatch tracker may not cover those transformations.

Define the depth of tracking required before selecting the system.

Use discrepancies to locate the missing event

A quantity difference may come from an unrecorded transfer, damaged goods, an incorrect dispatch allocation or another cause.

For the broader stock problem, see why stock never matches the system. Lot-level records help narrow an investigation when the underlying movements are complete.

6. A Worked Example: Following Lot 4471

Consider a hypothetical distributor receiving 100 units of one product under supplier lot 4471.

It dispatches 30 units to Customer A and 20 to Customer B. It records ten units as disposed of after damage. Forty remain physically held: 35 in normal storage and five on quality hold.

These figures illustrate traceability records, not a client case study.

Received 100 = dispatched 50 + disposed 10 + physically held 40

The current balance is not the same as availability

The business still physically holds 40 units. Only 35 are in normal storage, and even those may have other commitments or restrictions.

The five on hold should remain distinguishable. A flat “40 in stock” figure does not answer whether all 40 can be promised.

A return adds another event

Suppose Customer A returns four units whose identity is confirmed as lot 4471. The goods are received into a return-inspection location.

Gross dispatch history remains 50 units. Four have come back, so net shipped quantity is 46 and physical stock held becomes 44. Disposal remains ten.

100 = net shipped 46 + disposed 10 + physically held 44

Keep the original shipment and return events. Replacing the shipment history with a net number would conceal part of the trace.

Reconciliation does not prove every detail is right

The totals can balance while a customer allocation is wrong. Check the shipment evidence and physical identification, not just the arithmetic.

If goods were transformed in production, use the relevant input/output and yield records rather than assuming a simple one-product unit balance applies.

7. What Trustworthy Batch Tracking Looks Like

Reliable tracking makes lot detail part of normal work, supported by clear transaction rules and checks.

Capture identity at receiving

Record the product, supplier lot, accepted quantity and relevant dates. Confirm where goods are stored and whether they require inspection before release.

If an internal lot reference is created, preserve its relationship with the supplier’s identity.

Preserve identity through movement

Transfers, picks and dispatches should carry the relevant lot and quantity. Review partial movements and mixed-lot shipments explicitly.

A barcode inventory workflow can help capture identifiers, but scanning a product code alone may not identify its lot. Confirm the label contents and capture method.

Control exceptions and corrections

Unknown lots, missing dates, damaged labels and unallocated shipment lines need a defined route for review.

Record authorised corrections with their reasons. Do not silently reconstruct missing history from assumptions.

Make the trace retrievable

The report should show linked receipts, movements, production use and recipients at the required level of detail.

Users need permissions and training to retrieve the information. Export and access arrangements should remain clear if a supporting connection is unavailable.

8. Spreadsheet Batch Tracking vs Connected Records

Compare the actual controls and workload rather than assuming that software alone establishes traceability.

An inventory automation system can support the connected approach. Its scope needs to include the actual movements and transformations involved.

The trace depends on relationships between receipts, stock movements, production and shipments.

9. How to Implement and Test the Trace

Choose a representative product and follow the complete path its lot identity must take.

Define the tracking level and boundaries

Agree product, lot and serial requirements. Map suppliers, locations, production activities, fulfilment providers and customers involved.

Identify where you receive another party’s records and where your own evidence begins and ends.

Review data and physical identification

Check labels, product mappings, supplier references and date definitions. Establish an opening position that the team can verify.

Do not assume old stock has a reliable lot identity merely because a new system requires one. Record unresolved information through an appropriate review process.

Test representative exceptions

Include partial receipts, mixed-lot picks, location transfers, holds, returns, unreadable labels and authorised corrections.

For production, add input-lot consumption, output batches and relevant rework. For integrations, test delayed and duplicate events.

Run a two-way exercise

Select a lot and retrieve its destinations and current balances. Then select a shipment and retrieve the source lots and receipts.

Reconcile the quantities and review the evidence behind each link. Record retrieval time and any steps requiring manual reconstruction.

Assign ongoing ownership

Define who maintains lot records, handles exceptions and reviews near-expiry stock. Train the people capturing the movements and those retrieving the trace.

Repeat suitable exercises as the process changes. A successful pilot does not establish that every future record will be complete.

Frequently Asked Questions

What is the difference between batch and lot tracking?

The terms are often used interchangeably for tracking a defined group of stock. Their precise meaning can vary by product and process. Agree the grouping rule and preserve the product and supplier context.

Does lot tracking identify every individual unit?

Not necessarily. A lot identifies a group. Identifying particular units may require serialisation or another appropriate unique identifier.

Can spreadsheets support batch tracking?

Yes, with complete records, consistent identifiers and checks. Maintaining relationships across many movements can become demanding. Assess the actual gaps, volume and control requirements.

How does lot tracking support a recall?

It helps establish origin, movements, current balances and recorded recipients. It supports investigation and response decisions but does not independently determine the issue’s scope or prove unaffected goods are safe.

Is FEFO the same as shipping the oldest stock?

No. FEFO prioritises eligible stock with the earliest expiry. The oldest received lot may have a later expiry than one received more recently.

Does recording expiry automatically block expired stock?

Not always. Behaviour depends on the platform and configured rules. Test eligibility, warnings, holds and release controls explicitly.

Can a barcode give us the lot automatically?

Only when the code and application support that information or a reliable linked record. A product-only barcode may not identify the lot. Confirm the actual labels and scanning workflow.

What happens when a customer returns an item without a readable lot?

Use the available evidence, such as original shipment records, and preserve uncertainty if identity cannot be established. The return should follow the appropriate inspection or hold process.

Do we need a full ERP?

Not necessarily. A focused inventory product, custom system or ERP module may fit. Check lot movements, production transformations, reports and controls against the actual requirements.

How OpsMavix Can Help

OpsMavix builds inventory systems around how goods are received, stored, used and dispatched.

We start by mapping where lot identity is captured and where it becomes disconnected from the physical work. From there, we define the records, connections and checks needed for the agreed traceability scope.

That can include:

• Product and supplier-lot relationships.
• Receiving, location and status records.
• Lot-aware picking and shipment allocation.
• Expiry-aware selection and exception handling.
• Returns and inspection workflows.
• Relevant production input/output relationships.
• Forward and backward reporting with quantity reconciliation.
• A representative exercise before wider rollout.

If you cannot reliably connect a lot to its shipments or trace a shipment back to its source, book an Operations Leak Audit. Bring a recent receipt, the related movements and a shipment or return. We can help identify where the chain breaks and what to improve first.