Excess and Obsolete Inventory

Goal of the analysis:

The objective is to quantify, locate, and address inventory that exceeds operational needs (excess) or no longer has likely demand or saleable life (obsolete). Executives use this analysis to release working capital, reduce write-offs, protect gross margin, and improve service by ensuring inventory is in the right form and place. It connects supply planning, product lifecycle management, and commercial actions, and is a core driver of cash conversion cycle and EBIT through carrying cost reduction and avoidance of markdown and scrap.

Data required:

• Inventory and movement data:
  • On-hand by SKU-location-lot; quality status (available, hold, quarantine, returns).
  • Open POs/production orders and expected receipts; allocations/backorders.
  • Inventory age buckets (e.g., 0–30, 31–60, 61–90, 91–180, >180 days) and last movement date.
  • Shelf life/expiry, FEFO dates, batch/serial attributes.
• Demand and forecast data:
  • Historical shipments/consumption by SKU-location (12–24 months).
  • Current unconstrained forecast and demand plan; promotion/calendar flags.
  • Forecast accuracy and bias metrics (MAPE/BIAS) if available.
• Planning parameters:
  • Lead times (supplier, manufacturing, distribution), order cycle, review periods.
  • Safety stock, min/max, reorder points, MOQ, pack sizes, case/pallet multiples.
  • Substitutions, supersessions, and product lifecycle stage (NPI, active, end-of-life).
• Cost and financial data:
  • Standard cost or moving average cost; carrying cost rate (%), storage/handling rates.
  • Write-down policies, provisioning rules, salvage values, markdown history/elasticity.
• Master and segmentation data:
  • Item master, ABC/XYZ classification, product family/brand, channel/customer.
  • Location hierarchy (plant, DC, store), ownership (consignment/3PL).
  • Supplier info (lead time adherence, return-to-vendor terms, price protection).

Detailed step-by-step instruction on how to conduct the analysis:

1. Define scope and thresholds. Establish common definitions:
   • Excess = inventory above target stock (safety stock + cycle stock + pipeline buffer) or above X days of supply (e.g., >60 or >90 DoS) relative to forecast/consumption.
   • Obsolete = no demand for N months (e.g., >6), discontinued/EOL, non-movable lots, or expired/unsellable.
2. Extract data. Pull from ERP/WMS/PLM (e.g., SAP, Oracle, Microsoft D365, NetSuite), APS (Kinaxis, o9), and BI (Tableau, Power BI). Ensure consistency of SKU-location keys and time stamps.
3. Cleanse and harmonize. Remove duplicates, align costing method, normalize units of measure, map supersessions, and tag quality status. Validate forecast time-buckets to match inventory snapshots.
4. Compute target inventory.
   • Demand during lead time (DDLT) = average demand × lead time.
   • Safety stock = z × standard deviation of demand during lead time (use service level target).
   • Cycle stock = (order quantity or MOQ)/2 or demand × review period/2.
   • Target stock = safety stock + cycle stock + in-transit coverage buffers as policy dictates.
5. Calculate inventory position and excess.
   • Net inventory = on-hand (available) + on-order − allocations/backorders.
   • Days of Supply (DoS) = Net inventory / average daily demand.
   • Excess quantity = max(0, Net inventory − Target stock). Excess value = excess qty × unit cost.
6. Identify obsolescence.
   • Zero/low-velocity items: no shipments/consumption in N months or velocity below threshold.
   • Expired/near-expiry lots; compare remaining shelf life to lead time + sale window.
   • Lifecycle flags: discontinued/EOL with remaining stock or open POs.
   • Create an obsolescence risk score combining last-move age, shelf life, and forecast presence.
7. Segment and prioritize. Pareto by value (A/B/C), product families, locations, suppliers, lifecycle stage, and cause codes (e.g., MOQ, promo overbuy, returns, quality hold).
8. Financialize impact.
   • Carrying cost = excess value × carrying cost rate.
   • Provision estimate = obsolete value × write-down rate (policy-based), net of salvage/rework.
   • Scenario: liquidation vs transfer vs rework; compute recovery and P&L timing.
9. Root-cause analysis. Trace back to forecast bias, parameter mis-setup, lead-time changes, MOQ/pack-size constraints, NPI ramp misaligned with sell-through, delisted SKUs, promo residuals, returns, quality issues.
10. Action planning and what-if. Simulate:
    • Cancel/defer POs; adjust min/max and safety stock.
    • Inter-location transfers; substitution enablement; postponement/kitting.
    • Markdown plans with elasticity; liquidation channels; RTV/price protection claims.
    • Rework/relabelling; donate/scrap policy adherence.
11. Governance and cadence. Establish monthly E&O review in S&OP/IBP, owners by SKU-family, and KPIs (E&O % of inventory/sales, DoS, aging distribution).
12. Validate and implement. Review with Finance, Commercial, and Operations; lock actions, assign owners/dates, and track outcomes in a dashboard.

Format of the output of analysis:

• Executive summary slide with E&O value, top drivers, and cash/P&L impact.
• Dashboard: E&O by SKU-family-location, aging heatmap, DoS distribution, and trend lines.
• Pareto tables of top 50 SKUs by excess/obsolete value with root-cause tags and recommended actions.
• Waterfall chart from total inventory to sellable, excess, and obsolete components.
• Action register with owners, levers (cancel PO, markdown, transfer), expected recovery, and timing.
• “Stop/hold” list of POs to cancel/defer and parameter change log (safety stock/min-max updates).
• Finance exhibit: carrying cost savings, provisions, and cash release forecast.

How to interpret results:

• High E&O value or % indicates over-planning, parameter issues, or lifecycle mismanagement; immediate working capital and margin risk.
• Low E&O suggests disciplined planning and lifecycle control, but validate against service levels to ensure no understocking.
• Differences across segments matter: high excess on A-items often signals wrong min/max or lead time; high obsolete on C-items suggests SKU proliferation or poor delist discipline.
• Location patterns: DC excess with store stockouts implies poor allocation; plant WIP/hold indicates quality or scheduling issues.
• Benchmark comparisons should calibrate severity; prioritize items far from internal top-quartile performance or external norms.
• Trends: improving E&O over consecutive months signals parameter stabilization; spikes around promos/NPI point to process gaps.

Steps a company can take to improve on this measure:

• Process and policy:
  • Integrate E&O review into S&OP/IBP; enforce EOL triggers and ramp-down plans.
  • Right-size MOQs/pack sizes with suppliers; use postponement and late customization.
  • SKU rationalization cadence; gate new SKUs with exit strategy and target sell-through.
• Data, systems, and tools:
  • Improve master data accuracy (lead times, BOMs, yields); automate FEFO and shelf-life controls.
  • Adopt demand sensing and multi-echelon inventory optimization; enable substitution and ATP with alternatives.
  • Alerts for parameter drift, forecast bias, and near-expiry inventory.
• Capabilities and governance:
  • Train planners on bias management and safety stock math; establish an inventory council.
  • Commercial-supply collaboration on promo planning and NPI; supplier collaboration for RTV/price protection.
  • Clear provisioning policy and decision rights for liquidation vs hold.
• Commercial and operational levers:
  • Markdown optimization, bundles, cross-sell, alternative channels (outlets, wholesale, B2B).
  • Transfers and rebalancing; rework, relabel, or re-kitting; donation/scrap thresholds.
  • Scenario guidance: If excess high but obsolescence low, focus on PO deferrals and parameter fixes; if obsolescence high, prioritize liquidation/RTV and SKU delist discipline.

Benchmark comparisons:

General benchmarks:

• E&O as % of total inventory: many organizations see 10–20%; best-in-class can be 5–8%; >25% signals significant structural issues. Use with caution—mix and seasonality matter.
• Inventory aged >180 days (non-perishable): typically 10–15% for average performers; best-in-class <5%.
• Near-expiry (within remaining shelf life minus lead time): aim <2–3% of inventory for robust FEFO processes.

Segment- or industry-specific benchmarks:

• Perishables/CPG: expiry-related write-offs often 1–3% of COGS; strong FEFO and demand sensing can halve this.
• Electronics/high-tech: obsolescence risk 8–12% of inventory due to rapid lifecycle; top performers 4–6% with RTV/price protection.
• Industrial MRO: higher excess common (15–25%) due to service-level requirements; manage via segmentation and VMI.
• Apparel/retail: seasonal excess 10–20% post-season without aggressive markdown planning; leaders 5–10% with pre-planned liquidation.

If external benchmarks are limited, build internal ones: compare business units, top-quartile SKUs by velocity, and pre/post changes after parameter updates; set targets by ABC class and lifecycle stage.