How to Build an Inventory Aging Report From Receipts

If you run physical goods, an aging report shows how long each item has been on the shelf, so you can decide what to review — mark down, return to vendor, re-slot, or stop reordering. This guide builds it from your receipt and issue records and ages the surviving stock under a stated FIFO assumption, as of a date you pick. (The same idea applied to money owed to you rather than stock on the shelf is an accounts receivable aging report — a separate report with the same shape.)

The hard part isn't the buckets; it's deciding which surviving units to age, and how. Under FIFO, a unit's age isn't the date of the last receipt — it's the receipt date of the lot the allocation leaves on hand after issues draw down the oldest lots first. Get the allocation and the cutoff right and the totals reconcile to your stock on hand; get them wrong and the report ages the wrong units. That reconstruction only works when you have the complete receipt and issue history through your cutoff — each movement with its own transaction date and quantity, and each receipt or opening layer with its original receipt or acquisition date — so the FIFO drawdown starts from the right lots. Where the history doesn't reach far enough back, you need opening inventory layers instead: the opening quantity on hand carried as lots, each with its original receipt or acquisition date. If a lot's opening age genuinely isn't available, age that stock as unknown rather than guessing a date. Where you have them, actual lot or serial records — or your organization's approved costing method — override the reconstruction.

01What an inventory aging report is

An inventory aging report takes the stock you currently hold and distributes it across age buckets, so instead of one "units on hand" number you can see how much of that stock is fresh and how much has been sitting. A typical layout lists each item (or SKU) with the quantity on hand in each bucket — 0-30, 31-60, 61-90, 91+ days — and a total that ties back to on-hand. The quantity view flags which stock to review, and the older buckets are the first place to look. To turn that into a financial read — cash tied up, slow-moving, obsolete, or a write-down candidate — you add the columns that support it: unit cost or carrying value for cash exposure, demand or turn history to call stock slow-moving, and a net-realizable-value check before any write-down. IAS 2 measures inventory at the lower of cost and net realizable value; age alone doesn't establish it.

Two conventions decide what the report says. The as-of date is the day you're aging to — usually a period end — measured only over movements on or before it. The aging method is the allocation assumption that decides which units are treated as still on hand and how old they are. FIFO (first-in, first-out) assumes the oldest stock was used first; this guide uses FIFO. It is a costing/allocation convention, not proof of physical movement — under IAS 2, cost is assigned by specific identification for items that are not ordinarily interchangeable, and by FIFO or weighted average for ordinarily interchangeable items — so state the assumption you use. Where you have actual physical lot or serial records, use them to override the reconstruction with each unit's real operational age; and follow your organization's approved costing method where one applies.

02The Excel template, and where it strains

For a small, simple case Excel is fine — but only after you've reduced each item to its remaining open receipt quantity as of your date, not the original receipt quantity. Keep only movements dated on or before the as-of date, draw issues down against the oldest receipts first so each receipt row carries the quantity still on hand, then age those remaining quantities:

• Age — days from the receipt date to your as-of date, for receipts on or before that date: =as_of_date - [receipt date].
• Bucket — =IF(age<=30,"0-30",IF(age<=60,"31-60",IF(age<=90,"61-90","91+"))). Day 90 falls in 61-90, so anything over 90 days is 91+.
• Totals — a PivotTable of the remaining open quantity by item and bucket, or SUMIFS per bucket, gives the aged grid; the row totals should equal on hand (receipts − issues through the cutoff).

Where the template strains is the realistic case: multiple receipts per item and issues that have drawn stock down over time. Now a unit's age depends on which lot the allocation leaves on hand — under FIFO the oldest receipts are consumed first, so an item can have surviving stock split across two or three ages at once. Doing that in Excel means building per-lot helper columns that consume issues against receipts in date order, exclude post-cutoff movements, and re-check that the buckets still sum to on-hand every time the data changes. It's doable, and it's exactly the fiddly, error-prone part the sample below skips.

03Build it from receipt and issue records, as of a date

When items have multiple receipts and a history of issues, one workable path is to let the FIFO allocation run for you. In Anomaly you import the receipt-and-issue export as a table and ask for the report in plain language, naming the cutoff, the method, and how to handle exceptions:

"These are inventory receipts and issues. Use only movements dated on or before 2026-03-31. On-hand per SKU = receipts − issues through that date; exclude SKUs at zero, and flag any issue with no matching receipt. Age the surviving stock as of 2026-03-31 under a FIFO assumption — issues consume the oldest receipts first — and bucket the remaining open quantity into 0-30, 31-60, 61-90, and 91+ days (day 90 in 61-90). Confirm the buckets reconcile to on-hand."

It runs the FIFO allocation — matching each issue against the oldest open receipts, so a partially issued lot keeps only its surviving quantity at that lot's receipt-date age — ages each surviving lot to the as-of date, and returns the aged grid by SKU and bucket, with a reconciliation check that the buckets sum to on-hand (receipts minus issues, through the cutoff) for every SKU and in total.

The same job in Anomaly: from the receipt-and-issue file to a downloadable Excel workbook

1. Ask. The receipt-and-issue file is uploaded as a table, then the report is requested in plain language — the as-of cutoff, the FIFO assumption, the buckets, and the reconciliation check.

2. The result. The aged grid by SKU and bucket, reconciled: 70 / 0 / 40 / 0 across the 0-30, 31-60, 61-90 and 91+ day buckets sums to 110 units on hand (200 received − 90 issued), and each SKU's buckets match its on-hand exactly.

3. An Excel workbook. The result is written to an .xlsx with three sheets — the Aged Grid, a FIFO Detail sheet of open quantity per receipt, and a Method and Sources sheet — with the totals, reconciliation and bucket labels as live formulas rather than pasted values.

4. One refinement. Asking for a "% of on-hand" row adds it beneath the totals — 63.6% in 0-30 days, 36.4% in 61-90 — as live formulas dividing each bucket by the 110 units on hand. The quantities and the reconciliation stay exactly as they were.

5. Download. The finished workbook downloads as a standard .xlsx you can open in Excel, Google Sheets, or any spreadsheet tool.

Two things to pin down before you trust any inventory aging number are in that report: the allocation assumption (here FIFO) and the as-of date, with movements limited to that date. State both, keep the underlying rows inspectable, and check the reconciliation — buckets summing to on-hand is the guard that the allocation didn't lose or double-count a unit.

04If you use NetSuite

NetSuite's current standard report list doesn't include one named Inventory Aging. It does offer inventory Valuation and Activity reports and an Inventory Turnover report — which includes average days on hand — and reports and search results can be exported. Check whether those views answer your question. If you specifically need lot-level age as of a date, custom buckets, a chosen aging assumption, or a reconciled total those reports don't provide, export the item receipt and issue movements to CSV and build the report from those records, the same way as the sample above — aging is a calculation over movement history, and a tool built for that calculation can run it from the exported records. There is no native Anomaly connector to NetSuite; you work from the exported file.

05Buckets, partial receipts, and returns: the decisions that move the totals

Two aging reports over the same data can disagree because they made different choices. Worth deciding on purpose:

• Bucket edges. 0-30 / 31-60 / 61-90 / 91+ is a common default, but tie the edges to your lead times and product life cycle — perishable or seasonal goods usually need tighter buckets, and a business with 120-day supply cycles wants a longer tail. Keep the edges non-overlapping so a boundary day lands in exactly one bucket.
• Partial receipts and lots. When an issue only partly consumes a receipt, the remainder stays on hand at that lot's original receipt date — it doesn't reset. A FIFO allocation handles this; a single "last receipt date" per item does not, which is a common way a homemade aging report ages the wrong units.
• Returns. The signed sample and product path here did not include a return, so returns are an unverified modeling decision, not a proven extension. If you handle them, decide and disclose the convention: map the return type (a customer return to stock versus a return to vendor), the original lot and date, the item's condition/status, and the sign of the movement, then independently reconcile the result before relying on it. A customer return can be aged from the original receipt, or from the return date if you treat returned goods as new; a vendor return removes units.
• Zero and negative on-hand. Items that net to zero should drop out of the aged view; a negative on-hand usually means a data problem — an issue with no matching receipt — worth catching before it distorts a total.

FAQFrequently asked questions

—The bottom line

An inventory aging report is only as trustworthy as the allocation behind each unit and the cutoff you age to. Under a FIFO assumption, fix the as-of date, keep only movements on or before it, run issues against the oldest receipts first, bucket the remaining open quantity, and check that the buckets reconcile to on-hand. Excel can do it for a handful of single-receipt items; once items have multiple receipts and a history of issues, building it straight from the receipt-and-issue records — a stated assumption, as of your date, reconciled — is the version you can act on, with actual lot/serial records or your approved method overriding it where you have them.

Build an inventory aging report from your receipts