Free inventory control in Excel – there is the program without macros and programming
The inventory control in Excel is suitable for any trading or production organization, where it is important to take into account the amount of raw materials and materials, finished products. For this purpose, the company keeps inventory. Large firms, as a rule, purchase ready-made solutions for keeping records electronic. There are a lot of options for various activities today.
In small enterprises the goods movement are controlled on their own. For this purpose you can use Excel tables. The functional of this tool is quite enough. Let`s get acquainted with some possibilities and will make independently our program of inventory control in Excel.
At the end of the article you can download the program, which is here disassembled and described, for free.
How to keep inventory in Excel?
Any specialized solution for inventory control which was created independently or was purchased, will be work well only if subject to the basic rules. If we neglect these principles at the beginning, then the work will become more complicated.
- Fill out the directories as accurate and detailed as possible. If this is a nomenclature of goods, then it is necessary to make not only to names and quantity. For correct accounting you will need codes, articles, expiration dates (for individual productions and trading enterprises), etc.
- Initial residues are introduced in quantitative and monetary terms. It has the sense to make an inventory before filling in the appropriate tables.
- Honor the chronology in the registration of transactions. Enter dates about the receipt of products in the warehouse should be earlier, than about the shipment of the good to the buyer.
- Do not disdain by the additional information. To compile of a routing sheet, the driver needs to the date of shipment and the name of the customer. For accounting – is the payment method. Each organization has its own characteristics. Data series which were entered in the warehouse accounting program in Excel, will be useful for statistical reports, payroll to specialists, etc.
It is impossible to answer the question how to conduct inventory control in Excel unambiguously. It is necessary to take into account to the specifics of a particular enterprise, a warehouse, and goods. But you can withdraw to the general recommendations:
- For correct reference of inventory control in Excel you need to compile directories. They can take 1 - 3 sheets. There are directories: «Providers», «Buyers», «Accounting points». In a small organization, where there are not so many contractors, directories are not needed. You do not need to compile a list of goods points if the enterprise has only one warehouse and/or one store.
- With a relatively constant list of products, it makes sense to perform a nomenclature of goods in the form of a database. Subsequently the income, the expenditure and the reports should be filled with references to the nomenclature. The sheet of «Nomenclature» may contain to the name of the product, product groups, product codes, units of measures, etc.
- Admission of goods to the warehouse is recorded on the «Incoming» sheet. The retirement – is the «Shipments». The current status is «Turnover» (Reserve).
- Results, the report are generated using the «PivotTable» tool.
That the headings of each inventory table do not run away, it makes sense to fix them. This is done on the «View» tab using the «Freeze Panes» button.
Regardless of the number of entries, the user will see the headers of columns now.
The table in Excel «Inventory control»
Let's look at the example, how the warehouse accounting program should work in Excel.
We do «Providers». For supplier data:
*The form may be another.
For customer data on sheet «Buyers»:
*Please note: the title bar is fixed. Therefore, you can contribute as many dates as you like. The column names will be visible.
For the audit of goods items on sheet «Accounting points»:
Once again we`ll repeat: it makes sense to create such directories, if the company is large or medium.
You can make the list of Nomenclature goods on a separate sheet:
In this example, we use drop-down lists in the table for warehouse accounting. Therefore, we need References and Nomenclature: we will make references to them.
The range of the table on sheet «Nomenclature» A3:E8 is assigned the name «Table1» in «Name Box». For this highlight to the range of the table and enter the corresponding value in the name field (opposite the formula row). You also need to name «Table2» in the range of the table on sheet «Providers» A3:D11. And name «Table3» for sheet «Buyers» A3:D7. This will conveniently refer to their values.
For fixing of the income and the expense transactions, we fill two separate sheets.
We make a hat for the «Incoming»:
The next step - is automating the filling of the tables! It is necessary to make so, that the user selects from of the ready list to the name of the good, the supplier, the accounting point. The vendor code and unit of measure should be displayed automatically. The date, the invoice number, quantity and price are entered manually. Excel calculates the cost.
We proceed to the solution of the problem. First, all the directories will be formatted as tables. It`s necessary in order to subsequently be able to add or change something.
Create the drop-down list for the column «Name». Highlight the column A4:A15 (without the hat). Go to the «Data» tab - the «Data Validation» tool.
In the «Allow» field, select «List». Immediately appears an additional field «Source». So that the values for the drop-down list to be taken from another of the sheet, use the function:
Now when filling the first column of the table, you can select the name of the product from the list.
In the column «Unit » should be appeared the corresponding value by automatically. We do with helping with the VLOOKUP and ISNA function (it will suppress the error as a result of the VLOOKUP function when referring to the empty cell of the first column). The formula:
By the same principle we make the drop-down list and autocomplete for the columns «Provider» and «Code».
Also we form the drop-down list for the «Accounting point» - where the received goods were sent. For filling the column «Amount» we apply the multiplication formula (= price * quantity).
We form the table «Shipments of goods».
Drop-down lists are applied in the columns «Name», «Shipping point, deliveries», «Buyer». The units of measure and the cost are filled automatically with helping of the formulas.
We do the «Turnover» («Results»).
We put zeros at the beginning of the period, because inventory control is just beginning to be conducted. If it was conducted previously, then in this column will be residues. Names and units of measure are taken from the nomenclature of goods.
The «Incoming» and «Shipments» columns are filled with the function =SUMIFS().
Incoming:
Shipments:
We count to the remainders by means of mathematical operators.
So the self-made program is ready.
