# Last Time Buy Quantity Worksheet for EOL Components

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

## Short answer

Size last time buy quantities for end-of-life parts from annual demand, months of cover, growth and buffer. Editable rows, exports to Excel.

## Category

electronics

## Industry

Electronics Manufacturing

## Audience

- Electronics buyer
- Supply chain planner
- Component engineer

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Manufacturer part number

**Key:** mpn

**Type:** text

#### Manufacturer

**Key:** mfr

**Type:** text

#### LTB order deadline

**Key:** ltb_deadline

**Type:** date

#### Annual demand

**Key:** annual

**Type:** number

**Unit:** pcs

#### Months of cover wanted

**Key:** months

**Type:** number

#### Demand growth

**Key:** growth

**Type:** number

**Unit:** %

#### Safety buffer

**Key:** buffer

**Type:** number

**Unit:** %

#### On hand plus on order

**Key:** on_hand

**Type:** number

**Unit:** pcs

#### Cover quantity

**Key:** need

**Type:** number

**Expr:** annual / 12 * months * (1 + growth / 100) * (1 + buffer / 100)

**Format:** integer

#### LTB quantity

**Key:** buy

**Type:** number

**Expr:** max(0, ceil(need - on_hand))

**Format:** integer

#### Unit cost

**Key:** cost

**Type:** number

**Format:** currency

#### LTB spend

**Key:** ext

**Type:** number

**Expr:** buy * cost

**Format:** currency

**Rows:**

**Mpn:** STM32F407VGT6

**Mfr:** STMicroelectronics

**Ltb deadline:** 2026-12-15

**Annual:** 18000

**Months:** 60

**Growth:** 5

**Buffer:** 10

**On hand:** 12000

**Cost:** 9.4

**Mpn:** LM2596S-5.0

**Mfr:** Texas Instruments

**Ltb deadline:** 2027-01-31

**Annual:** 42000

**Months:** 36

**Growth:** 0

**Buffer:** 8

**On hand:** 20000

**Cost:** 1.85

**Mpn:** ATMEGA328P-AU

**Mfr:** Microchip

**Ltb deadline:** 2027-03-31

**Annual:** 9000

**Months:** 48

**Growth:** -10

**Buffer:** 15

**On hand:** 3500

**Cost:** 2.1

**Mpn:** MAX3232CSE

**Mfr:** Analog Devices

**Ltb deadline:** 2027-02-28

**Annual:** 6000

**Months:** 60

**Growth:** 0

**Buffer:** 10

**On hand:** 4000

**Cost:** 1.35

**Totals:**

#### Total LTB pieces

**Key:** buy

**Agg:** sum

**Format:** integer

#### Total LTB spend

**Key:** ext

**Agg:** sum

**Format:** currency

**Summary:**

#### Average spend per part

**Key:** avg_line

**Expr:** sum_ext / count

**Format:** currency

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

## Content

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

**Sections:**

#### How to use the worksheet

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

**Bullets:**

- 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

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

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

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

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

**Faq:**

#### How many months of cover should I buy?

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

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

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

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

## Search terms

last time buy calculator, last time buy worksheet, LTB quantity calculation, EOL component last time buy, end of life component buy template, last time buy excel, PCN last time buy planning, obsolete component buy quantity

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