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

A D V E R T I S E M E N T

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

average inventory in excel

 

If you are maintaining an Open to Buy excel template 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.

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.