Statistical
Kanban Bin / Card Count & On-Time In-Full (OTIF) supply chain formulas
Calculate a simplified replenishment-container estimate and measure the share of five complete shipment records that are both on time and in full.
Size shop-floor Kanban loops—and avoid the "On-Time * In-Full" OTIF fallacy!
This simplified sizing example divides average lead-time demand plus a percentage buffer by container capacity, then rounds up to whole containers. Daily demand and lead time must use compatible units; the buffer is a fraction such as 0.20. This covers the calculated quantity, but does not guarantee against shortages when demand or replenishment varies. Real systems may add lot-size, trigger and other policy constraints.
OTIF here is an order-level measure: both Yes flags must occur on the same row. The example requires exactly five complete order records with Yes/No flags. Missing or invalid fields return a data-quality message rather than silently improving the rate. Define the reporting window, due-date rule and treatment of partial shipments before applying this measure to operational data.
| Kanban / OTIF Metric | Inputs / Log | Formula | Exact Output |
|---|---|---|---|
| Daily Demand & Lead Time | B2 = 400/day, B3 = 2.5 days | Pipeline = 1,000 units | Base loop volume |
| Safety Factor & Bin Size | B4 = 20%, B5 = 150/bin | ROUNDUP(1200 / 150, 0) | 8 Kanban bins |
| On-Time (80%) & In-Full (80%) | 5 shipments (E2:F6) | 4/5 each individually | Naive 0.8*0.8 = 64% (Wrong!) |
| True Joint OTIF Rate | Both "Yes" on same row | COUNTIFS / COUNTA | 3 / 5 = 0.60 (60.0%)! |
Simplified container count
Round up after dividing. A value of 8.4 requires 9 containers to cover this modeled quantity; this does not imply that 8 would necessarily cause a real-world stockout. Capacity must be positive; demand, lead time and safety fraction nonnegative.
=IF(OR(COUNT(B2:B5)<>4,B2<0,B3<0,B4<0,B5<=0),"Invalid inputs",ROUNDUP((B2 * B3 * (1 + B4)) / B5, 0))Joint On-Time In-Full (OTIF) percentage formula
COUNTIFS requires both E2:E6 = "Yes" and F2:F6 = "Yes" on the exact same order row.
=IF(OR(COUNTBLANK(D2:D6)>0,COUNTIF(E2:E6,"Yes")+COUNTIF(E2:E6,"No")<>5,COUNTIF(F2:F6,"Yes")+COUNTIF(F2:F6,"No")<>5),"Incomplete shipment log",ROUND(COUNTIFS(E2:E6, "Yes", F2:F6, "Yes") / COUNTA(D2:D6), 4))Try the example
Use the original values or change them to test your understanding.