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.