Excel – Inventory and stock in Excel

Every business, large or small, needs to manage or control product inventory. To perform this task it is not necessary to acquire inventory management software. In a few minutes, you can create an Excel template to help you keep track of your warehouse stock.

Parts of your inventory

You will create 3 tables:

  • Product inventory table. Where you will reflect: the code of the product, its description, initial stock, entrances, exits and stock.

We can include in this table as much data as we consider necessary, for example, the price, department or any other data that for us is relevant.

  • Table of outputs. In this table, we will reflect the sales. The minimum data to include in this table would be invoice number, date, product code, description and quantity sold.
  • Table of entries. In this table, we will reflect the purchases. The minimum data to include in this table would be purchase invoice number, date, product code, description and quantity purchased.

We will give a table format to each one of the previous ones.

We place ourselves with the mouse in one of the cells, go to the tab Insert and select table. Always mark the option “The table has headings” so that Excel does not take the headings as data.

Once the table is created, we will change its name as appropriate; Products, inputs or outputs.

Finally, we will choose the style we like best for our table. In the example, we give different colour to each of the tables.

Automatic description of your inventory items
In order that when entering the code in the table of Outputs the corresponding product appears automatically, we are going to use the formula BUSCARV. We place ourselves in the first cell of Description of the product and introduce the following formula:

=SEARCH([PRODUCT CODE];PRODUCTS;2;FALSE)

Where:

Value searched for, corresponds to the product code of the output table.
Matrix to search in would be the table of products. We can select it with the mouse or write PRODUCTS directly since previously we assigned it that name.
Column indicator refers to the column of the table products where you must look for the data we want, in our case would be 2.
Ordered, we will write FALSE because we want the exact match.
We will introduce this formula in the table of Outputs and in the table of Inputs.

=SEARCH([PRODUCT CODE];PRODUCTS;2;FALSE)

Sum of inputs and outputs for inventory management
In order for the Product Inventory table to automatically add the product entries, we will use the formula SUMAR.SI. We place ourselves first in the first cell of Entries and introduce the following formula:

=ADD.SI(ENTRIES[PRODUCT CODE];[@[PRODUCT CODE]];ENTRIES[QUANTITY])

Where:

Range corresponds to the range where you are going to look for the criterion that we are going to introduce. In our case, the range will be the codes of the Inputs table.
Criterion, what you are going to look for in the range we have selected. In our case is the product code (of the row in which we are entering the formula) of the table Products.
Range of sum, the values that we want to add us in that cell, in our case the amount of the table Entries.
We will do the same operation in the output cell so that in that case we add in that cell all the outputs of the product.

Automate the inventory stock
Finally, in the stock cell of the Products table we will introduce a very simple formula so that, to the initial stock of the product, you add the entries and subtract the exits. Giving us in this way the current stock of that product. The formula would be:

=[@[INITIAL STOCK]]+[@ENTRIES]-[@OUTPUTS]

Each time we enter a product entry or exit will automatically be reflected in the stock of the corresponding product.

In initial stock, we will note the product stock. The normal thing is to carry out an inventory at least once a year, it is usually carried out with date 31 of December. Starting the inventory on January 1. In initial stocks, we would write down the quantity inventoried on December 31 and we would begin to introduce the exits and entrances as of January 1 of that year.

Sample Template

Download the example template.

This template is a resource created by Saber Programas and its use is subject to copyright and intellectual property rights.

As you can see, you can create your own custom inventory control tool. Another option is to purchase inventory management software.

About the author

Hello everybody! I dedicated this blog to applications and games that I think deserve attention. I hope you will be interested! Enjoy :)

Leave a Reply

Your email address will not be published.