Challenge Scenario
In strategic procurement and vendor management, tracking supplier delivery punctuality ensures production lines avoid costly downtime. Vendors that deliver on or before the promised delivery date are classified as ON TIME, while later deliveries are flagged as DELAYED.
As a Procurement Operations Manager, you are auditing five supplier purchase orders:
- Variance in Days (Column D): Subtract promised date from actual delivery date:
=C2-B2 (negative/zero = on time or early, positive = late).
- Delivery Status (Column E): If variance is less than or equal to 0 (
D2 <= 0), assign "ON TIME"; otherwise assign "DELAYED".
- Total On-Time Deliveries (Cell D8): Count shipments marked
"ON TIME" using COUNTIF(E2:E6, "ON TIME").
- Total Delayed Deliveries (Cell E8): Count shipments marked
"DELAYED" using COUNTIF(E2:E6, "DELAYED").
Your Sheet Layout
Understanding the supplier delivery audit schedule:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C6 |
PO & Vendor, Promised, Actual Date |
PO Schedule |
Supplier order codes and arrival timestamps |
Read-only delivery log. |
D2:D6 |
Variance (Days) |
Schedule Variance |
=C2-B2 |
Days variance (negative = early, positive = late). |
E2:E6 |
Delivery Status |
Vendor Rating |
=IF(D2<=0, "ON TIME", "DELAYED") |
Flags ON TIME vs DELAYED vendor status. |
D8 |
On-Time Shipments |
Summary |
=COUNTIF(E2:E6, "ON TIME") |
Total punctual PO deliveries (3). |
E8 |
Delayed Shipments |
Summary |
=COUNTIF(E2:E6, "DELAYED") |
Total late vendor deliveries (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells D2:D6, calculate delivery variance days (= Actual Delivery - Promised Date).
2
In cells E2:E6, assign status ("ON TIME" if variance <= 0, otherwise "DELAYED").
3
In cell D8, count total purchase orders delivered "ON TIME".
4
In cell E8, count total purchase orders flagged as "DELAYED".
Solution & Formula Breakdown
Let's review the vendor punctuality calculations:
1. Date Schedule Variance (D2:D6)
=C2-B2
- PO-101 (FastSteel): 2026-09-04 minus 2026-09-05 = -1 day (arrived 1 day early).
- PO-102 (Apex): 2026-09-09 minus 2026-09-05 = +4 days (late).
- PO-103 (MicroChip): 2026-09-10 minus 2026-09-10 = 0 days (exact day).
- PO-104 (Prime Rubber): 2026-09-18 minus 2026-09-12 = +6 days (late).
- PO-105 (Delta Glass): 2026-09-14 minus 2026-09-15 = -1 day (early).
2. Delivery Classification with IF (E2:E6)
=IF(D2<=0, "ON TIME", "DELAYED")
- PO-101 (-1 day): <= 0 → ON TIME.
- PO-102 (+4 days): > 0 → DELAYED.
- PO-103 (0 days): <= 0 → ON TIME.
- PO-104 (+6 days): > 0 → DELAYED.
- PO-105 (-1 day): <= 0 → ON TIME.
3. Summary Vendor Metrics (D8 & E8)
- Cell D8 (On-Time Count):
=COUNTIF(E2:E6, "ON TIME") → 3 POs.
- Cell E8 (Delayed Count):
=COUNTIF(E2:E6, "DELAYED") → 2 POs.
To calculate the vendor's On-Time Delivery Rate (OTIF %), you can divide on-time shipments by total shipments: =COUNTIF(E2:E6, "ON TIME") / COUNTA(E2:E6) → 60.0%!