Best Inventory Formulas and Ratios to Boost Efficiency in Stockrooms

Optimizing on-hand inventory at a point-of-use location requires knowledge of inventory principles and formulas, as well as the tools needed to gather and analyze data. Organizations with stockroom inventory and the distributors that supply them rely on several common inventory formulas to assess how well they are optimizing inventory.

Too often, stockroom managers overstock to ensure that they meet their mission – to have the right number of the right parts at the right time to keep operations moving smoothly. Without an efficient system like the eTurns TrackStock app and the right metrics to monitor and manage inventory levels, inventory can get out of control. Deciding what inventory to cut without a clear strategy can lead to critical shortages, affecting operations and even production.

In this article, we show you how to calculate and use the right inventory formulas to get you closer to your end goal: optimizing your quantity on hand so you have what you need, when you need it, and no more. 

What are the most relevant inventory formulas for stockrooms?

We've found that the most relevant inventory formulas for managing stockroom inventory are:  

• inventory turns (aka inventory turnover ratio, or ITR) 
• inventory usage (consumption) 
• inventory valuation (inventory investment) 

These key metrics are essential for optimizing inventory levels and are used to derive other key metrics, such as Cost of Goods Sold (COGS) formulas.  

How to calculate inventory turnover ratio

The Inventory Turnover Ratio (ITR), also known as Inventory Turns, is the number of times inventory is sold in a strictly defined time period – typically measured annually. Turnover directly relates your inventory level to the demand for that inventory. If your inventory is turning approximately once per month, your ratio is 12. Every industry would love for their products to turn once a month, or 12 times per year. However, a typical inventory turnover ratio is between 1 and 4 times per year, and a best-practice target for turnover in an MRO stockroom is between 2 and 3 turns per year.

The ITR formula is calculated as follows for "inventory sold": 

Where Average Inventory is your Beginning Inventory + Ending Inventory divided by two for the period chosen.

Because inventory turnover measures how many times in a given period a stockroom is expected to replace the inventories that it has consumed, it can also be calculated with usage data, as follows: 

The ITR formula is calculated as follows for "inventory used": 

Why is inventory turnover important?

You want to ensure you're turning items quickly to both meet customer demand and avoid the expense of expiring inventory or increased carrying costs. But simply knowing your ITR won't accomplish that goal. To improve that ratio and optimize your on-hand inventory, you'll need to know your pulled consumption value and act on that data.

A replenishment app like eTurns TrackStock provides daily usage data, allowing users to collect pulled consumption data that includes not only quantity but also the dollar value of the inventory (valuation), which is used as the numerator in the inventory turnover formula. Typically, a stockroom manager measures their inventory turnover by the annual inventory consumed. 

Average Inventory on hand can be measured in three ways, depending on what the organization keeps track of:

• Average of mins and maxes – used when an organization does NOT track consumption, receives, credits, and returns.
• Actual on-hand quantities – determined by keeping track of consumption, receives, credits and returns.
• Actual inventory value – determined by multiplying the actual on-hand quantity times the cost.

Usage can be measured in three ways, depending on what the organization uses for inventory transactions:

• Orders or simply the quantity ordered. This transaction type provides the least accurate measurement of Inventory Turns or Usage, as it occurs infrequently and is therefore lumpy.  Since it is lumpy, an order may not occur during the time period the user wants to measure Inventory Turns, resulting in "0" for Turns.
• Pull Quantity, or how much is used/consumed over a period of time. This transaction type provides good information on what is "used" during a time period because consumption is a frequent transaction type. If it doesn't happen often, Inventory Turns will be very low, signaling a need to reduce on-hand quantity.
• Pull Value, or the quantity used times the cost of the item, over a period of time.

To better understand these concepts, here are examples of how each type of usage measurement affects your ability to manage your inventory effectively and increase inventory turns.

Orders as the measurement of usage:  

• Over a 60-day period, an item was ordered once in a quantity of 100. The item's Min and Max were 50 and 100.  The annual Inventory Turns calculation for this item is: (100 / ((50+100) / 2)) / 6 OR (100 / 75) / 6 or 1.33 / 6, which gives you an ITR of 0.22 Turns per year.
• The problem is that the calculation of this ratio provides no guidance on what the Min or Max should be to either 1) optimize inventory or 2) maximize service levels.
• Also, the ratio does not provide an action plan. Simply knowing the ITR and saying, "Let's increase Turns" does not give managers a path to increase their ITR. The user can reduce the Min and/or Max to reduce "on-hand quantity," but that could create stockouts. The user could try to increase usage, which is always a good thing, but again there should be a consequent adjustment to Min and/or Max. There is still no guidance with this measurement.

Pull quantity and/or Pull value as measurement of usage:

• Over the same 60-day period, 40 units were used. The Average Inventory On Hand was 50. The annual Inventory Turns calculation for this item is: 40/50/6, resulting in an ITR of 0.13. This ratio remains abysmal, and it does not provide guidance on how to increase Turns.

How to Improve Stock Turnover

Drive higher annual inventory turns by focusing on inventory optimization with the eTurns Min/Max Tuning Dashboard, which proactively calculates and suggests the optimal minimum and maximum levels for each item based on usage. The three primary objectives and benefits for organizations using eTurns Mix/Max Tuning include:

• Optimize inventory by reducing on-hand quantities to the absolute lowest levels to free up cash, which can be used for other revenue-increasing efforts. Meanwhile, the annual carrying cost of the inventory, which runs between 25–55% of the inventory value, is also reduced by the same percentage as the inventory value, providing continual annual savings.
• Maximize service levels, meaning NO stockouts. In many cases, the cost of stocking out of an item far exceeds the item's total inventory value because work stoppages incur costs from many other halted processes. Knowing the average daily usage, along with the current on-hand, provides all the data necessary to eliminate stockouts.
• Increase annual Inventory Turns, meaning the minimum inventory level needed to satisfy all demand over a given period of time, including the resupply time of the supplier, is to deliver orders consistently. Reducing the on-hand quantity to its lowest level is the divisor in the equation, allowing the highest Inventory Turns result.

So, the question remains: How does an organization accomplish all three of these objectives? By using the eTurns TrackStock app with Min/Max Tuning as follows:

1. Determine the optimum levels for an item's Minimum and Maximum through the following features in eTurns.

• Time period: Choose the number of days of history to use.
• Measurement Method: Choose Orders, Pulls, and Pull Value as the transaction type to calculate "Usage."
• Average usage: Assuming the user will use Pull Value as the Measurement Method and 90 as the number of days in the most recent history, add up the value of all Pulls performed over the last 90 days for each item. Then divide that total value by 90 to get the average daily Pull Value usage for all items.
• Calculated Minimum: For the bulk of your items, if an order were placed today, how many days before would it arrive? Let's assume that number of days is 2.  Then, if each item had 4 days of Average Daily Usage on-hand before a new order were placed, it is highly probable that none of your items will stock out. Therefore, the "Calculated Minimums" are 4 times each item's "Average Daily Usage."
• Calculated Maximum: For the bulk of your items, how much more than the Minimum do you want the Maximum to be? Twice as much? 1.5 times? Let's assume it is twice the Minimum. That means you want to stock up to the level of 8 days of Average Daily Usage as the Maximum.
• Annual Inventory Carrying Costs: Typically, the cost of keeping, storing, protecting, and replacing inventory lost to theft, along with financing and related costs, is 25% to 55% of the total inventory value. To whatever percentage the total inventory value can be reduced, that same percentage can reduce the annual cost of carrying inventory.

2. Optimize inventory and maximize service levels with these capabilities in eTurns: 

• Calculate average inventory value. When the optimized Minimum and Maximum inventory levels are calculated for each item, we can compute the average inventory level as (Min + Max)/2. The theory is that the inventory system should aim to keep the on-hand quantity between Min and Max. Since no reordering happens until the Minimum is breached, this calculation is a conservative estimate of "Optimized Inventory Levels."
• Determine whether on-hand quantities are on target. Calculating the difference between Optimized Inventory levels and actual on-hand quantities will determine whether the current on-hand quantities are too high, too low, or close to correct. The total of the differences is the amount of money that can be saved, or that is needed to increase on-hand quantities. Typically, organizations' Mins and Maxes are set too high, and determining the correct target values can yield significant savings by removing excess inventory and reducing carrying costs.
• Improve Min/Max targets to continually optimize inventory. Using eTurns Min/Max Tuning allows for continual inventory optimization. Typically, an organization's current Mins and Maxes are significantly different than the calculated/optimized Mins and Maxes. Gradually adjusting these closer to the optimized values is needed. eTurns TrackStock provides a "slider" for both the Minimum and Maximum to create "trial" values, allowing the user to move closer to the calculated Mins and Maxes to assess the amount of savings, the impact of the change on the current Mins and Maxes, and any other impacts.

Once the user has adjusted the Mins and Maxes to levels that are very close to the calculated Mins and Maxes, the user can set any number of items to "automatic." This setting will automatically adjust the Min and Max each day, based on any additional Pulls or just the passage of another day. Both of these influence the "average daily usage" calculation and, therefore, result in the optimized Min and Max for each item.

The eTurns TrackStock app, based on usage data, has a powerful dashboard and analytics to optimize stockroom inventory. With Min/Max Tuning, TrackStock provides organizations with the data and tools needed to determine key inventory statistics and, more importantly, the means to continually determine optimal item levels for effective inventory optimization. 

The purpose of these formulas is to achieve your goal of optimizing on-hand quantity, while maximizing service and eliminating stockouts. With eTurns TrackStock, this goal is easily achievable. 

Stop relying on spreadsheets and manual calculations to manage inventory performance. Start tracking inventory metrics, optimizing stock levels, and reducing carrying costs with eTurns TrackStock.

Try it free for 30 days, including setup assistance and expert phone and email support.