Excess obsolete inventory analysis: demand and supported use

Excess obsolete inventory analysis separates stock above approved needs from stock with no supported future use. Calculate the quantities by SKU and location, value them on a consistent carrying-cost basis, and reconcile them to the stock balance. The resulting exposure is a review list; finance decides whether the evidence supports a valuation adjustment.

Define excess and obsolete separately

Excess means more stock than the business expects to need within its approved review horizon, after allowing for the stated buffer. The goods may still be usable and saleable. The question is whether holding that much is supported by the demand assumptions.

Obsolete means the business has no supported future use for the goods. Discontinued equipment, expired material, failed specifications, or a canceled product can trigger review, but each conclusion needs evidence. Ask who confirmed the status, on what date, and whether an alternative use remains available.

Age is a separate dimension. Recently received stock can become excess after a canceled order, while an old service spare can remain necessary. Use inventory aging by site to identify candidates for investigation, then apply the demand and use tests here.

Assemble the review file

Start with a dated stock export containing SKU, site, stocking unit, on-hand quantity, and unit carrying cost. Add approved demand for a stated horizon, an approved buffer, the obsolete flag, and the evidence owner. Keep source dates and approval references beside the figures.

Check physical quantities before valuing exposure. The GAO inventory-count guide describes controls for reliable counts, including cutoff procedures and research into discrepancies. An unsupported stock balance makes even a well-designed demand comparison unreliable.

Use consistent scope. A site analysis must specify whether transfers can satisfy demand elsewhere. This example covers owned, on-hand stock at one US distribution site; inbound orders are excluded and no intersite transfers are assumed. Demand already includes firm orders and the remaining forecast without duplication.

Review whether the demand assumptions remain credible. Persistent overstatement in forecast bias by SKU and site is a reason to revisit them with planning, not to quietly change the approved inputs during the calculation.

Calculate mutually exclusive quantities

The distributor uses a 90-day demand horizon for this review. These are its approved inputs; the Board SKU is confirmed obsolete, and the other SKUs have supported future use.

Put the inputs in A:F, rows 2:5. Calculate excess units in G2, obsolete units in H2, and retained units in I2:

=IF(F2="Yes",0,MAX(0,B2-C2-D2))
=IF(F2="Yes",B2,0)
=B2-G2-H2

Fill down. Confirmed obsolete stock goes wholly into its own category, so the same units cannot also inflate excess. Retained means outside these exposure categories under the supplied assumptions; it does not prove the stock is otherwise risk-free.

Calculate excess carrying value in J2, obsolete carrying value in K2, and total carrying value in L2:

=G2*E2
=H2*E2
=B2*E2

Motor excess is 120 - 60 - 10 = 50 units, worth 50 x $80 = $4,000. Cable exactly meets demand plus buffer. Seal has less stock than its approved requirement, so it has no excess; flooring the result at zero prevents a shortage from offsetting Motor's surplus.

Reconcile the exposure

Retained value is 70 x $80 + 500 x $4 + 40 x $5 = $7,800. The value identity is therefore 15,800 = 4,000 excess + 4,000 obsolete + 7,800 retained. Each row must also satisfy on-hand units = excess units + obsolete units + retained units.

Combined exposure is 4,000 + 4,000 = $8,000, or 8,000 / 15,800 = 50.6% of carrying value, rounded to one decimal place. A separate obsolete-value check in Excel is:

=SUM(K2:K5)-SUMIFS(L2:L5,F2:F5,"Yes")

It must return zero. Microsoft documents SUMIFS as summing values that meet the specified criteria. Covirage is not affiliated with Microsoft.

Test the assumption that moves the result

Planning then approves a higher Motor demand figure, with every other input unchanged. Keep the original and revised versions rather than replacing the evidence behind the first review.

The revision reduces excess by 30 units and $2,400. Combined exposure becomes 1,600 + 4,000 = $5,600. Nothing moved physically; the supported demand assumption changed. That is why the approval date and demand version belong in the report.

Finance next reviews recovery evidence and existing adjustments. For US tax inventory, IRS Publication 538 discusses damaged or otherwise subnormal goods and valuation using selling prices less direct disposition costs. Those tax rules are distinct from approving a book reserve; use the relevant accounting policy. Posted write-off analysis tracks entries after approval and posting.

Where it goes wrong

• Counting orders and forecast twice. Define how firm orders consume the forecast before computing demand.
• Treating blank demand as zero. An unavailable input requires review; it does not establish that all stock is excess.
• Counting obsolete units again as excess. Make categories exclusive before adding their values.
• Turning exposure into expense automatically. Preserve recovery evidence, existing allowances, and finance's approval separately.

Review your stock file in Covirage

Bring your stock export, approved demand, buffers, and disposition assumptions to Covirage; it maps the columns, and its tools compute the supplied comparisons and check the quantity and value totals. The external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own inventory file.

See Covirage for supply chain teams for the supporting business context.