Build an On-Time Delivery (OTIF) Report with Python

On-time delivery is the number customers judge a supplier by, and the one that starts arguments internally: was the order late because it shipped late, because the carrier was slow, or because it arrived in two parts? An OTIF (on time, in full) report answers that only if the rules are precise: what counts as on time, what happens with split shipments, whether weekends count. Python makes the rules explicit and applies them to every order line the same way. This page is part of Building Finance and Operations Reports.

Prerequisites

pip install pandas numpy xlsxwriter

deliveries_q3.xlsx has two sheets, the shape most ERP and warehouse systems export: Orders, one row per order line with the quantity ordered, the promised delivery date, the customer and the carrier; and Shipments, one row per delivery against an order line, with the quantity delivered and the proof-of-delivery date. Some lines were delivered in two shipments; some are short by a unit. The report covers July to September 2026 with a 95% OTIF target.

The rules

• On time: the line's last delivery arrives on or before the promised date. A delivery the day before counts; early deliveries are not penalised here, though some customers do penalise them.
• In full: the total quantity delivered against the line equals the quantity ordered.
• OTIF: both. A line delivered in full but a day late fails OTIF; so does one delivered on time but one unit short.
• Lateness is measured in working days, excluding weekends and the August bank holiday, so a delivery due Friday that arrives Monday is one day late, not three.

Step 1 — One row per order line

Shipments are many-to-one with order lines, so aggregate them first: the total quantity delivered, the number of shipments, and the date of the last one. Then join to the order lines with a left join, so lines with no shipment at all stay in the report:

import numpy as np
import pandas as pd

orders = pd.read_excel("deliveries_q3.xlsx", sheet_name="Orders", parse_dates=["PromisedDate"])
shipments = pd.read_excel("deliveries_q3.xlsx", sheet_name="Shipments", parse_dates=["DeliveredDate"])

per_line = shipments.groupby(["OrderID", "Line"]).agg(
    QtyDelivered=("QtyDelivered", "sum"),
    Shipments=("DeliveredDate", "size"),
    LastDelivery=("DeliveredDate", "max"),
).reset_index()
lines = orders.merge(per_line, on=["OrderID", "Line"], how="left", validate="one_to_one")
lines["QtyDelivered"] = lines["QtyDelivered"].fillna(0).astype(int)
print(f"{len(lines)} order lines; {int((lines['Shipments'] > 1).sum())} split across shipments; "
      f"{int(lines['LastDelivery'].isna().sum())} with no delivery yet")

validate="one_to_one" makes pandas raise if the aggregation step was skipped or the keys are duplicated, rather than quietly multiplying order lines. Validate a pandas Merge to Avoid Duplicate Rows explains the options.

Step 2 — Working-day lateness

numpy.busday_count(a, b) counts working days from a up to but not including b, which is exactly "how many working days after the promise did it arrive". Pass the holidays that apply in the delivery country:

HOLIDAYS = np.array(["2026-08-31"], dtype="datetime64[D]")     # August bank holiday

delivered = lines["LastDelivery"].notna()
promised = lines.loc[delivered, "PromisedDate"].to_numpy().astype("datetime64[D]")
last = lines.loc[delivered, "LastDelivery"].to_numpy().astype("datetime64[D]")
late_by = np.where(last > promised, np.busday_count(promised, last, holidays=HOLIDAYS), 0)
lines.loc[delivered, "DaysLate"] = late_by
lines["DaysLate"] = lines["DaysLate"].astype("Int64")

lines["OnTime"] = delivered & (lines["LastDelivery"] <= lines["PromisedDate"])
lines["InFull"] = lines["QtyDelivered"] >= lines["QtyOrdered"]
lines["OTIF"] = lines["OnTime"] & lines["InFull"]
print(lines["DaysLate"].value_counts().sort_index().to_string())

Lines without any delivery are neither on time nor in full; if their promised date is still in the future they should be excluded instead, which Step 3 does.

Step 3 — The headline rates

Only score lines that were due in the period. A line promised for next week is not late yet:

PERIOD_END = pd.Timestamp("2026-09-30")
due = lines[lines["PromisedDate"] <= PERIOD_END]

headline = pd.Series({
    "Lines due": len(due),
    "On time %": due["OnTime"].mean() * 100,
    "In full %": due["InFull"].mean() * 100,
    "OTIF %": due["OTIF"].mean() * 100,
    "Avg days late (late lines)": due.loc[~due["OnTime"] & delivered, "DaysLate"].mean(),
})
print(headline.round(1).to_string())

Reporting on-time and in-full separately next to OTIF shows which problem to fix: if on-time is 97% and in-full 90%, the warehouse is short-picking; if the reverse, look at dispatch or the carriers.

Step 4 — By carrier, customer and week

The same three rates grouped different ways point at the cause. A helper keeps the definitions in one place:

def rates(df):
    return pd.Series({
        "Lines": len(df),
        "On time %": df["OnTime"].mean() * 100,
        "In full %": df["InFull"].mean() * 100,
        "OTIF %": df["OTIF"].mean() * 100,
    })

by_carrier = due.groupby("Carrier")[due.columns].apply(rates).sort_values("OTIF %")
by_customer = due.groupby("Customer")[due.columns].apply(rates).sort_values("OTIF %")
weekly = due.groupby(due["PromisedDate"].dt.to_period("W-SUN"))[due.columns].apply(rates)
weekly.index = weekly.index.start_time.strftime("w/c %d %b")
print(by_carrier.round(1).to_string())
print(by_customer.round(1).head(3).to_string())

Every carrier misses the 95% target, but not equally, and not for the same reason. RoadLink's on-time rate of 84.9% is the main problem: it is late on roughly one line in seven. ParcelOne is late less often but by more when it is: its late lines average about 2.5 working days against RoadLink's 1.9. FastFreight's few late lines average 3.2 days, mostly lines where a split second shipment arrived days after the first. In-full rates are similar across carriers, as expected, since short deliveries start in the warehouse rather than on the road. By customer, Northwind Cafe fares worst at 77.6% OTIF; nearly half of its lines go with RoadLink, which explains part of it, and the next question is whether its delivery windows are tighter than other customers', which the promised-date data can answer.

The weekly view is where a new problem shows first. Individual weeks swing because each has only a few dozen lines, so read the trend rather than any single point: here the last two weeks of September, at 76.9% and 77.8%, sit below every week since mid-July and are worth a look before the next quarter starts.

Step 5 — Write the report

The workbook needs the summary for the meeting and the list of failures for the people who follow up with carriers and customers. Rates are written as fractions with a percent format, and a conditional format marks every rate below the target:

TARGET = 0.95
failed = due[~due["OTIF"]].copy()
failed["Reason"] = np.select(
    [~failed["InFull"] & ~failed["OnTime"], ~failed["InFull"], ~failed["OnTime"]],
    ["Late and short", "Short", "Late"], default="")
failed = failed.sort_values(["Carrier", "PromisedDate"])

with pd.ExcelWriter("otif_q3_2026.xlsx", engine="xlsxwriter") as xw:
    book = xw.book
    pct = book.add_format({"num_format": "0.0%"})
    below = book.add_format({"bg_color": "#FDE2E2", "font_color": "#9B1C1C"})
    for name, table in [("By carrier", by_carrier), ("By customer", by_customer), ("By week", weekly)]:
        out = table.copy()
        for c in ["On time %", "In full %", "OTIF %"]:
            out[c] = out[c] / 100
        out.index = out.index.astype(str)
        out.to_excel(xw, sheet_name=name)
        ws = xw.sheets[name]
        ws.set_column(0, 0, 24)
        ws.set_column(1, 1, 8)
        ws.set_column(2, 4, 11, pct)
        ws.conditional_format(1, 4, len(out), 4, {"type": "cell", "criteria": "<", "value": TARGET, "format": below})
    failed[["OrderID", "Line", "Customer", "Carrier", "SKU", "PromisedDate", "LastDelivery", "DaysLate",
            "QtyOrdered", "QtyDelivered", "Shipments", "Reason"]].to_excel(xw, sheet_name="Failed lines", index=False)
    fl = xw.sheets["Failed lines"]
    fl.set_column(0, 4, 14)
    fl.set_column(5, 6, 12, book.add_format({"num_format": "yyyy-mm-dd"}))
    fl.autofilter(0, 0, len(failed), 11)
    fl.freeze_panes(1, 0)

    ws = xw.sheets["By week"]
    chart = book.add_chart({"type": "line"})
    n = len(weekly)
    chart.add_series({"name": "OTIF", "categories": ["By week", 1, 0, n, 0], "values": ["By week", 1, 4, n, 4],
                      "marker": {"type": "circle", "size": 4}})
    chart.add_series({"name": "Target", "categories": ["By week", 1, 0, n, 0],
                      "values": "={" + ",".join([str(TARGET)] * n) + "}", "line": {"dash_type": "dash"}})
    chart.set_y_axis({"num_format": "0%", "min": 0.7, "max": 1})
    chart.set_title({"name": "OTIF by week against target"})
    ws.insert_chart("G2", chart, {"x_scale": 1.5})
print(failed["Reason"].value_counts().to_string())

The target line uses an array constant instead of a column of 0.95s, which keeps the week table clean. The failed-lines sheet has an autofilter, so the carrier manager can filter to their own lines; Add an Autofilter to an Excel Sheet with openpyxl covers filters in more depth.

Common pitfalls

• Scoring shipments instead of order lines. A line delivered in two parts is one line; aggregate shipments first.
• Using the first delivery date. On time means the whole line arrived by the promise; use the last delivery.
• Calendar-day lateness. A Friday promise delivered Monday is one working day late; use busday_count with holidays.
• Scoring lines not yet due. Filter to lines promised on or before the period end.
• Changed promise dates. If the system overwrites the promised date when a delivery is rescheduled, every line looks on time. Report against the original promise, stored separately.
• Over-deliveries. Decide whether delivering more than ordered is in full; >= treats it as in full.

Conclusion

Aggregate shipments to one row per order line — total delivered, number of shipments, last delivery date — and left-join to the order lines. Score on time against the last delivery, in full against total quantity, and OTIF as both, with lateness in working days from busday_count. Report only lines that were due, break the rates down by carrier, customer and week against the target, and list the failed lines with a reason for follow-up.

Frequently asked questions

What is OTIF? On time, in full: the share of order lines whose full quantity arrived by the promised date. It combines two measures that are worth reporting separately as well.

How do I count late days excluding weekends in pandas? Use numpy.busday_count(promised, delivered, holidays=...) on datetime64D arrays. It counts working days from the promised date up to the delivery date.

How should partial deliveries be counted? Sum the delivered quantity across all shipments for the line and use the last delivery date. The line is in full only if the total covers the order, and on time only if the last part arrived by the promise.

Should OTIF be measured per order or per line? Per line is more common and more precise; per order is stricter, since one late line fails the whole order. Choose one, state it in the report, and keep it the same over time.

Related

• Up one level: Building Finance and Operations Reports — the rest of the finance and operations reports.
• Build an Inventory Reorder Report — stock levels behind the in-full rate.
• Calculate Working Hours Between Two Timestamps — business time at a finer grain.
• Build a Weekly Sales Report — another weekly operations report.
• Add an Autofilter to an Excel Sheet with openpyxl — filtering the failed lines.