How to Create an Inventory Cost Carrying Calculator in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, you are going to learn to create an Excel inventory cost-carrying calculator. The overall costs incurred by a small firm to retain and store unsold goods are referred to as inventory carrying cost, also known as holding cost or having cost. This covers direct expenses like leasing a warehouse and paying employees’ salaries and indirect expenses like depreciation and shrinkage. Reducing inventory carrying costs makes sure you don’t have to spend a lot of money or take any risks before a consumer buys these things. Using inventory forecasting as a starting point, you may make more smart purchases and use that information to bargain with suppliers for cheaper pricing, which will lower the overall value of your inventory. So, before trying to reduce the inventory carrying cost, we need to calculate it.

Before starting the article, let’s have an overview of the final output you are going to get.

Overview to Calculating Inventory Carrying Cost


Inventory Carrying Cost Formula

  • Inventory Carrying Cost = (Average Inventory x Holding Cost) + (Ordering Cost)
  • Holding Cost = Capital costs + Service costs + Risk costs + Space costs
  • Inventory Carrying Cost (% of Total Inventory Value) = (Inventory Carrying Cost/Total Inventory Value) × 100

We learned the definition of Inventory Carrying Cost at the beginning of this article. Now, Let’s have a look at the definitions of the newly introduced terms.

Storage cost, risk cost, service cost, and capital cost are all components of the holding cost used in the formula for inventory carrying cost.

  • Storage cost refers to the cost of physically storing the inventory, such as warehouse rent, utilities, and maintenance.
  • Risk cost refers to the costs associated with the potential loss or damage of the inventory, such as insurance and security.
  • Service cost refers to the costs associated with providing a certain level of service to customers, such as maintaining a certain level of stock on hand to meet customer demand.
  • Capital cost refers to the opportunity cost of tying up capital in inventory, such as the cost of lost investment opportunities if the funds used to purchase the inventory were instead invested in other assets.
  • Total inventory value refers to the total monetary value of a company’s inventory. This value is determined by multiplying the number of units of each item in inventory by its unit price.

The formula for inventory carrying cost is:

Carrying Cost = (Average Inventory x Holding Cost) + (Ordering Cost)

Where:

  • Average Inventory is the average amount of inventory held over a certain period of time (e.g., annually)
  • Holding Cost is the cost of storing and maintaining the inventory, such as warehouse rent, insurance, and taxes
  • Ordering Cost is the cost of placing and receiving orders, such as purchasing and transportation costs.

How to Create an Inventory Carrying Cost Calculator in Excel: with Easy Steps

We will create an Inventory dataset named Inventory Data of Various Stores in Different States of US during One Month. Here, unit Captial Costs, unit Service Costs, unit Risk Costs, and unit Space Costs are given along with Average Inventory quantities, Ordering Costs, and Total Inventory Values of various stores located in various states of the US.

Collected Raw Data to Calculate Inventory Carrying Cost

Now, let us look at the step-by-step solution to the calculation of inventory carrying cost and inventory carrying cost as % of total inventory value.


Step 1: Calculating Unit Holding Cost

To calculate the total holding cost for a single merchandise unit, we need to add the unit values of the four component prices associated with the holding cost. To do that, go to the cell G5 and Type the following:

=C5+D5+E5+F5

Unit Holding Cost Calculation Formula

Press ENTER. After that, place the cursor at the bottom right corner of cell G5, left-click on it, and drag the cursor down to cell G14. You will get the calculated result for the rest of the states too. Unit Holding Cost is thus Calculated.Unit Holding Cost Calculation Result


Step 2: Calculating Inventory Carrying Cost

To calculate the Inventory Carrying Cost in dollar form, we need to multiply the unit holding cost and the average inventory level together and add the ordering cost with the multiplication result. To do so in Excel, go to cell J5 and type the following formula:

=(G5*H5)+I5

Inventory Carrying Cost Calculation Formula

Press ENTER. After that, place the cursor at the bottom right corner of cell J5, left-click on it and drag the cursor down to cell J14. You will get the calculated result for the rest of the states too. Inventory Carrying Cost is thus Calculated.

Inventory Carrying Cost Calculation Result


Step 3: Calculating % Inventory Carrying Cost

Finally, to calculate the inventory carrying cost as a percentage of the total inventory value, we need to divide the former with the latter one and then multiply the result by 100. So in Excel, go to cell L5 and type the following formula:

=(J5/K5)*100

% Inventory Carrying Cost Formula

Press ENTER. After that, place the cursor at the bottom right corner of cell L5, left-click on it, and drag the cursor down to cell L14. You will get the calculated result for the rest of the states too. % Inventory Carrying Cost is thus Calculated.

% Inventory Carrying Cost Result


Things to Remember

  • It is important to note that the four component costs of the unit holding cost are not fixed and may vary depending on the specific situation and the type of inventory.
  • Another point to note is that the formula is an approximation and the actual carrying cost may vary depending on the specific situation for different stores.

Download Practice Workbook

You can download and practice the dataset we used to prepare this article.


Conclusion

Now, you know how to create an Inventory Cost Carrying Calculator in Excel. Use the procedure whenever you need and let us know if you have any better or additional way to carry out the task. You can drop any comment in the comment section below if you have any queries.


<< Go Back to Excel Inventory Management Templates | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo