Sample Inventory Costing Excel Sheet: Graph & Pie-Chart of Expenditures

A number of my customers have asked that I come up with a sample inventory costing excel sheet that shows a company’s yearly inventory holding costs. A company’s holding costs refer to its costs to support, hold and maintain inventory over time. A number of companies are shocked to hear just how much these support costs are and ultimately, what they include. Companies simply fail to realize that these costs include far more than just the price for parts and materials. In order to truly understand a company’s inventory holding costs, one must take a wider look at these cost drivers and track them over time. Afterwards, the company can then adopt plans to reduce their impact.

When looking at a company’s holding costs, think of the company’s daily cost of money, its costs of electricity, warehousing & rent, its freight costs, the overtime it pays employees to stay late shipping and receiving and ultimately, the costs associated with inventory damage & obsolescence. When companies look at these aforementioned costs, they gain a much greater perspective of what goes into their monthly inventory support costs. 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 above and allows you to depict them in a graph & pie-chart of expenditures. Once you’ve done that, you can then adopt strategic plans to reduce the impact of these costs on your company’s bottom line.  

The table below is from the article: The Entrepreneur's Guide to Monthly Inventory Costs. It 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 & lost customers when inventory isn’t available? Surprised to hear that lost sales can be a direct cost of inventory? Well, it can. In fact, any cost that can be traced back to inventory is a cost of managing that inventory. Damaged & obsolete product is a direct cost, as is the overtime paid to ship and receive parts & raw materials. The table below is accompanied by a graph depicting the inventory holding costs as a % of the inventory value on hand.  

Summary of Inventory Holding Costs
Cost of Money: Let’s assume your yearly interest rate on loans and credit lines is approximately 4%. This yearly interest rate can be transferred to a daily interest rate. Since you use money to purchase inventory, every day you hold inventory is a direct cost to your company.	Cost of money	4%
Ruined Inventory: These are parts and materials that are damaged beyond repair. This is because of poor handling methods inside the warehouse. This happens frequently when inventory is not properly packaged and stored.	Ruined Inventory	5%
Electricity Costs: These are costs relating to lighting, and general electricity. It can also include the cost to keep the warehouse cool in the summer, and warm in the winter.	Electricity	5%
Lost Customers: It’s common to lose customers because you did not have the inventory available, or the inventory you delivered was damaged. Very few companies track lost customers because of poor inventory management practices.	Lost Customers	2%
High Freight Costs: These costs occur when you have to pay expedited freight to rush parts and materials in because your inventory was low, or the inventory you did have, was damaged. This is also includes those times when you have to rush product to your customers because you didn't maintain a proper inventory count.	High Freight Costs	7%
Overtime: This is when your company pays overtime for employees to either receive, or ship out products. Again, this happens when you don’t have the inventory available when it's needed and must therefore rush parts & materials into your warehouse.	Overtime	2%
Damaged inventory: The more inventory is handled, the more likely damage will occur. This often happens when you have to move slow moving inventory to a new location in your warehouse to make room for faster selling products.	Inventory Damage	5%
Dead stock or obsolete inventory: These are parts that can not be sold because there is no use for them anymore. They can be considered obsolete or outdated products. In this case, they can only be sold for scrap.	Inventory Obsolescence	5%
Total Yearly Holding Cost		35%

  


Expressing Holding Costs & Expenditures ($$$)

This entire example is based on a company that has a monthly inventory value on hand of approximately $1 million. This next table (below) provides a pie-chart and expresses the holding costs in a dollar value ($$$). In this particular example, the company has tracked its inventory costs and have determined that their holding costs are 35% yearly or approximately 3% per month (the 3% holding costs per month is pretty standard for most companies). To calculate your company's costs, take your average monthly inventory value on hand and then track these same cost drivers (listed below). At the end of a given period or quarter, 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 & miscellaneous costs
• Lost customers & Lost Sales
• Freight costs
• Overtime
• Inventory damage
• Inventory obsolescence 

Summary of Inventory Holding Costs: Based on $1 Million Dollars of Inventory	Amount in $	Percentages
Cost of money	$      40,000.00	4%
Ruined Inventory	$      50,000.00	5%
Electricity	$      50,000.00	5%
Lost Customers	$      20,000.00	2%
High Freight Costs	$      70,000.00	7%
Overtime	$      20,000.00	2%
Inventory Damage	$      50,000.00	5%
Inventory Obsolescence	$      50,000.00	5%
	$     350,000.00	35%

   

I’ve included this sample inventory costing excel sheet with a graph & pie-chart of expenditures 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 holding costs 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:Download ExcelSheetInventory

Comments

[Graphs]

I am very much impressed with the charts that has been drawn out. This is benevolent.