Thursday, June 25, 2026
🇺🇸 FDA

Manufacturing Tools · Inventory · FEFO expiry risk

Inventory Expiry Risk Calculator

Paste lot-level inventory to estimate expired quantity, projected at-risk stock, months to expiry, and FEFO priority bands using monthly demand — for supply planners, QA, and finance triage.

Quick Answer

Pharmaceutical inventory expiry risk projection sorts lots by expiry date (FEFO), then compares each lot's quantity against cumulative demand available before that expiry date. Lots with remaining quantity after projected consumption are flagged as at-risk; expired lots are flagged separately. Risk bands (Expired, High, Medium, Low) support supply planning triage — not ERP replacement or quality disposition under EU GMP and GDP inventory controls.

FEFO projection logic

Demand before expiry = max(0, months to expiry) × monthly demand

Input: lot, quantity, expiry date (ISO), monthly demand. At-risk = max(0, lot qty − consumable demand after earlier FEFO lots). Example: RM-2406-A, 1200, 2026-12-31, 300.

Calculator

Use non-confidential lot aliases. The same demand unit must be used for quantity and monthly demand.

Projection settings
Lot inventory

Separate columns with comma, tab, or pipe. Lines starting with # are ignored.

FEFO output

Expiry Risk Summary

Review
Total inventory-
Expired quantity-
Projected at risk-
High-risk lots-

FEFO order Lot Quantity Expiry Months to expiry Projected at risk Risk band

How to Use the Expiry Risk Calculator

1
Set the review date to today or the planning cycle anchor. Only released, usable lots belong in the paste table.
2
Enter one row per lot: identifier, on-hand quantity, ISO expiry date, and monthly demand in the same units (kg, units, packs).
3
Adjust the at-risk horizon to match site policy — e.g. 6 months for raw materials, 3 months for finished goods near market.
4
Calculate and review FEFO order, projected at-risk quantity per lot, and summary metrics (expired, total at risk, high-risk lot count).
5
Confirm actions through planning, QA, and finance — allocation, tender pull-forward, return, or destruction per approved SOPs.

Worked Example

Three raw material lots · 300 units/month demand

Lots: RM-2406-A 1,200 qty exp 2026-12-31; RM-2408-B 900 qty exp 2027-03-31; RM-2411-C 1,500 qty exp 2027-09-30.

RM-2406-A: ~6 months to expiry → demand capacity 1,800. Lot qty 1,200 → fully consumable → 0 at risk (if review date mid-2026).

If demand were 100/month: capacity 600 < 1,200 → 600 at risk on earliest lot — illustrates demand sensitivity.

Interpretation: Validate demand assumptions against forecast, tenders, and minimum shelf-life commitments before finance exposure sign-off.

Risk Band Reference

Band Criteria (this tool) Typical action
Expired Review date past expiry date Quarantine; quality and finance disposition per SOP
High Projected at risk and ≤3 months to expiry Daily ops review; allocation or pull-forward
Medium Projected at risk or within horizon window Monitor; adjust forecast and FEFO picking priority
Low No projected risk under assumptions Routine FEFO; re-run when demand shifts

Pharma / Supply Chain Context

Expiry management is a GMP and GDP expectation: inventory must be rotated so oldest suitable stock is used first, and expired material must not enter distribution. FireAI and similar analytics playbooks describe days-to-expiry (DTE) bands from green through critical; this calculator adds forward demand depletion so planners see projected write-off quantity, not only calendar alerts.

FEFO can be overridden by quality holds, country-specific release, customer remaining shelf-life clauses, or cold-chain history on a lot. Pair results with the Cold Chain Temperature Log when quarantine after excursion affects allocatability. Reduce overstock drivers using the Safety Stock Calculator and scale batch demand via the BMR Raw Material Calculator.

ERP and WMS systems (Better Data expiry risk modules, ASC Software FEFO guides) remain the system of record for lot status, pick execution, and audit trail. Use this free tool for monthly S&OP triage, scenario what-ifs, and cross-functional reviews before updating official plans.

Evidence & sources

Frequently Asked Questions

FEFO means first-expiry-first-out (also written first-expired-first-out). Lots with the earliest expiry are prioritized for use or allocation before later-expiring lots, subject to quality release, market authorization, customer shelf-life requirements, and quarantine status.
It sorts lots by expiry date and compares cumulative FEFO inventory against cumulative demand available before each lot expires (months to expiry × monthly demand). Remaining quantity not consumed before expiry is flagged as projected expiry risk. Earlier lots consume demand capacity before later lots are evaluated.
Expired: review date past expiry. High: projected at-risk quantity with ≤3 months to expiry. Medium: projected at-risk or within the configurable at-risk horizon (default 6 months). Low: no projected write-off under entered demand assumptions and outside the horizon window.
No. Demand variability, regulatory holds, recalls, release delays, market restrictions, cold-chain constraints, and customer minimum remaining shelf-life can prevent consumption of near-expiry stock even when FEFO is applied correctly.
Use ISO date format such as 2026-12-31. The parser accepts browser-recognized date strings, but ISO YYYY-MM-DD is preferred for fewer parsing errors in batch uploads from ERP or WMS exports.
FIFO (first-in-first-out) prioritizes by receipt date. FEFO prioritizes by expiry date — critical when a newer receipt has an earlier expiry due to manufacturing or supplier batch differences. ASC Software and StockPilot guides emphasize FEFO for pharma because expired stock cannot be legally distributed.
Excel and WMS workflows often color-code lots at 30, 60, or 90 days to expiry. This tool projects whether monthly demand can consume each lot before expiry — a forward-looking write-off estimate. Use both: alerts for operations rhythm, projection for planning and finance exposure.
For a single SKU projection, use consistent monthly demand across rows unless modeling SKU-specific allocation rules. Mixed demand per row allows rough what-if by lot but is uncommon in standard FEFO reviews — confirm units match quantity columns.
Quarantine, rejected, recalled, or under-investigation lots must be handled under approved quality and inventory procedures — they should not be picked for FEFO dispatch until released. This calculator does not read ERP quality status; exclude non-releasable lots from inputs.
No. It supports arithmetic FEFO projection only. Destruction, return, tender pull-forward, and write-off require approved planning, QA, finance, and market access workflows. Better Data and FireAI describe enterprise expiry dashboards with audit trails — this is a free planning triage aid.
High safety stock on short-shelf-life materials increases expiry exposure. After identifying at-risk lots here, review whether safety stock targets in the Safety Stock Calculator should be reduced or whether demand pull-forward and allocation rules can salvage near-expiry inventory.
ElyxAI and StockPilot templates use DATEDIF and conditional formatting for days-to-expiry alerts. NovaPharmaNews adds FEFO demand depletion math — projected at-risk quantity per lot — plus risk bands and manufacturing hub cross-links, free in the browser with no login.

Related Manufacturing Tools

SS Safety Stock Calculator 2–8°C Cold Chain Temperature Log BMR BMR Raw Material Calculator Yield Yield Calculator Dev Deviation Investigation

Drug approvals, clinical trials, M&A, event intelligence, and company updates — weekly.

Customize topics & region
Topics
Region

No spam. Unsubscribe anytime. See our privacy policy.