Blog · Board and management reporting · Supply chain
Build a supply chain workbook that compares delivery reliability, inventory and supplier lead times. Use the layout, formulas and worked checks to keep each measure tied to the right export.
A supply chain KPI dashboard in Excel should compare delivery reliability, supplier lead times and inventory without mixing their source rows. Build separate input tables, calculate the measures, and put reconciliation checks beside the results. The dashboard becomes useful when a planner can identify the supplier, SKU or site behind a change.
For an Ohio industrial distributor, use the following layout. The examples below cover September 2026 and use calendar days for lead time. The delivery measure is inbound supplier performance at the receiving site; customer deliveries would need their own table and agreed event dates.
| Worksheet | One row represents | Required columns | Output |
|---|---|---|---|
| Controls | A reporting selection | Period start, period end, site, definition owner | Visible scope |
| DueLines | A purchase order line due in the period | Line ID, supplier, release date, due date, ordered units, units received by due date, completion date | Delivery and lead-time measures |
| Inventory | A SKU at a site | Opening value, closing value, period COGS | Inventory investment and turnover |
| Checks | A control comparison | Source totals, workbook totals, differences | Exceptions to resolve |
| Dashboard | A KPI and its comparison | Actual, prior period, target, owner, action | Management view |
Convert the input ranges to tables and give them the worksheet names above. Keep canceled lines and their exclusion reasons visible outside the eligible delivery population. Resolve missing quantities and dates before scoring performance.
The broader Excel dashboard guide covers the presentation steps. This layout concentrates on the different operational populations behind the measures.
For this workbook, a line passes on-time-in-full when the accepted quantity received by its agreed due date equals or exceeds the ordered quantity. All lines due during the selected month stay in the denominator, including lines still open at month-end. Freeze the agreed due date used for scoring so later rescheduling does not rewrite performance.
Here are the eligible lines; dates are in September 2026. The completion date is when the full accepted quantity arrived.
| Line | Supplier | Released | Due | Completed | Ordered units | Units by due date | Lead days | Pass |
|---|---|---|---|---|---|---|---|---|
| D01 | Pine | September 1 | September 8 | September 8 | 100 | 100 | 7 | 1 |
| D02 | Pine | September 2 | September 9 | September 11 | 200 | 150 | 9 | 0 |
| D03 | Lake | September 4 | September 14 | September 14 | 150 | 150 | 10 | 1 |
| D04 | Lake | September 7 | September 17 | September 18 | 50 | 0 | 11 | 0 |
| Total | 500 | 400 | 37 | 2 |
Use these calculated columns after validating that ordered units are positive and received quantities are numeric:
=IF([@[Units by due date]]>=[@[Ordered units]],1,0)
=IF([@Completed]="","",[@Completed]-[@Released])
Then calculate the dashboard results:
=SUM(DueLines[Pass])/ROWS(DueLines[Line])
=SUM(DueLines[Units by due date])/SUM(DueLines[Ordered units])
=AVERAGE(DueLines[Lead days])
The results are 50.0% line OTIF, 80.0% of units received by the due date, and 9.3 calendar days mean lead time. The arithmetic is 2 / 4, 400 / 500 and 37 / 4. Display percentages and days to one decimal, retaining full precision underneath.
Microsoft documents AVERAGE as the arithmetic mean. It ignores empty referenced cells, so show the completed-line count next to lead time; otherwise an increase in unfinished lines can disappear from that average. Here every line is complete, so its count matches the due-line population.
To show Pine's units received by the due date, sum only its matching rows:
=SUMIFS(DueLines[Units by due date],DueLines[Supplier],"Pine")
That returns 250 units from D01 and D02. Microsoft's SUMIFS reference confirms that matching criteria control which cells enter the sum.
Inventory is a balance at a date. Do not repeat that balance against every delivery line. Keep the cost values in a separate table with COGS for the same month and valuation basis.
| SKU and site | Opening inventory (USD) | Closing inventory (USD) | September COGS (USD) | Average inventory (USD) | September turns |
|---|---|---|---|---|---|
| A, Ohio | 60,000 | 40,000 | 100,000 | 50,000 | 2.0 |
| B, Ohio | 30,000 | 50,000 | 80,000 | 40,000 | 2.0 |
| Total | 90,000 | 90,000 | 180,000 | 90,000 | 2.0 |
=([@[Opening inventory]]+[@[Closing inventory]])/2
=IF([@[Average inventory]]=0,NA(),[@[September COGS]]/[@[Average inventory]])
=SUM(Inventory[September COGS])/SUM(Inventory[Average inventory])
The total is 180,000 / 90,000 = 2.0 turns for September. This uses a two-point inventory average; use daily or weekly balances when large swings make it unrepresentative. The separate inventory KPI dashboard covers aging and availability in more depth.
The delivery panel must reconcile to the eligible purchase order lines: four lines, 500 ordered units and 400 units received by the due dates. Check receipt totals against the receipt register after aggregating partial receipts to their purchase order lines. Keep quantities above the ordered amount visible as exceptions.
The inventory panel must tie opening and closing values to the inventory control reports and September COGS to the finance-approved cost report. A dashboard total that matches itself is not an independent reconciliation.
Show actuals, the prior comparable period, an owner-approved target and an action owner. Use the KPI dashboard template for common target and status rules. For a measure's operating definition, consult the supply chain KPI guide before extending this workbook.
Covirage is not affiliated with Microsoft.
Bring your delivery, receipt and inventory workbook, with the agreed dates and reporting scope. 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 a sample of your own data.
See Covirage for supply chain teams for the supporting business context.
Start with delivery performance against the agreed due date, supplier lead time and inventory investment. Add detail only when it supports a decision. Each measure needs a named owner, a reporting period, an explicit denominator and an export that can reproduce the result.
Either can work if the definition stays consistent. A line-based measure counts each due line once. An order-based measure usually requires every eligible line on the order to pass. Label the dashboard clearly because the two percentages answer different questions.
Only after making the periods comparable. Monthly turnover uses that month's cost of goods sold and inventory average. Annual turnover needs annual cost of goods sold and a suitable annual average. A seasonal business should avoid treating one month's turnover as a reliable annual run rate.