Blog · Board and management reporting · Supply chain
Build a supply chain dashboard from purchase orders, receipt records, and inventory exports. Match the data grains, calculate service at the promised-date cutoff, and reconcile stock by site before presenting results.
A supply chain dashboard reconciles order promises, receipt events, and inventory positions into comparable measures by site. Build the underlying tables before designing charts: an attractive service percentage is unreliable if split deliveries duplicate orders or stock snapshots use different cutoffs.
Start with the decisions the weekly review must support: which supplier needs follow-up, which site has an unexplained stock difference, and which overdue line needs action. Use the existing KPIs for supply chain teams to select measures rather than building another metric glossary.
State the reporting period, eligible sites, unit of measure, and promise-date policy. Keep the original commitment if that is the agreed service rule; silently replacing it with a revised date changes the result. Procurement should approve supplier definitions, receiving should confirm receipt completeness, and the inventory owner should approve movement signs.
The worked example covers October 1 through October 7, 2026, for one SKU at two US sites. All quantities use the same base unit. Only positive, noncanceled order lines due within the period enter service measurement.
Use these tables, with headers in the first row:
| Sheet | One row represents | Columns needed here |
|---|---|---|
| Due | One eligible purchase-order line | A line ID; B site; C supplier; D SKU; E promised date; F ordered units |
| Receipts | One accepted receipt event | A receipt ID; B line ID; C receipt date; D accepted units; E site |
| Stock | One site-SKU position at the closing cutoff | A site; B opening units; C period receipts; D issues; E signed adjustments; F closing snapshot |
The Due line ID must be unique. A receipt ID must also be unique, but several receipts may belong to one order line. Use real spreadsheet dates and a consistent site business date for receipt timestamps.
Do not merge the raw receipt rows into the order table and then total ordered quantities: that repeats a line's order quantity for every delivery. Aggregate the receipts first. Preserve an exceptions list for unknown line IDs, mismatched sites, missing dates, and unconfirmed quantities.
The hypothetical receipt export contains these events. Receipt dates below are in October 2026.
| Receipt ID | Line ID | Receipt date | Accepted units | Site |
|---|---|---|---|---|
| R1 | PO100 | October 3 | 60 | Dallas |
| R2 | PO100 | October 5 | 40 | Dallas |
| R3 | PO101 | October 6 | 60 | Dallas |
| R4 | PO101 | October 8 | 20 | Dallas |
| R5 | PO102 | October 4 | 120 | Tulsa |
| R6 | PO103 | October 7 | 90 | Tulsa |
| Total | 390 |
In Due, add column G for units received by promise and H for the on-time-in-full flag. The formulas below work in Excel and Google Sheets using comma separators. Both Microsoft's SUMIFS documentation and Google's SUMIFS documentation describe summing a range subject to multiple criteria.
In G2, then fill down:
=SUMIFS(Receipts!$D$2:$D$7,Receipts!$B$2:$B$7,$A2,Receipts!$C$2:$C$7,"<="&$E2)
In H2, then fill down:
=IF(OR(E2="",F2=""),NA(),--(G2>=F2))
This treats delivery by the promised date of at least the ordered quantity as a pass. Check overreceipts separately. The supplier OTIF guide explains the service definition in more depth.
| Line ID | Site | Supplier | SKU | Promised date | Ordered units | By promise | OTIF flag |
|---|---|---|---|---|---|---|---|
| PO100 | Dallas | A | Part-X | October 5 | 100 | 100 | 1 |
| PO101 | Dallas | A | Part-X | October 6 | 80 | 60 | 0 |
| PO102 | Tulsa | B | Part-X | October 5 | 120 | 120 | 1 |
| PO103 | Tulsa | B | Part-X | October 7 | 100 | 90 | 0 |
| Total | 400 | 370 | 2 |
The order-line OTIF result is 2 / 4 = 50.0%, rounded to one decimal place:
=SUM(H2:H5)/COUNTA(A2:A5)
The receipt total reconciles as 370 by promise + 20 late = 390 received. The ordered quantity reconciles as 390 received + 10 not received = 400. A delivery after the promise does not repair the historical service flag.
Period receipts for inventory use movement dates, including receipts against orders due in other periods. This is a different selection from the service cohort. In this example, period receipts happen to equal receipts by promise because the only later event is R4.
In Stock C2, sum October 1 through October 7 receipts for the site:
=SUMIFS(Receipts!$D$2:$D$7,Receipts!$E$2:$E$7,$A2,Receipts!$C$2:$C$7,">="&DATE(2026,10,1),Receipts!$C$2:$C$7,"<"&DATE(2026,10,8))
Bring opening units, issues, signed adjustments, and closing snapshots from the inventory exports. Calculate closing units in G2 with =B2+C2-D2+E2, and the difference in H2 with =G2-F2.
| Site | Opening | Period receipts | Issues | Adjustments | Closing snapshot | Calculated closing | Difference |
|---|---|---|---|---|---|---|---|
| Dallas | 200 | 160 | 190 | -10 | 160 | 160 | 0 |
| Tulsa | 100 | 210 | 180 | 0 | 130 | 130 | 0 |
| Total | 300 | 370 | 370 | -10 | 290 | 290 | 0 |
The company check is 300 + 370 - 370 - 10 = 290 units. Investigate a difference rather than overwriting it. Keep balances at one cutoff; use inventory aging by site for a separate view of how long that stock has been held.
Show service by supplier and site, closing inventory by site, and an exception table with an owner and next action. Keep source totals and reconciliation differences visible beside the report.
Check Due IDs with =COUNTIF($A$2:$A$5,A2)=1, filled down; every result should be TRUE. Google's COUNTIF documentation explains the range-and-criterion test. Apply the same uniqueness check to receipt IDs before refreshing calculations.
Use supply chain dashboard examples for presentation choices after the measures reconcile. Covirage is not affiliated with Microsoft or Google.
Bring purchase-order lines, receipt events, and inventory exports: Covirage maps the columns, and its tools compute and check comparable figures from the supplied files. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for supply chain teams for the operational context.
Include measures tied to decisions: supplier service against promises, inventory balances, and the exceptions requiring action. Show the reporting period, cutoff, denominator, and owner beside each view. Link the headline results to the underlying orders, receipts, or stock records used to calculate them.
Keep each purchase-order line once in the service table and store receipt events separately. Sum eligible receipts to that line before testing whether the promised quantity arrived on time. Counting receipt events as orders changes the denominator and gives split deliveries extra weight.
No. A stock snapshot describes a position at a particular cutoff. Add compatible locations or items at that cutoff, or calculate an explicitly defined average across dates. Adding successive daily closing balances counts the same physical inventory repeatedly and does not represent stock on hand.