Inventory KPI dashboard in Excel

An inventory KPI dashboard in Excel compares stock value, aging, turnover and availability by SKU and location using the same reporting cutoff. Build the source checks before the charts, and calculate total ratios from the underlying amounts. The layout below provides a working structure you can recreate in your own workbook.

Give each worksheet a clear job

Use a Stock sheet for beginning and ending balances, an Activity sheet for period cost of goods sold and fulfillment, a Measures sheet for calculations, and a Dashboard sheet for the results. Retain lot-level aging detail in the source workbook even when the dashboard summarizes it.

The stock grain is one SKU and location at a stated cutoff. Activity covers the period between the beginning and ending snapshots. Summarize activity to that same grain before joining it to balances; otherwise repeated order lines will multiply inventory value.

This is an inventory workbook. The broader inventory dashboard discusses the operating view, while the supply chain KPI dashboard in Excel adds delivery and supplier measures.

For presentation mechanics, use the separate Excel dashboard guide. The KPI dashboard template provides a common layout for targets and status rules; use the inventory definitions here for its inputs.

Fill the stock and activity inputs

A US distributor is reviewing October 2026. Beginning value is the opening inventory at October 1; closing units are the balance at October 31. The example assumes a constant unit cost within each SKU, with beginning and ending values on the same cost basis.

Age means elapsed days since the remaining lot's receipt date. Aggregate the value of lots aged at least 180 days. A recent receipt for a SKU must not reset the age of its older lots.

Blocked and reserved units are mutually exclusive in this export. Establish that rule with the source owner before subtracting both columns.

The activity export covers October requests and shipments allocated to those same requests through the report cutoff. Requested units include the unfilled units; shipments from other request periods are excluded.

This defines a unit fill rate at the cutoff, rather than an order fill rate or an on-time delivery measure. Write that definition beside the dashboard.

Calculate the measures

Copy the stock columns into A:H on Measures in the order shown above. Use I for closing value, J for aged value, K for available units and L for average inventory value. In row 2 enter:

=D2*E2
=F2*E2
=D2-G2-H2
=(C2+I2)/2

Use M:O for period COGS, requested units and shipped units. Name the activity table Activity, with columns SKU, Site, COGS, Requested and Shipped. Populate M2, N2 and O2:

=SUMIFS(Activity[COGS],Activity[SKU],$A2,Activity[Site],$B2)
=SUMIFS(Activity[Requested],Activity[SKU],$A2,Activity[Site],$B2)
=SUMIFS(Activity[Shipped],Activity[SKU],$A2,Activity[Site],$B2)

Microsoft documents SUMIFS as a sum subject to multiple criteria; here those criteria are SKU and site. Keep the activity table restricted to the defined reporting period.

Put turnover in P, fill rate in Q and aged value share in R:

=IF(L2=0,"Check denominator",M2/L2)
=IF(N2=0,"No requests",O2/N2)
=IF(I2=0,"No stock value",J2/I2)

Fill down. Turnover uses the simple average of opening and closing value. If inventory swings sharply within the month, replace that approximation with a consistently calculated average of more frequent snapshots.

Values and quantities are exact. Display turnover to two decimals and percentages to one decimal; calculate from the unrounded values.

Prove the dashboard totals

Closing value is 100 × 10 + 60 × 10 + 80 × 25 = $3,600. Aged value is 20 × 10 + 5 × 10 + 30 × 25 = $1,000, or 1,000 / 3,600 = 27.8% of closing value.

Beginning value totals $5,000. Average inventory is (5,000 + 3,600) / 2 = $4,300, so October turnover is 7,800 / 4,300 = 1.81. Company unit fill rate is 220 / 240 = 91.7%.

Use summed amounts in the total formulas:

=SUM(M2:M4)/SUM(L2:L4)
=SUM(O2:O4)/SUM(N2:N4)
=SUM(J2:J4)/SUM(I2:I4)

The SUM behavior is covered in Microsoft's formula guide. Available units also reconcile: 240 on hand - 15 blocked - 65 reserved = 160. Check stock balances with the stock movement reconciliation before accepting these dashboard totals.

The next action is visible in the detail: B200 in Reno has $750 of the $1,000 aged value and the lowest fill rate. Check which lots and requests explain both results before proposing a stock transfer or another purchase.

Where it goes wrong

• Averaging row percentages. Company fill rate and aged share need their combined denominators.
• Mixing cost and selling value. Turnover compares COGS with inventory carried on a compatible cost basis.
• Overlapping restrictions. Subtracting units that are both blocked and reserved twice understates availability.
• Aging the whole SKU from its latest receipt. Preserve the remaining lot quantities and dates.
• Hiding negative availability. A negative result can reveal over-allocation or a source error. Investigate it before applying a display rule.

Covirage is not affiliated with Microsoft.

Check your inventory workbook in Covirage

Bring your stock snapshots, lot aging and activity exports in a workbook or Google Sheet. Covirage maps the columns, and its tools compute and check the figures; 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 inventory file.

See Covirage for supply chain teams for related inventory and operations work.