Retail Dogma

Average Inventory

Average inventory is usually used during the calculations of different inventory metrics that are used to assess the efficiency of inventory management practices at a company.

Average Inventory Calculation

What is Average Inventory?

Average Inventory is the average value of inventory that a company holds over a certain period of time. This period could be a month, a quarter, a year,..etc.

How to Calculate Average Inventory in Retail or Ecommerce?

There are two ways to calculate Average Inventory for a retail or ecommerce business. We will explain both ways.

Let’s say a company wants to calculate its AI for the full year.

1. First Method

AI = (Beginning Inventory ($) + Ending Inventory ($)) ÷ 2

In the simplest form the company will just take the amount of inventory (at cost value) at the beginning of the year and add it to the amount of inventory (at cost value) at the end of the year and then divide the result by 2 to get the average of the two numbers.

2. Second Method

AI = (Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) ÷ 12

In the second way the company will add the inventory levels (at cost value) at the end of each month of the year and then divide the sum value by 12.

If it wants to calculate AI for a quarter (3 months), it will add 3 months and divide by 3,.. and so on.

Some might add to that the current inventory as a data point and divide the sum value by 13 instead of 12. Both methods are correct and will give similar results if the data points are large enough. The main thing is to divide by the number of data points you have used to get the correct average value.

This method will be more accurate for businesses that are seasonal and have wide fluctuations in inventory levels throughout the year. This is because this method takes into account inventory levels at each month of the year, and not only beginning & ending.

Average Inventory in Excel

If you are maintaining an Open to Buy excel sheet for your buying, you will simply select the closing stocks (at cost) for the months in question and the average will be shown in the status bar at the bottom of the sheet.

When To Use Average Inventory?

AI is used in calculating the following retail KPIs:

GMROI = Gross Profit ($) ÷ Average Inventory at Cost($)

Inventory Turnover = COGS ÷ Average Inventory at Cost

Days Sales in Inventory = (Average Inventory at Cost ÷ COGS ) x 365

All of these KPIs are important in evaluating how the retail or ecommerce business is doing in terms of managing their stock levels and buying the right amount of stocks in the first place.

Read Also: Open to Buy – How Much Inventory Do You Need?

Practice Questions

A company has delivered the sales of 125,000 $ at a gross margin of 55% during the period from Jan 2019 to June 2019 (6 months)

Its inventory level at the end of each month was as follows:

MonthInventory at Cost Value
Jan 201937,150 $
Feb 201925,050 $
March 201943,460 $
April 201932,000 $
May 201923,543 $
June 201933,948 $

1. Calculate the Gross Margin Return on Inventory (GMROI)

GMROI = Gross Profit ÷ Average Inventory

Gross Profit ($) = 125,000 $ x 0.55 = 68750 $

Average Inventory = (37150 $ + 25050 $ + 43460 $ + 32000 $ + 23543 $ + 33948 $ ) ÷ 6 = 32,525 $

GMROI = 68,750 $ ÷ 32,525 $ = 2.11 $

GMROI is 2.11 $. This means that for every 1 $ the company invests in inventory, it gets 2.11 $ return.

2. Calculate Inventory Turnover

Inventory Turnover = COGS ÷ Average Inventory

COGS = 125,000 $ x (1-0.55) = 125,000 $ x 0.45 = 56,250 $

IT = 56,250 $ ÷ 32,525 $ = 1.73

Inventory Turnover = 1.73. This means that the company turned its inventory 1.7 times during the 6 months in question.

Read our free guide on Inventory Management