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.
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.
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:
|Month||Inventory at Cost Value|
|Jan 2019||37,150 $|
|Feb 2019||25,050 $|
|March 2019||43,460 $|
|April 2019||32,000 $|
|May 2019||23,543 $|
|June 2019||33,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
Retailer & Founder of Retail Dogma, Inc.
Rasha has 14 years of retail & ecommerce experience. She has started an ecommerce business in 2008, and later worked at H&M, Bath & Body Works, Victoria’s Secret and Landmark Group. She’s lived in 4 different countries, speaks 3 different languages and holds a BSc in Pharmaceutical Sciences and an MBA in Strategic Management & Marketing.