A company's inventory holding costs are often misunderstood. Many believe these costs are only defined by the company's cost of financing. However, there is far more to inventory costs than just your company's cost of capital.
There are several cost drivers, and they all influence how much it costs to hold inventory and how much it costs not to have inventory. As such, I decided to put together two sample Excel sheets calculating holding costs within your warehouse.
Understanding Your Company's Yearly Inventory Holding Costs
Yearly inventory holding costs differ from one company to the next and can be anywhere from 25% to 40% of the inventory value on hand. Because this range is so broad, a company must track its yearly costs for a more accurate estimate.
For the most part, companies typically fall between these two ranges, with most enterprises assuming their inventory holding costs to be approximately 30% to 35% a year. As such, companies often apply a standard 3% monthly holding cost. However, your company's costs may be higher or lower, so tracking your costs is a good idea. This is precisely what the first sample sheet allows you to do.
The first sample Excel sheet is focused entirely on defining your company's specific inventory holding costs. While most companies stick with the 3% above, it's a good idea to go through the exercise of itemizing your company's specific costs.
You'll do this by itemizing 1) your company's cost of money and financing (cost of capital), 2) costs of ruined inventory, 3) costs of electricity, 4) costs of lost customers due to low inventory counts, 4) costs of freight on incoming shipments of materials and outgoing shipments of finished goods 5) costs of warehouse over time, 6) costs of damaged inventory and 7), costs of deadstock, obsolete inventory, and pilferage (theft).
First Excel Sheet Summarizing Your Holding Costs
This first Excel sheet takes all those cost drivers and has you enter specific values for your company. Each blue highlighted section requires you to input your confirmed values or, if needed, an estimate if you are unsure of your current costs. However, tracking these monthly costs from this day forward is essential. You can then use the sheet at the year's end to summarize your company's performance.
First, input your average monthly inventory value in the highlighted blue area. In this case, it's $750,000.00. The second blue highlighted area is where you input your company's cost of capital. In this case, our company is paying 5% to finance its inventory.
The rest of the sections are where you input your total dollar values for each category. This sample sheet also includes two pie charts (not shown). One is for summarizing your holding costs by percentages, and the other is for your holding costs by expenditures ($$$).
Here is the first sheet: Download Sample Excel Sheet Calculating Inventory Holding Costs
Second Excel Sheet Defining Holding Costs on Finished Goods
The second sample sheet forces you to input your company's specific inventory holding costs, as we did above. This second sheet calculates inventory holding costs on your company's finished goods inventory by multiplying your holding costs against the product line's cost of goods sold or COGS.
For example, Funnel Tubes (the second product line in the table below) has COGS of $21,000.00. If the company held that inventory for a month, its holding costs would be $630.00 ($21,000.00 multiplied by 3%) or $21.00 a day (based on 30 days a month). However, the product is held for 12 days before it's sold, so the company's holding costs are only $252.00.
Example Summarized:
- Funnel Tubes COGS: $21,000.00
- Holding Costs: 3%
- Monthly Holding Costs: $21,000.00 x 3% = $630.00
- Daily Holding Costs: $630.00 divided by 30 days = $21.00
- Total Holding Costs: 12 days x $21.00 = $252.
Once you input your company's specific monthly holding costs, the second Excel sheet will perform this calculation for you. Therefore, you must input the following variables inside the blue highlighted areas:
- Your company's specific monthly holding costs (again, from above).
- Your cost of goods sold (COGS) on finished goods.
- The average number of days inventory is held on these finished goods before they're sold.
Here is the second sheet: Download Sample Excel Sheet Calculating Inventory Holding Costs On Finished Goods
The above video explains the two costs of inventory - holding costs and lost sales. It is from The Bell Curve of Inventory Management: Finding the Middle Ground.
When using this sample Excel sheet to calculate inventory holding costs, track the average number of days your finished goods are stored before being sold. In our example, we've incorporated the cost of goods sold (COGS) for each product line. However, this sheet could be used to analyze raw material holding inventory costs.
This should provide insight into the importance of quick inventory turns, as the longer that inventory is held, the more likely it is to become damaged, obsolete, or stolen. However, tracking your lost sales cost of inventory is equally important. Not having inventory and encountering a stock out has huge costs, defined by lost sales, lost customers, and eventually, lost market share.
To read about holding costs in Just In Time inventories, please refer to What Are the Costs to Hold Inventory in Just In Time - JIT?
To read about holding costs in Min-Max inventories, please refer to What are the Costs of Holding Inventory in Min-Max Supply Chains?
Or, if you manufacture custom-made parts and want to run a version of Dell's Push-Pull, please read: What are the Costs of Holding Inventory in Dell's Push-Pull?
Comments