Blog · Finance metrics and formulas · Supply chain
Calculate average inventory from opening and closing balances or dated snapshots. Work through unequal intervals, spreadsheet formulas, and checks that keep SKU and site comparisons consistent.
The average inventory formula is opening inventory plus closing inventory, divided by two. That is a useful estimate when balances move fairly evenly; dated snapshots give a better view when stock builds, sells down, or changes sharply within the period. Unequal gaps between snapshots need time weights rather than equal votes.
Endpoint average inventory = (Opening inventory + Closing inventory) / 2
Snapshot average inventory = Sum of comparable snapshot balances / Number of snapshots
Time-weighted average inventory = Sum of (Balance × Days represented) / Total days represented
These are alternative measurement choices. The endpoint method assumes the endpoints reasonably represent what happened between them. A snapshot average describes the observations you selected. A time-weighted average describes the duration covered by each balance, under an explicit assumption about when balances change.
Use inventory value at cost for financial comparisons. Use units for an operational question about a particular SKU. Adding quantities of unlike products can obscure the answer even when the arithmetic is correct. The existing inventory turnover calculation explains how average inventory becomes a denominator; this page focuses on measuring that denominator consistently.
Valuation belongs in the definition. The IRS inventory guidance discusses identifying and valuing inventory and calls for consistent inventory practices from year to year for tax reporting. Your management average should likewise avoid combining a retail-price balance with a cost balance. A change in valuation can move the result without changing stock quantities.
Export the snapshot date and time, SKU, site, on-hand quantity, inventory value, currency, and stock status. Record whether the extract includes consigned goods, goods owned in transit, damaged stock, or quantities held for another business. The report should say what it includes before anyone compares sites.
Keep one row per SKU, site, status, and snapshot. If a location export has several bins, sum them to the chosen site grain before calculating the average. Do not let an extra bin row become an extra observation of time.
Sort each key by date. Check that dates increase, each balance has the same accounting basis, and the observation window has both a start and an end. Align sites to a common calendar before combining them. A site observed only during its busy weeks is not comparable with a site observed throughout the period.
A US distributor measures inventory value from the start of October 1 to the start of October 29, 2026. The window covers 28 days. For this example, each observed balance remains in force until the next observation; October 29 is the boundary observation and has no duration inside the window.
| Observation date | Inventory balance (USD) | Days until next observation | Balance × days (USD-days) |
|---|---|---|---|
| October 1, 2026 | 120,000 | 7 | 840,000 |
| October 8, 2026 | 200,000 | 7 | 1,400,000 |
| October 15, 2026 | 160,000 | 14 | 2,240,000 |
| October 29, 2026 | 180,000 | 0 | 0 |
| Total | 28 | 4,480,000 |
The endpoint estimate is (120,000 + 180,000) / 2 = $150,000. The equal-weight average of the four observations is (120,000 + 200,000 + 160,000 + 180,000) / 4 = $165,000. The time-weighted result is 4,480,000 / 28 = $160,000.
The results differ because they assign different weights. The last observation receives a full vote in the snapshot average but represents no elapsed time in this window. The longest interval belongs to the $160,000 balance. Do not select whichever method produces the preferred turnover figure; select the method that describes the available evidence.
Put dates in A2:A5, balances in B2:B5, and the interval days in C2:C5. Enter this in C2 and fill through C4, then enter zero in C5:
=A3-A2
The endpoint calculation is:
=(B2+B5)/2
The snapshot calculation uses the arithmetic mean, which is what Microsoft documents for AVERAGE:
=AVERAGE(B2:B5)
The time-weighted calculation multiplies corresponding balances and durations before dividing by the total duration. That uses the operation described in Microsoft's SUMPRODUCT documentation:
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)
For the broader spreadsheet technique, see weighted averages in Excel. Covirage is not affiliated with Microsoft.
The duration check is 7 + 7 + 14 = 28 days, matching October 29 minus October 1. The reverse check is $160,000 × 28 = 4,480,000 USD-days, exactly the sum of the weighted rows. Keep full precision in formulas; round displayed currency only after calculating.
Repeat the calculation separately for each SKU and site. With shared dates and weights, adding those averages must equal averaging the portfolio's dated totals. If they disagree, look for a missing key, a duplicate balance, or a site using a different window.
Then consider what an average hides. A stable value can contain older unsold stock and newer fast-moving goods. Use inventory aging to examine that mix. When the purpose is translating inventory into elapsed selling time, the days inventory outstanding guide owns that next calculation.
Bring a dated inventory file with SKU, site, quantity, and value columns. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.
See Covirage for supply chain teams for the business context behind those comparisons.
Use the most frequent reliable balances available for the decision. Daily balances capture short peaks that month-end snapshots miss. Monthly observations can support a stable monthly comparison, but disclose their timing and avoid treating a month-end sample as a complete record of daily inventory.
No. Average inventory measures the quantity or value held across time. Weighted average inventory cost assigns a unit cost using quantities and purchase costs. You can average inventory values produced by a cost method, but the two calculations answer different questions.
Yes, when every warehouse uses the same reporting window, valuation basis, and ownership rules. Calculate each warehouse's time average and add them, or add its balances on each shared date before averaging. Missing dates at one warehouse must be resolved before the totals are comparable.
A missing snapshot does not mean inventory was zero. Recover the balance from movement records where possible. Otherwise document the estimation method and its affected dates. Carrying the last observation forward is an assumption, especially risky around large receipts or seasonal peaks.