Order — purchase order tracker

Pro workbook: Orders, Items, Suppliers, Monthly, Setup, Formulas. Sample data, real formulas.

Purchase orders
One row per PO. Days, the Late flag and the summary are calculated; the amount comes from the Items sheet.
As of14 SepOpen POs4
Open value3,572.00
Late2
Received this month356.00
PODateSupplierWhatAmountStatusExpectedReceived onPaidDaysFlagLinesOutstanding qtyOrdered this year8,565.50
PO-10418 JanNorthwind PaperA4 stock, Q1430.00Received15 Jan14 Jan✓61Received, not paid532.00
PO-104220 JanCadence OfficeDesk lamps ×6228.00Received3 Feb6 Feb✓171Avg lead time, days11.5
PO-104311 FebFathom ElectronicsLabel printers ×2442.00Received25 Feb24 Feb✓132
PO-10442 MarLumen PackagingMailer boxes, 500310.00Received12 Mar12 Mar✓101
PO-104518 MarBright PrintSpring catalogue1,700.00Received1 Apr8 Apr✓211
PO-10466 AprNorthwind PaperA4 stock, Q2430.00Received13 Apr13 Apr✓71
PO-104722 AprCadence OfficeChairs ×4840.00Cancelled20 May1
PO-10485 MayLumen PackagingTape and void fill162.00Received12 May11 May✓62
PO-10491 JunFathom ElectronicsBarcode scanners ×3222.00Received15 Jun19 Jun✓181
PO-105024 JunBright PrintBusiness cards176.00Received3 Jul2 Jul81
PO-10517 JulNorthwind PaperA4 stock, Q3537.50Received14 Jul14 Jul✓71
PO-105228 JulLumen PackagingMailer boxes, 1000768.00Part received11 Aug48Late2600
PO-105312 AugCadence OfficeMonitor arms ×5230.00Ordered30 Aug33Late15
PO-105426 AugFathom ElectronicsRouter and switches356.00Received9 Sep8 Sep132
PO-10553 SepBright PrintAutumn catalogue2,450.00Ordered24 Sep1112500
PO-105610 SepNorthwind PaperEnvelopes124.00Ordered17 Sep4Due this week14
Line items
One row per line on a PO. The PO's amount on Orders is the sum of its lines.
Lines20
Still to arrive, lines5
Still to arrive, value3,224.00
POItemQtyUnit priceLine totalReceived qtyOutstandingSupplierStatus
PO-1041A4 80gsm, box of 5 reams2021.50430.0020Northwind PaperReceived
PO-1042LED desk lamp638.00228.006Cadence OfficeReceived
PO-1043Label printer LP-3002189.00378.002Fathom ElectronicsReceived
PO-1043Label rolls106.4064.0010Fathom ElectronicsReceived
PO-1044Mailer box 30×20×105000.62310.00500Lumen PackagingReceived
PO-1045Catalogue, 24 pp20000.851,700.002000Bright PrintReceived
PO-1046A4 80gsm, box of 5 reams2021.50430.0020Northwind PaperReceived
PO-1047Task chair4210.00840.0004Cadence OfficeCancelled
PO-1048Packing tape, 36 rolls328.0084.003Lumen PackagingReceived
PO-1048Void fill, 10 kg419.5078.004Lumen PackagingReceived
PO-1049Barcode scanner BS-2374.00222.003Fathom ElectronicsReceived
PO-1050Business cards, 500822.00176.008Bright PrintReceived
PO-1051A4 80gsm, box of 5 reams2521.50537.5025Northwind PaperReceived
PO-1052Mailer box 30×20×1010000.58580.00600400Lumen PackagingPart received
PO-1052Mailer box 40×30×152000.94188.000200Lumen PackagingPart received
PO-1053Monitor arm, single546.00230.0005Cadence OfficeOrdered
PO-1054Router R-9001240.00240.001Fathom ElectronicsReceived
PO-10548-port switch258.00116.002Fathom ElectronicsReceived
PO-1055Catalogue, 32 pp25000.982,450.0002500Bright PrintOrdered
PO-1056DL envelopes, 1000431.00124.0004Northwind PaperOrdered
By supplier
POs, spend this year, what's open, how long they take, how often they're late.
SupplierPOsSpend this yearOpen valueAvg lead timeLate nowShareBiggest supplierBright Print
Northwind Paper41,521.50124.006.718%Slowest, days17.0
Fathom Electronics31,020.0014.712%
Lumen Packaging31,240.00768.008.0114%
Cadence Office2458.00230.0017.015%
Bright Print34,326.002,450.0014.551%
All158,565.503,572.002
Avg lead time averages the Days column of received POs — order date to received date. Late now counts open POs past their expected date.
Month by month
What was ordered and what arrived, twelve months from the first month on Setup.
MonthPOs raisedOrderedReceivedStill open from this month
Jan 20262658.00430.00
Feb 20261442.00670.00
Mar 202622,010.00310.00
Apr 20261430.002,130.00
May 20261162.00162.00
Jun 20262398.00222.00
Jul 202621,305.50713.50768.00
Aug 20262586.00230.00
Sep 202622,574.00356.002,574.00
Oct 2026
Nov 2026
Dec 2026
Year158,565.504,993.503,572.00
Ordered is by PO date, Received by received date — so a PO raised in one month and delivered the next appears in both columns, once each.
Setup
The as-of date, the first month of the Monthly view, and the lists.
As of2026-09-14Type =TODAY() to keep it live
Monthly view startsJan 2026
StatusSuppliers
OrderedNorthwind Paper
Part receivedFathom Electronics
ReceivedLumen Packaging
CancelledCadence Office
Bright Print
The formulas are written for these four status names — keep them. The PO list for Items comes straight from Orders.
How the formulas work
Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign.
WhereFormulaWhat it does
Orders · AmountSUMIF(Items!$A$8:$A$307,A8,Items!$E$8:$E$307)The PO's line totals on Items, added up.
Orders · DaysIF(H8<>"",H8-B8,IF(F8="Cancelled",0,$B$3-B8))Received: the lead time. Open: days since the order. Cancelled: nothing.
Orders · FlagIF(OR(F8="Received",F8="Cancelled"),"",IF(AND(G8<>"",G8<$B$3),"Late",IF(AND(G8<>"",G8<=$B$3+7),"Due this week","")))Only open POs get a flag, against the expected date.
Orders · Open valueSUMIF(F8:F87,"Ordered",E8:E87)+SUMIF(F8:F87,"Part received",E8:E87)Two statuses count as open; adding two SUMIFs is the portable OR.
Orders · Received, not paidSUMIFS(E8:E87,F8:F87,"Received")-SUMIFS(E8:E87,F8:F87,"Received",I8:I87,"✓")Received minus received-and-ticked.
Orders · Avg lead timeAVERAGEIFS(J8:J87,F8:F87,"Received")Average of Days over received POs. IFERROR wraps it for an empty sheet.
Items · OutstandingMAX(0,C8-F8)Ordered quantity minus received, never negative.
Items · Still to arrive, valueSUMPRODUCT((I8:I307<>"Cancelled")*G8:G307*D8:D307)Outstanding × unit price across every line, skipping cancelled POs — one SUMPRODUCT instead of a helper column.
Suppliers · Spend this yearSUMIFS(Orders!$E$8:$E$87,Orders!$C$8:$C$87,A5,Orders!$F$8:$F$87,"<>Cancelled",Orders!$B$8:$B$87,">="&DATE(YEAR(Setup!$B$3),1,1),Orders!$B$8:$B$87,"<="&Setup!$B$3)By supplier, not cancelled, dated this year up to 'as of'.
Monthly · ReceivedSUMIFS(Orders!$E$8:$E$87,Orders!$H$8:$H$87,">="&A5,Orders!$H$8:$H$87,"<="&EDATE(A5,1)-1)PO amounts whose received date falls in the month.