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

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

Who can help spreadsheets?

Large companies acquire turnkey solutions for this purpose in electronic format. However, they are quite expensive, and some complex programs require the involvement of a qualified employee with high salaries in the warehouse. This is beyond the reach of start-ups or small companies. Fortunately, there is a way out, and you can use Excel spreadsheets. This electronic tool, which is inferior in popularity only to the Word office program, has functionality that is quite sufficient for solving warehouse accounting problems.

Some important rules

Those who are interested in the question of how to keep inventory records should seriously approach the question of creating their own computer program from the very beginning. In this case, from the very beginning, adhere to the following rules:

• All directories should initially be created as accurately and in detail as possible. In particular, one cannot limit oneself to a simple indication of the names of goods and should also indicate articles, codes, expiration dates (for certain types), 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 to the warehouse earlier than on shipment to the buyer.
• Before filling out Excel tables, it is imperative to take an inventory.
• It should be provided what additional information may be needed, and enter it, so that in the future did not have to clarify 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. General recommendations in this case are as follows:

• It is necessary to draw up reference books: “Buyers”, “Suppliers” and “Points of accounting for goods” (small companies do not need them).
• If the list of products is relatively constant, then we can recommend creating their nomenclature in the form of a database on a separate sheet of the table. In the future, expenses, receipts and reports must be filled out with links to it. The sheet in the Excel table with the heading "Nomenclature" should contain the name of the product, product codes, product groups, units of measurement, etc.
• The report is generated using the PivotTable tool.
• The receipt of goods (products) at the warehouse should be recorded on the “Arrival” sheet.
• It is required to create the “Expense” and “Balance” sheets to track the current status.

We create reference books

To develop a program to keep inventory in Excel, create a file with any name. For example, it may sound like “Warehouse.” Then fill out the directories. They should look something like this:

To ensure that the headers do not "run away", they need to be fixed. For this purpose, on the “View” tab in Excel, you need to click on the “Freeze areas” button.

The Buyers table looks exactly the same.

The program will be able to provide a convenient and partially automated warehouse accounting if the program creates an auxiliary directory of points of delivery of goods in it. True, it will be needed only if the company has several outlets (warehouses). As for organizations that have one point of issue, it makes no sense to create such a guide for them.

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

First of all, we need to create a table for the item. Its headings should look like “Product Name”, “Variety”, “Unit of Measure”, “Characteristic”, “Comment”.

Then:

• Select the range of this table.
• In the "Name" field, located directly above the cell with the name "A", enter the word "Table1".
• Do the same with the appropriate range on the "Suppliers" sheet. At the same time indicate "Table2".
• Fixation of incoming and outgoing operations is carried out on two separate sheets. They will help to keep warehouse records in Excel.

For the “Parish”, the table should look like the image below.

Accounting Automation

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

Wherein:

• the unit and supplier code should be displayed in the table automatically, without operator intervention;
• invoice number, date, price and quantity are entered manually;
• the program “Warehouse” (Excel) calculates the cost automatically, thanks to mathematical formulas.

For this, all the directories must be formatted in the form of a table and for the “Name” column, create a drop-down list. For this:

• select the column (except for the header);
• we find the tab “Data”;
• click on the “Data Validation” icon;
• in the "Data Type" field, look for "List";
• in the "Source" field, specify the function "= INDIRECT (" item! $ A $ 4: $ A $ 8 ")."
• check the boxes “Ignore empty cells” and “List of valid values”.

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

In the same way, autocomplete is created for the "Code" and "Supplier" columns, as well as a drop-down list.

To fill in the columns “Cost” use the multiplication formula. It should look like - "= price * quantity".

You also need to create a drop-down list called "Accounting Points", which will indicate where the goods arrived. This is done exactly the same as in previous cases.

“Turnover sheet”

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

To do this, we start working with the corresponding table and set zeros at the beginning of the time period, since we are only going to keep inventory records. If it was carried out earlier, then in this column the residuals should be displayed. In this case, units of measurement and names of goods should be taken from the nomenclature.

To facilitate inventory control, the free program should fill in the “Shipment” and “Receipt” columns using the SUMMSlimn function.

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

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