Learning how to create an inventory aging report starts with identifying the inventory still on hand, tracing its receipt history, and grouping the remaining quantities into age ranges. A useful report shows both units and inventory value as of a specific date. It should explain how stock movements affect those quantities instead of assigning every unit the date of the most recent delivery.
The report answers a practical question: which products have remained in inventory long enough to deserve investigation? It does not automatically determine that older stock is obsolete or that newer stock is healthy. Demand, customer commitments, shelf life, and replacement cost still influence the decision.
What Is an Inventory Aging Report?
An inventory aging report groups remaining inventory by how long it has been held. Microsoft’s documentation describes inventory aging as a report that categorizes on-hand quantities and inventory values into time buckets. The resulting view helps distinguish recently received stock from inventory carried over from earlier periods.
An aging report differs from an inventory turnover calculation. Turnover measures the relationship between goods sold and average inventory during a period, while aging examines the stock that remains at a particular date. A company can have acceptable overall turnover while retaining a costly group of slow-moving products.
Inventory age is also different from time since the last sale. A product received yesterday may belong to a category that has not sold for months. Conversely, an older spare part may sell slowly by design because the business promises long-term customer support.
What Data Is Needed to Create an Inventory Aging Report?
Creating an inventory aging report requires receipt dates, inventory movements, and a reconciled on-hand balance. A current stock quantity and a “last received” field are usually insufficient for identifying the age of separate receipt layers. The business needs enough history to explain which quantities remain.
A dependable perpetual inventory system can provide the transaction trail. However, an export still needs review for missing receipts, inconsistent units, returns, and transfers. Accurate software records depend on accurate operational entries.
The following fields provide a practical starting point.
The report should preserve the transaction detail behind each total. If an employee cannot explain why 40 units fall into an older bucket, the number is difficult to act on confidently. Traceability is especially important when the report supports purchasing freezes or clearance decisions.
| Field | Why it matters |
|---|---|
| SKU or item identifier | Keeps different products separate |
| Warehouse and inventory status | Distinguishes locations and restricted stock |
| Receipt date and reference | Establishes the original age |
| Receipt quantity | Identifies the quantity entering inventory |
| Issue quantity and date | Explains sales and other outbound movements |
| Transfer and return references | Prevents misleading age resets |
| Unit of measure | Keeps cases and individual units consistent |
| Inventory cost | Supports valuation of remaining quantities |
| Report date | Defines the snapshot being analyzed |
Step 1: Define the Report Date and Aging Policy
An inventory aging report needs a fixed “as of” date and a documented rule for measuring age. Without those decisions, two employees can generate different results from the same stock history. The policy should also define whether age follows original receipt, physical lot identity, or an analytical allocation assumption.
For a basic receipt-age report, calculate:
Inventory age in days = Report date − Original receipt date
A receipt dated October 20, 2026, is nine days old on October 29, 2026. That calculation assumes the receipt date itself is day zero. The convention should remain consistent across reporting periods.
The business must also decide how to handle opening balances with incomplete history. Assigning them today’s date makes old inventory appear new. A separate “age unknown” category is more transparent until reliable historical information becomes available.
Step 2: Choose Inventory Aging Buckets
Inventory aging buckets divide remaining stock into nonoverlapping age ranges. The ranges should reflect the business’s buying cycle and product characteristics. A food distributor and an industrial spare-parts supplier should not automatically use identical action thresholds.
A general starting structure might use the following categories.
These labels are management categories, not universal definitions of healthy or obsolete inventory. A 100-day-old seasonal product may be deliberately positioned for a future selling window. A 20-day-old perishable product may already require urgent attention.
Receipt age should therefore be paired with commercial context. Add shelf-life information, open customer orders, expected future demand, and any contractual stocking obligation where relevant. The aging bucket identifies the question; it does not supply the whole answer.
| Bucket | Receipt age |
|---|---|
| Recent | 0–30 days |
| Developing | 31–60 days |
| Review | 61–90 days |
| Older | 91–180 days |
| Extended | 181–365 days |
| Long-held | More than 365 days |
| Unknown | Insufficient receipt history |
Step 3: Determine Which Receipt Quantities Remain
The most important inventory aging calculation identifies the receipt layers still represented in current stock. Where the system tracks actual lots, those records can establish physical age. Where it does not, the report needs a disclosed allocation assumption.
A common analytical approach assumes that earlier receipts are issued first. Inventory Planner, for example, describes an aging calculation that uses a FIFO-based stock history. That reporting assumption should not be confused automatically with proof of physical picking behavior or the company’s financial costing method.
Consider a hypothetical SKU with the following history and a report date of October 29, 2026. Assume all quantities are individual units and no returns or transfers occurred.
Under the stated FIFO aging assumption, the 130 issued units consume all 100 units from Receipt A and 30 from Receipt B. The remaining stock consists of 50 September units and 70 October units. There are no June units left in this analytical reconstruction.
The 50 September units are 44 days old, while the 70 October units are nine days old. Assigning all 120 units the October receipt date would hide the older layer. Assigning all 120 units the June date would be equally misleading.
| Transaction | Date | Quantity |
|---|---|---|
| Receipt A | June 1, 2026 | +100 |
| Receipt B | September 15, 2026 | +80 |
| Sales issues | After Receipt B and before Receipt C | −130 |
| Receipt C | October 20, 2026 | +70 |
| Remaining on hand | October 29, 2026 | 120 |
Step 4: Value the Remaining Inventory
An inventory aging report should show value as well as quantity because units alone do not reveal the cash exposure. Ten expensive components can matter more financially than hundreds of inexpensive accessories. The valuation basis must be stated so readers understand what the dollar total represents.
For the hypothetical example, assume a constant inventory carrying cost of $20 per unit. This keeps the arithmetic simple and avoids introducing different cost layers. The resulting aging summary is:
The report reconciles to 120 units and $2,400 under the example’s assumptions. In a real system, receipt costs, average costs, financial adjustments, and report configuration can produce a different valuation pattern. Do not assume that an age bucket’s value equals its original receipt invoice amount.
| Age bucket | Units | Value at $20 per unit |
|---|---|---|
| 0–30 days | 70 | $1,400 |
| 31–60 days | 50 | $1,000 |
| 61–90 days | 0 | $0 |
| More than 90 days | 0 | $0 |
| Total | 120 | $2,400 |
Step 5: Reconcile Before Making Purchasing Decisions
An inventory aging report should reconcile to the inventory records for the same date, scope, and valuation basis. A mismatch can arise from timing, excluded warehouses, negative inventory, unposted transactions, or different status filters. It should be investigated before the report drives a large purchase or write-down decision.
Quantity reconciliation and value reconciliation are separate checks. Quantities can agree while values differ because of later cost adjustments. Values can also appear reasonable while quantities are wrong because several errors offset one another.
The site’s inventory control techniques provide useful context for improving the underlying records. Aging analysis becomes more dependable when receiving, issuing, counting, and adjustments follow clear procedures. A polished dashboard cannot compensate for unexplained stock movements.
How Should Transfers and Returns Be Handled?
Transfers and returns need explicit aging rules because a new transaction date does not necessarily mean newly acquired inventory. Moving stock between warehouses can restart a location-based clock without changing the company-wide acquisition age. Both views can be useful if they are labeled correctly.
For example, a component purchased six months ago may reach a second warehouse today. Its time at the second location is zero days, but its enterprise holding age remains approximately six months. Reporting only the transfer date can conceal long-held stock.
Customer returns require similar care. Where the original lot or receipt can be identified, preserve that history in the appropriate view. Where it cannot, flag the uncertainty rather than automatically presenting returned goods as new inventory.
How to Turn Inventory Aging Into an Action Plan
Inventory aging becomes useful when each material exception has an owner, a decision, and a review date. The appropriate response may be to stop replenishment, transfer stock, contact customers, correct a listing, or prepare a commercial clearance plan. Automatic discounting is only one option.
A practical review can use the following decision sequence.
- Confirm that the reported quantity exists and is sellable.
- Check customer commitments and upcoming seasonal demand.
- Identify why the stock has not moved.
- Stop or revise unnecessary replenishment.
- Compare recovery options and associated costs.
- Record the action and review the result.
The reason for aging matters. A product with an incorrect catalog code needs a different intervention from a discontinued model. Treating every old SKU as a pricing problem can destroy margin without fixing the underlying cause.
When evaluating distribution management software, test the report using known transactions. Include a transfer, partial issue, return, and missing opening date. The useful question is whether the system can explain the result, not merely display colored age bands.
Frequently Asked Questions
- Is inventory aging the same as obsolete inventory?
- Inventory aging measures holding time, while obsolescence concerns whether stock retains commercial usefulness or value. Older goods may still have dependable demand. New goods can become obsolete quickly after a specification or market change.
- Can an inventory aging report be created in Excel?
- Excel can support an inventory aging report when the transaction history is complete and the remaining-layer logic is correct. Subtracting the last receipt date from today is not enough for mixed-age stock. The workbook should reconcile to the inventory source.
- Should reserved inventory appear in the report?
- Reserved inventory can remain visible because it still represents stock held by the business. A separate reservation field explains why it may not require the same action as uncommitted stock. Excluding it without explanation understates the total holding position.
- How often should inventory aging be reviewed?
- Monthly review is a practical starting point for many businesses. Short shelf lives, rapid product changes, or significant seasonal exposure may justify more frequent monitoring. The cadence should match how quickly the commercial risk develops.
- Does an aging report determine an accounting write-down?
- An aging report can identify stock requiring further valuation review. It does not automatically establish the accounting adjustment or its amount. The financial decision should follow the applicable accounting policy and supporting evidence.
