OTIF — On-Time In-Full — is the percentage of orders delivered both by the promised date and complete. The concept is simple; the spreadsheet reality is where scores quietly go wrong. This is the clean way to build the calculation in Excel, with a free template, and an honest note on when the spreadsheet stops being enough. (New to the metric itself? Start with our full OTIF guide — formula, benchmarks, and retailer penalty programs.)
Download the template
Download the OTIF calculation template (.xlsx) — no email required. It contains sample orders, the three formulas below, and a summary block that computes your OTIF percentage. Replace the sample rows with an export from your ERP or TMS and the score updates live.
The calculation, step by step
You need five columns per order: order ID, promised date, actual delivery date, quantity ordered, quantity delivered. Then three formulas:
On time? =IF(DeliveredDate<=PromisedDate,"Yes","No") — delivered on or before the promise.
In full? =IF(QtyDelivered>=QtyOrdered,"Yes","No") — full quantity delivered.
OTIF pass: =IF(AND(OnTime="Yes",InFull="Yes"),1,0) — both must hold.
Your OTIF percentage is then =SUM(OTIFpass)/COUNT(orders). Fifteen orders, thirteen passes: 86.7%.
The two definition traps that skew every spreadsheet score
Trap 1: which date is 'promised'? Customer-requested date, first-confirmed date, or last-updated date? Each produces a different score from the same deliveries — and your customer scores you on their definition. If you report OTIF against re-promised dates while the retailer measures against the original PO date, your 96% is their 88%.
Trap 2: what level is 'in full'? Order level (every line complete), line level, or case level? Retail compliance programs typically measure at levels stricter than most internal spreadsheets. Align the formula to each customer's scoring rules before you trust the number — the template has a column for customer so you can segment.
The honest limits of Excel OTIF
A spreadsheet can tell you last month's score. It cannot tell you that an order currently in transit is about to fail — which is the only moment the information is worth money. By the time the miss is a row in the export, the expedite window is closed, the penalty is accrued, and the customer already noticed. There's also a quieter cost: if producing the number takes a monthly export-and-reconcile exercise, the effort goes into measuring OTIF instead of improving it — and the misses cluster in execution, as we cover in the OTIF guide.
Where Orkestra fits
Orkestra computes OTIF live from the order and shipment data already in your ERP, TMS, and WMS — segmented by customer, scored by each customer's own definition — and, more usefully, flags the at-risk orders while there's still time to protect the promise. The spreadsheet measures the score; the platform moves it. OTIF is also only one layer of a scorecard: see where it sits among the supply chain KPIs that matter for end-to-end reporting. See how on supply chain analytics, or bring your current OTIF number to a 30-minute call and we'll show you what sits behind it.
