# Supplier Lead Time Variability Tracking Worksheet

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

## Short answer

Record the last three actual lead times per part and see average, worst case and the gap to the lead time in your item master.

## Category

supply-chain

## Audience

- Materials planner
- Buyer
- Supplier development

## Tool

**Kind:** worksheet

**Mode:** editable

**Columns:**

#### Part number

**Key:** part

**Type:** text

#### Supplier

**Key:** supplier

**Type:** text

#### Planned lead time in ERP

**Key:** planned

**Type:** number

**Unit:** days

#### Receipt 1 actual

**Key:** r1

**Type:** number

**Unit:** days

#### Receipt 2 actual

**Key:** r2

**Type:** number

**Unit:** days

#### Receipt 3 actual

**Key:** r3

**Type:** number

**Unit:** days

#### Average actual

**Key:** avg

**Type:** number

**Expr:** (r1 + r2 + r3) / 3

**Format:** decimal

#### Worst actual

**Key:** worst

**Type:** number

**Expr:** max(r1, max(r2, r3))

**Format:** integer

#### Average minus planned

**Key:** gap

**Type:** number

**Expr:** (r1 + r2 + r3) / 3 - planned

**Format:** decimal

#### Spread

**Key:** spread

**Type:** number

**Expr:** max(r1, max(r2, r3)) - min(r1, min(r2, r3))

**Format:** integer

**Rows:**

**Part:** BRK-1001

**Supplier:** Kestrel Fasteners

**Planned:** 14

**R1:** 12

**R2:** 15

**R3:** 14

**Part:** HSG-2250

**Supplier:** Alder Castings

**Planned:** 28

**R1:** 35

**R2:** 41

**R3:** 33

**Part:** PCB-7710

**Supplier:** Northgate Electronics

**Planned:** 42

**R1:** 40

**R2:** 62

**R3:** 45

**Part:** SEAL-0412

**Supplier:** Marlow Rubber

**Planned:** 10

**R1:** 9

**R2:** 10

**R3:** 9

**Part:** CBL-3300

**Supplier:** Tamsin Wire

**Planned:** 21

**R1:** 24

**R2:** 22

**R3:** 30

**Totals:**

#### Average gap to planned

**Key:** gap

**Agg:** avg

**Format:** decimal

#### Longest single receipt

**Key:** worst

**Agg:** max

**Format:** days

#### Largest spread

**Key:** spread

**Agg:** max

**Format:** days

**Summary:**

#### Average days actual exceeds planned

**Key:** late_bias

**Expr:** avg_gap

**Format:** decimal

**Primary:** true

**Notes:** Use days from PO release (or confirmed order date) to goods receipt. A positive gap means the ERP lead time is too optimistic.

## Content

**Intro:** Reorder points, safety stock and MRP dates all rest on the planned lead time stored in the item master. This worksheet checks that number against what suppliers actually delivered on the last three receipts.

**Sections:**

#### How to fill it in

**Body:** List one row per part and supplier. Enter the planned lead time currently in your ERP, then the actual days from PO release to receipt for the three most recent receipts. Use the same start point every time; mixing PO release dates with confirmed dates makes the comparison meaningless. The sheet computes the average, the worst case, the gap to planned and the spread between best and worst.

#### Reading the result

**Body:** A positive average gap means the ERP believes the supplier is faster than they are, so MRP will release orders late and the plan will show shortages that were avoidable. A large spread with a small gap means the average is fine but the supplier is unpredictable, which is a safety stock problem rather than a lead time problem. Fix the first by updating the item master and the second by adding buffer or working on delivery reliability with the supplier.

**Bullets:**

- Large positive gap: update the planned lead time
- Large spread: increase safety stock or manage the supplier
- Negative gap: you may be holding too much buffer

#### Where the data lives

**Body:** Receipt history is in every ERP. In SAP, table EKBE holds goods receipt history for purchasing documents; in Oracle and Epicor the PO receipt tables carry receipt dates; in NetSuite item receipts are a standard saved search. Export the last receipts per part and compute days from order to receipt, then enter them here.

#### Common mistakes

**Body:** Do not average over a long history that includes a pandemic-era spike unless it is still representative. Do not ignore partial receipts: use the date the quantity you needed arrived. And do not change the item master lead time for every small wobble; update it when the gap persists across at least three receipts.

**Cta:** We can calculate lead time variability for your whole item master directly from ERP receipt history. Ask for a quick review of your data.

**Faq:**

#### Why three receipts and not more?

**Answer:** Three is the minimum that shows a pattern without needing a data export. Where you have more history, average a longer window in your ERP and enter the figures here as a summary. The method is the same.

#### Should I use calendar or working days?

**Answer:** Use whatever unit your ERP lead time field uses. Most systems store calendar days. Mixing the two will create a false gap, so check the field definition before comparing.

#### What gap should trigger a change?

**Answer:** A common working rule is to review any part where the average actual differs from planned by more than about 10 to 20 percent, or more than a few days on short lead times. Set your own threshold with your buyers.

#### Does the spread feed safety stock?

**Answer:** Yes. Safety stock formulas that include lead time variability use the standard deviation of lead time. The spread here is a rough guide to how large that variability is for each supplier.

## Search terms

supplier lead time tracking excel, actual vs quoted lead time, lead time variability worksheet, supplier delivery performance tracker, lead time update item master, planned lead time review

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