# Component Lead Time Risk Worksheet

> Source: https://netray.co/tools/component-lead-time-risk-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

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.

## Category

supply-chain

## Industry

Electronics Manufacturing

## Audience

- Electronics buyer
- Commodity manager
- Supply chain planner

## Tool

**Kind:** worksheet

**Mode:** editable

**Columns:**

#### Internal part number

**Key:** part

**Type:** text

#### Manufacturer part number

**Key:** mpn

**Type:** text

#### Manufacturer

**Key:** mfr

**Type:** text

#### Quoted lead time

**Key:** quoted_lt

**Type:** number

**Unit:** weeks

**Help:** Current distributor or factory quote

#### Historical lead time

**Key:** std_lt

**Type:** number

**Unit:** weeks

**Help:** Normal lead time you planned with

#### On hand plus open supply

**Key:** onhand

**Type:** number

**Help:** Usable stock plus confirmed inbound

#### Weekly usage

**Key:** weekly_use

**Type:** number

**Help:** Average gross requirement per week

#### Qualified sources

**Key:** sources

**Type:** number

**Help:** Count of AVL entries you can actually buy from

#### Lead time stretch

**Key:** stretch

**Type:** number

**Format:** decimal

**Expr:** if(gt(std_lt, 0), quoted_lt / std_lt, 1)

**Help:** Quoted divided by historical

#### Cover

**Key:** cover

**Type:** number

**Unit:** weeks

**Format:** decimal

**Expr:** if(gt(weekly_use, 0), onhand / weekly_use, 0)

#### Exposure gap

**Key:** gap

**Type:** number

**Unit:** weeks

**Format:** decimal

**Expr:** if(gt(weekly_use, 0), max(0, quoted_lt - onhand / weekly_use), 0)

**Help:** Weeks of demand not covered if you order today

#### Risk score (0-100)

**Key:** risk

**Type:** number

**Format:** integer

**Expr:** min(100, if(gt(weekly_use, 0), max(0, quoted_lt - onhand / weekly_use), 0) * 3 + max(0, if(gt(std_lt, 0), quoted_lt / std_lt, 1) - 1) * 20 + if(lte(sources, 1), 20, 0))

**Rows:**

**Part:** CAP-0402-100N

**Mpn:** GRM155R71C104KA88D

**Mfr:** Murata

**Quoted lt:** 16

**Std lt:** 12

**Onhand:** 90000

**Weekly use:** 4000

**Sources:** 3

**Part:** MCU-STM32-48

**Mpn:** STM32F407VGT6

**Mfr:** STMicroelectronics

**Quoted lt:** 40

**Std lt:** 20

**Onhand:** 1200

**Weekly use:** 150

**Sources:** 1

**Part:** CONN-HDR-2X10

**Mpn:** 61002021621

**Mfr:** Wurth Elektronik

**Quoted lt:** 12

**Std lt:** 10

**Onhand:** 6000

**Weekly use:** 300

**Sources:** 2

**Part:** PMIC-3V3-BUCK

**Mpn:** TPS62130RGTR

**Mfr:** Texas Instruments

**Quoted lt:** 26

**Std lt:** 14

**Onhand:** 800

**Weekly use:** 200

**Sources:** 1

**Part:** RES-0603-10K

**Mpn:** RC0603FR-0710KL

**Mfr:** Yageo

**Quoted lt:** 10

**Std lt:** 10

**Onhand:** 120000

**Weekly use:** 5000

**Sources:** 4

**Totals:**

#### Total exposure weeks

**Key:** gap

**Agg:** sum

**Format:** decimal

**Summary:**

#### Average risk score

**Key:** mean_risk

**Expr:** avg_risk

**Format:** integer

#### Largest exposure gap

**Key:** worst_gap

**Expr:** max_gap

**Format:** weeks

**Notes:** The risk score is a screening aid: 3 points per exposed week, 20 points per 1.0x of lead time stretch above historical, and 20 points if only one qualified source exists. Tune the weights to your own tolerance. Sort by score in Excel and review the top 10 percent first.

## Content

**Intro:** A lead time list that only shows quoted weeks tells you little. What matters is how far the quote has moved from what you planned with, and whether your stock and open orders cover that window. This worksheet scores each part on exactly those three things.

**Sections:**

#### How to use the worksheet

**Body:** Paste your critical or top-spend parts, one per row. Enter the current quoted lead time from your distributor or factory, the historical lead time your ERP item master uses, usable stock plus confirmed inbound, average weekly usage, and the number of sources on your approved vendor list that you can really buy from. The computed columns give lead time stretch, weeks of cover, the exposure gap and a 0 to 100 score. Export to Excel and share the ranked list with planning and engineering.

**Bullets:**

- Use gross requirement from MRP, not last month's issues.
- Count only sources that are qualified and have recent price quotes.
- Refresh quoted lead times at least monthly during tight markets.

#### The method behind the score

**Body:** Exposure gap is quoted lead time minus weeks of cover, floored at zero. It tells you how many weeks of demand would be unprotected if you placed an order today and the quote held. Lead time stretch is quoted divided by historical, and a part that doubled from 20 to 40 weeks is a market signal even if you currently have stock. A single qualified source adds a fixed penalty because you have no alternate if the factory allocates. The result is a ranking tool, not a probability, so use it to decide where to spend buyer time.

#### Reading the results and common mistakes

**Body:** In the example, the microcontroller scores highest: lead time doubled, only one source and a gap of about 32 weeks. The 0402 capacitor scores low even though its lead time rose, because it has three sources and deep stock. Common mistakes are mixing days and weeks, forgetting that safety stock is not usable cover, and ignoring parts with zero usage that sit on a new product BOM. Feed forecast demand for new builds, not just history.

**Bullets:**

- Treat any score above 60 as a call-the-factory item.
- Check for last time buy notices on parts with high stretch.
- Update the ERP planned lead time when the new quote is stable.

**Cta:** If you want this score refreshed automatically from your ERP item master, open POs and distributor quotes, we can build that as a read-only view over your data, on-prem if needed.

**Faq:**

#### What counts as a long lead time component?

**Answer:** There is no fixed threshold. Many teams flag anything above 12 to 16 weeks, or anything whose quote has stretched more than about 30 percent beyond the lead time used in planning. Judge it against your own build cycle and customer lead times.

#### Should safety stock be counted as cover?

**Answer:** Only if you are willing to consume it. Safety stock exists to protect against variation, so counting it as normal cover double counts it. Many buyers enter on hand excluding safety stock for this exercise and review the result both ways.

#### How often should I refresh the worksheet?

**Answer:** Weekly for critical parts during allocation, monthly otherwise. Distributor quotes move quickly, and an out of date lead time is worse than none because it hides risk behind a number that looks authoritative.

#### Can I change the scoring weights?

**Answer:** The weights are a starting point that works for general electronics. If you are in defense or medical and a single source is unacceptable, raise that penalty. Download the Excel file and edit the formula to match your policy.

## Search terms

component lead time risk worksheet, electronic component lead time tracker excel, long lead time components list, supply risk scoring electronics, lead time stretch calculator, component shortage risk template, buyer lead time report excel, single source component risk

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