Warehouse accounting in Excel. A simple program for automating warehouse accounting

Warehouse accounting in Excel is an excellent solution for any trading company or production organization, which is important to keep records of the amount of materials used raw materials and finished products.

Who can help spreadsheets

Large firms acquire ready-madesolutions in electronic format. However, they are quite expensive, and some complex programs require the involvement of a qualified employee with a high salary to work in the warehouse. This is beyond the power of beginning or small companies. Fortunately, there is an exit, and you can use Excel tables. This electronic tool, which is second only to the Word office program, has a functional enough to solve the problems of warehouse accounting.

A few important rules

Those who are interested in the question of how to leadwarehouse accounting, should from the outset seriously approach the issue of creating their own computer program. At the same time, the following rules should be adhered to at the outset:

• All directories should be created from the very beginningas accurately and in detail. In particular, one should not limit oneself to simply indicating the names of goods and should also indicate the articles, codes, expiration dates (for certain species), etc.
• Initial balances are usually entered in tables in monetary terms.
• It is necessary to observe the chronology and enter data on the receipt of certain goods in the warehouse earlier than on shipping to the buyer.
• It is necessary to make an inventory before filling out the Excel tables.
• It should be provided, what additional information may be needed, and enter it, so that in the future it is not necessary to specify the data for each of the goods.

Warehouse accounting in Excel: general recommendations

Before you start developing a spreadsheet to ensure the normal functioning of your warehouse, you should consider its specifics. The general recommendations in this case are as follows:

• It is necessary to compile directories: "Buyers", "Suppliers" and "Points of accounting of goods" (small companies do not need them).
• If the list of products is relatively constant,then you can recommend to create their nomenclature in the form of a database on a separate sheet of the table. In the future, the expense, income and reports are required to be filled in with references to it. The sheet in the Excel table with the heading "Nomenclature" must contain the name of the product, product codes, commodity groups, units of measure, etc.
• The report is generated using the Pivot Table tool.
• The receipt of goods (products) in the warehouse must be accounted for on the "Parish" sheet.
• You need to create the "Expense" and "Balances" sheets to keep track of the current state.

Create directories

To develop a program to guidewarehouse accounting in Excel, create a file with any name. For example, it can sound like "Warehouse". Then we fill out the reference books. They should have approximately the following form:

That the headings are not "run away", they need to be fixed. To do this, on the "View" tab in Excel, you need to click on the "Pin areas" button.

Similarly, the table "Buyers".

Provide a convenient and partially automatedwarehouse program can be free of charge if you create in it a supplementary directory of goods delivery points. True, it will be required only if the company has several outlets (warehouses). As for organizations that have one issuance point, there is no point in creating such a directory for them.

Own program "Warehouse": create a sheet "Parish"

First of all, we need to create a table for the nomenclature. Its headings should look like "Product name", "Sort", "Unit of measure", "Characteristic", "Comment".

Then:

• We select the range of this table.
• In the "Name" field, located directly above the cell with the name "A", enter the word "Table1".
• The same is done with the appropriate range on the "Suppliers" sheet. This is indicated by "Table 2".
• Fixation of income and expenditure transactions is carried out on two separate sheets. They will help to keep inventory in Excel.

For the "Arrival" table should look like the picture below.

Automation of accounting

Warehouse accounting in Excel can be made more convenient, if the user can choose from the ready list of the supplier, the name of the goods and the point of accounting.

Wherein:

• the unit of measure and the vendor code must be displayed in the table automatically, without operator involvement;
• the invoice number, date, price and quantity are entered manually;
• the program "Warehouse" (Excel) calculates the cost automatically, thanks to mathematical formulas.

To do this, all the directories need to be formatted in the form of a table and for the "Name" column create a drop-down list. For this:

• select the column (except the cap);
• find the "Data" tab;
• click on the icon "Check data";
• in the "Data type" field we look for the "List";
• in the field "Source" we specify the function "= INDIRECT (" nomenclature! $ A $ 4: $ A $ 8 ")".
• check the box next to "Ignore Empty Cells" and "List of Valid Values".

If everything is done correctly, then when filling the 1st column, you can simply select the name of the product from the list. In the column "Unit. change "the corresponding value appears.

In the same way, AutoComplete for the columns "Code" and "Vendor" are created, as well as a drop-down list.

To fill the column "Cost" use the multiplication formula. It should look like "= = price * quantity".

It is also necessary to create a drop-down list called "Points of Accounting", which will indicate where the goods were sent to. This is done in exactly the same way as in previous cases.

"Turn-by-turn sheet"

Now that you have almost created a handy tool that allows your company to keep inventory in Excel for free, it only remains to teach our program to display the report correctly.

To do this, we start working with the correspondingtable and at the beginning of the time period, we expose zeros, as we are still going to keep inventory records. If it was carried out earlier, then in this column the remains should be displayed. In this case, units of measurement and names of goods must be taken from the nomenclature.

To facilitate inventory management, the program is free to fill in the columns of "Shipment" and "Receipt" by means of the SUMMARY function.

We consider the remainder of the goods in the warehouse using mathematical operators.

Here is the "Warehouse" program. Over time, you can independently make adjustments to it to make accounting of goods (your products) as convenient as possible.