Component Lead Time Risk Worksheet
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.
| Internal part number | Manufacturer part number | Manufacturer | Quoted lead time (weeks) | Historical lead time (weeks) | On hand plus open supply | Weekly usage | Qualified sources | Lead time stretch | Cover (weeks) | Exposure gap (weeks) | Risk score (0-100) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
1.3333 | 22.5 | 0 | 7 | |||||||||
2 | 8 | 32 | 100 | |||||||||
1.2 | 20 | 0 | 4 | |||||||||
1.8571 | 4 | 22 | 100 | |||||||||
1 | 24 | 0 | 0 |
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.
Automate lead time risk from your ERP
Optional: ask for a review of how this score could be fed from your item master, open POs and supplier quotes.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
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.
- 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
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
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.
- 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.
Frequently Asked Questions
What counts as a long lead time component?
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?
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?
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?
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.
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.
Related Tools
Electronics Excess and Obsolete Inventory Worksheet
Worksheet that finds excess quantity beyond 24 months of demand, estimates broker resale recovery and calculates the write-down per component.
Supply Chain & InventoryApproved Vendor List (AVL) Template
A controlled AVL document with document header, scope, approval criteria, vendor and manufacturer part table, and signature block.
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 & InventorySupplier Lead Time Variance Worksheet
Compare the lead time in your ERP with the last three actual receipts, and see the gap in days and percent. Export to Excel.
Electronics ManufacturingElectronic Component RFQ Request Template
A fill-in request for quotation to send to franchised distributors and manufacturers, with traceability and compliance requirements built in.
More Supply Chain & Inventory tools
See all- PCBA RFQ Request for Quote Template
- Electronic Component Distributor Quote Comparison Worksheet
- SMT Reel and MOQ Buy Quantity Calculator
- Broker vs Franchise Component Buy Cost Calculator
- Reel MOQ Overbuy and Carrying Cost Calculator
- Component Shortage Line-Down and Expedite Cost Calculator
- Electronic Component Sourcing Resilience Assessment
- Oracle Fusion Inventory Turns and Carrying Cost Calculator
- Distribution ERP Fill Rate Calculator