Inventory ageing: identify slow stock without misleading averages

An average inventory age can hide a small quantity of expensive old stock. Calculate age at the remaining-lot level and report both units and value in age bands. The right priority may depend more on capital tied up, expiry or demand than on the average age alone.

Old stock is a diagnostic signal. Age by itself does not prove that an item is obsolete, unsellable or should be discounted.

Define the snapshot before calculating age

The original synthetic inventory-lot fixture represents remaining balances for a January 31, 2026 snapshot, with one deliberately included future receipt that must be excluded.

Age is calendar days from receipt to January 31. Value is remaining units multiplied by the fixture's unit cost in paise. These costs are teaching inputs, not a valuation policy for financial reporting.

L5 arrives February 1 and is outside the snapshot. Including it would introduce future inventory and could produce a negative age.

Calculate age without replacing lot history

SELECT lot_id,product,remaining_units,unit_cost_paise,
 CAST(julianday('2026-01-31')-julianday(received_at) AS INTEGER) AS age_days,
 remaining_units*unit_cost_paise AS value_paise
FROM inventory_lots
WHERE received_at<'2026-02-01' AND remaining_units>0
ORDER BY age_days DESC,lot_id;

Oracle's inventory metric documentation includes quantity-weighted receipt-age measures. The example below adds value exposure so a single weighted average does not hide the older expensive lot.

from datetime import date
from math import isclose
from build_and_verify import database

db = database()
rows = db.execute("SELECT * FROM inventory_lots WHERE received_at<'2026-02-01'").fetchall()
db.close()
assert all(r[3] >= 0 and r[4] >= 0 for r in rows)
aged = [(lot,(date(2026,1,31)-date.fromisoformat(received)).days,units,units*cost)
        for lot,product,received,units,cost in rows if units>0]
units = sum(r[2] for r in aged)
value = sum(r[3] for r in aged)
old = [r for r in aged if r[1]>90]
weighted_age = sum(r[1]*r[2] for r in aged)/units
assert units == 40 and value == 88000
assert weighted_age == 49.875
assert sum(r[2] for r in old) == 10
assert sum(r[3] for r in old) == 50000
assert isclose(sum(r[3] for r in old)/value, 25/44)
print({'unit_weighted_age_days':weighted_age,
       'over90_unit_share':10/40,'over90_value_share':50000/88000})

The unit-weighted average is about 49.9 days. Only 25% of units are older than ninety days, but they represent approximately 56.8% of the recorded inventory value. That concentration is the useful investigation signal.

Avoid resetting age through an unrelated movement

A transfer between locations does not necessarily make stock newly received for the business question. A repack, return or lot merge may also need an explicit age policy. Preserve the relevant original receipt lineage when the purpose is to understand how long the organization has held the material.

Do not calculate historical ageing from today's remaining balance unless you can reconstruct the historical stock movements. The lab explicitly supplies balances for its snapshot; a live warehouse needs dated receipts, issues, adjustments and transfers or a reliable historical snapshot.

Turn age bands into an operational review

For the old high-value lot, inspect demand, open orders, substitution options, expiry and stock condition. Some slow-moving spare parts may be intentionally held for service obligations. Other stock may need a replenishment change or a targeted clearance decision.

Keep blocked, damaged and available stock separate where those states affect action. A unit that exists physically but cannot be sold should not be treated as available demand coverage.

Exercise: split L3 into two lots with the same total units and cost but different receipt dates. Recalculate the age bands and explain why product-level last-receipt date would lose important information.

NeuraPath's Data Analytics with Generative AI course connects SQL dates with operational interpretation. A useful inventory report shows where units, value and ageing risk concentrate without turning age into an unsupported disposal recommendation.

Continue learning

This article is part of the Domain analytics and business cases sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Retail sales analysis: separate price, volume and mix.
• Continue with Stockout analysis with incomplete availability data.