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.
Index( )
- How to insert different sheets in Excel?
- What should the inventory table carry?
- Product key
- Product name
- General description
- Primer stock
- product entries
- Output or product sales
- Total remaining after entry and sale of products
- What criteria to include in the product inputs and outputs tables?
- bill information
- Product key
- Product entry or exit dates
- Quantity of entry or sale of the product
- How to correctly configure the tables and sheets of the Document?
- Is there a way to insert stock faster?
- How to automate table totals?
How to insert different sheets in Excel?
One of the virtues of Microsoft Excel is that you can have several sheets with different topics or themes in the same document, for example, in sheet 1 you can have a kind of presentation or introduction to a job, in sheet 2 you may be interested in having a database of some products, workers, among others.
This way you can have several sheets, actually the ones you need to satisfactorily complete your records. But, you know how to add or insert these new sheets in Excel , then we show you how to do it. The first option is to click on the “+” symbol that you get at the bottom of your spreadsheet, by doing so you will have your new sheet in the workbook.
The second option is through the keyboard, by pressing “Shift+F11” you will also be able to insert a new spreadsheet in your Excel workbook, so now you have two techniques that will allow you to add the new sheets that you need.
What should the inventory table carry?
When starting 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 it. Now, to develop an inventory in Excel it is necessary to know the parts that must be carried:
Product key
It is usually 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 real identification of the product , that is, assuming that you have a business that sells consumer products and you want to enter “Rice”, “Eggs”, “Wheat Flour”, among others.
General description
This item develops the explanation of the product , 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.
Primer stock
This is the initial amount that enters for each item, product, good or service that is being inventoried, that is, this amount does not consider replacements, but rather the original state or initial number of each product with which you started the business.
product entries
The product entry is the number associated with the replenishments you make of each product within your inventory, that is, the quantity you bought of an existing or out of stock product that you want to replenish in your stock.
Output or product sales
When you have a business and there are product outputs from your inventory, it is usually related to sales of that item or product , and therefore it is subtracted from the existing amount 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 the main aspects or elements of an inventory, now, according to your needs, you must decide which of all the items will complement your inventory control table. To establish the tables of inputs and outputs of products from your warehouse, you can count on the following elements:
bill 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 before, the product key is essential for the identification of the good, service, item or product within your inventory system, remember that it is generally an alphanumeric expression.
Product entry or exit dates
As important as the previous elements, the dates of entry or exit of the products is fundamental in inventory control, since it not only allows you to know exactly the moment of receiving or dispatching the merchandise, but it is also associated with the control purchases and sales of products and the financial situation of your company.
Quantity of entry 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 quantities of entry or sale of a certain product, remember that it is the difference that results in the total of existing product.
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 configuration of 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 “Example_Inventory.xlsx”.
- Remember that you can create as many sheets as you need, in this example we will use one sheet.
- Proceed to create the document header, to do this use the format you prefer. For example purposes, we will be using data from a non-existent company.
- Allocate the corresponding cells to place the fiscal data of the company.
- Places in an empty cell or field and in the same row the data of the inventory table, that is, “Product Key”, “Product Name”, “General Description”, “First Stock”, “Product Entries” , “Output or product sales” and “Total or Stock”.
- Then you will have to carry out the “Control of product entries”part , for this you must fill in the fields of the same row that you used in the general control, with the following information, “Invoice information”, “Product key”, “Date product input”, “Product input quantity”.
- To build the “Product output control”part , follow the same steps as in 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, at this point you will probably be thinking that you will have to fill your table manually. Well, this will depend on your programming skills, since you can create a macro with VBA to fill in the inventory information efficiently.
How to automate table totals?
As you already know, to obtain the total of the inventory tables, you must subtract the existing quantities in the product input and output fields, for this you can have formulas to subtract product quantities in Excel , and in this way obtain the stock or total quantity of each product.
In general, having an inventory record is extremely useful for any company , and if in addition 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 in other topics on our website.
c