Inventory Aging Report: How to Build and Use One

An inventory aging report shows how long stock has been sitting, grouped into age bands, and it is one of the cheapest and most revealing reports a planning or finance team can produce. Total inventory value tells you how much cash is tied up. The aging report tells you how much of it is at risk. Stock that arrived last month is almost always fine. Stock that has been on the shelf for a year is a candidate for a markdown, a reserve or a write-down. This guide explains how to define age, how to build the report, how to read it with a worked example including weighted average age and carrying cost (all arithmetic checked), how to connect it to reserves, what actions to take for each band and how to automate the report. It is part of demand planning and inventory optimization, follows markdown planning and inventory write-downs, and leads to FIFO vs. LIFO and inventory valuation methods. Aged stock is cash at risk, as discussed in cash flow management.

Define age

Age can be measured from several starting points. Date received is the most common: the number of days since the goods entered the warehouse. Date of manufacture matters for products with shelf life. Date of last sale shows how long the item has been inactive. Date of last movement captures any transaction. Choose the definition that fits the risk you care about, and say what it is on the report. For multiple receipts of the same item, the age is tracked by layer, using the first in, first out assumption for consumption, so that the older units show as the oldest stock.

Choose the bands

Typical bands are 0 to 30, 31 to 60, 61 to 90, 91 to 180, 181 to 365 and over 365 days, but fit them to your product. A fast-moving consumer product with a 12-month shelf life needs finer bands over a shorter span. A durable good with a long life can use wider bands. Include both quantity and value, and show the share of the total in each band. Add a column for forward coverage (days of supply), because old stock with high forward demand is less worrying than old stock with none.

A worked example

A company has $5.0 million of inventory (illustrative), distributed by age as follows:

Share over 180 days: 0.6 + 0.4 = $1.0 million, or 1.0 / 5.0 = 20 percent.

Weighted average age: use band midpoints as an approximation: 45 days, 135 days, 272.5 days and, for the oldest band, an assumed 450 days. The weighted average is (3.1 × 45 + 0.9 × 135 + 0.6 × 272.5 + 0.4 × 450) / 5.0 = (139.5 + 121.5 + 163.5 + 180) / 5.0 = 604.5 / 5.0 = 120.9 days.

Carrying cost of the aged stock: at an 18 percent annual rate, holding $1.0 million over 180 days costs 1.0 × 0.18 = $180,000 a year, and clearing half of it would release $0.5 million of cash and save $90,000 a year. If the target is to reduce stock over 180 days to 10 percent of inventory, the aged balance should fall to $0.5 million, a reduction of $0.5 million. Check: 3.1 + 0.9 + 0.6 + 0.4 = 5.0; 62 + 18 + 12 + 8 = 100; 139.5 + 121.5 + 163.5 + 180 = 604.5. These midpoints are assumptions, so the weighted age is an estimate. The oldest band has no upper limit, so using a better figure for the oldest stock raises the estimate.

Reading the report

Look at the trend before the snapshot. Is the share over 180 days rising or falling? A rising share suggests that buying is outpacing demand. Look at the concentration. Often a few items make up most of the aged value. Rank them and attack the top ten. Compare aging with turnover by category: a category with low turnover and a high share of old stock is a candidate for tighter buying rules. Cross-check against the forecast. Old stock with healthy forecast demand may simply be a slow period. Old stock with no forecast demand is excess. Check by location, since the same item may be old in one warehouse and fresh in another, which suggests a transfer, not a markdown.

Link to reserves

The aging report is the usual starting point for the reserve for excess and obsolete inventory. Apply reserve percentages by band, adjusted for known circumstances, and document the method with finance and auditors. See excess and obsolete inventory and inventory write-downs. The report also provides evidence of recoveries: record how much the aged stock actually realized when cleared, and compare with the reserve rate.

Actions by band

• 0 to 90 days: normal. Monitor against days of supply.
• 91 to 180 days: review. Is forward demand supported? Pause replenishment where coverage is high.
• 181 to 365 days: act. Plan a clearance: targeted promotion, bundle, transfer, return to vendor.
• Over 365 days: decide. Liquidate, donate or dispose, and recognize the loss. Hold only with a documented reason.

Assign each flagged item an owner and a date. Track clearance month by month.

Building the report

In a spreadsheet, start from a receipts table by item, date and quantity, and the on-hand quantity by item. Apply first in, first out: allocate the on-hand to the most recent receipts and compute the age of each layer as the report date minus the receipt date. Group into bands with a lookup, multiply quantities by cost and summarize with a pivot table. Many inventory systems offer a standard aging report, and a BI tool can refresh it daily. Automate the data pull so the report is ready at the start of each month. See dashboard examples.

Governance

Produce the report monthly and review it in the S&OP cycle. Set thresholds, such as no more than 10 percent of value over 180 days, and escalate breaches. Track the movement between bands: what rolled into the oldest band this month and why. Include the report in the information given to finance for quarter-end reserves.

Aging by layer: a small example

Consider one item with 500 units on hand (illustrative). Receipts, from the newest, were 200 units 15 days ago, 250 units 70 days ago and 400 units 200 days ago. Under first in, first out consumption, the older receipts have been sold first, so the 500 units on hand are made up of the newest 200, the next 250 and the remaining 50 from the oldest receipt of 400. The ages are 15, 70 and 200 days. The weighted average age by units is (200 × 15 + 250 × 70 + 50 × 200) / 500 = (3,000 + 17,500 + 10,000) / 500 = 30,500 / 500 = 61 days. If the last receipt date alone were used, the report would show the whole position as 15 days old and hide the 50 units that are 200 days old. For a product with a shelf life of 240 days, those 50 units are weeks from expiry. Layer-based aging avoids that distortion.

Aging, turnover and coverage together

An aging report reads best alongside two other measures. Inventory turnover shows how quickly stock moves on average, and days of supply shows how long the current quantity would last at the forecast rate. An item with an average age of 61 days but 300 days of supply is a different story from one with the same age and 30 days of supply. Combine the three in a single table by item and sort by value at risk, defined as the value of stock that is both old and beyond forward demand. This is the list that deserves attention first. See inventory turnover ratio formula, with examples for the underlying calculation.

Special cases

Some stock needs special handling in the report. Raw materials and components may age differently from finished goods, and a component used across many products can be less risky than a unique part. Stock in transit and on consignment should be shown separately so it is not mistaken for warehouse stock. Returns and refurbished units should carry their own flags. Products with expiry dates need aging against remaining shelf life, not just receipt date, and customers or retailers often apply minimum remaining shelf life rules at receipt, so stock may become unsellable to key accounts well before the expiry date. Seasonal items should be aged against the season calendar. A summer item that is 60 days old in October is already out of season.

What the report should contain

A practical layout has one row per item and location with columns for description, category, status, quantity and value in each age band, total value, average age, days of supply, forecast for the next three months, last sale date and an owner. Add a summary at the top with the totals by band and the trend against the prior three months. Filters for category, location and supplier help meetings stay focused. Keep the layout stable from month to month so that people learn to read it, and add a short commentary on the three biggest changes since last time.

Using the report in the monthly review

Bring the report to the S&OP review and the finance close. For each of the top aged items, agree one of four decisions: hold with a documented reason, clear with a specific plan, transfer, or write down. Record the decision, the owner and the date. At the next review, begin with the open actions. Over a few months, the share of stock in the oldest bands should start to fall, and the discussion should shift upstream to why the stock became old in the first place. See excess and obsolete inventory for causes and markdown planning for the clearance tools.

Common mistakes

1. Using the last receipt date for a mixed-age item, which hides old units.
2. Ignoring forward demand.
3. Looking only at totals and not the top items.
4. Producing the report quarterly, too late to act.
5. No owners or deadlines for aged items.
6. Not feeding results back to buying.

FAQs

What is an inventory aging report?

A report showing how long inventory has been on hand, grouped into age bands by quantity and value.

How do I calculate inventory age?

Subtract the receipt date from the report date, usually by receipt layer under first in, first out consumption.

What age bands should I use?

Common bands are 0 to 30, 31 to 60, 61 to 90, 91 to 180, 181 to 365 and over 365 days, adjusted to your product's life.

How often should I run it?

Monthly at a minimum, and weekly for perishable or fashion products.

What do I do with aged inventory?

Review forward demand, stop replenishment, then clear through promotions, bundles, transfers, returns or liquidation, and recognize losses where needed.