Sell by Maximum Time — Supply Chain Risk

Challenge

Supply Chain faced the critical challenge of managing products with Best Before Dates (BBD). Each product had a specific timeframe before it could no longer be sold to third parties. To avoid potential losses, it was imperative to track the "Sell by Maximum Time" (SMT) date — the latest date a product could be sold. The team needed an algorithm that identifies the count of products at risk of exceeding their SMT date based on current stock and sales forecasts, accounting for product batches, storage locations, and stock types.

Approach

We developed a sophisticated, automated pipeline. The core is an iterative algorithm that calculates the number of products at risk by simulating sequential sales by batch, location, and stock type — so the answer reflects how stock will actually be consumed, not just headline totals.

An automated data-collection layer pulls stock and forecast data (daily and weekly) from customer ERP and user Excel files. Informatica Intelligent Cloud Solution processes and enriches the data with risk information before storing it in Snowflake. Power BI translates the processed data into reports covering risk values per product and batch, heads-up alerts for business users, key dates (BBD, SMT, Production Date), locations, statuses, forecast values, stock age, and potential losses.

All stock, risk, loss, and forecast metrics are displayed in four units of measure — pieces, tons, kilograms, and pallets. The pipeline recalculates risk every three hours to keep planners working from current data.

Outcomes

• Supply Chain efficiently monitors and manages stock against SMT dates, significantly reducing the risk of product wastage
• Clear, actionable Power BI reports help the planning department make informed decisions
• Heads-up alerts surface at-risk products before they become losses
• Multi-unit display (pieces / tons / kg / pallets) supports both operational and commercial users
• Risk recalculated every 3 hours — planners always have current data
• Demonstrates how a domain-specific algorithm + clean data foundation turns a recurring loss problem into a manageable one

Technology