logo
logo
Inventory

How To Create Inventory Management System In Excel [Pre-made Template]

Ngoc Lee
How To Create Inventory Management System In Excel [Pre-made Template]
Share this:

An inventory management system is required for every retailer or wholesaler. Tracking inventories without the support of specialized software can be time-consuming. Most users might give up after considering the price of purchasing such software. Inventory management by Excel can assist you in effectively organizing and managing inventory as well as stock goods, allowing you to have a better understanding of how much you have and also what you need. 

The following post will show you how to create an inventory management system in Excel.  We will use the pre-made Excel template in this post; thus, don't forget to download it for free. Open the template and here we go. 

Create Inventory Management System In Excel - Pre-made Template
 Create Inventory Management System In Excel - Pre-made Template

First and foremost, you should be familiar with our given template. In this template, we’ve created 7 different Excel sheets corresponding to each element and process we need in an inventory system. Here they are:

  • Category
  • Items
  • Purchased
  • Sales price
  • Sales
  • Inventory
  • Report

With such an Excel template above, the inventory management will be involved in 6 steps as follow:

  1. Add Category and Suppliers
  2. Add Items
  3. Add Purchase
  4. Add Sales Price
  5. Add Sales
  6. Add Report

After you get used to this template, let’s move to the first step of managing inventory in Excel! 

- Read More: Order Management System: Definition, Process And Value 

- Read More: Order management system for Ecommerce: Definition, Key Effect, Benefit

Add Category and Suppliers

  • Add your Category

The first stage in developing an inventory management system is to generate a category and enter supplier data into the “CATEGORY” sheet. 

Add your Category
Add your Category

 If you want to add more categories, click the button ‘ADD CATEGORY’, and a new cell will be added.

  • Add Suppliers

To add your suppliers, you do the steps similar to the way you add categories. As a result, your ‘CATEGORY’ sheet will be like this: 

Add-Suppliers
Add-Suppliers

Add Items/Products

  • Add Items list

Select the ‘ITEMS’ sheet, and you will see an item list with  the elements below: 

Add-Items-list
Add Items list

Product code. Remember not to use the same codes for all cells of the ‘Product code’ row. The codes must be unique. 

The-product-code-must-be-unique
The product code must be unique

Product description. Here you type the product name of some product descriptions. A single product can be used for different codes.

Add-product-description
Add product description

Category. In this field, we use the information of categories on the “CATEGORY” sheet. 

Choose-category
Choose category

 Sizes and Color. Fill in your products of sizes and colors.

Fill-in-sizes-and-colors
 Fill in sizes and colors

Reorder point. Finally, put the value of reorder point, and you will be done with your item list.

Put-the-value-of-reorder-point
Put the value of reorder point

Note: If you add 15 products to the ITEMS LIST, your INVENTORY product should also be 15. This leads you to the “INVENTORY” sheet.

  • Check Stock Inventory

Move to the “INVENTORY” sheet. With the data you put into your ITEM LIST, they will be transferred to the “INVENTORY” sheet. 

👉 Read More: QR Code Inventory Management in Excel: A Complete Guide To Create 

This helps you check your Stock Inventory with the information as follows:

  • Current stocks
  • Stock unit price
  • Stock value
  • Sale amount
  • Rank by sold out
  • Rank by sales amount
Stock-inventory-sheet
Stock inventory sheet
Expand-unhide-products
Expand unhide products

Remember to click ‘Expand’ to unhide other products on stock inventory. In order to know how those elements work, you need to perform step 3.

Add Purchase

  • Add your first funds

Open the “PURCHASE” sheet. Now you can see the cell ‘BALANCE’ still at the value of 0.

Balance-=-0
 Balance = 0

That will be your mission to record the product purchased for a certain period. Firstly, add your funds to the row ‘STARTING BALANCE’. Assume your starting fund is 1 billion on 6th November 2017. Your balance will be automatically updated.

  • Start with your new Purchase Order

You have a fund, then started to fill in the information of your first Purchase order. 

Add-your-first-funds
 Add your first funds

After you type the code, your product description with current stock will be updated. 

Stock-=-0
 Stock = 0

Next, determine this PO from which suppliers and enter the Quantity you purchased. This quantity will be added to the current stocks.

Stock-is-updated
 Stock is updated

Fill in the unit price for each product the the total cost of this PO will equal to Unit price*Qty. After the first PO, your balance will decrease. 

Fill-in-the-unit-price
 Fill in the unit price
Your-balance-decreases
Your balance decreases

 Do the same work with other purchased orders. Add your expiration date. The date will turn red when it reaches the expiration date.

Add-your-expiration-date
 Add your expiration date

Copy the unit prices to your available stocks corresponding to each type of product.

Copy-the-unit-prices
 Copy the unit prices

However, as you can see, Product1/ size1/ color1 has two prices $200 and $100. In this case, we will take the average price to get the stock value of the same products with different purchase prices. Then we have (200+100)/2=$150.

You-get-the-stock-value
 You get the stock value
  • Track current stocks

You can search product current stocks on the ‘PRODUCT CODE’ tab search. 

Track-current-stocks
Track current stocks
Search-by-code
Search by code

 You also can search by date range. 

Search-by-date
 Search by date

Add Sales Price

Now move to the “SALES PRICE” sheet! 

Sales-price-sheet
 Sales price sheet

To update the sales price, copy the old price and paste them to the next column the adjust them. For more price updates, you just need to insert new columns.

👉 Read More: How Can You Disposal Of Obsolete Inventory? 

Update-the-sales-price
Update the sales price

Add Sales

  • Enter sale data

Open the “SALES” sheet and enter the sales data. 

Sale-sheet
 Sale sheet

Enter the price range from the “SALES PRICE” sheet. Remember to choose the right date that the price is updated. 

Sales-price
Sales price

 Then, the unit price will be automatically updated following the date. 

Unit-price-will-be-automatically-updated
 Unit price will be automatically updated

Do the same with other sales. When the quantity of sales increases, that means the current stock decreases.It will be updated following the goods sold over time. 

The-current-stock-is-updated
 The current stock is updated
  • Update current stock upon sales

After a certain period of time, you can track product sales, stock availability, and summary report by searching on the search tab. Besides, you must update your current stock value in order to make prompt purchases for goods out of stock. As you can see, you’ve already sold out all of your inventory. Now make new purchases for unavailable products.

Make-a-new-purchase
 Make a new purchase

New products are added to your stocks. After a sales time, your data becomes more and more complete. You can track which product is selling well, and which one is not. 

The-inventory-system-is-more-and-more-complete
 The inventory system is more and more complete

Add Report

It’s important to create reports weekly or monthly to get to know how effectively your business operates. Therefore, in this inventory management template, the monthly reports will be created once your purchase and sales sheet start to populate and you add the monthly report. 

Monthly-report
 Monthly report
Report-by-chart
 Report by chart

👉 Read More: How To Create An SKU Number And SKU Generator 

👉 Read More: ABC Analysis Example, Formula, and How To Calculate

Conclusion

Inventory management may be challenging for any business. It is easy to feel overwhelmed to keep track of your many types of inventory and gather data on every product that flows in and out of your company. That’s why Excel exists! Creating inventory system in Excel is a better solution for your business. 

With our ultimate guide “how to create inventory management system in Excel”, everything becomes easier for your inventory management. However, if your company grows, investing in the software will allow you to enhance your inventory management and save time and money.

avatar
Ngoc LeeNgoc Lee is an Content Creator Manager at EFEX. She wields her long-term expertise in Logistics and Supply Chain, harnessing her top-notch writing and research skills to bring incredibly valuable content. Whether you're a small startup or a well-established enterprise, Ngoc Lee is here to equip you with the essential knowledge of e-commerce, fulfillment, and all things business-related.