How To Make a Spreadsheet For Inventory – In an article posted some time ago in my blog helps store owners, how to use an Excel template to inventory a store or a warehouse more easily, by using the Bar Code Scanner
Now, we will see how to make these types of templates for use in our business to start the new season of small inventories, I mean, provided we are not Lluís Serra and worked with an assortment of 100,000 products.
Creating a template to make an inventory count using Microsoft Excel and an uncomplicated barcode scanner, with a couple of well-chosen formulas and having stored the bar code information of our products, can create this type of document in Few minutes.
To create the template, simply follow these three steps:
1.- Space for our product list
We put four columns on the right side of the screen (the columns of F to I, for example) an incorporation of the information about the products that you want to invent, that is, we paste a list of all our products with the following information:
Column F: Product bar code. In this space we will paste each bar code corresponding to each product. We assume that this code is unique and representative of each product.
Column G: Product code. In this column we write our internal code in case of having and that sea different from the bar code that identifies each product. It is information that only serves us to facilitate our internal management and to identify more easily each product, it is not an essential data to make the count of inventory and uses the bar code.
Column H: Product description. The data that does not serve to verify that, every time we scan a bar code, the article you are referencing is the one that is actually recounting and not another, that is, it is a correction control of our management data. If you find an error in assigning the barcodes of a product, we should correct it before continuing with the inventory count.
Column I: Total inventoried quantity. In this column, we will see a sum formula of all the quantities of each product, but to be able to see its arguments in detail, we will dedicate to step 3.
2.- Space to scan the bar codes and write down the units
We put four other columns on the left side of the screen (columns A to D, for example) to leave a space where “pistolear” bar codes and note the units that we are finding each product; We will also dedicate a part of this space to the descriptive information of each product, which allows us to visually verify that the code we scan exactly matches the product we are recounting at any time.
We will use columns A and B for scanning bar codes and note the units and columns C and D to display descriptive information of the product. To implement this space on the left side of our spreadsheet, simply add the following information to each column:
Column A: Product bar code. In this column we will scan the different barcodes of each product that we retell.
Column B: Units. We simply note the quantity of each product we count in each count.
Column C: Description of the product. We apply a formula so that every time we scan a barcode, we will see the description of the product that corresponds, based on the information we have specified in the right part of the sheet, in columns F, G and H.
To obtain this data, it is only necessary to apply the FINDV formula throughout column C.
Here is the detailed example for cell C2 (below the column title): = SEARCHVAL (A2, F: I, 3, FALSE) -> What this formula does is look for the value of the third column (argument: “3” ) Of the matrix formed by columns F to I (argument: “F: I”), once in the first column find the value of the bar code (argument: “A2”), by means of an exact match of each number (Argument: “FALSE”).
Simply write this formula in cell C2 and drag it to as many rows as you wish; Each time you enter a bar code in column A, the product description will appear in column C.
Column D: Product code (our internal code). To make this data appear, we can use the same formula as in the previous column. We simply have to change the “3” by a “2”: = SEARCHVAL (A2, F: I, 2, FALSE) -> What this formula does is look for the value of the second column of the matrix formed by columns F To I, once you find in the first column of the matrix the value of the bar code.
3.- Sum of all inventories in each product
We return to column I (Inventoried Units) and apply a sum formula that will look for each repetition of the same bar code in column A and add up all the amounts we have counted from that same product.
You have to do it this way so that the inventory template allows us to find the same product more than once and we do not have to search if we have already counted it or not, and add the quantities to a previous count; Simply, if we find the same product in more than one place, we re-scan it and Microsoft Excel will be responsible for adding all the quantities.
The formula that we have to apply in column I is (for example, in cell I2): = SUMIF.SI (A: A, F2, B: B) -> What this formula does is to indicate that for each time (“F2”) in any cell in column A (argument “A: A”) will add up the quantities we have indicated in column B (“B: B” argument). We only have to drag this formula in column I for as many rows as we have listed in our list.