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 the costs of inventory than just your company's cost of capital. There are a number of cost drivers and they all play a role in 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 are different 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 wide, it's essential that a company track its yearly costs in order to get a more accurate estimate. However, 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 it is a good idea to track your own costs. This is exactly 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 aforementioned 3%, 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 overtime, 6) costs of damaged inventory and 7),costs of dead stock, obsolete inventory and pilferage (theft).
First Excel Sheet Summarizing Your Holding Costs
This first excel sheet takes all those aforementioned cost drivers and has you enter in specific values for your company. Each blue highlighted section requires you input your confirmed values or, if needed, an estimate if you are unsure of your current costs. However, it's important to track these costs on a monthly basis from this day forward. You can then use the sheet at the end of a given year in order to summarize your company's performance.
First, input your average monthly inventory value on hand in the first 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 is 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 inventory of finished goods by multiplying your holding costs against the product line's cost of good sold or COGS. For example, Funnel Tubes (second product line in table below) has COGS of $21,000.00. If the company held that inventory for a full month, their holding costs would be $630.00 ($21,000.00 multiplied by 3%) or $21.00 a day (based on 30 days in a month). However, the product is held for 12 days before it's sold, so the company's holding costs are only $252.00.
- 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.
The second excel sheet will perform this calculation for you - once you input your company's specific monthly holding costs. 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 taken from the post: The Bell Curve of Inventory Management: Finding the Middle Ground
When using this sample excel sheet calculating inventory holding costs, be sure to track the average number of days your finished goods are held before being sold. In our example, we've incorporated the cost of goods sold (COGS) for each product line. However, this same sheet could be used to analyze raw material holding costs of inventory. This should provide you with some insight into the importance of having quick inventory turns, as the longer that inventory is held, the more likely it is to become damaged, obsolete or stolen. However, it's equally important to track your lost sales cost of inventory as not having inventory, and encountering a stock out, has huge costs, ones that are 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, then please read: What are the Costs of Holding Inventory in Dell’s Push Pull?