# Excess and Obsolete Electronics Inventory Worksheet

> Source: https://netray.co/tools/electronics-excess-obsolete-inventory-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Worksheet that finds excess quantity beyond 24 months of demand, estimates broker resale recovery and calculates the write-down per component.

## Category

cost-finance

## Industry

Electronics Manufacturing

## Audience

- Materials manager
- Cost accountant
- Electronics buyer
- Controller

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Part number

**Key:** part

**Type:** text

#### Description

**Key:** desc

**Type:** text

#### Lifecycle status

**Key:** status

**Type:** select

**Options:**

- Active
- NRND
- Last time buy
- Obsolete

#### On hand

**Key:** onhand

**Type:** number

#### Unit cost

**Key:** cost

**Type:** number

**Format:** currency

#### Annual usage

**Key:** usage

**Type:** number

**Help:** Forecast or trailing 12 months, whichever you trust

#### Expected resale

**Key:** resale

**Type:** number

**Unit:** % of cost

**Help:** Broker or excess market recovery estimate

#### Extended value

**Key:** ext

**Type:** number

**Format:** currency

**Expr:** onhand * cost

#### Months of supply

**Key:** months

**Type:** number

**Format:** decimal

**Expr:** if(gt(usage, 0), onhand / usage * 12, 99)

#### Excess qty (over 24 months)

**Key:** excess_qty

**Type:** number

**Format:** integer

**Expr:** max(0, onhand - usage * 2)

#### Excess value

**Key:** excess_val

**Type:** number

**Format:** currency

**Expr:** max(0, onhand - usage * 2) * cost

#### Estimated recovery

**Key:** recover

**Type:** number

**Format:** currency

**Expr:** max(0, onhand - usage * 2) * cost * resale / 100

#### Write-down

**Key:** writedown

**Type:** number

**Format:** currency

**Expr:** max(0, onhand - usage * 2) * cost * (1 - resale / 100)

**Rows:**

**Part:** IC-LDO-1V8

**Desc:** LDO regulator 1.8 V

**Status:** Active

**Onhand:** 12000

**Cost:** 0.42

**Usage:** 4000

**Resale:** 40

**Part:** IC-FPGA-A7

**Desc:** FPGA, Artix class

**Status:** NRND

**Onhand:** 300

**Cost:** 38.5

**Usage:** 60

**Resale:** 35

**Part:** CAP-1206-10U

**Desc:** 10 uF X7R 1206

**Status:** Active

**Onhand:** 80000

**Cost:** 0.11

**Usage:** 20000

**Resale:** 50

**Part:** CONN-D37

**Desc:** D-sub 37 pin, legacy

**Status:** Obsolete

**Onhand:** 450

**Cost:** 6.2

**Usage:** 0

**Resale:** 15

**Part:** XTAL-25M

**Desc:** 25 MHz crystal

**Status:** Active

**Onhand:** 5000

**Cost:** 0.55

**Usage:** 2400

**Resale:** 45

**Totals:**

#### Total inventory value

**Key:** ext

**Agg:** sum

**Format:** currency

#### Total excess value

**Key:** excess_val

**Agg:** sum

**Format:** currency

#### Estimated recovery

**Key:** recover

**Agg:** sum

**Format:** currency

#### Proposed write-down

**Key:** writedown

**Agg:** sum

**Format:** currency

**Summary:**

#### Excess as share of value

**Key:** excess_share

**Expr:** sum_excess_val / sum_ext * 100

**Format:** percent

#### Write-down as share of value

**Key:** wd_share

**Expr:** sum_writedown / sum_ext * 100

**Format:** percent

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the worksheet

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### How many months of supply counts as excess?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

excess and obsolete inventory worksheet, excess inventory electronics components, e and o inventory excel template, sell excess electronic components, obsolete component write down, months of supply calculation, excess inventory broker resale, inventory reserve electronics

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json