How to Make an Inventory and Stock Control in Excel? – Entrances and Exits

Excel is a very versatile tool that is used today by many users worldwide. You can use Excel to calculate VAT on products, keep records of operations, perform simple and complex calculations, make histograms, among many other applications. In this post we will show you how to do an inventory and stock control in Excel. Continue reading and learn how to do it.

How to insert different sheets in Excel?

One of the virtues of Microsoft Excel is that in the same document you can have several sheets with different topics or topics, for example, in sheet 1 you can have some kind of introduction or introduction to a job, on sheet 2 you may be interested in having a database of some products, workers, among others.

So you can have several sheets, actually the ones you need to successfully complete your records. But, you know how to add or insert these new sheets in Excel, because we show you how to do it. The first option is click on the “+” symbol that you get at the bottom of your spreadsheet, doing so will have your new sheet in the workbook.

The second option is via the keyboard, al press “Shift + F11” You will also be managing to insert a new spreadsheet in your Excel workbook, so now you have two techniques that will allow you to add the new sheets you need.

What should the inventory table carry?

When undertaking a business whatever its nature, it is always very important to have an inventory record, since it allows you to know the exact quantity of a certain product at any time you need to know. Now for develop an inventory in Excel it is necessary to know the parts to take:

Product key

It is generally an alphanumeric expression that refers to a specific product, service or good. It is the identity that said product will have in the inventory record and its code must be unique.

Product name

As indicated it is the name or actual product identification, that is, assuming you have a consumer product sales business and you want to enter “Rice”, “Eggs”, “Wheat Flour”, among others.

General description

This item develops the product explanation, that is, the form of presentation, the weight, the volume, the nature of the product, among other relevant aspects that you can show in the inventory.

First stock

It is the initial amount that is entered for each item, product, good or service that is being inventoried, that is, this amount does not consider the replacements, but the original status or initial number of each product you started the business with.

Product inputs

The product input is the number associated with the reruns you make of each product in your inventory, that is, the amount you bought of an existing or out-of-stock product that you want to replace in your stock.

Product output or sales

When you have a business and there are products out of your inventory it is usually related to sales of that item or product, and therefore it is subtracted from the existing quantity in your stock of the product.

Total remaining after entry and sale of products

This concept is known as product stock, which is the result of the difference between the inputs and outputs of products from your warehouse, the balance or final result is called stock.

What criteria to include in the product inputs and outputs tables?

We have named you the main aspects or elements of an inventory, however, according to your needs you must decide which of all the items will complement your inventory control chart. For establish the product input and output tables from your warehouse, you can count on the following elements:

Invoice information

It is essential to associate any item or product with the information or invoice number, since you will have access at any time to determine under which order a certain merchandise entered or left. To do this, you must organize your invoices electronically and you can associate them with your inventory.

Product key

As we indicated previously, the product key is essential for the identification of the good, service, item or product Within your inventory system, remember that this is generally an alphanumeric expression.

Dates of entry or exit of the product

As important as the previous elements, the dates of entry or exit of products It is essential in inventory control, since not only does it allow you to know exactly when to receive or dispatch the merchandise, but it is also associated with the control of purchases and sales of products and the financial status of your company.

Amount of input or sale of the product

To determine the stock or total quantity of existing products in the warehouse, it is important to place in this control sheet the amounts of entry or sale of a specific product, remember that it is the difference that the total existing product produces as a result.

How to correctly configure the tables and sheets of the Document?

The configuration of the tables and sheets that the document contains, although it is very important for inventory control, it is also true that it will depend on your needs. So in this post we will show you the setting up a basic inventory table, which you can use as a guide to develop the one you need:

• The first step is to open the Excel interface.
• Create a new document and proceed to save it with the name you prefer. For this case we will place “Sample_Inventario.xlsx”.
• Remember that you can create as many sheets as you need, in this example we will use one sheet.
• Proceed to create the header of the document, for this use the format you prefer. For example purposes, we will be using data from a non-existent company.

• Destine the corresponding cells to place the tax data of the company.
• Put in an empty cell or field and in the same row the data from the inventory tableie “Product Key”, “Product Name”, “General Description”, “First Stock”, “Product Tickets”, “Product Sales or Exit” and “Total or Stock”.
• Then it will be time to perform the part of “Product entry control”For this you must fill in the fields in the same row that you used in the general control, with the following information, “Invoice information”, “Product key”, “Product entry date”, “Product entry quantity”.
• To build the part of “Product output control”Follow the same steps from the previous point, but with the product output information.
• Proceed to fill in your information in the tables and you will have your inventory control.

Is there a way to insert stock faster?

Now you know the process to build your inventory control table, however, up to this point you will surely be thinking that you will have to fill your table manually. Well, this will depend on the programming skills you have, since you can create a macro with VBA to fill in inventory information effectively.

How to automate table totals?

As you already know to obtain the total inventory tables You must subtract the existing quantities in the input and output fields of products, for this you can have formulas to subtract quantities of products in Excel, and in this way obtain the stock or total quantity of each product.

In general, having a Inventory record is extremely useful for any company, and if added to this you generate your record in Microsoft Excel, you will be saving money, and obtaining reliable information. We hope this information has been useful to you and we invite you to continue reading other topics on our website.