A number of my customers have asked that I come up with a sample inventory costing excel sheet, one that shows a company’s yearly inventory holding costs. These holding charges refer to a company's costs to support, hold and maintain inventory over time. A number of companies are shocked to hear just how much these inventory costs are, and ultimately, what they include. Companies simply fail to realize that these costs include far more than just the costs of holding parts, raw materials and finished goods.
Inventory Holding Costs
When looking at a company’s holding costs, think of the company’s daily cost of money, its costs of electricity, warehousing and rent, its freight costs, the overtime it pays employees to stay late shipping and receiving, and ultimately, the costs associated with inventory damage, obsolescence and theft. When companies look at these aforementioned costs, they gain a greater appreciation of what goes into their monthly carrying charges. However, to properly ascertain what these costs are means to track them during a given period.
If your company relies upon manual inventory processes, don’t worry. This sample inventory costing excel sheet provides a summary of these cost drivers and allows you to depict them in a graph and pie-chart of expenditures and percentages. The entire sheet allows you to input your own variables in order to determine your specific costs. Once you’ve done that, you can then adopt strategic plans to reduce their impact.
The video above explains the bell curve of inventory management - a situation where a company's inventory is constantly behind its market's demand: The Customer Demand & Inventory Gap: The Bell Curve
Identifying the Causes of Holding Costs
The table below shows these aforementioned cost drivers of inventory. For instance, does your company track its daily cost of money and its impact on inventory? Are you aware that your company’s inventory costs should account for lost sales and lost customers when inventory isn’t available? Surprised to hear that lost sales can be a direct cost of inventory when confronted with a stock out? Well, it is. In fact, any cost that can be traced back to inventory is a cost of managing that inventory. Damaged, obsolete product and theft are also direct costs, as is the overtime paid to ship and receive parts and raw materials. The sample sheet allows you to input your variables within the highlighted blue sections.
First, you input your total inventory value on hand. It's best to use a monthly average. Next, you fill out your company's cost of capital (cost of money) which is simply the interest rate you must cover on financing. Afterwards, you fill in the remaining blue highlighted areas by providing dollar value figures for ruined inventory, costs of electricity, costs of lost business (customers) due to inventory stock outs, costs of freight, costs of overtime, costs of inventory damage and costs pertaining to inventory obsolescence and theft. The excel sheet will provide a total based on the information you've inputed into these sections.
Expressing Inventory Holding Costs as Expenditures ($$$) and Percentages (%) in Pie-Charts
Our example is based on a company that has an average monthly inventory value on hand of approximately $1 million. There are two pie-charts that express the holding costs in dollar values ($$$) and in percentages (%). In this particular example, the company has tracked its inventory costs and have determined that these costs are 35% yearly, or approximately 3% per month. This 3% holding costs per month is pretty standard.
To calculate your company's costs, take your average monthly inventory value on hand and then track these same cost drivers. At the end of a given period, summarize these costs and divide this total by your monthly inventory value on hand. In this case, it would be $350,000.00 divided by $1,000,000.00 which would be 0.35 or 35%. Remember, track the following costs.
- Cost of money
- Ruined inventory
- Electricity and Miscellaneous Costs
- Lost Customers and Lost Sales
- Freight Costs
- Overtime
- Inventory Damage
- Inventory Obsolescence and Theft
The video above explains the two main cost drivers of inventory; high holding costs (carrying charges) and lost sales cost of Inventory: The following post explains how to balance out these costs in order to meet the demand within your market: Small Business Inventory Asset Management: Using an Inventory Analyst
I’ve included this sample inventory costing excel sheet in order to allow you to track your own inventory holding costs during a given period. Again, most companies are shocked to see just how much it costs them to support their inventory. As such, many just assume that their inventory carrying charges are 3% a month. However, what if they’re actually higher? Isn’t it best to be aware of these costs? In our example, the company is able to itemize those costs that pertain to its inventory and provide an overview of where it needs to focus its efforts at cost reduction. Here is the excel sheet that you can use to input your own variables: Download Sample Inventory Costing Excel Sheet Graph & Pie-Chart of Expenditures
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?
Comments