Purchase order dashboard: open orders and delivery gaps

A purchase order dashboard shows what remains to arrive, which promised dates have been missed, and the value attached to those gaps. Build it from purchase-order lines and receipt records, then assign the exceptions to buyers and receiving teams who can resolve them.

The useful distinction is between open and overdue. A future delivery belongs in the open balance, while a partially received line past its promised date belongs in both.

Begin with one row per order line

Export the purchase-order number, line number, supplier, buyer, receiving site, item, unit of measure, ordered quantity, canceled quantity, unit price, currency, and promised date. Add the order revision or last-change date when available.

Bring the receipt export separately. It needs the order and line identifiers, receipt quantity, receipt date, and acceptance or reversal status. Sum receipts to the order line before joining them to the order export. Otherwise, repeating the ordered quantity beside every receipt inflates the original order.

Use quantities accepted by the receiving team for this example. Keep rejected goods, returns, and receipt reversals in a separately defined adjustment field where the source system requires it. Do not guess whether a returned item reopens the supplier's delivery obligation.

The distinction between delivery and acceptance also appears in federal guidance on physical contract completion. For your company, apply the receiving and acceptance definitions agreed by procurement and operations.

Define the headline measures

For fixed-price goods lines, use:

Remaining quantity = Ordered quantity − Accepted quantity − Canceled quantity

Open value = Remaining quantity × Unit price

Overdue open value = Open value for lines with a promised date before the reporting date

The overdue test also requires a positive remaining quantity. A line already received in full must not become an exception merely because its promised date is in the past.

Do not hide negative remaining quantities with a zero floor before checking them. A negative result may reveal overdelivery, a missing order revision, or duplicated receipts. Show it as an exception, investigate, and then determine the valid open balance.

For service orders measured in dollars rather than units, use authorized and accepted service amounts. Keep those calculations separate until both groups have been expressed as comparable USD values.

A worked US purchasing file

A distributor reviews these goods lines on September 30, 2026. Every line uses its stated unit price, and all values exclude sales tax and freight. Each remaining quantity is measured in the item's own ordering unit; quantities across different items are not added together.

P204 has 120 − 40 − 20 = 60 units remaining, worth 60 × 15 = $900. The canceled units are excluded from future demand rather than reported as a delivery failure.

The date test produces the action list:

There are two overdue lines. Their $1,900 is 1,900 / 8,100 = 23.5% of open value, rounded to one decimal place. This is an outstanding-value measure, not an on-time delivery rate. For performance over completed and due deliveries, use the separate OTIF worked example.

Rebuild the measures in a spreadsheet

Place ordered quantity in B, accepted quantity in C, canceled quantity in D, price in E, and promised date in F. Put the report date in N1. For the first data row:

G2 = B2-C2-D2
H2 = G2*E2
I2 = IF(AND(G2>0,ISNUMBER(F2),F2<$N$1),$N$1-F2,0)
J2 = IF(I2>0,H2,0)

Copy those formulas down through the supplied lines. Count open lines and add overdue value with:

=COUNTIFS(G2:G6,">0")
=SUM(J2:J6)

COUNTIFS counts rows meeting the positive-remaining-quantity condition. A blank promised date needs its own exception count; it cannot safely be interpreted as an on-time line.

To total overdue value directly from valid date cells:

=SUMIFS(H2:H6,G2:G6,">0",F2:F6,">0",F2:F6,"<"&$N$1)

The positive-date condition excludes blanks and zero dates. SUMIFS supports multiple criteria for the same total, so the formula requires both an outstanding quantity and a past promised date.

Prove the balance before calling suppliers

The order value must split into accepted value, canceled value, and remaining value at the same line prices:

The identity is 17,000 − 8,600 − 300 = $8,100. Tie the ordered total to the source export separately: an internally consistent table can still omit orders. The distinction between those checks is explained in control totals and identities.

Suppose receiving confirms another 20 accepted units on P201 before the same cut-off. Its accepted quantity becomes 80 and its open value becomes (100 − 80) × 25 = $500. Total open value falls to $7,600 and overdue value falls to $1,400; the overdue share becomes 18.4%. This is a receipt correction, so the dashboard alone does not prove the supplier improved.

Assign the review and avoid the common traps

Buyers own supplier follow-up and revised commitments. Receiving teams own missing receipt evidence; operations identifies which shortage threatens production or customer delivery. Review the largest overdue values alongside business criticality, rather than assuming price alone determines urgency.

• Replacing the original promise. Preserve original and revised promised dates if you want to distinguish supplier lateness from an agreed schedule change.
• Mixing units or currencies. A case is not an individual unit. Convert before calculating value, and document any currency conversion.
• Using invoice status as receipt status. An invoice does not establish that goods were accepted. Keep the separate order, invoice, and paid-spend measures distinct.
• Counting lines as orders. A buyer can have several overdue lines on the same purchase order. Label both populations accurately.

Approval timing is another control question, addressed in after-the-fact purchase orders. It should not be inferred from delivery dates alone.

Use cost per purchase order to measure purchasing administration expense; that processing cost is separate from the value of goods still due.

Check your own purchase-order file in Covirage

Bring a sample of your own purchase-order and receipt exports to Covirage. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your file.

See Covirage for procurement teams for the supporting purchasing analysis context.