Blog · How-to guides · Supply chain
Compare inventory and delivery performance by site, supplier and product without multiplying your records. Build matching summaries in Excel, calculate the measures, and prove the totals before acting on an exception.
Supply chain data analysis in Excel compares inventory and delivery performance after each export has been summarized to a consistent grain. Keep shipment activity separate from the inventory snapshot, then combine their summaries by site and product. That prevents extra delivery rows from multiplying the stock balance.
Ask a practical question: which site-product combinations have weak delivery performance while holding older stock? That is an exception screen, not proof that the stock caused the service problem.
For a US distributor, use orders promised during September 2026 and inventory at September 30, 2026. The order cohort stays fixed even when a late delivery arrives in October. Use one reporting cutoff for deciding whether each line passed the delivery test.
Write those boundaries above the workbook. Also record whether canceled lines, partial shipments and rescheduled promises qualify. The broader business analysis workflow in Excel covers the same sequence of question, clean table, summary and reconciliation.
Keep separate tables for these records:
| Export | One row represents | Required fields |
|---|---|---|
| Orders | One order line | Line ID, site, SKU, supplier, promised date, ordered quantity |
| Deliveries | One receipt or delivery event | Line ID, delivery date, delivered quantity |
| Inventory | One stock lot at the snapshot date | Site, SKU, lot, stock value, aging category |
Consolidate delivery events by line ID before assigning a service result. Otherwise a line delivered in several parts becomes several opportunities to pass. Use the agreed promised date and quantity test; the existing OTIF calculation guide owns that metric definition and its treatment of partial deliveries.
For this workbook, the final Lines table contains one eligible order line, its site and SKU, and Pass set to either 1 or 0. The Stock table retains individual lots, with their value in USD and an Over90Value field. That field contains the lot value when the agreed aging basis exceeds 90 days, otherwise zero. Use the inventory aging by site guide to choose and document the aging basis.
Do not silently replace missing delivery dates, unknown aging dates or blank values with a successful result. Put those records on a review list.
Use one comparison row per site and SKU. In this example each SKU has one supplier of record: SKU A comes from North Supply and SKU B from South Supply. That makes supplier attribution unambiguous. Actual names and identifiers here are fictional.
Create a Summary table containing the distinct site-SKU keys from both sources. For each row, count eligible lines and sum passes:
=COUNTIFS(Lines[Site],[@Site],Lines[SKU],[@SKU])
=SUMIFS(Lines[Pass],Lines[Site],[@Site],Lines[SKU],[@SKU])
Summarize inventory separately in InventorySummary:
=SUMIFS(Stock[ValueUSD],Stock[Site],[@Site],Stock[SKU],[@SKU])
=SUMIFS(Stock[Over90Value],Stock[Site],[@Site],Stock[SKU],[@SKU])
Microsoft documents SUMIFS as summing a range subject to multiple criteria. Use the same site and SKU fields for both measures, with matching range sizes.
In each summary table create a key, after confirming the separator does not appear in either identifier:
=[@Site]&"|"&[@SKU]
Check key uniqueness with =COUNTIF(InventorySummary[Key],[@Key]). Every populated inventory summary key must return 1. Then bring its stock value into Summary:
=XLOOKUP([@Key],InventorySummary[Key],InventorySummary[StockUSD],"CHECK",0)
Repeat for the older-stock value. Microsoft's XLOOKUP documentation explains that it returns the first matching item. That makes the uniqueness check essential: a lookup does not prove there was only one match. Leave missing matches as CHECK until their meaning is resolved.
The resulting summary has these inputs. Stock values are measured at the snapshot date; delivery counts refer to the September promised-date cohort.
| Site | SKU | Supplier | Eligible lines | Passing lines | Stock (USD) | Over 90 days (USD) |
|---|---|---|---|---|---|---|
| East | A | North Supply | 100 | 85 | 22,000 | 7,000 |
| East | B | South Supply | 50 | 45 | 8,000 | 1,000 |
| West | A | North Supply | 80 | 72 | 16,000 | 4,000 |
| West | B | South Supply | 70 | 63 | 14,000 | 2,000 |
| Total | 300 | 265 | 60,000 | 14,000 |
Add the ratios only after matching the inputs:
=IF([@EligibleLines]=0,"",[@PassingLines]/[@EligibleLines])
=IF([@StockUSD]=0,"",[@Over90USD]/[@StockUSD])
| Site and SKU | Delivery pass rate | Older-stock share |
|---|---|---|
| East A | 85.0% | 31.8% |
| East B | 90.0% | 12.5% |
| West A | 90.0% | 25.0% |
| West B | 90.0% | 14.3% |
| Combined | 88.3% | 23.3% |
Percentages are rounded to one decimal place after calculation. The combined rates are 265 / 300 = 88.3% and 14,000 / 60,000 = 23.3%. Do not average the displayed row percentages.
Prove the combined file with separate controls: eligible lines total 300, passing lines total 265, stock totals $60,000 and older stock totals $14,000. Each must equal the corresponding source summary before matching. Any nonzero difference stops the comparison.
East A warrants investigation because its delivery rate is lower and its older-stock share is higher than the other combinations. Ask whether the stock was unavailable, allocated, damaged or unrelated to the specific lines that failed. Assign purchasing and the site operations owner to review the underlying records. A supply chain KPI dashboard can present the checked measures after those definitions are settled.
Covirage is not affiliated with Microsoft.
Bring the checked site-product summary as an Excel file, with delivery counts and dated inventory values. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the exceptions 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 export.
See Covirage for supply chain teams for the business context.
Use an order-line export with promised dates and quantities, delivery records with receipt dates and quantities, and a dated inventory snapshot. Keep site and product identifiers consistent across files. Supplier comparisons also need an agreed supplier assignment that does not duplicate stock.
Avoid matching every delivery row to every inventory row for the same product. Both sources can contain repeated keys, which multiplies values. Summarize each source to the intended comparison grain first, verify unique keys, and then match those summaries.
Calculate the combined percentage from total passing lines divided by total eligible lines. A simple average gives a small supplier the same influence as a large supplier. Keep the eligibility rule and measurement period identical across every supplier in the comparison.
Check source row counts, missing keys and duplicate summary keys. Reconcile eligible delivery counts and inventory values separately before and after combining summaries. Each total should match its source control, and unmatched records should remain visible until someone resolves them.