Challenge
Products with Best Before Dates had to be sold before their SMT date, and the team needed to see which stock was at risk.
Sell by Maximum Time - Supply Chain Risk
See which stock may pass its Sell by Maximum Time before it becomes a loss.
An iterative algorithm simulates sales by batch, location and stock type; Power BI shows risk, alerts and key dates in four units of measure.
Delivered
An automated pipeline and iterative risk algorithm on Informatica IICS, Snowflake and Power BI.
Enabled
Heads-up alerts and risk per product and batch, recalculated every three hours.
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.
From stock data to risk alerts
-
01
Collect
Stock and forecast data (daily and weekly) from customer ERP and user Excel files.
-
02
Enrich and store
Informatica Intelligent Cloud Solution processes and enriches the data with risk information before storing it in Snowflake.
-
03
Simulate
The iterative algorithm simulates sequential sales by batch, location and stock type.
-
04
Report
Power BI shows risk per product and batch, heads-up alerts, key dates and potential losses in four units of measure.
Outcomes
Qualitative project outcomes; no measured savings are claimed.
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
Informatica IICS
Snowflake
Power BI
Custom risk algorithm
Solution areas: Data Foundations ยท Data Products
