# Supplier Lead Time Variance Worksheet

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

## Short answer

Compare the lead time in your ERP with the last three actual receipts, and see the gap in days and percent. Export to Excel.

## Category

supply-chain

## Audience

- Buyer
- Production planner
- Supplier quality

## Tool

**Kind:** worksheet

**Mode:** editable

**Columns:**

#### Part number

**Key:** part

**Type:** text

#### Supplier

**Key:** supplier

**Type:** text

#### ERP lead time

**Key:** quoted

**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_actual

**Type:** number

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

**Format:** decimal

#### Longest actual

**Key:** worst

**Type:** number

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

**Format:** number

#### Variance (days)

**Key:** var_days

**Type:** number

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

**Format:** decimal

#### Variance (%)

**Key:** var_pct

**Type:** number

**Expr:** if(gt(quoted, 0), ((r1 + r2 + r3) / 3 - quoted) / quoted * 100, 0)

**Format:** percent

**Rows:**

**Part:** PN-48213

**Supplier:** Northline Castings

**Quoted:** 35

**R1:** 38

**R2:** 42

**R3:** 45

**Part:** PN-51002

**Supplier:** Ridge Machining

**Quoted:** 12

**R1:** 12

**R2:** 13

**R3:** 11

**Part:** PN-60110

**Supplier:** Apex Metal Forming

**Quoted:** 21

**R1:** 24

**R2:** 20

**R3:** 26

**Part:** PN-88014

**Supplier:** Apex Fasteners

**Quoted:** 7

**R1:** 7

**R2:** 8

**R3:** 7

**Part:** PN-91030

**Supplier:** Delta Electronics

**Quoted:** 28

**R1:** 35

**R2:** 33

**R3:** 40

**Totals:**

#### Average variance (days)

**Key:** var_days

**Agg:** avg

**Format:** decimal

#### Worst variance (%)

**Key:** var_pct

**Agg:** max

**Format:** percent

#### Longest actual lead time

**Key:** worst

**Agg:** max

**Format:** days

**Summary:**

#### Parts reviewed

**Key:** lines

**Expr:** count

**Format:** integer

**Notes:** Measure lead time from PO release or order date to receipt into stock, in calendar days, the same way your ERP defines it. Three receipts is a minimum; use more history when you have it. Consider the longest actual for critical parts.

## Content

**Intro:** MRP trusts the lead time in the item or vendor record. If suppliers really take ten days longer, every planned order is released late. This worksheet compares the number in your ERP with what actually happened.

**Sections:**

#### How to use it

**Body:** For each part, enter the lead time in your ERP and the actual days for the last three receipts, measured from order date to receipt. The sheet gives average actual, longest actual and variance in days and percent. Sort by variance to find the parts where the ERP is most wrong, then decide whether to update the lead time, change the safety lead time, or talk to the supplier.

**Bullets:**

- Use the same start and end definitions as your ERP
- Exclude one-off events with a known cause
- Update critical long lead items first
- Re-check after you change a value

#### Reading the variance

**Body:** A small positive variance can be absorbed by safety lead time. A large and consistent positive variance means the planning lead time is simply wrong and should be updated; planned orders and release dates will move accordingly. A wide spread between receipts means the supplier is erratic, so look at safety stock or a second source rather than only changing the average. Negative variance means you may be releasing too early and holding stock.

#### Mistakes and ERP notes

**Body:** Do not copy the supplier's quote without checking receipts. Do not overwrite lead times on many items without reviewing the demand impact, because longer lead times create immediate past due exceptions. In SAP see planned delivery time in the material master and the info record; in Epicor and SyteLine check the vendor part lead time; in Oracle the item or supplier lead time. Update in controlled batches.

**Cta:** Netray can help calculate actual lead times from your ERP receipt history, read-only, and flag the parts where the planning value has drifted.

**Faq:**

#### How many receipts should I use?

**Answer:** At least three, and ideally six to twelve months of history. Fewer receipts give noisy averages. For infrequent items use what exists and treat the result with caution.

#### Average or longest actual?

**Answer:** Use the average for the planning lead time and cover the spread with safety stock or safety lead time. For critical items where a stop is costly, many planners look at the longest actual as well.

#### What if my ERP uses working days?

**Answer:** Enter the actual in the same unit as the ERP value so the comparison is fair, and convert the whole row consistently.

#### Will changing lead times cause exceptions?

**Answer:** Yes, for lengthened lead times MRP will show past due orders until demand and supply are re-planned. Plan the change with buyers and communicate it.

## Search terms

supplier lead time variance, actual vs planned lead time excel, purchase lead time analysis template, supplier delivery performance worksheet, update item lead time from receipts, vendor lead time tracking spreadsheet, planned lead time accuracy

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