50 suppliers, 500 SKUs: The distributor's expiry nightmare

The Excel file that grew until it collapsed

A pharmaceutical and FMCG distributor in Hyderabad ran his expiry tracking on a single Excel spreadsheet for four years. It started with 80 rows — one per product — and a simple structure: product name, batch number, expiry date, quantity. When a batch was fully sold, the row got deleted. When a new batch arrived, a new row was added.

By the time the spreadsheet stopped working — not crashed, but became functionally useless — it had 2,400 rows. One person was responsible for updating it: a data entry operator who spent approximately two hours per day on this single file. Despite this, the spreadsheet was perpetually inaccurate.

The problem was not Excel. Excel can handle 2,400 rows without breaking a sweat. The problem was that the operational requirements of batch-level expiry tracking exceed what a manually-maintained spreadsheet can deliver when the variables start multiplying.

50 suppliers. 500+ SKUs. Multiple batches per SKU. Each batch with a different expiry date, received on a different date, from a different supplier, under a different invoice, with a different return policy. The data entry operator was not maintaining a spreadsheet. He was maintaining a relational database in a tool that has no concept of relations.

Where Excel fails specifically

The distributor — let us call him Ravi — identified four specific failure modes that compounded over the four years:

Duplicate entries. The same batch would appear in multiple rows because the data entry operator added a new row when stock arrived without checking if the batch already existed from a previous partial delivery. At one point, batch HUL2304A of a Surf Excel variant appeared in three separate rows with three different quantities, none of which matched the actual stock on the shelf.

Deletion lag. When a batch was fully sold, the row should be deleted. In practice, deletion happened during a weekly cleanup session. Between cleanups, sold-out batches cluttered the spreadsheet, making it harder to find active batches and creating confusion about actual stock levels.

No alerts. Excel does not proactively notify you when a cell value crosses a threshold. Ravi had conditional formatting — cells turned red when expiry was within 90 days — but conditional formatting requires someone to look at the spreadsheet. In a warehouse processing 200+ invoices per day, nobody was regularly scanning a 2,400-row spreadsheet for red cells.

Multi-user breakdown. Three people needed access to the expiry data: the warehouse manager (for physical stock rotation), the accounts person (for return claims), and Ravi himself (for management decisions). They shared the file on a local network drive. Concurrent edits were impossible. The file was frequently locked. Version conflicts created data loss. Eventually they stopped updating it simultaneously and consolidated updates once daily, which meant the data was always at least 12 hours stale.

The transition: what replaced the spreadsheet

Ravi moved to a dedicated inventory management system over a period of six weeks. The system — I will not name it because I do not want this to read as an endorsement of a specific competitor — had batch-level tracking as a core feature rather than an afterthought.

The transition was not a flip-the-switch event. It was a migration project with specific phases:

Week 1-2: Complete physical stock audit. Every item in the warehouse was counted, and for each item, the batch number and expiry date were recorded. This was painful — 500+ SKUs across approximately 8,000 batch-quantity combinations. It took four people working full days for two weeks. Ravi considered this the most valuable two weeks his business had experienced, because it uncovered ₹1.4 lakhs of expired stock that had been hiding in the warehouse corners, unreported and unaccounted for.

Week 3: System setup and data entry. All audited stock was entered into the new system with batch-level detail. Supplier records were loaded with their specific return policies (return window, documentation requirements, claim process).

Week 4-5: Process change. Every goods receipt was captured in the system at the batch level before physical stocking. Every dispatch was recorded against a specific batch. The system enforced FEFO — it would not allow dispatching a later-expiring batch while an earlier-expiring batch of the same product had remaining stock.

Week 6: Alert calibration. Expiry alerts were configured per supplier: 90 days for suppliers with a 3-month return window, 180 days for suppliers with a 6-month window. The system generated a daily report of approaching-expiry batches, sorted by urgency.

Six months later: the numbers

Ravi shared his before-and-after numbers with me. I am presenting them as he gave them, without independent verification, but they are consistent with what I have seen from other distributors who have made similar transitions:

Expired stock write-off: Down from ₹2.1 lakhs per quarter to ₹38,000 per quarter. An 82% reduction. The remaining ₹38,000 was from genuinely unsaleable surplus — products where demand collapsed due to market changes, not from tracking failures.

Distributor return claim approval rate: Up from 61% to 93%. Because claims now included complete batch-level documentation, invoice references, and timestamped photographs captured during the stock audit process, brands had no grounds for rejection.

Time spent on expiry management: Down from 2 hours daily (the data entry operator on Excel) to 30 minutes daily (warehouse manager reviewing the system-generated alerts and acting on them). The data entry operator was reassigned to goods receipt processing, where he added more value.

Stock rotation compliance: The system's FEFO enforcement meant that the oldest batch always moved first. Before the system, Ravi estimated that 30-40% of dispatches were not FEFO-compliant (warehouse staff would pick whatever was most physically accessible). After, FEFO compliance was 98%+ because the system would not generate a dispatch note for a non-FEFO pick without a supervisor override.

The cost equation

The system cost ₹5,000 per month. The physical audit cost approximately ₹80,000 in staff time (a one-time cost). Annual system cost: ₹60,000 plus the initial ₹80,000 in year one.

The quarterly expiry reduction was ₹1.62 lakhs (₹2.1 lakhs minus ₹38,000). Annualized: ₹6.48 lakhs. Add the improved claim recovery (estimated ₹1.2 lakhs annually in previously-rejected claims now approved). Total annual benefit: approximately ₹7.7 lakhs against a first-year cost of ₹1.4 lakhs.

This is not a marginal return. It is a 5.5x return in year one, improving to 12.8x in subsequent years as the one-time audit cost is amortized.

The lesson Ravi wishes he had learned earlier

"The spreadsheet felt free," Ravi told me. "It was familiar, I could customize it, and I did not have to learn anything new. But the spreadsheet cost me ₹6 lakhs per year in expiry losses and rejected claims that I could not see because the spreadsheet was the wrong tool for tracking them. The ₹5,000 per month system was the cheapest investment I ever made. I just made it four years too late."

At ShelfLifePro, we build exactly this kind of batch-level tracking. Our production deployment is at Dharmik Supermarket in Coimbatore, where Kavitha tracks perishable inventory across hundreds of SKUs with the same FEFO enforcement and alert system that Ravi's warehouse needed. Different business type, same underlying problem: when you scale past a few hundred SKUs with batch-level variation, manual tracking is not just inconvenient. It is actively generating losses that you cannot see because the tool you are using to track them is the same tool that is failing to track them.

The spreadsheet is not the problem. The problem is asking a single-table tool to manage a multi-dimensional data problem. At 50 SKUs, it works. At 500, it costs you lakhs.