Excess and Obsolete Inventory Reserve Calculator

Why do aging buckets get the reserve wrong?

Because they measure how long stock has sat, and the question that matters is whether anyone still wants it. Those are different questions, and the gap between them runs in both directions.

A part can be old and needed. A pump seal kit that ships in quarterly batches shows 210 days since last movement, which lands it in a 25% bucket on most policies. But annual demand is 1,200 units and you hold 300 — exactly three months of coverage, exactly what the policy says to carry. The calculator's defaults reserve $6,450 against it on aging and nothing on demand coverage. You have written down stock you will consume.

A part can be fresh and dead. A display module superseded by an engineering change was moving last week, so every aging bucket puts it at the lowest reserve rate on the day its demand went to zero. Forward demand is now 150 units a year for service, against 400 on hand — 32 months of coverage. Aging reserves nothing; demand coverage reserves $30,450.

That second case is the expensive one, and it is also the common one. Engineering changes, product end-of-life, a lost customer programme and a design revision all kill demand instantly while leaving the movement history looking healthy. The aging report cannot see any of them.

On the defaults, aging reserves $13,425 and demand coverage reserves $45,525 — 3.39 times more, with $32,100 understated.

How is the demand-coverage reserve actually calculated?

Three steps per part, and the third is the one that keeps it inside GAAP.

First, compute forward coverage. Quantity on hand plus inbound on order, divided by monthly forecast demand. The defaults show the controller board at 4.00 months, the seal kit at 3.00, the display module at 32.00, and the dead harness at no demand path at all.

Second, isolate the excess. Subtract the stock you intend to carry — forecast demand times your target coverage — from what you hold. At a three-month target, the controller board's 1,600 units against 1,200 needed leaves 400 units excess. A part with zero forecast demand, or no movement past the obsolete threshold, is excess in its entirety.

Third, write the excess down to what you will actually recover. This is where a flat bucket rate goes wrong in principle, not just in targeting. GAAP requires inventory at the lower of cost and net realisable value. So the reserve is the excess quantity times unit cost times one minus the expected recovery rate.

That recovery rate is doing real work and should differ sharply by part. The controller board is excess but current, so it recovers 85% — reserve $2,520 on $16,800 of excess. The superseded display module liquidates at 30% — reserve $30,450. The legacy harness scraps at 10% — reserve $12,555.

Applying one bucket percentage to all four implicitly assumes they all recover the same amount. They do not.

How do I know which reserve is right?

Back-test it. A reserve policy without evidence is an assertion, and this is the single most useful thing you can put in front of an auditor.

Compare each method's reserve against what you actually scrapped or liquidated in the last twelve months. The defaults carry $38,000 of actual disposals:

The aging method covered just over a third of what actually left the building. It was not conservative, it was wrong — and the $24,575 gap is a reserve top-up that would have landed in a later period as a surprise.

The demand method lands at 119.8%, slightly ahead of experience. That is the position you want: covering last year's outcome with a modest margin, not three times it and not a third of it.

Run this comparison every quarter and keep the history. Two or three periods of back-test data turns the recovery rates from assumptions into experience, and that is what makes the estimate defensible rather than merely documented.

What reserve percentage should I expect to see?

It depends heavily on what you stock, and the benchmark gap is diagnostic rather than a target to hit.

Disciplined operations hold E&O under about 7% of inventory value. Industrial and MRO operations commonly carry 15% to 25% excess or obsolete stock, so the reserve behind it is a material, recurring drag on earnings rather than an occasional charge.

The defaults land at 32.95%, which is 12.95 points above a 20% benchmark. Read that as a signal about stocking, not about the accounting. One superseded part at 32 months of coverage and one dead legacy part account for nearly all of it. The reserve is correct; the inventory position that created it is the problem.

That is the useful way to use the benchmark. A high reserve percentage with a clean back-test means your measurement is right and your purchasing or engineering-change process is leaking money. A low reserve percentage with a failing back-test means the opposite — the stock position may be fine and the measurement is understating it.

Two things that legitimately push the percentage up without indicating a problem: long-life capital equipment whose service parts must be held for a decade, and regulated or safety-critical spares where stocking is mandated regardless of turnover. If that is your situation, segment those parts and set their coverage target separately rather than letting them distort the portfolio number.

When does this method stop working?

Four boundaries, and the first two will matter to most users.

One location, no demand cascade. The model nets supply against demand at a single level. Real E&O analysis consumes finished-goods demand first, then cascades the remainder down through work in process, semi-finished and raw material, checking alternate bills of material along the way. A raw component that looks dead at its own level may still be consumed by a different parent. If you run multi-level BOMs, this calculator sizes the exposure rather than finalising it.

The forecast is yours and the answer depends on it. Everything here turns on forecast annual demand. A part is excess because the forecast says so, and a stale forecast produces a confident wrong reserve. The classification output exists partly so you can sanity-check it: if a part reads "Obsolete" and your planners disagree, the forecast is the thing to fix first.

Excess reserves are a policy choice, not a measurement requirement. Strictly, GAAP requires a write-down only where net realisable value has fallen below cost. Stock that is simply slow but will eventually sell at full price does not require a reserve at all. Many companies reserve for it anyway, to reflect carrying cost and risk. That is defensible, but it is a policy you should state and apply consistently rather than something the standard compels.

Four parts, not four thousand. The model demonstrates the method on a handful of parts. Running it across a real item master means the same logic in a query against item, location, on-hand, inbound, demand and aging — which is the point at which a spreadsheet becomes the prototype rather than the production tool.