Best Inventory Formulas and Ratios to Boost Efficiency in Stockrooms
Optimizing inventory on hand in a point-of-use location takes knowledge of inventory principles and formulas, as well as the tools needed to gather and analyze the data. Organizations with stockroom inventory, and the distributors that supply them, rely on several common inventory formulas to determine 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 on 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 the most important to help you optimize inventory levels and are used in deriving 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 level of inventory to the level of demand for that inventory. If your inventory is turning approximately once per month, your ratio is 12. Every industry would love to have their products 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-3 turns per year.
The ITR formula is calculated as follows for "inventory sold":
Inventory Turn Over Ratio
Cost of Goods Sold
Average Inventory on-Hand
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":
Inventory Turn Over Ratio
Usage (Pulled Consuption Value)
Average Inventory on-Hand
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 inventory on hand, you'll need to know your pulled consumption value and then be able to act on that data.
A replenishment app like eTurns TrackStock provides daily usage data, allowing users to collect pulled consumption data, which uses not only quantity, but the dollar value of the inventory (valuation) to use as the numerator in the inventory turnover formula. Typically, a stockroom manager measures their inventory turnover by the inventory consumed annually.
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 Inventory Turns or Usage measurement as it occurs infrequently and therefore is lumpy. Since it is lumpy, an order may not occur in the time period the user wants to measure Inventory Turns, thus creating "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, then Inventory Turns will be very low providing a signal to reduce on-hand quantity.
- Pull Value or the quantity used times the cost of the item over a period of time.
To understand these concepts better, here are some examples of how each type of usage measurement affects your ability to effectively manage your inventory 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, there was a quantity of 40 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 is still abysmal; plus the ratio 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, meaning reduce the on-hand quantities to the absolute lowest levels to remove 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 reduction in inventory value and provides a continual annual savings.
- Maximize service levels, meaning NO stockouts. In many cases, the cost of stocking out of an item far exceeds the total inventory value of the item because work stoppages accrue costs from many other halted processes. Knowing what the average daily usage is along with the current on-hand provides all the data necessary to eliminate stockouts.
- Increase annual Inventory Turns, meaning what is the minimum inventory level needed to satisfy all demand over a given period of time, including the resupply time of the supplier, 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 of most recent history, add up the value of all of the Pulls done 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 the order arrives? 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, replacing from theft, financing and so on is 25% to 55% of the value of the total inventory. 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 calculate the average value of what the inventory should be: (Min + Max)/2. The theory is that the inventory system should strive to keep the on-hand quantity somewhere between the 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 will determine if 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 too high and determining the correct target values can lead to a 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 for the user to move closer to the calculated Mins and Maxes to assess the amount of savings, the impact of the change to the current Mins and Maxes, and any other impact.
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 the data and tools needed to determine important inventory statistics but, more importantly, provide 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.
For more information on how eTurns TrackStock mobile inventory app with Min/Max Tuning can help you achieve inventory optimization in your stockrooms and lower carrying costs, reach out to us or try eTurns free for 30 days, including set up and great phone & email support.