Inventory Template in Excel

Inventory Spreadsheet Template – Excel Product Tracking

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 costs you a considerable amount from your pocket, but in this article, I will show you how to build an 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 Template in Excel?

You can download this Inventory Tracking Excel Template here – Inventory Tracking Excel Template

Follow the below steps to create an Inventory Tracking Template in Excel.

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

    Inventory template Example 1

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

    Inventory template Example 1-1

  3. Now Create Stock Outflow Sheet. This is to update when the actual sale happens.

    Inventory template Example 1-2

  4. Now in the Product Master Sheet Arrive How Many Units Actually Available as the stock in the warehouse.

    Inventory template Example 1-3

  5. The technique I have used here is; first, I have arrived at how many units received from the vendors from the stock Inflow sheet, then I have deducted the quantities sold data from the Stock Outflow sheet. This would give me how many stocks are available for each Product.

    Now, arrive at an available stock value by multiplying the available stock into Unit PriceUnit PriceUnit Price is a measurement used for indicating the price of particular goods or services to be exchanged with customers or consumers for money. It includes fixed costs, variable costs, overheads, direct labour, and a profit margin for the more.

    Inventory template Example 1-4

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

    Now we need to create a more sheet called Calculations.

  7. In the calculation sheet first thing we need to do is arrive total stock available and total value of the stock.

    Inventory template Example 1-6

    From the Product List table, I have added an available stock column and available stock value column.

  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.

    Inventory template Example 1-7

  9. Now arrive current month stock inflow and stock outflow.

    Inventory template Example 1-8

  10. Arrive Category-wise Current Month Sales and Stocks Available.

    template Example 1-9

  11. Now, we need to create an excel dashboard for our inventory Control template. Create a sheet and name it as Dashboard.

  12. First name the heading as “Inventory Control Template.”

    Example 1-10

  13. Draw text box and type Available Stock Level.

    Example 1-11

  14. Draw one more text box under this and give a link to the calculation sheet for available stock cells.

    Example 1-12

  15. Like this, do the same for available stock value as well.

    Example 1-13

  16. Similarly, create boxes and give a link for the current month stock in and stock out.

    Example 1-14

  17. Create a simple column chartColumn ChartColumn chart is used to represent data in vertical columns. The height of the column represents the value for the specific data series in a chart, the column chart represents the comparison in the form of column from left to more for current month category-wise sales.

    Example 1-15

  18. Now create a down list in excelDown List In ExcelA drop-down list in excel is a pre-defined list of inputs that allows users to select an more of all the items from the product list table.

    template Example 1-16

  19. Apply VLOOKUPApply VLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more and arrive at the ideal stock quantity and current stock quantity.

    Inventory template Example 1-17

  20. Create a simple bar chartSimple Bar ChartBar charts in excel are helpful in the representation of the single data on the horizontal bar, with categories displayed on the Y-axis and values on the X-axis. To create a bar chart, we need at least two independent and dependent more and show the difference in ideal stock and available stock.

    Inventory template Example 1-18

  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.

    Inventory template Example 1-19

Ok, now your inventory tracking excel template is ready to use.

Things to Remember

  1. 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.
  2. 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.
  3. If any sales happened then, you need to update the data in the Stock Out sheet.
  4. If any stocks are received from the vendor, then update the data in the Stock In sheet.
  5. If followed, all the above steps dashboard will work fine and show the analytics of your inventory.

Recommended Articles

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 a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion