Inventory Formulas and Ratios 101: Using Formulas in Inventory Management
August 9, 2024
And after you get your inventory tracking in a perfect order – to the point where you know the exact count for each product, for each company location – then the next step in managing inventory is to apply Inventory Management Formulas and Ratios for analysis and optimization.
You may want to start with Average Inventory Formula, Safety Stock, Lead Time and Reorder Point, which, if properly applied, will help you avoid those pesky overstocks on one hand and, possibly, supply shortages on the other.
There are also some great formulas, which can be used to analyze whether a financial resource is used in the correct manner. The most popular indicators for that are Inventory Turnover Ratio and Inventory Days.
Some of the formulas, such as Cost of Goods Sold Formula (COGS), Economic Order Quantity (EOQ), Sell-Through rate, are central to the company's financials and evaluating effectiveness of the business as a whole.
You don't have to learn all the ratios and formulas known to humans, just pick a few that will be useful for you and will allow your inventory management to be more effective.
Let's take a look at some of the most used formulas. Firstly, Inventory Turnover Formula, or Turnover Ratio.
Inventory Turnover Formula
This formula is to be found almost everywhere. Inventory Turnover is an indication of how many times delivering inventory replaces previous inventory through a year. The formula itself is simple, and the numbers you can get from the business's accounting books. It is a key indicator of the efficiency of inventory handling and a control tool.
Formula:
Inventory Turnover Ratio = Cost of Goods Sold (COGS) / Average Inventory Value
Example:
If the total COGS for the year was $200,000 and the average inventory for the year was $50,000 then: Inventory Turnover Ratio = 200,000 / 50,000 = 4
Learn more about managing Inventory Turnover.
Average Inventory Formula
Average Inventory, sometimes referred to as Average Stock, is probably the first ratio every person who deals with an inventory and watches over the business process, as well as every financial analyst gets acquainted with. Average Inventory shows a total investment of the business's inventory in one entity. Average Inventory is used to calculate with other formulas, such as: Inventory Turnover Ratio, Inventory Days, Inventory Carrying Cost. Average inventory can be calculated like any average. The time period in the calculation of the Inventory Average can be a month, year, or another period, depending on what's being analyzed.
Formula:
Average Inventory = (Beginning Inventory + Ending Inventory) / 2
Example:
If my beginning inventory is $15,000 and ending inventory is $10,000: Average Inventory = (15,000 + 10,000) / 2 = 12,500
Average inventory in the period is $12,500.
Days in Inventory Formula
It is also called Days Sales of Inventory (DSI). Some companies refer to it as Inventory Days on Hand. All three names denote the same formula and the same efficiency indicator. DSI reflects the average number of days that a certain company needs to liquidate its whole or part of inventories. Just like Inventory Turnover Ratio and Formula, Inventory Days Ratio reflects liquidity of inventory and serves as an important indicator of efficiency. DSI is often used for comparing inventory operations in various branches or businesses. DSI is calculated for a specific period – most often for a quarter and a year.
There are two formulas to be used to calculate DSI, each offering a slightly different viewpoint on efficiency of your inventory. The first method suggests using average inventory over some period and COGS, and the other method looking at the ending inventory and annual sales. The Average Inventory method is more commonly used, so let's explore the formula for it.
Formula:
DSI = (Average Inventory / Cost of Goods Sold) × 365
A retail company has the following data:
Average Inventory: $50,000
Cost of Goods Sold (COGS): $300,000
DSI = (Average Inventory / COGS) x 365
DSI = ( 50,000 / 300,000 ) x 365
DSI = 60.83 days
This indicates that it will take the company approximately 61 days to sell its full stock.
Learn more about Inventory Days calculation.
More Inventory Formulas
Let's now turn to a couple of pages and spend a few minutes poring over some more useful Inventory Management formulas. You might want to use some of them for your work. Here we go.
Beginning and Ending Inventory
Two numbers that every business will have and be registered in their account for Inventory will be the Beginning Inventory and the Ending Inventory. Inventory records might show Beginning Inventory and Ending Inventory in monetary value and in number of items. In order to rebalance inventory management and accounting books, businesses have to reconcile between them by the end of every accounting period.
The beginning inventory, sometimes called opening inventory, is entered using the Inventory Audit data, but more frequently it is rolled over from the end of the period prior.
The formula for Ending Inventory tells how the Beginning Inventory changed through time period out of additions and deductions.
Formula:
Ending Inventory = Beginning Inventory + Purchases - COGS
Safety Stock
Safety stock is added on top of the normal inventory, as a ‘buffer' to lower the chance of a stockout occurring when supplier deliveries are late or when unexpected surges in demand occur. The more sensitive a business is to the consequences of a stockout, the more likely it is to plan for safety stock. For instance, in the case of a party supplies store, if it runs out of tableware once, then the event organizers may choose to go to another supplier and, if satisfied, may continue to purchase from the new supplier into the future. In this context, to avoid losing customers, the store needs to manage safety stock more carefully.
The first step in calculating safety stock is deciding which method you want to use.
When using the Fixed Safety Stock methodology, you simply need to add a certain amount of inventory to your usual stock level, and that's it – a ‘buffer' created! No complicated calculations are required.
The Time-Based Method. If you know how lead time and demand were changing in the past, during the previous year, you may use this method. It is a simple formula which gives you a good idea about the safety stock based on the history of your operations.
Time-Based Safety Stock Formula
Safety Stock = (Maximum Lead Time - Average Lead Time) × Average Product Demand
There are also two more advanced methods to calculate safety stock. The Heizer Render method and Greasley's method both take into consideration standard deviations in lead time and Z-factor or service level (desired service level) in order to come up with a more exact forecast. These methods are employed in enterprise-size inventory management.
Lead Time
Lead Time is the number of days passed between issuing of the purchase order and receipt of the goods. It is always wise to know how long it takes from each supplier to get your order delivered in case you have to schedule your stock order dates.
Lead Time is an internally used measure of your system in measuring how long it takes to get your product in your customer's hands. It is from the point your customer hits your checkout button to receiving it. This is important in setting expectations with your customer but also in controlling the speed with which your operations flow.
Formula:
Lead Time = Delivery Date - Order Date
Reorder Point
Reorder point is one of these metrics that you may want your inventory software to monitor and give you an email or text message to let you know if you reach it. It's the quantity level where you may need to place a new purchase order to your supplier. It's simple in its formula, but it requires thought and careful observations of your sales fluctuations and your supplier's behavior to come up with an appropriate Reorder Point.
Formula:
Reorder Point = Safety Stock + (Average Consumption × Lead Time)
Economic Order Quantity (EOQ)
This magic formula provides us with optimal quantity in order to minimize the inventory-related expenses: ordering cost and holding cost. EOQ helps to optimize ordering frequency and volume to benefit from cost savings and reduce inventory-carrying cost. Image via Shutterstock Having information about Ordering Cost, Holding Cost and Demand is the essential initial step for calculating EOQ.
Formula:
EOQ = √((2*Demand * Ordering Cost)/Holding Cost)
Demand (D): the annual demand for the product in units.
Fixed cost for ordering (S): cost incurred on each ordering, irrespective of the quantity ordered.
Holding Cost (H): the annual cost associated with holding or carrying one unit of inventory.
Inventory Carrying Cost
The total cost to a company of holding its inventory is known as the Inventory Carrying Cost or the Holding Cost. This is a useful figure, as on the balance sheet the capitalized excess of inventory above its original purchase cost is written off when it is sold. The carrying cost can be aggregated for each inventory item separately and calculated for the business as a whole. Expenses included in the carrying cost could be categorized as tangibles, such as the cost of storage, handling, transportation, insurance, employee payroll, taxes etc., as well as intangible, the lost opportunity to put the money to use elsewhere or depreciation on the warehouse facility. Inventory Carrying Cost is frequently quoted as a percentage of the inventory value: A typical range would be about 20% to 30%.
Formula:
Inventory Carrying Cost = (Average Inventory × Carrying Cost Percentage)
Conclusion
Using inventory formulas will give you a more complete understanding of your inventory management practice, will help you make informed decisions and set measurable goals. As the Inventory Formula Pro you can take your Inventory Management to the next level, hence increasing profitability of your business.
