How does this template calculate OTIF?

OTIF = lines delivered on time and in full / lines delivered. A line is on time when it arrives no more than the allowed days early and no more than the allowed days late, and in full when the delivered quantity is within your short and over tolerances. In the example, 111 of 144 lines pass both tests: 77.1 percent.

Does the on-time rate match the On-Time Delivery Rate calculator?

Yes, when each order line is a delivery. The calculator's example is 940 on-time deliveries out of 1,000: 94.0 percent, 3.0 points below a 97 percent target. The workbook counts on-time lines the same way: in the example 129 of 144 lines, 89.6 percent, 7.4 points below its 97 percent on-time target.

Should an early delivery count as on time?

Only if your customer accepts it. Set the days early allowed on Setup: with 2, a line due on 10 September is on time if it arrives from 8 September; with 0, only on the day or late within the late allowance. In the example, five lines failed because they were shipped three or four days early.

Why is OTIF so much lower than the unit fill rate?

Because OTIF counts lines and fails a whole line for one missing unit, while the fill rate counts units. In the example a 400-unit line delivered 360 short is one failed line but still fills 90 percent of its units, so the unit fill rate is 98.2 percent while OTIF is 77.1 percent. Report both, and work on the reasons behind the failed lines.

How do I log a part shipment?

Log the first delivery against the order line with the quantity delivered; it will read In full = No. Ship the balance as a new line with its own promised date. That way the miss is counted once, the balance can still be on time, and the reason says why the line was split.

Can I use it without Excel?

Yes: a CSV with live formulas of the Delivery Log opens in any spreadsheet, with days late, On time, In full, OTIF and units filled as formulas against the window and tolerances listed below the rows. The dashboard, charts, dropdowns and self-check are in the .xlsx.

Planning and Supply Chain

OTIF Delivery Tracker Excel Template

Log every delivered order line against its promise and see on-time, in-full and OTIF performance by week and by customer, with the reason behind every miss.

Overview

This OTIF delivery tracker is a working Excel workbook for plant and operations managers, customer service and logistics leads and planners who report delivery performance. It ships with example data for a fictional auto-components maker in India, 144 order lines to six fictional customers over eight weeks, so the dashboard, charts and tables are populated the moment you open it; replace the rows with your own deliveries and everything recalculates.

Each delivered line is tested twice. On time compares the delivered date with the promised date, inside a window you set (two days early and none late in the example). In full compares the delivered quantity with the ordered quantity, inside a short and an over-delivery tolerance you set (none short and 5 percent over in the example). A line is OTIF only when it passes both, and every percentage counts lines. The on-time rate is the same calculation as the On-Time Delivery Rate calculator on this site, with each line as a delivery; the unit fill rate adds the quantity view.

In the example, 111 of 144 lines were OTIF, 77.1 percent against a 95 percent target: 89.6 percent on time and 87.5 percent in full, while the unit fill rate was 98.2 percent, so most misses were a few units short rather than lost orders. A casting shortage pulled OTIF down to 61 to 67 percent in the three weeks from 17 August, material shortage is the biggest reason in the Pareto, and the customer three days away by road gets only 58.8 percent of its lines OTIF.

What this template includes

Delivery Log table: order line, customer, item, quantity ordered, promised date, delivered date and quantity delivered, one row per order line delivered
Per line: days early or late, On time, In full and OTIF words (Yes/No) and the units filled, all against the rules you set
Your own OTIF, on-time and in-full targets, delivery window (days early and late allowed) and short and over-delivery tolerances on Setup, with customers and miss reasons as dropdown lists
Dashboard KPI tiles: OTIF, on time and in full against your targets, and the unit fill rate
Two native charts: OTIF by promised week with the target line, and a Pareto of missed lines by reason
Tables by promised week (lines, on time, in full, OTIF, lines missed) and by customer (with an OTIF status word and the unit fill rate)
Check column that names problems in words: a miss without a reason, a customer or reason not on the list, an order line listed twice, a missing value
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference, including the On-Time Delivery Rate calculator's example (Self-check: PASS)

Suggested use case

Use it to report delivery performance the same way every week, to see whether lines fail on timing or on quantity, to give each miss an owner through its reason code, and to discuss performance with each customer using their own lines.

How to use it

On Setup, enter your company name, OTIF, on-time and in-full targets, the days early and late a delivery may be, and the short and over-delivery tolerances, then list your customers and miss reasons.
On Delivery Log, add one row per order line when it is delivered: customer, item, quantity ordered, promised date, delivered date and quantity delivered.
Pick a reason for every line that reads OTIF = No; the Check column flags misses without one.
Open the Dashboard to see OTIF by week against target, the Pareto of reasons and the table by customer.
Work on the top reason, and share each customer's row of the table in your next review.

Frequently Asked Questions

How does this template calculate OTIF?: OTIF = lines delivered on time and in full / lines delivered. A line is on time when it arrives no more than the allowed days early and no more than the allowed days late, and in full when the delivered quantity is within your short and over tolerances. In the example, 111 of 144 lines pass both tests: 77.1 percent.
Does the on-time rate match the On-Time Delivery Rate calculator?: Yes, when each order line is a delivery. The calculator's example is 940 on-time deliveries out of 1,000: 94.0 percent, 3.0 points below a 97 percent target. The workbook counts on-time lines the same way: in the example 129 of 144 lines, 89.6 percent, 7.4 points below its 97 percent on-time target.
Should an early delivery count as on time?: Only if your customer accepts it. Set the days early allowed on Setup: with 2, a line due on 10 September is on time if it arrives from 8 September; with 0, only on the day or late within the late allowance. In the example, five lines failed because they were shipped three or four days early.
Why is OTIF so much lower than the unit fill rate?: Because OTIF counts lines and fails a whole line for one missing unit, while the fill rate counts units. In the example a 400-unit line delivered 360 short is one failed line but still fills 90 percent of its units, so the unit fill rate is 98.2 percent while OTIF is 77.1 percent. Report both, and work on the reasons behind the failed lines.
How do I log a part shipment?: Log the first delivery against the order line with the quantity delivered; it will read In full = No. Ship the balance as a new line with its own promised date. That way the miss is counted once, the balance can still be on time, and the reason says why the line was split.
Can I use it without Excel?: Yes: a CSV with live formulas of the Delivery Log opens in any spreadsheet, with days late, On time, In full, OTIF and units filled as formulas against the window and tolerances listed below the rows. The dashboard, charts, dropdowns and self-check are in the .xlsx.