Average Inventory Value

Excel Formulas › Retail & Inventory

All versionsAVERAGE

Average inventory smooths stock levels over a period so turnover and DIO are meaningful. Average the beginning and ending balances, or better, the monthly snapshots across the period.

Quick formula: average of beginning and ending inventory:
=(beginning_inventory + ending_inventory) / 2
The mean of start and end balances, or AVERAGE of monthly snapshots, is your average inventory.

Functions used (tap for the full reference guide):

AVERAGE

The example

$70k start, $90k end.

AB
1ItemValue
2Beginning70000
3Ending90000 → $80,000

The formula

The formula:

=(B2 + B3) / 2 // mean of start & end

How it works

How it works:

  1. The quick version: (beginning + ending) / 2 — fine when stock changes steadily.
  2. A better version averages the monthly snapshots: =AVERAGE(Jan:Dec balances).
  3. Average inventory feeds turnover (COGS ÷ avg inventory) and DIO.
  4. Keep it at cost to match COGS-based metrics; at retail to match sales-based ones.

Two-point vs monthly average: the start/end average is easy but misses mid-period swings — a holiday build-up and clearance can both vanish from a January-to-December snapshot. Averaging 12 month-end balances captures the real carrying level and gives a far more honest turnover figure for seasonal businesses.

Try it: interactive demo

Live demo

Beginning and ending inventory.

Average inventory:

Variations

Monthly average

All snapshots:

=AVERAGE(monthly_balances)

Feed turnover

Next step:

=COGS / average_inventory

At retail

Sales basis:

=(begin_retail + end_retail) / 2

Pitfalls & errors

Two points can mislead. Average the monthly snapshots for seasonal stock.

Cost or retail, not both. Match the basis to the metric that uses it.

Same period. Average over the window your COGS or sales covers.

Practice workbook

📊
Download the free Average Inventory Value practice workbook
An average-inventory sheet with the monthly, turnover-feed, and retail variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate average inventory in Excel?
For two points use =(beginning_inventory + ending_inventory) / 2; for accuracy, =AVERAGE(monthly_balances) across the period.
Why does average inventory matter?
It feeds turnover (COGS / average inventory) and days inventory outstanding, so the period's stock level is represented fairly.
Should I use cost or retail value?
Match the metric: cost to pair with COGS-based turnover, retail to pair with sales-based measures.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Inventory turnover · Running average · Days inventory outstanding

Function references: AVERAGE