How to Create an Inventory Cost Carrying Calculator in Excel

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.

Download Practice Workbook

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

Inventory Carrying Cost Formula

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.

3 Easy Steps to Create an Inventory Carrying Cost Calculator in Excel

We took a 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, unit Space Costs are given along with Average Inventory quantities, Ordering Costs, and Total Inventory Values of various stores located in various states of US.

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:

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.

Read More: How to Calculate Cost per Unit in Excel (With Easy Steps)

Similar Readings

• How to Calculate Price Per Pound in Excel (3 Easy Ways)
• Calculate Unit Price in Excel (3 Quick Methods)
• How to Calculate Coupon Rate in Excel (3 Ideal Examples)
• Calculate Retail Price in Excel (2 Suitable Ways)
• How to Calculate Weighted Average Price in Excel (3 Easy Ways)

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:

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.

Read More: How to Calculate Production Cost in Excel (3 Effective Ways)

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 one with the latter one and then multiply the result by 100. So in Excel, go to cell L5 and type the following 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.

Read More: How to Calculate Variable Cost Per Unit in Excel (with Quick Steps)

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.

Conclusion

Now, you have the ability 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. Do explore Exceldemy to get more articles like this.

Related Articles

• How to Create Opportunity Cost Calculator in Excel
• Use Cost Benefit Analysis Calculator in Excel
• How to Construct Cost Inflation Index Calculator in Excel
• Create Shipping Cost Calculator in Excel (with Easy Steps)
• How to Calculate Average Price in Excel (7 Useful Methods)
• Calculate Average Selling Price in Excel (3 Easy Methods)
• How to Calculate Price Per Square Meter in Excel (3 Handy Methods)