How you manage your inventory can make or break your business. Keep too much inventory on hand, and you risk having items sitting on the shelf that you can’t sell. But run out of product, and you’ll lose sales and damage your reputation. To help you find the perfect balance, you can use the average inventory formula.
This inventory management method allows you to track your average inventory level over a specific period of time. That way, you can figure out if you’re ordering way too much, or way too little when it comes time to get more stock. Here we'll show you how to calculate your average inventory and put it to work at your company.
What Is the Average Inventory Formula?
The average inventory formula uses your beginning and ending inventory levels to average out your inventory numbers. In a nutshell, you add those two numbers together and divide them by the number of periods or months you’re calculating for.
This process can help you:
- Track how much your inventory is worth
- Gauge how quickly it’s selling
- Set your company’s price points
- Figure out how much inventory to order next
- Decide on when to do sales or promotions
Who Should Use the Average Inventory Formula?
Companies of all shapes and sizes can use this formula. It can help you quickly gauge inventory levels. You can also plug it into other formulas to figure out how fast your inventory is turning over and how quickly it’s being sold, among other things.
How to Use the Average Inventory Formula
So you’ve decided this is the right metric for you–but how, exactly, do you find it? You can use the average inventory formula:
Average Inventory = (Beginning Inventory + Ending Inventory) / 2
Now before we dive into the actual math, it’s important to be working with the right numbers.
- Beginning inventory: The ending inventory of your previous time period. So if you’re calculating for this month, you’d want to start with where you ended last month.
- Ending inventory: It’s that beginning inventory you just found, plus any inventory you purchased, minus the cost of goods sold (COGS). The formula for it looks like this: Ending inventory = Beginning Inventory + Purchased Inventory - COGS
Calculating Average Inventory
Now that we’re on the same page with what each part of the equation means, let’s jump back into finding your average inventory.
You simply add up the value of your beginning inventory to the value of your ending inventory and then divide that number by two.
For example, let's say you have a small online store, and you start the month with $5,000 worth of inventory. Then, over the course of the month, you sell some items and buy a few more, so your ending inventory is $6,500.
To calculate your average inventory at the end of the month, you would do the following:
($5,000 + $6,500) / 2 = $5,750
So, your average monthly inventory was $5,750. If your company has a goal to keep $6,000 worth of product on hand so you don’t run out, you can see that you need to order a bit more. But if you are trying to reduce inventory costs to below $5,000, you can see that you have a ways to go still.
Pretty simple, right? So far, yes. But it can get a little more complicated. We’ll go into that next.
Calculating Average Inventory for a Quarter
This formula is pretty flexible. You can use it to average your company’s inventory for a month, a quarter, or any other time. All you have to do is change the number of data points in the formula and match that number to the one you divide by.
For example, let's say you want to calculate your average amount of inventory for the year’s first quarter. In that case, you'd be working with more than two data points like we were above.
Instead, you'd have four data points from that accounting period: your beginning inventory and an ending value for each of the three months in the quarter.
You'd simply add up all of your beginning and ending values and then divide that number by the number of data points you're working with, in this case, four.
So, your quarterly formula would look like this:
Average Inventory = (Beginning Inventory + Ending Inventory Month 1 + Ending Inventory Month 2 + Ending Inventory Month 3) / 4
As you can see, the math doesn't get harder. It just gets bigger. Let's plug some pretend numbers in so you can see what it looks like.
For our example, let's say your beginning inventory for the first quarter was $5,000. Then, at the end of January, you had an ending inventory of $6,000. At the end of February, you had an ending inventory of $7,000. And at the end of March, following a big spring sale, you had an inventory of $4,000.
To find your average inventory calculation for the first quarter, your math would look like this:
($5,000 + $6,000 + $7,000 + $4,000) / 4 = $5,500
And there you have it, your average value of inventory for the first quarter was $5,500.
Knowing that number can help you track your inventory goals. For instance, in your business, how much stock do you need on hand at any given time to avoid having to deal with a shortage of items? When you know your goal, the average can help you quickly keep tabs on your progress.
You can scale this calculation up or down, depending on the information you need. You can even find your balance for an entire year.
Pros and Cons of This Inventory Formula
Now that you know how to use this formula, it's time to take a step back and look at the bigger picture. This formula is helpful, but it's not perfect.
Here are a couple of the benefits.
- Flexibility: You can use this formula to calculate your average inventory for any given period. Whether you want to know your average monthly or quarterly inventory, this formula has you covered.
- Helpful for planning: If you know your average inventory, you can ensure you have the right number of units on hand to meet that demand.
- Needed for other numbers: As you’ll see below, you need this figure to find other ratios, like ones that tell you how often you sell your entire inventory.
However, this method of inventory calculation also has a few cons:
- It’s not complete: While simple to calculate, this formula may not give you the whole picture when it comes to your inventory. For instance, seasonal sales can have a big impact on your inventory levels since you'll have a large increase in inventory as you prepare for them, and after, you'll (hopefully) be nearly out of things to sell. Moreover, since you’re dealing with averages and not exact numbers, if you have a big difference in prices between your products, it will be hard to tell if one big ticket item or a bunch of smaller products are affecting your inventory.
- Can’t calculate potential changes: This formula tells you what happened in the past and can help you plan for the future. However, it can't predict upcoming trends or forecast changes in your inventory levels.
While this method has some drawbacks, it's still helpful for quickly calculating your average inventory figures. And if you're careful about its limitations, it can be a valuable part of your inventory management strategy.
Important Numbers You Can Calculate With this Formula
While the average inventory rate might not be your primary inventory valuation method on your financial statements, you’ll need this number to learn other important facts about your business, like:
Inventory Turnover Ratio
Without knowing your average inventory, you can’t calculate your inventory turnover ratio. This number tells you how often you sell all the products in your stock and start fresh with new items.
A high inventory turnover rate means you’re selling through your inventory quickly. As a result, you don’t have a lot of old, unsold products taking up space inside your company. It also means that your products are in demand and you’re doing a good job selling them.
If you have a low inventory turnover rate, it’s a sign that you’re not selling through your inventory quickly enough. With a low rate, you might end up stuck holding products that are no longer in demand and losing money.
To see how your company is doing, divide the cost of goods sold (COGS) by your average inventory. Here’s what the formula looks like:
Inventory Turnover Rate = Cost of Goods Sold / Average Inventory
Calculation
Let’s say your COGS for the year was $200,000, and your average inventory was $50,000. To calculate your inventory turnover rate, you’d do the following:
$200,000 / $50,000 = 4
This means your inventory turnover rate is four. That means you turn over your entire stock four times a year. On its own, this number doesn’t tell us much since so many factors are at play.
For example, does your company sell perishable goods that expire quickly? If you do, you’re going to have a higher turnover rate than a hardware store since nails and lumber have a much longer shelf life.
A good rule of thumb is that your rate should be between four and six. However, this truly depends on your industry!
Average Inventory Period
Once you know your inventory turnover rate, you can calculate your average inventory period. That tells you how long it takes to turn your inventory into those ever-important sales.
Here’s what that looks like:
Average Inventory Period = Number of Days in a Period / Inventory Turnover Rate
So if your inventory turnover rate is four and you want to find your annual number, you’d divide 365 by four. With these numbers, your average inventory period would be 91.25 days.
That means it’d take your company an average of 91.25 days to sell all the products in your inventory.
You can use this number to set goals for your company. For example, if you want to decrease the time it takes to turn over your inventory, you can plan to lower your average inventory period.
To meet your goal, you could work on restocking your shelves more quickly after a sale to keep inventory moving. You might also decide to run a sale on any specific products that aren’t selling as well.
Average Inventory Formula vs. Average Cost Method
It’s important to note that business owners use the average inventory formula for internal planning and benchmarking purposes—it’s not actually used to value inventory for financial statement purposes.
There’s an inventory valuation method that sounds similar to the average inventory formula: the average cost method, also known as the weighted average cost method. Here's a bit more about it:
Weighted Average Inventory Cost
The weighted average inventory cost (WAC) helps you quickly find the value of your stock on hand. Instead of having to add the value of each individual product together, you can save time by grouping your items together.
So, if you’re a t-shirt company, you might have several different types, sizes, and colors of t-shirts in stock. They all sell for relatively the same price. With the weighted method, instead of counting and maintaining an inventory for each individual variation, you’d group all of your t-shirts together.
Calculation
For the weighted average, you average all of the inventory you purchased during a period of time, and assign a value to your remaining inventory based on the average.
For example, say during the first quarter of the year, you purchased t-shirts for your t-shirt company as follows:
So during the quarter you purchased 75 t-shirts for a total cost of $366.25. By dividing the total cost ($366.25) by the total number of t-shirts purchased (75), you arrive at the weighted average cost per t-shirt of $4.88.
It’s a quick way to value your inventory to get a big picture view of things. This variation is perfect for eCommerce businesses that sell many items with similar value.
Moving Average Inventory Cost
If your company moves a lot of volume, the moving average inventory cost might be helpful to know. It helps you calculate a new average every time you restock your inventory. This can help you figure out the best pricing strategy to use, especially if you’re dealing with raw materials or other parts and products that change price frequently.
Calculation
To find this number, use this formula:
Moving Average Inventory Cost = Inventory Value + Cost of Inventory Order / Total Units on Hand
Say your initial inventory value of widgets is $1,395, and you purchase another $520 worth of them. After this order, you have 2,000 units on hand. Now it’s time to do some math.
Inventory value: $1,395
Cost of inventory order: $520
When you add these numbers together, you get $1,915. Then, you divide that by the 2,000 units you have on hand. With these numbers, your moving average is $0.958 per unit. In other words, the average moving cost for each unit in your inventory is just under $1.
The next time you order widgets, you'd rerun the numbers and, in that way, keep your moving average cost updated with each inventory purchase.
You can use these figures to help you with your pricing strategy. That way, you’re making a profit no matter how much you had to pay for the latest shipment of goods.
Let’s say that your latest shipment of widgets was much more expensive than normal. Instead of paying $520, you had to pay $835 for the same amount of widgets. And to keep the math simple, we’ll also say you reordered before selling a single widget since you were anticipating prices rising even more.
So when you add this latest purchase to your moving average calculation, your math looks like this:
Inventory value: $1,915
Cost of inventory order: $835
Total units on hand: 2,750
When you do the math, you see that the cost for each unit in your inventory has jumped from $0.958 to an even $1. At that price point, you may need to raise the price of your products by $0.05 each to ensure you continue to make a profit.
How Do You Calculate Average Inventory in EOQ?
The economic order quantity (EOQ) is a sweet spot in your inventory management, designed to help you find the balance between holding inventory and placing orders.
To find this number, you’ll need to know how much demand there is each year for your product. You’ll also need to know the cost of placing a single order and the cost of holding a single unit for one year.
Once you have those numbers, you can solve for your EOQ, which is your reorder quantity. The entire formula looks like this:
EOQ = Square Root of [(2 x (Cost of One Order x Demand) / Cost of Holding One Unit for a Year]
To get the average inventory from your EOQ, you divide your EOQ by 2. Why? Well, it goes back to a big assumption the math makes–essentially, the average amount of inventory you hold onto SHOULD be about half of your EOQ, or the amount you’re reordering.
For all you math whizzes out there, the formula looks like this:
Average Inventory = EOQ/2
So, if you’re already calculating your EOQ, you can use this formula to see how much inventory you should have on hand.
How Do You Calculate Average Inventory in Excel?
If you’re a spreadsheet person, you can easily calculate the average inventory in Excel. You need a spot to enter your beginning inventory and a cell to put your ending inventory. If you already have spreadsheets for your business, you might even have these numbers on your sheet already. (Also, you can use our template for a quick calculation. Just click "Make a copy.")
Once you have your beginning and ending numbers entered, you need to create a formula. It needs to be =((cell with beginning number) + (cell with ending number)) / 2
In Excel, here’s what that looks like, assuming you start with $7,520 and end with $9,152 worth of inventory.
.png)
When you hit enter, you’ll see the final calculation:
.png)
In this example, your average inventory is $8,336 worth of product on hand.
The Formula Works for Multiple Inventory Systems
As mentioned earlier, this formula is flexible. So whether you’re using perpetual or periodic inventory systems, this inventory metric can work.
With perpetual inventory, inventory management software updates your records in real-time. With a periodic inventory system, you only update your records at the end of each accounting period.
If you have a perpetual inventory system, you already have the data you need to calculate your average. First, you locate your beginning and ending inventory values for a specific period, and then you plug those numbers into the average inventory formula.
You don’t have a real-time inventory count of your items on hand with a periodic system. Instead, you’ll need to physically count the items on hand at the end of each accounting period. Then you can use those numbers to plug into the formula above to find your average.
Is This Formula Right for Your Business?
While this formula can help you calculate the inventory you have on hand, it doesn’t give you the full story. So, if you already have a different inventory management system in place, don’t feel like you need to suddenly switch.
However, adding this calculation to your current inventory practices could help you make better inventory decisions. And when you have the right amount of raw materials and products on hand, your business can continue to flourish!
Ready to transform your business into a profit-pumping machine? Learn how with our monthly newsletter.
