Sign in

Blog · How-to guides

How to calculate OTIF in Excel: on time, in full, and both, with a stated tolerance

A step-by-step guide to computing on-time-in-full in Excel from an order and delivery export: the on-time test with early and late tolerances, the in-full test with a fill threshold, OTIF at line level and at order level, the rate per customer with COUNTIFS, the four-state identity of OTIF, late only, short only and both, and the customer's rule beside your own. Includes the exact formulas and the point at which the spreadsheet stops being enough.

By CoviragePublished 17 September 20263 min read
The short answerTo calculate OTIF in Excel you need one row per order line with requested date, delivered date, quantity ordered and quantity delivered. Put the tolerances in cells. On time is =AND(delivered<>"",delivered>=requested-early,delivered<=requested+late). In full is =delivered_qty/ordered_qty>=threshold. OTIF for the line is =AND(on_time,in_full), and the rate is =COUNTIF(otif,TRUE)/COUNTA(otif). For order-level OTIF, an order passes only if none of its lines fail: =COUNTIFS(order,this_order,otif,FALSE)=0. Per customer, use COUNTIFS with the customer column. Every line should fall into exactly one of four states, and those four must sum to the line count.

OTIF is the share of orders, or lines, delivered on time and in full. In Excel it is three test columns and a COUNTIFS. This guide gives the formulas, both levels, and the identity.

The data you need

One sheet, Lines, one row per order line:

Column Content
A Order number
B Customer
C Requested date
D Delivered date, blank if not delivered
E Quantity ordered
F Quantity delivered

On Calc: early tolerance in days in B1, late tolerance in B2, fill threshold, such as 0.98, in B3, as-of date in B4.

Step 1: which lines are due

G2:

=C2<=Calc!$B$4

Only due lines are measured.

Step 2: on time

H2:

=AND(D2<>"",D2>=C2-Calc!$B$1,D2<=C2+Calc!$B$2)

Early beyond tolerance is a miss. Set the early tolerance high if your customers accept early deliveries.

Step 3: in full

I2:

=IF(E2>0,F2/E2>=Calc!$B$3,FALSE)

Step 4: OTIF per line, and the state

J2:

=AND(H2,I2)

State, K2:

=IF(NOT(G2),"Not due",IF(J2,"OTIF",IF(AND(NOT(H2),NOT(I2)),"Both",IF(NOT(H2),"Late only","Short only"))))

Step 5: the rate, line level

=COUNTIF(Lines!K:K,"OTIF")/(COUNTA(Lines!K2:K50000)-COUNTIF(Lines!K:K,"Not due"))

Step 6: order level

An order passes if it is due and none of its lines fail. L2:

=AND(G2,COUNTIFS(A:A,A2,G:G,TRUE,J:J,FALSE)=0)

To count each order once, flag the first line of each order, M2:

=COUNTIF(A$2:A2,A2)=1

Order-level rate:

=COUNTIFS(Lines!M:M,TRUE,Lines!L:L,TRUE)/COUNTIFS(Lines!M:M,TRUE,Lines!G:G,TRUE)

Expect it to be several points below the line-level rate.

Step 7: per customer

Customer list in Calc!A10 down. Line-level OTIF per customer:

=COUNTIFS(Lines!B:B,A10,Lines!K:K,"OTIF")/(COUNTIFS(Lines!B:B,A10)-COUNTIFS(Lines!B:B,A10,Lines!K:K,"Not due"))

Add the line count beside it. A customer at 70 percent on ten lines is not the same finding as one at 88 percent on two thousand.

The identity

=COUNTIF(Lines!K:K,"OTIF")+COUNTIF(Lines!K:K,"Late only")+COUNTIF(Lines!K:K,"Short only")+COUNTIF(Lines!K:K,"Both")+COUNTIF(Lines!K:K,"Not due")-COUNTA(Lines!A2:A50000)

Zero. The split of misses across late only, short only and both is the first step of the cause analysis: late only points at transport and scheduling, short only at stock and picking.

The customer's rule beside your own

Copy the Calc tolerances into a second set of cells with the customer's terms, typically zero days early, zero or one day late, order level. Duplicate columns H to L referencing the second set. Two rates per customer: yours and theirs. The one they charge against is theirs.

Where it goes wrong in a spreadsheet

Undelivered lines left blank. They drop out of the denominator and the rate rises.

Promised date edited after the fact. If the export carries the latest promised date rather than the original, every line is on time. Ask for the original.

Split deliveries. One line delivered in two drops appears as two rows; total delivered quantity per order line first, and take the last delivery date.

Units mismatch. Ordered in cases, delivered in eaches; every line short or every line over.

Where the spreadsheet stops being enough

A monthly OTIF figure for one site is a fine spreadsheet. Weekly, per customer under each customer's own rule, with misses joined to warehouse and carrier reason codes and to the chargebacks they caused, is where it strains; see the four signs a spreadsheet is no longer enough. For what the rate should be, see what is a good OTIF rate; for the arithmetic by hand, OTIF on ten purchase order lines. Covirage computes the same figures from the same exports every week, with both rules and the identity built in.

Questions people ask

Requested date or promised date?

Compute both. The promised date measures whether you did what you said; the requested date measures whether the customer got what they wanted, and it is the one most retailers score you on. The gap between the two rates is how much of your OTIF is negotiated at order entry.

Line level or order level?

Line level is kinder: nine good lines and one short line is 90 percent. Order level counts that order as a miss. Retail scorecards are usually order level or case-fill based. State which you use, and compute the customer's version for any customer that charges against it.

What about lines not yet delivered?

Exclude lines whose requested date is still in the future. Lines past their requested date with no delivery are late, not blank; the formula's first test handles it. Leaving them out is the commonest way an OTIF figure flatters.

Related reading

How-to guides

Answer-first writing for commercial measures: the template behind every page on this site

The seven-part template every measure page on this site follows, and why: the answer in one paragraph, the formula in a line, the rows needed with identifiers only, one worked table, the roll-up and its identity, the four mistakes, and the three questions answered in full. What each part is for, the order and why it is fixed, the length each part gets, and how a company can use the same template for its own definitions document.

16 Sept 20264 min readHow-to guides

Excel or an analytics tool: when a spreadsheet is enough, and the four signs it is not

A fair account of what a spreadsheet does well for sales analysis, the point at which it stops, and the four signs a team has passed it: the same measure computed two ways by two people, a monthly refresh that takes days, a roll-up that does not reconcile and nobody knows why, and a number in a deck that nobody can trace. What changes when the measures are computed from stated definitions on every upload, and what does not need to change at all.

16 Sept 20263 min readHow-to guides · Finance and FP&A teams

Five checks before you trust a spreadsheet total

The five checks a commercial analyst runs on a workbook before its totals go into a deck: the SUM range covers every row, hidden and filtered rows are counted or excluded on purpose, no number is typed over a formula, the pivot matches the sheet, and the total matches the source system. Each with the symptom it catches and the one-minute test.

16 Sept 20263 min readHow-to guides

GDPR and sales analytics: what an analytics tool should never need from you

A plain guide for a sales leader and a data protection officer evaluating an analytics tool under GDPR and UK GDPR: which fields coverage and share-of-wallet analysis actually needs, why customer and contact names are not among them, what pseudonymisation at export means in practice, the lawful basis question, the data that stays in the building, and the six questions a DPO should ask before an export leaves.

16 Sept 20263 min read