Excess and Obsolete Electronics Inventory Worksheet
Electronics inventory loses value fast: parts go end of life, designs change and a single over-buy during a shortage can sit for years. This worksheet shows how much of each line exceeds your demand window and what you might realistically recover versus write off.
| Part number | Description | Lifecycle status | On hand | Unit cost | Annual usage | Expected resale (% of cost) | Extended value | Months of supply | Excess qty (over 24 months) | Excess value | Estimated recovery | Write-down | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
$5,040 | 36 | 4,000 | $1,680 | $672 | $1,008 | ||||||||
$11,550 | 60 | 180 | $6,930 | $2,426 | $4,505 | ||||||||
$8,800 | 48 | 40,000 | $4,400 | $2,200 | $2,200 | ||||||||
$2,790 | 99 | 450 | $2,790 | $419 | $2,372 | ||||||||
$2,750 | 25 | 200 | $110 | $50 | $61 |
Excess is quantity beyond 24 months of annual usage, a common planning threshold; change the 2 in the formulas for 12 or 36 months. Resale percentages are your own estimates, not quotes. Reserve policy and accounting treatment belong to your controller and auditors.
Feed E and O analysis from live ERP data
Optional: ask how this analysis could run directly on your inventory and demand tables.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Export on hand, unit cost and demand from your ERP and paste them into the table. Set lifecycle status from your component data, enter annual usage that reflects forecast and not just history, and add a conservative resale percentage. The sheet calculates months of supply, quantity beyond 24 months, excess value, the amount you might recover and the remaining write-down. Sort by excess value and work the top lines first. Share the exported Excel file with finance and materials.
The method
Months of supply is on hand divided by annual usage times 12. Excess quantity is on hand minus two years of usage, floored at zero. Excess value is that quantity times unit cost. Recovery applies your resale percentage, and write-down is the remainder. Obsolete parts with zero usage are entirely excess. Note that the sheet works on single items, so check customer owned stock, consigned stock and quantities already committed to firm orders before acting.
- Use forecast demand for products that are growing or ramping down.
- Net out open work orders and firm customer demand.
- Never sell parts with a customer proprietary marking without permission.
Options for excess stock and common mistakes
Options include returning to the distributor or manufacturer, selling to an independent distributor or broker, offering to a contract manufacturer, using the part in an engineering change, or scrapping with a documented disposition. Check return and stock rotation rights first, since they often recover more than the open market. A common mistake is writing down everything at once and losing the chance to sell, or the opposite, carrying obsolete stock at full cost. Only sell traceable, correctly stored parts, and keep moisture sensitive and ESD handling intact so value is not lost in storage.
Frequently Asked Questions
How many months of supply counts as excess?
There is no universal rule. Many electronics companies use 12 to 24 months, shorter for fast-moving or short-life parts. Choose a threshold that matches your policy and your auditors, and change the factor in the worksheet formulas accordingly.
How do I estimate resale value?
Ask two or three brokers or independent distributors for indicative quotes, check recent sold listings, and use a conservative percentage of cost. Active, in-demand parts may fetch well; obsolete or custom parts may recover little. These are estimates, not offers.
Does this replace an E and O reserve calculation?
No. This is a working analysis for materials and buyers. Reserve methodology is an accounting decision, often rule-based on age and usage. Use the output as supporting detail and align with your controller.
Should NRND parts be treated as excess?
Not automatically. Not recommended for new designs means the part is still in production but being phased out. If it is in current products, the question is whether to place a last time buy, not whether it is excess. Review demand first.
If you want this refreshed from live ERP inventory and demand instead of pasted extracts, we can build a read-only view over your data, on-prem if required.
Related Tools
Excess and Obsolete Inventory Reserve Calculator
Apply reserve percentages to aged inventory buckets and see the total E&O reserve, net inventory and the margin impact of changing the policy.
Electronics ManufacturingElectronic Component Lifecycle Status Reference
Searchable guide to lifecycle terms such as NRND, last time buy, EOL and obsolete, with what each means and what a buyer or engineer should do.
Supply Chain & InventoryComponent Lead Time Risk Worksheet
Score each electronic component for lead time risk using quoted vs historical lead time, stock cover and source count, then export the ranked list to Excel.
Costing & FinanceSlow Moving and Obsolete Inventory Disposition Memo Template
A ready memo for proposing disposition of slow moving or obsolete stock, with options, financial effect and approval lines.
Electronics ManufacturingComponent Obsolescence Risk Assessment
Answer 10 questions to score how exposed your BOMs are to component EOL, DMSMS, and lifecycle blind spots, with graded recommendations.
More Costing & Finance tools
See all- PCBA BOM Cost Rollup Worksheet
- Component Price Escalation BOM Impact Calculator
- SAP QM Inspection Effort and Cost Calculator
- SAP Standard Cost Estimate BOM Rollup Calculator
- Legacy ERP Retirement Cost and Payback Calculator
- JD Edwards World Month-End Close Procedure Template
- Distributor Customer Credit Application Template
- Distributor Customer Profitability Worksheet
- Distributor GMROI and Inventory Turns Calculator