How to Keep Track of Inventory in Excel (With Easy Steps)

If you want to know how to keep track of inventory in Excel, this article is for you. Here, we will walk you through some easy steps to do the task effortlessly. You will get to know the basics of inventory tracking, learn the steps to track inventories of different items and find answers to some frequently asked questions about inventory tracking. So without any delay, let’s dive in.

This is an overview image of inventory tracking.

Download Practice Workbook

This is a free sample of how to keep track of inventory in Excel. You can download it from here.

What Is Inventory Tracking?

Inventory tracking involves monitoring and managing a company’s inventory levels, movements, and status. The primary purpose of inventory tracking is to ensure that the right products are available at the right time to meet customer demand while minimizing costs associated with overstocking and stockouts. The common parameters of inventory tracking are,

• Opening Stock
• New Purchases
• Sales
• And, Hand-in-stocks.

These parameters will be explained subsequently. To effectively track inventory using these parameters, businesses can use a perpetual inventory system. This system involves recording and updating inventory levels to continue to ensure that businesses have accurate and up-to-date information about their inventory levels.

How to Keep Track of Inventory in Excel: With Easy Steps

In this section, I will explain how to keep track of inventory in Excel. I have divided the entire task into some easy steps for everyone’s convenience. This is the sample dataset.

There are multiple sheets (Item, Stock In, Stock Out) in this Excel file. I will extract the required values from respective sheets and track inventories in the “Tracker” sheet.

Step 1: Measure Opening Stock

Opening Stock refers to the quantity of a particular product a business has in stock at the beginning of an accounting period. It is the starting point for tracking inventory levels throughout the period.

• This is a list of the items and their opening stocks.

• Select the entire table.
• Then, give it a name. I am going to call it “Item”.

• Now, I will use the VLOOKUP function to get the opening stock in the “Tracker” sheet. The formula in D5 is,

• Use Fill Handle to AutoFill up to D12.

Here, the lookup_value is B5. Excel will look for B5 in the array “Item” and return the corresponding value from the 3rd column of the array. The FALSE argument corresponds to an EXACT Match.

Step 2: Calculate New Purchases

New purchase refers to the quantity of a particular product that a business acquires from suppliers during the accounting period.

• This is the sheet for New Purchases. I am going to call the table “New_Purchase”.

• Then, write down the following formula in E5 of the “Tracker” sheet.

• AutoFill up to E12.

The lookup_value is B5. Excel will look for B5 in the array “New_Purchase” and return the corresponding value from the 3rd column of the array. The FALSE argument corresponds to an EXACT Match.

Step 3: Make Note of Sales

Next, you should record the number of sales for the accounting period. This refers to the quantity of a particular product a business sells to customers during the accounting period.

• This is the table that contains the information on the sales. I am going to call it “Sales”.

• Write down the following formula in F5 of the “Tracker” sheet >> AutoFill up to F12.

The lookup_value is B5. Excel will look for B5 in the array “Sales” and return the corresponding value from the 3rd column of the array. The FALSE argument corresponds to an EXACT Match.

Step 4: Track Inventory for Different Items

Now, I will show how to track the inventory for different items. This is the quantity of a particular product that a business has in stock at the end of an accounting period.

It is calculated by adding the opening stock to the number of new purchases and subtracting the number of new sales. The resulting number represents the number of products that are available for sale at the end of the period.

• To calculate it, go to G5 and write down the following formula.

• Then, AutoFill up to G12.

Step 5: Calculate Inventory Amount

Finally, I will calculate the inventory amount. To do so,

• I need to list the cost per unit of each item.

• Then, I will simply multiply the values by the Hand-In-Stock value to get the amount. The formula in I5 will be

• Finally, I will AutoFill up to I12.

Read More: How to Keep Track of Customer Orders in Excel (With Easy Steps)

Similar Readings

• How to Create a Recruitment Tracker in Excel (Download Free Template)
• Make a Sales Tracker in Excel (Download Free Template)
• How to Track Multiple Projects in Excel (Download Free Template)
• Track Project Progress in Excel (Download Free Template)
• How to Make To Do List in Excel with Checkbox (With Quick Steps)

Frequently Asked Questions

1. What is Inventory?

Answer: Inventory refers to the stock of goods or materials that a business holds for the purpose of eventual resale or use in production. Inventory can include finished goods, work-in-progress (partially completed goods), and raw materials.

Inventory plays a critical role in the operation of businesses as it enables them to fulfill customer demand, avoid stockouts, and maintain a continuous flow of production. However, inventory also represents a significant investment of capital, and businesses must balance the cost of carrying inventory with the benefits of having it available.

2. What are the differences between the inventories of a manufacturing industry and a merchandising industry?

Answer: The key difference between inventory in manufacturing and merchandising industries is in the nature of their inventory.

In the manufacturing industry, inventory refers to the raw materials, work-in-progress (WIP), and finished goods that are used in the production process. Raw materials are the inputs that are used to create the final product, WIP refers to partially completed products that are still in the manufacturing process, and finished goods are the final products that are ready for sale.

In contrast, in the merchandising industry, inventory refers to the finished goods that are purchased from suppliers and resold to customers. Merchandising businesses do not engage in the production process, and their inventory is limited to the products they purchase and sell.

Read More: How to Create a Daily Task Sheet in Excel (3 Useful Methods)

Conclusion

In this article, I have explained how to keep track of Inventory in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.

Related Articles

• Tracking Student Progress Excel Template (Free Download)
• How to Keep Track of Clients in Excel (Download Free Template)
• Create Fully Functional To Do List in Excel (4 Handy Methods)
• Students Tracking Their Own Progress Template
• How to Track Attendance in Excel (with Detailed Steps)
• How to Maintain Store Inventory in Excel (Step by Step Guide)