PREVENTS €380K IN WRITE-OFFS INVENTORY SQL EXCEL

Batch Expiry Risk Manager

SQL + Excel model that extracts batch-level expiry data from ERP, calculates days-to-expiry, applies three-tier risk classification (Critical / Warning / Monitor), and generates actionable disposal, markdown, and diversion recommendations per batch — surfacing alerts before write-off becomes unavoidable.

Project Overview

⚑ Problem

Expiry Discovered Too Late

Batch expiry dates exist in the ERP/WMS but are never surfaced proactively. Stock nearing expiry is typically discovered during a periodic warehouse walk — by which point markdown or diversion windows have closed and write-off is the only option. The result: recurring quarterly write-offs that hit COGS with no early warning.

⚙ Solution

SQL + Excel Batch Risk Model

A scheduled SQL query extracts all batch records with shelf-life <90 days, computes Days-to-Expiry (DTE), and feeds into an Excel risk model that applies traffic-light tiering (Critical <30d, Warning 30–60d, Monitor 60–90d). Each batch receives a recommended action (Use Now / Markdown / Divert / Write-Off Plan) with estimated recovery value.

✓ Findings

€380K Write-Off Risk Identified

52 batches at risk across 6 categories. €92K in Critical tier (≤30 days, immediate action). €188K in Warning tier (actionable with markdown/diversion). Estimated recovery through proactive action: €267K of the €380K at risk — a 70% save rate. FEFO enforcement gaps in Dairy and Pharma categories are the primary root cause.

Critical Expiry Alerts

Batches Requiring Immediate Action (≤30 Days)

— batches

Batch Expiry Timeline — Days to Expiry (Gantt View, All Tiers)

Critical ≤30d

Warning 31–60d

Monitor 61–90d

Safe >90d

Dataset Explorer

Expiry Risk Analysis

Write-Off Exposure by Category & Tier (€K)

Recovery vs Write-Off Risk — Bubble by Quantity

Recommended Action Distribution

DTE Distribution — Batches by Tier

Value at Risk by Site

FEFO Compliance Rate by Category

Batch Register

BATCH ID	SKU	PRODUCT	CATEGORY	SITE	QTY (UNITS)	VALUE (€)	MFG DATE	EXPIRY DATE	DTE (DAYS)	RISK TIER	ACTION	RECOVERY (€)	WRITE-OFF RISK (€)

90-Day Write-Off Exposure Forecast

Rolling Write-Off Exposure — Next 90 Days by Category

Company Advice

💡 Recommendations for Inventory & Warehouse Teams

FEFO (First Expiry, First Out) discipline prevents 60–70% of expiry write-offs. It requires zero manual overrides in the WMS pick sequence — enforce it as a system-locked rule, not a training guideline. Every manual pick override should trigger an alert to a supervisor.
Set automated DTE alerts at three thresholds: 90 days (plan), 60 days (act), 30 days (escalate). The 90-day trigger is the critical one — it gives enough time for markdown, customer diversion, or manufacturing schedule adjustment before value is destroyed.
For pharma and medical batches, write-off is often the only compliant option once DTE < shelf-life threshold for patient safety. Build regulatory disposal cost into the risk model — it's frequently 3–4× the product write-off value.
Markdown recovery at the 31–60 day window typically recovers 60–75% of face value. At <30 days, recovery drops to 20–40%. The markdown decision delay from 45 days to 15 days effectively destroys half the potential recovery value.
Root cause is almost always a forecasting or inbound ordering error. Map every Critical-tier batch back to the original PO and demand forecast — batch expiry is a lagging indicator of upstream planning failure, not a warehouse problem.
Run the SQL extract weekly, not monthly. Monthly cadence means batches can enter Critical tier between runs with no actionable window remaining.

Key Takeaway: Every euro of batch write-off represents a compound failure — a forecasting miss, a reorder error, and a FEFO enforcement gap — all undiscovered until too late. Prevention starts upstream; the expiry tracker just makes the damage visible before it becomes permanent.