Purchase order tracking: the template, the formulas, and where the spreadsheet breaks
A complete PO tracker you can build this afternoon, the variance formulas that make it predictive instead of decorative, and the honest arithmetic on where a spreadsheet stops being the right tool. Build the sheet if 30 POs is your problem. Skip to the arithmetic if 200 is.
Building the tracker?
Start with the 34 columns and the formulas.
Already past the spreadsheet?
Skip to where it breaks, what the options do, and the arithmetic on 200 open POs.
PO Number | PO Line | Supplier | Supplier Contact | Item/SKU | Description | Destination | Qty Ordered | UOM | Unit Price | Line Value | Currency | Incoterm | PO Date | Requested Date | Confirmed Date | Current Promise | Date Change Count | Transit Days | Ship Mode | Ship Date | ASN Ref | Carrier | Container/AWB/PRO | ETA | Receipt Date | Qty Received | Qty Open | Days Open | Confirmation Lag | Promise Slip | Receipt Variance | Line OTIF | Risk Flag
Paste into A1, then Ctrl+T. Thirty-four columns once the calculated fields are in.
What purchase order tracking actually is
Purchase order tracking is the practice of following every PO line from the moment it is issued to the moment it is received and closed, capturing the supplier's confirmed date, the ship date, the carrier reference, the live ETA, and the actual goods receipt. Done properly, it tells you which open lines will miss while there is still time to act.
That last clause is the whole job. Most PO trackers, spreadsheet or software, are historical records. They tell you a PO was late after it was late. A tracker that earns its keep tells you on day 12 of a 45-day lead time that a line cannot possibly make its date, because the goods have not shipped and there are fewer calendar days remaining than the transit takes.
The reason most trackers cannot do this is structural: they carry three states. Open, Received, Closed. A PO in the real world passes through eight, and six of them happen before anything arrives at your dock.
Issued
You sent it. Nothing agreed yet.
Acknowledged
Supplier confirms receipt. Silence past 48h is your earliest signal.
Confirmed
Supplier commits to a date. Often not the one you asked for.
In production
Goods being made or allocated.
Shipped
ASN or booking exists. Quantity is now fixed.
In transit
Container, AWB or trailer reference with a moving ETA.
Received
Goods receipt posted against the line, with an actual qty.
Closed
Invoice matched, line settled.
States 2–6 = "Open" — the one state most trackers carry
If your tracker collapses steps 2 through 6 into "Open", you have no early warning system. You have a list.
And the cost of that shows up somewhere specific: in how your team spends its mornings. Ask a buyer what they did before ten o'clock and the answer is rarely "resolved a problem." It is "worked out what the problem was" — the supplier portal, the forwarder's email, the ERP, the sheet, then a call. The assembly is the job. The resolution is what is left over when the assembly finishes, and on a bad week there is nothing left over. Every column and formula below is aimed at shortening the assembly step, and the honest limit of a spreadsheet is that it can shorten it but never remove it, because one of the columns will always be a fact you have to go and fetch.
Not fixing anything. Finding out. Three minutes a line, about a hundred and seventy lines a week, to rebuild a picture that is a day old by the time the meeting starts. The arithmetic is further down this page.
The Excel purchase order tracking template: the columns that matter
Thirty-four columns in five blocks. Each one has to justify itself, so here is the reason for every single one.
Build it as a proper Excel Table (select the range, Ctrl+T, name it Tracker) so structured references work and formulas fill down automatically. Do not merge cells. Do not use a separate tab per supplier.
The single most important design decision: one row per PO line, not one row per PO. A PO with six lines where two are late is not "late" and is not "on time". It is four lines fine and two lines short, and if your sheet cannot represent that, every downstream number you calculate is wrong. This is the mistake in roughly every free template you will download.
Group the columns into five blocks and freeze panes after the identity block so the dates stay visible while you scroll. Columns 1 to 7 are identity. Columns 8 to 13 are commercial. Columns 14 to 19 are the date spine, and this is the part free templates get wrong: you need three separate dates that are never overwritten. Requested date is what you asked for. Confirmed date is what the supplier committed to. Current promise is where it stands today after any changes. Overwrite the confirmed date with each new promise and you permanently destroy your ability to measure supplier reliability, because a supplier who moves the date four times and then hits the fourth one looks identical in your data to a supplier who hit the first.
Columns 20 to 25 are execution: how it is actually moving. Columns 26 to 34 are the receipt and the calculated fields, and those are covered in the next section.
| # | Column | Type | Why it earns its place |
|---|---|---|---|
| Identity — cols 1–7 · who and what | |||
| 1 | PO Number | Text | Join key back to the ERP |
| 2 | PO Line | Number | The row grain. Never omit this |
| 3 | Supplier | List | Data validation off a supplier master tab |
| 4 | Supplier Contact | Text | Who you chase. Not a shared inbox |
| 5 | Item / SKU | Text | Join key to inventory and demand |
| 6 | Description | Text | For the humans reading the sheet |
| 7 | Destination / Plant | List | Multi-site shippers cannot filter without it |
| Commercial — cols 8–13 · quantity and value | |||
| 8 | Qty Ordered | Number | Denominator for fill rate |
| 9 | UOM | List | Cases vs eaches errors are the classic silent failure |
| 10 | Unit Price | Currency | Needed for value-weighted risk |
| 11 | Line Value | Calc | Qty Ordered x Unit Price |
| 12 | Currency | List | Do not mix currencies in one value column |
| 13 | Incoterm | List | Determines who owns the freight and the risk |
| Dates — cols 14–19 · never overwritten | |||
| 14 | PO Date | Date | Clock start for the acknowledgement SLA |
| 15 | Requested Date | Date | What you asked for |
| 16 | Confirmed Date | Date | First supplier commitment. Freeze it forever |
| 17 | Current Promise | Date | Today's live date after any changes |
| 18 | Date Change Count | Number | Increment on every promise move. Best supplier metric you have |
| 19 | Transit Days | Number | Planned transit. Powers the will-miss formula. Non-negotiable |
| Execution — cols 20–25 · how it is actually moving | |||
| 20 | Ship Mode | List | Ocean, air, road, parcel |
| 21 | Ship Date | Date | Blank past confirmed date minus transit means it cannot arrive |
| 22 | ASN Ref | Text | Proof the supplier actually shipped |
| 23 | Carrier / Forwarder | List | Who to call |
| 24 | Container / AWB / PRO | Text | The tracking handle |
| 25 | ETA | Date | Manually typed here. This is the column that rots |
| Receipt + calculated — cols 26–34 · the sheet does these | |||
| 26 | Receipt Date | Date | Goods receipt from the ERP, not from an email |
| 27 | Qty Received | Number | Actual, cumulative across partials |
| 28 | Qty Open | Calc | Qty Ordered minus Qty Received. Catches the 60%-and-closed line |
| 29 | Days Open | Calc | Aging. Drives the escalation list |
| 30 | Confirmation Lag | Calc | Days from PO date to first commitment. Feeds the scorecard |
| 31 | Promise Slip | Calc | How far the supplier pushed you off the date you asked for |
| 32 | Receipt Variance | Calc | Days late or early against Confirmed Date |
| 33 | Line OTIF | Calc | 1 or 0, at line level. Rolls up to a supplier scorecard |
| 34 | Risk Flag | Calc | The only column anyone should look at daily |
One row per PO line, not per PO. A six-line PO with two late lines is neither "late" nor "on time", and a tracker that cannot say which is which produces wrong numbers everywhere downstream.
Why you never overwrite a date
Here is the same purchase order twice. Nothing about the goods changed between the two rows. The only difference is how many dates the tracker kept.
What a one-date-column tracker records
Mar 24
Received
ON TIME ✓
What actually happened
Mar 3
Requested
Mar 10
Confirmed
Mar 13
WILL MISS was already knowable — no ship date, 11 days to promise, 14-day transit
Mar 17
Promise #2
Mar 24
Promise #3
Mar 24
Received
+21 days of slip · 3 promise moves
Same PO. Same receipt date. The top row is what a tracker with one date column can tell you: on time. The bottom row is the supplier you actually have — and the red line is the morning you could have known.
Four fields — requested, confirmed, current promise, and a change counter — are the entire cost of never being lied to by your own data.
Everything left of the receipt column is somebody's claim
The confirmed date is the supplier's claim. The ETA is the carrier's claim. Transit Days is your own planner's claim, made once, probably two years ago and never revisited. None of them were observed. The goods receipt is the only entry in the whole row that somebody physically saw, counted and posted.
That is the reason the date spine is worth the four columns it costs. When the receipt finally lands, a row that kept the claims apart can score which of them was true — and which supplier, which carrier, and which planning assumption has been quietly wrong all year.
The formulas that make the template predictive
A column of typed dates is a filing cabinet. These formulas turn it into a warning system. Two ground rules before you paste anything. First, the table must be named Tracker (select the range, Ctrl+T, then Table Design > Table Name). Second, structured references match your header text exactly, character for character — so use the short header names below, not the annotated ones. Rename columns 21 and 26 to Ship Date and Receipt Date, and add two calculated columns the formulas depend on: Confirmation Lag and Promise Slip. That makes 34 columns, not 32. If a formula returns #NAME?, a header does not match; that is the only thing it ever means.
Days open (aging)
Counts to today while the line is open, then freezes on receipt.
=IF([@[Receipt Date]]="", TODAY()-[@[PO Date]], [@[Receipt Date]]-[@[PO Date]])
Confirmation lag
How long the supplier took to commit. Anything over 3 days is a supplier conversation.
=IF([@[Confirmed Date]]="", "", [@[Confirmed Date]]-[@[PO Date]])
Promise slip
How far the supplier pushed you off your requested date at the point of confirmation.
=IF([@[Confirmed Date]]="", "", [@[Confirmed Date]]-[@[Requested Date]])
Receipt variance
Positive is late, negative is early, measured against the original commitment.
=IF([@[Receipt Date]]="", "", [@[Receipt Date]]-[@[Confirmed Date]])
Qty open
The quiet one. A supplier who ships 60% and closes the line looks fine on every date column and is still short.
=[@[Qty Ordered]]-[@[Qty Received]]
Line-level OTIF
Both conditions, one flag, per line. Rolls up to the supplier scorecard.
=IF(OR([@[Receipt Date]]="",[@[Confirmed Date]]=""),"", IF(AND([@[Receipt Date]]<=[@[Confirmed Date]],[@[Qty Received]]>=[@[Qty Ordered]]),1,0))
The risk flag — the one that pays for the sheet
Order matters, because IFS returns the first condition that is true.
=IFS( [@[Receipt Date]]<>"", "Received", AND([@[Confirmed Date]]="", TODAY()-[@[PO Date]]>2), "UNACKNOWLEDGED", [@[Current Promise]]="", "Awaiting confirmation", TODAY()>[@[Current Promise]], "LATE", AND([@[Ship Date]]="", [@[Current Promise]]-TODAY()<=[@[Transit Days]]), "WILL MISS", [@[Current Promise]]-TODAY()<=7, "Due this week", TRUE, "On track" )
IFS needs Excel 2019 or Microsoft 365, and works in Google Sheets. On Excel 2016 or older, nest plain IF statements in the same order — first true condition wins either way.
The WILL MISS branch is the whole point. If nothing has shipped and the calendar days remaining are fewer than the planned transit, the line is arithmetically incapable of arriving on time. You know that today, not on the due date. On a 32-day ocean transit out of Ningbo, that is roughly four weeks of warning, which is the difference between re-planning production and paying for air freight.
If Ship Date is blank and (Current Promise minus TODAY) is less than or equal to Transit Days, the line cannot arrive on time. On a 32-day ocean leg that gives you about four weeks of warning.
Supplier scorecard, on a second tab with one row per supplier:
=COUNTIFS(Tracker[Supplier],[@Supplier],Tracker[Line OTIF],1) /COUNTIFS(Tracker[Supplier],[@Supplier],Tracker[Line OTIF],"<>") =AVERAGEIFS(Tracker[Confirmation Lag],Tracker[Supplier],[@Supplier], Tracker[Confirmation Lag],">=0")
Finally, apply conditional formatting to the Risk Flag column with the rule =$AF2="WILL MISS" for red and =$AF2="UNACKNOWLEDGED" for amber — adjust AF to wherever Risk Flag actually lands in your sheet, then sort by that column every morning.
Sort by risk flag every morning and work the top of the list. That five-minute routine, done every day without exception, will beat any tool nobody opens. Take it and use it.
Worked example: what 200 open POs cost you to track by hand
Two hundred is not a threshold, it is just the size we are doing the arithmetic at. Volume is the weakest of the three break triggers; the other two arrive earlier.
200
Open POs
40
Suppliers
840
Open PO lines
~1,300
POs a year
Assumptions stated up front so you can swap your own numbers in. A mid-size shipper with 200 open POs across 40 suppliers, averaging 4.2 lines each, so 840 open lines. On a mixed domestic-and-import book, a PO stays open around eight weeks from issue to receipt, which puts annual volume at roughly 1,300 POs, or 5,500 PO lines. One buyer-planner keeps the tracker current. Assume 8% of lines miss their confirmed date — realistic for an unmanaged inbound book, where disciplined programs run 3% to 5% — so 440 late lines, clustered into roughly 150 affected POs. Most are absorbed by float; assume 5% are urgent enough to force an expedite.
$30k–$60k
390 hrs
Two different currencies. Don't add them. The freight number has to clear the licence cost on its own; the hours are capacity, and capacity only counts if you can name what your buyers would do with it instead.
Nobody chases all 200 POs weekly, so don't model that. What a buyer actually works each week is everything unconfirmed, everything already flagged, and everything due inside the next fourteen days — call it 20% of the open book, about 170 lines at 3 minutes a chase. One caveat, because your CFO will raise it: recovered hours are rarely a headcount saving. They are capacity your buyers get back for supplier development and cost work they currently do not get to.
On the freight side, be honest with yourself about the recovery rate. Knowing four weeks out that a line will miss does not make the supplier produce faster — it buys you the option to re-sequence production, pull safety stock, or re-promise the customer instead of flying it. Convert a quarter of those 22 events and you avoid about $30,000. Convert half and it is $60,000. Pick your own number; the arithmetic is yours, not ours.
So on these volumes: $30,000 to $60,000 of avoidable air freight, plus about 390 buyer-hours currently spent retyping dates. Two different currencies — keep them apart. The freight number is cash your CFO recognizes. The hours are capacity, and capacity only counts if you can name what your buyers would do with it instead. Run this with your own volumes before you talk to any vendor. If the freight half comes out at $6,000, the spreadsheet in the sections above is the correct answer and you should stop reading.
Count the workarounds, not the purchase orders
Nobody decides to outgrow a spreadsheet. What happens is that workarounds accumulate around it: a second tab only one person understands, a Friday afternoon merge of four files, a Teams message that repeats a flag the sheet already raised because nobody trusts the sheet was read.
Each one is small and each one is rational, which is why they never get counted. Together they are the actual measurement. When keeping the tracker honest costs more than the tracker saves, the tool has already been outgrown — usually a good year before anyone says so out loud. The five failure points below are where that year is spent.
Where the spreadsheet actually breaks
Not vaguely. Specifically. Five failure points, in roughly the order they arrive as you scale.
1. The ETA column is a number someone typed.
2. There is no single tracker — there are four.
IFS, conditional formatting and everyone's private filters degrades fast — filters clobber each other, formulas get pasted over as values, and within a quarter you have PO_Tracker_v4_FINAL_maria.xlsx in three inboxes.The consequence is not inconvenience, it is that nobody can answer "what is at risk across all inbound this week" without a human merging four workbooks by hand on a Friday afternoon. Ask for that list on a Tuesday and you will get last Friday's.
2b. And it lives on one laptop.
The tracker's logic — which supplier always confirms late, why that one line is flagged manually, what the fourth tab actually does — is in one buyer's head. When they take two weeks off, or resign, your inbound visibility leaves with them. This is the failure mode nobody puts in a business case and everybody has survived at least once.
3. The sheet never tells you anything.
4. Reconciliation drift against the ERP — and against your own change orders.
Note that half of this churn is not the supplier's fault, it is yours: engineering changes, cancelled lines, quantity reduces, re-sourced parts. Your team does not call that reconciliation drift, they call it "the PO changed again", and it is at least as common as supplier-side slippage. Over a year a manually reconciled tracker typically drifts far enough that people stop trusting the aggregate numbers, which is worse than not having them at all.
5. No audit trail on the promise.
None of these are Excel's fault. Excel is doing exactly what it was designed to do: hold a static model of a situation. The problem is that an open purchase order is not static, and it is not yours alone.
The column the template cannot have: what this PO was for
Here is what separates a buyer's tracker from a supply chain leader's view. The template above can tell you a line will miss. It cannot tell you whether that matters.
Two lines both flag WILL MISS on the same morning. Same flag, same colour, three orders of magnitude apart in consequence.
Note it. Move on.
Chargeback conversation. Possible line-down claim.
Same flag. Same colour. Three orders of magnitude apart in consequence — and nothing in Excel closes that gap, because the answer lives in a different system.
The answer lives in the demand, the build plan, the customer order, the install date. Joining an inbound PO line to the downstream commitment it feeds is the difference between an exception list and a priority list, and it is why teams that get very good at inbound tracking still report that the escalations feel random. They are triaging on lateness instead of on consequence.
Add one manual column — Criticality, values of 1, 2 or 3, set by the planner when the PO is raised, based on cover and single-source status — and sort by Risk Flag then Criticality. It is a judgement call rather than a calculation, it goes stale, and it is still the highest-value column in the sheet. If a late line breaks a customer promise, the downstream half of this is an OTIF problem and the arithmetic on what that costs sits in our guide to reducing supply chain costs.
Purchase order tracking: what the options actually do
Every category below is genuinely good at something. The failure mode in most stacks is not picking the wrong one, it is assuming the thing you already own covers the window between PO issued and goods received. The ERP owns the commercial truth. The procurement suite owns everything before the PO is issued. The carrier tool owns the box. The supplier portal owns whichever suppliers bothered to onboard. Nobody's default stack owns the join between them, which is exactly the window where POs go wrong.
| Option | Genuinely good at | Cannot do | Breaks at |
|---|---|---|---|
| Excel / Sheets tracker | Free, fully custom, live in an afternoon, models your exact process | Live carrier data, alerting, single source of truth, audit trail | The third simultaneous editor, or the day the freight goes international — volume is the last of the three to bite |
| ERP purchase order module | System of record, three-way match, receipts, financial truth | Anything outside your four walls: no carrier ETA, no forwarder milestone, thin exception workflow | The moment the answer depends on where the freight physically is |
| Supplier portal / EDI 855 + 856 | Structured acknowledgement and ASN from suppliers who use it | Coverage. Long-tail suppliers will not onboard, and there is still no carrier telemetry | Supplier adoption below about 70% of spend |
| Carrier or freight visibility tool | Live container, AWB and trailer milestones, predictive ETA | Knows the box, not what is in it. Cannot say which PO line or SKU is at risk | When you need PO-level answers, not container-level ones |
| Procurement / source-to-pay suite | Sourcing, requisition, approval, contracts, spend analytics | Post-issue inbound execution. Not built to chase a container across three carriers | Exactly the window between PO issued and goods received |
| Orchestration layer over your stackthat's us | Joins PO lines to shipments to receipts, live ETA on the line, exceptions with an owner | It is not a system of record. Does not create POs, approve requisitions, or pay invoices | Depends entirely on your ERP data being reliable in the first place |
If the carrier-visibility row is the one you are weighing, the evaluation criteria and the plateau that follows most deployments are covered in our guide to supply chain visibility platforms.
Nine PO tracking best practices that survive contact with reality
1Track at line level, always.
Order-level tracking hides partial shipments, which is where most of your real problems live. A supplier who ships 60% of the line and closes it is the single most common way a PO looks fine and is not.
2Never overwrite a date.
Requested, confirmed, current promise, and a change counter. Four fields. Overwriting is how you lose supplier accountability permanently, and you cannot reconstruct it later.
3Make acknowledgement an SLA.
An unacknowledged PO at 48 hours is the cheapest early warning in the entire process, and it costs nothing to measure. Most teams have no idea what their acknowledgement rate is. Find out, then hold suppliers to 24 or 48 hours in writing.
4Alert on lead time, not on due date — and log what the alert cost you.
By the time a PO is past due, every option left is expensive. Alert when remaining calendar days drop below planned transit, which is weeks earlier and free. Then add one column your finance team will thank you for: for every line that ends up expedited, record who authorized it and the air-versus-plan delta. Most teams cannot say what expedited freight cost them last quarter or who approved it, which is why the number never gets managed. It is also the number that funds any fix you eventually propose.
5Reconcile to the goods receipt, not to an email.
The ERP posting is the only receipt evidence that survives an audit or a dispute. “Supplier said it arrived” is not a receipt.
6Give every exception a named human.
Not a distribution list, not a shared inbox. A name and a date. Exceptions that belong to everybody belong to nobody, and the fix dies in a thread.
7Score the supplier, not just the PO.
Confirmation lag, date-change count, and line-level OTIF per supplier, reviewed quarterly. Three numbers. They will change which suppliers get your growth volume more than any negotiation will.
8Separate the two questions.
“Is this PO late?” is a data question. “What are we doing about it?” is a workflow question. Teams that solve only the first one end up with excellent dashboards and unchanged delivery performance.
9Bring the scorecard to the negotiation, not just to the review.
Confirmation lag, date-change count and line-level OTIF per supplier are worth having quarterly. They are worth ten times more in the annual pricing conversation and the QBR, where the supplier's account manager will quote you their own on-time number and you currently have nothing to put beside it. Twelve months of your own line-level data changes that conversation permanently, and it costs nothing but not overwriting the confirmed date.
Where Orkestra fits, and where it does not
Everything above works without us. If you run 30 open POs across 8 domestic suppliers, build the spreadsheet, use the WILL MISS formula, and get on with your day. It is the right tool and it costs nothing.
The argument changes at around 200 open POs across 40 suppliers on international freight, because at that point the two columns your spreadsheet cannot fill are the two that decide everything: the live ETA, and whether anyone was told.
Orkestra is an orchestration and visibility layer that sits over the systems you already own. It reads the PO out of your ERP, joins each line to the shipment actually carrying it across 200+ pre-built carrier and system connectors, puts a live ETA on the line instead of a date somebody typed on Tuesday, and fires when the promise moves. The order management module holds the line-level record from PO issued to goods receipt posted — acknowledgement, confirmed date, every promise change, the shipment carrying it, the actual received quantity. Not a status field. The same eight states the template above tries to fake with one column.
Workflow automation handles what happens next: the Exception Monitoring Agent watches for emerging risk continuously so nobody has to remember to open the file, AI Exception Summary states the issue with context and a recommended next step, and the Workflow Trigger Agent fires the playbook across your systems.
If you want a reference point rather than a promise: DBW Advanced Fiber Technologies ran a multi-continent inbound and outbound flow on spreadsheets, moved it onto the platform, and also handed us North American logistics management as their 4PL. They published an 18% reduction in supply chain costs off the combined engagement — platform plus managed operations, not the tracking layer alone. Your mileage depends entirely on which of those two you actually buy, and we would rather tell you that now than after you have quoted the number internally.
Orkestra is not a procurement suite, an ERP, or a TMS. We do not create purchase orders, run sourcing events, hold your contracts, approve requisitions, perform three-way match, or pay invoices. Your ERP stays the system of record and your procurement tools keep everything that happens before the PO is issued. We own the window in between: PO issued to goods received, joined across whatever systems and carriers that journey touches. If your ERP data is unreliable at source, fix that first, because no layer sitting above it will save you.
Purchase order tracking questions, answered
DBW runs three continents on one platform with zero spreadsheets in daily operations — and 18% less supply chain cost.
“Full visibility from factories in Europe and Mexico all the way to final customers in North America.”— DBW Advanced Fiber Technologies, Automotive Supplier
