How to Create an Inventory Cost Carrying Calculator in Excel – 3 Steps

This is an overview of the final output.

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.

Inventory Carrying Cost Formula

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

• 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 service to customers, such as maintaining stock 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)

• Average Inventory is the average amount of inventory held over a 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: Easy Steps

Create an Inventory dataset: “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 different stores.

Step 1 – Calculating Unit Holding Cost

To calculate the total holding cost for a single merchandise unit, add the unit values of the four component prices associated with the holding cost.

• Go to G5 and enter the following:

• Press ENTER.
• Place the cursor at the bottom right corner of G5, left-click it, and drag the cursor down to G14.

Unit Holding Cost is Calculated.

Step 2 – Calculating the Inventory Carrying Cost

To calculate the Inventory Carrying Cost in dollars, multiply the unit holding cost by the average inventory level and add the ordering cost.

• Go to J5 and enter the following formula:

• Press ENTER.
• Place the cursor at the bottom right corner of J5, left-click it and drag the cursor down to J14.

The  Inventory Carrying Cost is Calculated.

Step 3 – Calculating % Inventory Carrying Cost

To calculate the inventory carrying cost as a percentage of the total inventory value, divide the former by the latter and multiply the result by 100.

Go to L5 and enter the following formula:

• Press ENTER.
• Place the cursor at the bottom right corner of L5, left-click it, and drag the cursor down to L14.

The % Inventory Carrying Cost is Calculated.

Download Practice Workbook

Download the worksheet.

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