Supply Chain & InventoryFree worksheetPDF, Excel, Word export

Supplier Lead Time Variability Tracking Worksheet

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.

Download:

Average actual	Worst actual	Average minus planned	Spread
13.6667	15	-0.3333	3
36.3333	41	8.3333	8
49	62	7	22
9.3333	10	-0.6667	1
25.3333	30	4.3333	8

Average gap to planned

3.7333

Longest single receipt

62 days

Largest spread

22 days

Average days actual exceeds planned

3.7333

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

Lead time audit from your ERP data

Optional: a short review to compare planned and actual lead times across your full item master.

Optional. The tool and every export work without this. No spam.

How to fill it in

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

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.

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

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

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.

Frequently Asked Questions

Why three receipts and not more?

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?

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?

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?

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.

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

Talk to an Expert Explore More Free Tools

Related Tools

Supply Chain & Inventory

More Supply Chain & Inventory tools

See all

Back to All Tools