Keep one sheet with a row for every open order line: supplier, order number, item, order value, original promised date, current promised date and received date. Add formulas for days late and a lateness band, then have AI draft a chase email for each band. Review the sheet weekly, send the drafts you agree with and update the dates from the replies.
A procurement system is not the missing piece for most small firms. The missing piece is a single place where every open order is visible with a date that someone is responsible for, and a habit of acting on the ones that slip. A spreadsheet gives you the place. The bands give you the habit, because they decide in advance what a three-day delay and a three-week delay each deserve.
Why chasing fails without a sheet
Late deliveries rarely arrive as a surprise; they arrive as a silence. The order goes out, the supplier acknowledges it and the promised date lives in an email thread. Three weeks later someone asks why stock has run out. By then the date has been missed for ten days and nobody noticed, because a missing delivery produces no notification.
Shopify illustrates the gap. Since Stocky stopped on 31 August 2026, purchase orders live in Shopify admin, but Shopify's help centre says you can't email a purchase order from the admin: you export it as a PDF (More actions, then Export PDF) and send it yourself. So the sending and the chasing happen in email, and the record sits somewhere else. Whichever store platform you use, assume the same: the sending happens in one place and the tracking in another, and you need one sheet that joins them.
The sheet: columns and what each is for
Take a craft brewery supplier with a dozen suppliers and about fifteen open orders at any time. The sheet has one row per order line, not per order, because lines arrive separately. Keep the columns few; every column you add is one more to keep up to date.
| Column | Content | Notes |
|---|---|---|
| A. PO number | PO-0501 | The reference you quote in every email |
| B. Supplier | Name as in your records | One spelling only, so filters work |
| C. Item and quantity | Pale malt, 20 x 25 kg | Plain words |
| D. Value | 1,860 | The line's order value, in your currency |
| E. Original promise | 4 Nov | Never overwritten; it is the baseline |
| F. Current promise | 4 Nov | Updated each time the supplier gives a new date |
| G. Received | blank until delivered | Date the goods arrived, not the date of the invoice |
| H. Days late | formula | See below |
| I. Band | formula | See below |
| J. Promise changes | count | Add one each time column F changes |
| K. Last chased and next action | text and date | What was sent and when to look again |
The distinction between E and F is the most valuable design choice in the sheet. If you overwrite one promised date, you lose the history that shows a supplier has moved the date four times. Keep the original in E, change only F, and count the changes in J.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowFormulas for days late and the bands
For the received-or-not logic, one formula covers both cases. Days late for an open line compares today with the current promise; for a received line it compares the received date with the original promise. In a spreadsheet with the first data row at row 2:
H2: =IF(G2="", MAX(0, TODAY()-F2), MAX(0, G2-E2))
I2: =IF(G2<>"", "Received",
IF(F2-TODAY()<=3, IF(F2>=TODAY(), "Due soon", IF(H2<=3, "Band A", IF(H2<=10, "Band B", "Band C"))),
"On track"))Reading it plainly: a received line shows Received; a line due within three days shows Due soon; a line past its promised date falls into Band A (up to 3 days late), Band B (4 to 10 days) or Band C (11 days or more); anything else is On track. Choose thresholds that fit your lead times. A firm with six-week lead times will find three days a rounding error; one that orders daily food stock may find three hours too long.
Add a conditional-format colour on column I so the sheet reads at a glance. Then sort by band and value, so the biggest orders in the worst band sit at the top.
An example week
Say it is Monday 9 November (illustrative), and the sheet shows these open lines.
| PO | Item | Value | Current promise | Days late | Band |
|---|---|---|---|---|---|
| PO-0492 | Stainless kegs, 40 | 7,200 | 21 Oct | 19 | Band C |
| PO-0498 | Bottle caps, 20,000 | 940 | 2 Nov | 7 | Band B |
| PO-0501 | Pale malt, 20 x 25 kg | 1,860 | 4 Nov | 5 | Band B |
| PO-0505 | Hops, 10 kg | 1,150 | 6 Nov | 3 | Band A |
| PO-0507 | Labels, 5,000 | 410 | 11 Nov | 0 | Due soon |
| PO-0509 | Sanitiser, 12 cartons | 620 | 20 Nov | 0 | On track |
Six lines, and the sheet already decides the week's work: one escalation (the kegs, which are 19 days late and worth $7,200), two firm requests, one polite query, one reminder and nothing for the last line. Without the bands, the loudest message in the inbox would decide.
Four chase messages, one per band
The writing is where AI saves most time, because the messages follow a pattern and the tone matters. Give the assistant the supplier, the order details, the history and the band, and ask for one email. Keep a short instruction block you paste each time.
Write a short email chasing a supplier. Use plain, courteous, specific wording. No threats.
Details: supplier contact [first name]; PO number PO-0498; item bottle caps, 20,000;
promised date 2 Nov; days late 7; this is the first chase; the promise has not changed before.
Band B: firm request. Ask for (1) a confirmed ship date in writing, (2) whether a partial
shipment can go earlier, and (3) the cause of the delay in one sentence.
Keep it under 100 words. Sign off as [your name]. Leave the subject line to me.An illustrative result:
Hello [first name],
Our order PO-0498 (20,000 bottle caps) was promised for 2 November and has not arrived.
Could you confirm in writing by Wednesday: the date it will ship, whether any part of it can
go earlier, and what caused the delay?
We are starting to plan around this, so a firm date matters more to us than a quick reply.
Thank you,
[your name]What you would change: it is good, but "by Wednesday" is a deadline you chose, so check that it is realistic; and the sentence about planning is true only if you are. Do not let the assistant add claims, such as "our production will stop", that are not so.
The four bands at a glance
- Due soon: a friendly check two or three days before the date. Ask only whether it is on schedule and whether anything could prevent it.
- Band A, 1 to 3 days late: a short query. Assume an innocent cause, ask for the new date, and note it in column F.
- Band B, 4 to 10 days late: the firm request above, asking for a confirmed date in writing, a partial shipment and the cause.
- Band C, 11 days or more: an escalation to a named senior contact, with the cost to your business, the history of promised dates and the specific outcome you want (ship date, partial, or cancellation of the balance).
For Band C, the facts matter more than the adjectives. Give the assistant the chase history, any promise changes and the value: "promised 21 October, chased on 26 October and 2 November with no new date given, now 19 days late, value $7,200, 22 customer orders waiting". A message built on those facts is stronger than anything it can write about disappointment.
Turning replies into updated dates
The sheet is only as good as its dates, and the dates live in supplier replies that arrive in different forms: "should ship next week", "with the carrier on Thursday", "we are out of stock until the 20th". Paste the reply into the assistant and ask it to extract what you need.
From this supplier reply, extract: (a) a new promised date if one is given, as a date;
(b) whether it is a firm date or an estimate; (c) any partial shipment mentioned, with
quantity; (d) the reason given. If no date is given, say NO DATE.
Do not invent a date from words like "soon" or "next week"; quote the phrase instead.
Reply: "Sorry for the hold-up. The caps are being packed and should leave us next week."The right answer (illustrative) is NO DATE with the quoted phrase "should leave us next week", plus "estimate, not firm". That matters. If the assistant had turned "next week" into a date and you had entered it into column F, your sheet would show a promise the supplier never made. The extraction rule is the same as in any record-keeping: quote what was said, and enter a date only when one was given.
A weekly routine that takes thirty minutes
- Monday, update. Enter received dates for anything delivered last week (your goods-received notes or delivery paperwork are the source; see matching deliveries to orders). Update column F from replies.
- Sort. By band, then by value, highest first.
- Draft. Ask the assistant for one email per line that needs one, giving it the band, history and the last chase date. Skip any line chased in the last three working days.
- Read and send. Read every draft against the sheet. Check order numbers, quantities and dates. Send from your own email.
- Log. Write the chase date and the next action in column K.
- Look ahead. Filter for Due soon and for any item whose stock-out date comes before its promised date. Those are the ones to act on this week.
The final step connects the sheet to your stock. A late order matters when it will run out of stock before it arrives. Add a column for "stock cover, days" if you can; a Band A line for an item with 40 days of cover is far less urgent than a Due soon line for an item with two. If you are building the ordering side too, automating purchase orders and supplier emails covers the other half.
Stock cover: which late order actually hurts
Lateness alone ranks orders by embarrassment, not by damage. Add one column, stock cover in days, calculated as stock on hand divided by average daily use. Then add a second column that compares it with the delay: the order starts to cost you something when the promised arrival is later than the day you run out.
Take two lines from the example. The hops (PO-0505) are 3 days late, but you hold 14 days of cover, so nothing is lost. The bottle caps (PO-0498) are 7 days late with 5 days of cover left, and the supplier's only answer is "next week": if that means the end of next week plus transit, bottling stops before the caps arrive. Under the lateness bands alone, the caps are Band B and the hops Band A, which gets the order roughly right. Now picture the labels (PO-0507), Due soon on the sheet, with 2 days of cover and a promised date 2 days away. They are on track, yet any further slip stops bottling. Stock cover is the only column that tells you that.
A simple rule built on it: if cover is less than the supplier's average delay plus the transit time, raise the line one band, and ask the assistant to say so in the draft ("we will run out on [date] without this"). Say it only where it is true. The sheet makes that statement checkable, which is what stops a chase email being a bluff.
Setting the sheet up so people really use it
Put it in a shared workbook everyone who orders can open, with edit rights limited to those people, and give one person ownership of the weekly review. A sheet nobody owns decays within a month. Freeze the header row, protect column E and the formula columns, and use a drop-down list for supplier names so the spelling never varies. Keep the prompts in a second tab so any team member can produce the same drafts, and note in the tab which assistant and plan you use.
Keep privacy simple: the sheet holds order numbers, items and values, which is fine for a chat assistant on a business plan or, on a personal plan, once you have checked the training setting. It should not hold supplier bank details, passwords or customers' personal data; none of the drafts need them. If a supplier sends terms marked confidential, check your agreement before pasting them anywhere.
Cancelling, splitting and short deliveries
Three situations do not fit the one-line-one-date shape, and the sheet needs a rule for each. A short delivery (18 of 20 sacks arrive) is entered as a received line for 18 and a new open line for the remaining 2, with the same PO number and a new promised date. A split delivery planned from the start gets one row per lot from the outset. A cancelled balance is not deleted; mark it Cancelled with a reason and the date, because the history of why you cancelled belongs in the supplier's record.
Say a supplier of the kegs (PO-0492, $7,200) offers 28 of the 40 now and the rest in three weeks. Split the row into 28 received at $5,040 (illustrative, at $180 a keg) and 12 open at $2,160 with a new promise. The sheet now shows a smaller exposure and a new date to chase, and the supplier's performance still counts the original 21 October promise as missed. That honesty about the baseline is what makes the scorecard worth reading.
Judging suppliers from the sheet
After a quarter, the sheet is a supplier scorecard. Filter by supplier and calculate three figures. First, on-time rate: lines received on or before the original promise, divided by lines received. Second, average days late on the late ones. Third, promise changes per line. An illustrative reading: Supplier X has an 85 per cent on-time rate, averaging 2 days late on the rest, with 0.1 promise changes a line; Supplier Y has 40 per cent on time, averaging 9 days late, and 1.6 changes per line.
That is information you can act on: build a larger safety stock for Y's items, order earlier, add delivery dates to the order terms or find a second source. Bring the numbers into any review of your suppliers. They also give you a basis for a firm but friendly conversation: "your last eight lines averaged nine days late; what do we need to change?" is a better start than a complaint.
Other businesses, other date problems
A kitchen fitter waiting on made-to-order doors
Lead times run to weeks and a late door stops a whole installation. Add the install date to the sheet and compute "days of float" between the promised delivery and the install. Chase by float, not by days late: a door 2 days late with 15 days of float is a note, and one with 3 days of float is an emergency.
A childcare nursery ordering consumables
Items such as nappies, wipes and craft supplies have a stock cover that is easy to measure. Chase when promised date plus transit is later than the stock-out date, and use a second supplier as a stopgap, not a complaint.
A care agency ordering protective supplies
Unit costs are small, but a gap in stock affects people. Keep a safety stock level on each line, and move any item that falls below it to Band C treatment, whatever the days late say.
A sheet that stayed green while orders ran late
An illustrative failure worth rehearsing: a team ran the sheet for a month and every line showed green. Two faults caused it. Received dates were entered only when the supplier's invoice arrived, often weeks after the goods, so the sheet treated delivered orders as open and the days-late figure was meaningless. And when a supplier moved a date, the team overwrote the original promise in column E instead of the current one in F, so a line promised on 21 October and now promised for 15 November still read as if it had never slipped.
The cure was two written rules. "Received" is the date the box was opened, taken from the delivery note, and column E is protected so that only the current promise can change. To check your own sheet, pick three received orders and trace each from the delivery paperwork back to the sheet. If any date differs, the formulas are not the problem; the entry habit is.
When a sheet stops being enough
The sheet works while one or two people raise orders and a few dozen are open. It starts to creak when several people place orders without a common record, when approvals are needed before an order goes out, or when finance wants the commitments in the books. At that point, look at what your accounting or store software already provides before buying a new system, and use the sheet's column list as a requirements list, since you now know exactly what you track and why.
Until then, keep it simple. Open the sheet on Monday, trust the bands, read every draft before it goes, and update the dates from what the supplier says, not from what you hope. If you would like help fitting this to the way your team orders, that is the kind of thing my AI implementation consultation covers; the contact page is the place to start.
Questions about tracking open purchase orders
Do I need procurement software to track purchase orders?
Not at small scale. A shared spreadsheet with one row per order line, a promised date, a revised date and a received date covers most small businesses. Software earns its place when several people raise orders, approvals are needed or order volume makes a weekly sheet review too slow.
How often should I chase a supplier?
Chase on a rhythm tied to lateness, not on impulse. A reminder just before the promised date, a polite query within a few days of it passing, a firmer request for a committed date after about a week, and an escalation to a named senior contact beyond that. Chasing daily wastes goodwill.
Can AI send the chase emails by itself?
It can draft them, and with the right setup it could send them, but start with drafts that a person reads and sends. A chase email carries your commercial position, and a wrong date, tone or order number sent automatically is harder to retract than a draft you skipped.
What should I measure to judge a supplier?
Three numbers per supplier: the share of lines received by the first promised date, the average days late on the ones that were late, and how many times the promised date was moved. The first shows reliability, the second severity, and the third whether promises mean anything.
Further reads
- Supply Chain AI Software: What a Small Importer Needs First — What a small importer needs before buying supply chain software.
- AI Supplier Negotiation Emails for Importers: Price, MOQ, Terms — Set delivery terms that make chasing easier.
- How to Sync Stock Across Shopify, Amazon and eBay Automatically — Keep channel stock right while a delivery is late.
- How to Compare Supplier Quotes Side by Side With AI — Use lateness data when comparing suppliers' quotes.
- AI Invoice Processing: Stop Typing Supplier Bills by Hand — Process the invoices when the delayed goods finally arrive.
- Alibaba Sourcing With AI: Vet Suppliers and Plan a Sample Order — Vet a replacement supplier when one keeps missing dates.
- Travel Agent Commission Tracking: Match Supplier Payments With AI — A manual-to-AI method for matching supplier commission statements to your bookings, with a worked month, prompts and a chase list.
- AI Tool for Construction Planning: Pre-Start Checklist and Long-Lead Items — A 42-item pre-start checklist for small construction jobs, with prompts for scope, long-lead order-by dates and a risk register you actually review.
- Interior Design Project Timeline: Draft Phases and Lead Times With AI — Draft an interior design timeline backwards from the install date, using supplier lead times from your own records, client decision dates and a buffer.
- Joinery AI: Build a Cutting List and Material Order From a Drawing — Extract every part from a joinery drawing into a sheet, audit it against the drawing, add waste by material and turn the totals into a supplier order.
- AI Tools for Shipping Documents: Commercial Invoices and Packing Lists — A small exporter's comparison of AI tools for invoices and packing lists, with prices, a worked order and the checks that stay human.
- AI in Plant Engineering: Breakdown Logs and Maintenance Plans — Turn a messy breakdown log into ranked repeat faults and a draft preventive maintenance plan, with the safety items left to a named engineer.
- AQL Sampling Plan: Pick Sample Sizes for Incoming Goods With AI — Work out a sample size and accept/reject numbers for an incoming lot, with a spreadsheet lookup you can test against the standard.
- Advance Shipping Notice: Prepare an ASN From Pick Lists With AI — Build a draft ASN from your pick list and carton data, run the mismatch checks against the PO, and send only after the customer's guide is satisfied.
- MRO Inventory Management: AI-Set Minimums for Maintenance Spares — One factory's spares sheet, built from work orders and a criticality score, with min and max rules and an obsolete-parts check.
- Packaging Specification Sheet: AI Drafts Specs Buyers Can Order From — A field-by-field packaging specification sheet, drafted with AI from your product data, with a filled example and a pre-release checklist.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: Shopify help centre pages on transitioning from Stocky and on creating purchase orders (purchase orders in Shopify admin, no emailing from the admin, PDF export).