Inventory Template in Excel (Table of Contents)
Inventory Excel Template
If you are a retailer then managing stocks or inventory is very important. Without the help of professional software, it is almost impossible to keep an eye on your warehouse stocks. Professional Software’s cost you a considerable amount from your pocket, but in this article, I will show you how to build inventory tracking template in excel and you can download and use it as a free of cost.
When the bulk order comes from a customer you need to be ready to tell the customers how many stocks are there in the warehouse and what the time is required you to deliver all the products.
How to Create Inventory Stock Tracking Template in Excel?
Follow the below steps to create an Inventory Tracking Template in Excel.
Step 1: First thing is you need to create product master. This sheet should include all the products basic information.
Step 2: Now create one more sheet for Product Inflow. This is to add all the incoming items from the vendors.
Step 3: Now Create Stock Outflow Sheet. This is to update when the actual sale happens.
Step 4: Now in the Product Master Sheet Arrive How Many Units Actually Available as the stock in the warehouse.
The technique I have used here is, firstly I have arrived how many units received from the vendors from Stock Inflow sheet then I have deducted the quantities sold data from Stock Outflow sheet. This would give me how many stocks are available for each Product.
Step 5: Now arrive available stock value by multiplying the available stock into Unit Price.
Step 6: Let us now look at the next step to create Inventory template in excel. We have created a Product Master Sheet, Stock Incoming Tracker, and Stock Outgoing tracker. Then we have arrived the available stock by deducting stock received from stock sold.
Now we need to create a more sheet called Calculations.
Step 7: In the calculation sheet first thing we need to do is arrive total stock available and total value of the stock.
From the Product List table, I have added an available stock column and available stock value column.
Step 8: Create Current Month Starting Date & Ending Date. I have applied the formula which will create the first day and last day of the current month automatically.
Step 9: Now arrive current month stock inflow and stock outflow.
Step 10: Arrive Category-wise Current Month Sales and Stocks Available.
Step 11: Now we need to create an excel dashboard for our inventory Control template. Create a sheet and name it as Dashboard.
Step 12: First name the heading as “Inventory Control Template”.
Step 13: Draw text box and type Available Stock Level.
Step 14: Draw one more text box under this and give a link to the calculation sheet for available stock cell.
Step 15: Like this do the same for available stock value as well.
Step 16: Similarly create boxes and give a link for current month stock in and stock out.
Step 17: Create a simple column chart for current month category-wise sales.
Step 18: Now create a drop-down list of all the items from product list table.
Step 19: Apply VLOOKUP and arrive ideal stock quantity and current stock quantity.
Step 20: Create a simple bar chart and show the difference in ideal stock and available stock.
Step 21: Now enter the recommendation as “if the available stock is less than the ideal stock then the recommendation is Order Quantity or else the recommendation is You have excess Quantity.
Ok, now your inventory tracking excel template is ready to use.
Things to Remember About Inventory Stock Template in Excel
- Firstly you need to update all the available products in the Product List worksheet. You need to make sure there are no duplicate entries to it.
- When you receive the fresh items or new items you need to go back to the Product List sheet and update the new products accordingly.
- If any sales happened then you need to update the data in Stock Out sheet.
- If any stocks received from the vendor then update the data in Stock In sheet.
- If followed all the above steps dashboard will work fine and show the analytics of your inventory.
This has been a guide to Inventory Template in Excel. Here we discuss the steps to create an Inventory Stock Template in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –