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:

  • Ensure Availability: To make sure the right products are available when needed to meet customer demand.
  • 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.

How to keep track of inventory in excel

 


Dataset Overview

We’ll use the below sample dataset.

Dataset for inventory tracker

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.

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

Opening stock sheet

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

Naming opening stock table

  • 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):
=VLOOKUP(B5,Item,3,FALSE)
    • 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.
  • 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).

Transfer opening stock to inventory tracker


Step 2 – Calculate New Purchases

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

  • Set up a new sheet or table specifically for recording new purchases.
  • Include columns for the date, item, and quantity purchased.

Stock-in sheet

  • In the Tracker sheet, enter the following formula in cell E5 (assuming the item code is in cell B5):
=VLOOKUP(B5,New_Purchase,3,FALSE)
    • 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.

Transfer new purchases to inventory tracker

  • 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:

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

Sales Sheet

  • In the Tracker sheet, enter the following formula in cell F5 (assuming the item code is in cell B5):
=VLOOKUP(B5,Sales,3,FALSE)
  • Excel will search for the item code (B5) in the Sales table and return the corresponding quantity sold from the 3rd column.

Transfer sales information to inventory tracker

  • 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.

  • In cell G5 of the Tracker sheet, enter the following formula:

=D5+E5-F5

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

Calculate hand-in stock

  • AutoFill this formula down to G12.

Step 5 – Calculate Inventory Amount

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

  • Create a separate list with the cost per unit for each item.

Unit cost on inventory items

  • In cell I5 of the Tracker sheet, enter the following formula:
=G5*H5
    • This formula multiplies the hand-in-stock value (G5) by the cost per unit (H5).

Calculation of inventory amount

  • AutoFill this formula down to I12.

Read More: How to Maintain Store Inventory in Excel


Frequently Asked Questions

  • 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.
  • 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


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

Get FREE Advanced Excel Exercises with Solutions!
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of NAME from Bangladesh University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also interested... Read Full Bio

4 Comments
  1. Hi. This is an excellent reference page, thank you. However, in the “Current status” page section, it only totals the number of sales. It does not show how much stock is on hand, which is what I thought it would do.

    Is there a way in the current status page to show the current stock levels of various items, based on what has been purchased and what has been sold in the other sections? I was hoping to be able to have a row/column for each item that shows that information.

    • Hi STEWART SPEEDIE
      Thank you for your insightful comment. We have just updated this article. The inventory tracker now includes the hand-in stocks. I hope this satisfies you.
      Have a good day!!

  2. I can’t thank you enough for this tutorial. It took me three days to figure it all out (my lack of Excel knowledge) – but it’s done!!! I can’t wait to start populating the fields with real information!!!!!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo