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

What Is Inventory Tracking?

Inventory tracking involves monitoring and managing a company’s inventory levels, movements, and status. The primary goals of inventory tracking are:

1. Ensure Availability: To make sure the right products are available when needed to meet customer demand.
2. Cost Optimization: Minimize costs associated with overstocking (holding excess inventory) and stockouts (running out of stock).

The common parameters used in inventory tracking include:

• Opening Stock: The initial inventory level at the beginning of a specific period (e.g., month, quarter, year).
• New Purchases: The quantity of items purchased during the period.
• Sales: The quantity of items sold during the period.
• Stock-in-Hand: The remaining inventory at the end of the period.

Here is an overview image of inventory tracking in Excel.

In this section, I will I’ll provide concise instructions for each part of the process on how to keep track of inventory in Excel.

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 that a business has in stock at the beginning of an accounting period. It serves as the starting point for tracking inventory levels throughout that period.

• Create the “Item” Table:
  • In your Excel workbook, create a table listing all the items you want to track. Include a column for the opening stock quantity.

• • Select the entire table.
  • Give this table a name (e.g., Item).

• Use VLOOKUP to Retrieve Opening Stock:
  • In the “Tracker” sheet, where you want to track inventory, enter the following formula in cell D5 (assuming the item code is in cell B5):

• • • The lookup_value (B5) corresponds to the item code. Excel will search for this code in the “Item” table and return the corresponding opening stock value from the 3rd column of the table. The FALSE argument ensures an exact match.

• AutoFill the Formula:
  • Drag the fill handle (small square at the bottom-right corner of the cell) down to fill the formula for all items (up to D12).

Step 2: Calculate New Purchases

New Purchases represent the quantity of a particular product that a business acquires from suppliers during the accounting period.

• Create the “New_Purchase” Table:
  • Set up a new sheet or table specifically for recording new purchases.
  • Include columns for the date, item, and quantity purchased.

• Retrieve New Purchase Data:
  • In the “Tracker” sheet, enter the following formula in cell E5 (assuming the item code is in cell B5):

• • • Similar to before, Excel will search for the item code in the “New_Purchase” table and return the corresponding quantity purchased from the 3rd column.

• AutoFill the Formula:
  • Drag the fill handle down to fill the formula for all items (up to E12).

Step 3: Record Sales

Sales refer to the quantity of a particular product that a business sells to customers during the accounting period. To track sales:

• Create the “Sales” Table:
  • Set up a new sheet or table specifically for recording sales.
  • Include columns for the date, item, and quantity sold.
  • Name this table “Sales.”

• Retrieve Sales Data:
  • In the “Tracker” sheet, enter the following formula in cell F5 (assuming the item code is in cell B5):

• • Excel will search for the item code (B5) in the “Sales” table and return the corresponding quantity sold from the 3rd column.

• AutoFill this formula down to F12.

Step 4: Track Inventory for Different Items

Now let’s calculate the inventory for different items. This represents the quantity of a particular product that a business has in stock at the end of an accounting period.

• Calculate Stock-in-Hand:
  • In cell G5 of the “Tracker” sheet, use the following formula:

• • • This formula adds the opening stock (D5) to new purchases (E5) and subtracts sales (F5).

• AutoFill this formula down to G12.

Step 5: Calculate Inventory Amount

Finally, let’s determine the inventory amount. To do this

• List Cost Per Unit:
  • Create a separate list with the cost per unit for each item.

• Calculate Total Inventory Value:
  • In cell I5 of the “Tracker” sheet, use the following formula:

• • • This formula multiplies the hand-in-stock value (G5) by the cost per unit (H5).

• AutoFill this formula down to I12.

Read More: How to Maintain Store Inventory in Excel

Frequently Asked Questions

1. What is Inventory?
   • Answer: Inventory refers to the stock of goods or materials that a business holds for eventual resale or use in production. It encompasses finished goods, work-in-progress (partially completed goods), and raw materials.
   • Importance: Inventory plays a critical role in business operations by enabling companies to meet customer demand, prevent stockouts, and maintain a continuous production flow. However, managing inventory involves balancing the cost of holding it against the benefits of availability.
2. Differences Between Manufacturing and Merchandising Industry Inventories:
   • Answer: The primary distinction lies in the nature of their inventory.
     • Manufacturing Industry Inventory:
       • Includes raw materials, work-in-progress (WIP), and finished goods used in production.
       • Raw materials serve as inputs for creating the final product.
       • WIP represents partially completed products still in the manufacturing process.
       • Finished goods are ready for sale.
     • Merchandising Industry Inventory:
       • Comprises finished goods purchased from suppliers and resold to customers.
       • Merchandising businesses do not engage in production; their inventory is limited to purchased and resold products.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Make Inventory Aging Report in Excel
• How to Create Inventory Database in Excel
• Min Max Inventory Calculation in Excel
• How to Calculate Economic Order Quantity in Excel
• How to Calculate Stock to Sales Ratio Using Formula in Excel

<< Go Back to Inventory Management in Excel | Learn Excel