Supply Chain & InventoryFree worksheetPDF, Excel, Word export

Supplier Lead Time Variance Worksheet

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.

Download:

Average actual	Longest actual	Variance (days)	Variance (%)
41.6667	45	6.6667	19%
12	13	0	0%
23.3333	26	2.3333	11.1%
7.3333	8	0.3333	4.8%
36	40	8	28.6%

Average variance (days)

3.4667

Worst variance (%)

28.6%

Longest actual lead time

45 days

Parts reviewed

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.

Compute lead times from receipt history

Optional: ask about a read-only analysis of actual versus planned lead times in your ERP.

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

How to use it

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.

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

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

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.

Frequently Asked Questions

How many receipts should I use?

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?

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?

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?

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.

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

Talk to an Expert Explore More Free Tools

Related Tools

Supply Chain & Inventory

More Supply Chain & Inventory tools

See all

Back to All Tools