Last Time Buy Quantity Worksheet for EOL Components
A last time buy is a one-shot decision: once the manufacturer's deadline passes you cannot buy more, so the quantity has to be right the first time. This worksheet turns demand, desired cover and a buffer into a buy quantity per part and a total spend you can take to finance.
| Manufacturer part number | Manufacturer | LTB order deadline | Annual demand (pcs) | Months of cover wanted | Demand growth (%) | Safety buffer (%) | On hand plus on order (pcs) | Cover quantity | LTB quantity | Unit cost | LTB spend | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
103,950 | 91,951 | $864,339 | ||||||||||
136,080 | 116,080 | $214,748 | ||||||||||
37,260 | 33,760 | $70,896 | ||||||||||
33,000 | 29,000 | $39,150 |
Cover quantity = annual demand / 12 x months of cover x growth factor x buffer factor. Negative growth models a product in decline. Check the manufacturer minimum order and packaging multiple before you place the order, and confirm shelf life and MSL storage for long holds.
Generate LTB lists from your ERP
Optional: we can show how demand, stock and open orders feed this worksheet automatically.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Add one row per part named in the end-of-life notice. Enter the order deadline from the notice, your annual demand from the MRP forecast, and how many months of production you want to cover. Growth adjusts demand upward or downward over the cover period, and the buffer absorbs yield loss, rework and forecast error. Enter stock on hand plus open purchase orders and the sheet subtracts them to give the net quantity to buy. Unit cost then drives the total spend. Export to Excel to attach to the approval request.
- Use the deadline column to sort and chase the earliest dates first
- Pull annual demand from MRP, not from the last purchase order
- Include customer service and repair demand, not only new builds
- Round the final order to the packaging multiple
The logic behind the quantity
The formula is monthly demand multiplied by months of cover, scaled by growth and buffer, less what you already hold. It is deliberately simple so it can be defended in a review. Real programs often add a lifetime view: the number of units the product will still ship, times the quantity per unit, plus spares. If your contract requires support for ten years, set the months of cover to 120 and decide separately whether to buy that much or to qualify a replacement part.
Cost of getting it wrong
Buying too little means a redesign or a broker purchase later, usually at several times the original price with counterfeit risk. Buying too much ties up cash and may end as write-off. Carry cost, shelf life, moisture sensitivity and storage conditions should be part of the discussion. For parts with a long hold, ask the manufacturer about extended shelf life or re-tinning options, and keep records of storage conditions so the parts remain acceptable to the customer.
Common mistakes
Frequent errors are ignoring service demand, forgetting open purchase orders already placed against the part, using last year's volume when the product is ramping, and missing a second source that is not affected by the notice. Also check whether the notice covers the whole family or only one package and temperature grade, since a drop-in alternate may already exist.
Frequently Asked Questions
How many months of cover should I buy?
It depends on the product life and contract obligations. Many teams cover the remaining production forecast plus a service tail. If a redesign is planned, buy only enough to bridge until the new revision ships, plus a buffer for delays.
Should the buffer be a percentage or a fixed quantity?
A percentage is easier to defend for high volume parts. For low volume, high value parts a fixed quantity of spares is often better. You can enter either in the sheet by adjusting the buffer percentage until the buy matches the number you want.
What if the manufacturer has a minimum LTB quantity?
Compare the computed buy to the minimum and the packaging multiple. If the minimum is much larger than your need, ask a distributor about splitting the order or sharing with another customer, or weigh it against a redesign.
Does this replace a full obsolescence plan?
No. It sizes one decision. A full plan also covers alternates, redesign timing, stock rotation and customer notification. Use this sheet as the quantity input into that plan.
If your ERP already holds demand, stock and open orders, Netray can help generate this buy list directly from the data with a human approval step before anything is ordered.
Related Tools
PCN Product Change Notification Response Template
Document your review of a manufacturer PCN or EOL notice: affected parts, impact, decision, actions and approvals, ready to export.
Electronics ManufacturingComponent Cross Reference and Alternate Part Approval Worksheet
Track alternate and second source parts, their approval status and the saving each could bring. Editable rows, exports to Excel.
Supply Chain & InventorySMT Reel and MOQ Buy Quantity Calculator
See how minimum order quantity and reel size change what you actually buy, how much excess you hold and what it costs to carry.
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 Electronics Manufacturing tools
See all- J-STD-020 MSL Floor Life and Soak Reference
- PCB Panelization Calculator
- Solder Paste Volume Calculator
- SMT Placement Rate and Cycle Time Calculator
- Solder Alloy Melting Point Reference Table
- PCBA First Article Inspection Report Template
- MSD Moisture Sensitive Device Floor Life Log
- SMT Stencil Usage and Cleaning Log
- PCB Trace Current Capacity and Temperature Rise Calculator