Inventory Template In Excel

Inventory template in Excel is useful to manage stocks in Excel. Managing stocks or inventory is very important if you are a retailer. However, without the help of professional software, it is almost impossible to keep an eye on your warehouse stocks. Unfortunately, professional software costs you a considerable amount from your pocket. Still, in this article, we will show you how to build an inventory tracking template in Excel. You can download and use it free of cost.

When the bulk order comes from a customer, you need to be ready to tell the customer how many stocks are there in the warehouse and the time required for you to deliver all the products.

Follow the below steps to create an inventory tracking template in Excel.

1. The first thing is we need to create a product master. This sheet should include all the product’s basic information.

2. Now, create one more sheet for Product Inflow. This sheet is to add all the incoming items from the vendors.

3. Now, create a Stock Outflow sheet. This sheet is to update when the actual sale happens.

4. In the Product Master sheet, arrive at how many units are available as the stock in the warehouse.

5. The technique we have used here is: first, we have arrived at how many units were received from the vendors from the stock inflow sheet. Then, we have deducted the quantities sold data from the stock outflow sheet. This sheet would give me how many stocks are available for each product.

Now, arrive at an available stock value by multiplying the available stock by  Unit Price.

6. Let us now look at the next step. First, we have created a Product Master Sheet, Stock Incoming Tracker, and Stock Outgoing Tracker. Then, we arrived at the available stock by deducting stock received from a stock sold.

Now, we need to create more sheets called “calculations.”

7. We first need to arrive at the Total Stock Available and the Total Stock Value in the calculation sheet.

We have added an available stock column and available stock value column from the product list table.

8. Create the current month’s starting date and ending date. We have applied the formula, which will automatically create the first day and last day of the current month.
 

9. Now, the current month’s stock inflow and stock outflow have arrived.

10. Arrive category-wise current month sales and stocks available.

11.We need to create an excel dashboard for our inventory control template. Create a sheet and name it Dashboard.

12. First, name the heading Inventory Control Template.

13. Draw a text box and type Available Stock Now.

14. Draw one more text box under this and link to the calculation sheet for Available Stock Now cells.

15. Like this, we must do the same for Available Stock Value.

16. Similarly, create boxes and give a link for the Current Month Stock In and Current Month Stock Out.

17. Create a simple column chart for the current month’s category-wise sales.

18. We must create a down list in excel of all the items from the product list table in Excel.

19. Apply VLOOKUP and arrive at the ideal and current stock quantities.

20. Create a simple bar chart and show the difference between Ideal and Availableu stock.

21. Now, we must 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 for this product.

Now, your inventory tracking Excel template is ready to use.

• Inventory template, as the name suggests, helps users analyze, manage and understand their inventory details with ease.
• We can easily create our inventory template in excel with a few steps.
• This template is highly useful to maintain profits and losses, and to look after purchase and stock details.