Inventory accounting in Excel is suitable for any trade or industrial organization where it is important to take into account the quantity of raw materials and finished products. For this purpose, the company maintains warehouse records. Large companies, as a rule, purchase ready-made solutions for electronic accounting. Today there are a lot of options available for various areas of activity.
In small enterprises, the movement of goods is controlled on their own. Excel tables can be used for this purpose. The functionality of this tool is quite sufficient. Let's get acquainted with some of the possibilities and create our own warehouse accounting program in Excel.
At the end of the article you can, which is analyzed and described here.
How to keep inventory records in Excel?
Any specialized warehouse solution, whether built in-house or purchased, will only work well if the basic rules are followed. If you neglect these principles at the beginning, then the work will become more difficult later.
- Fill out the reference books as accurately and thoroughly as possible. If this is a product range, then it is necessary to enter not only the names and quantities. For correct accounting, you will need codes, articles, expiration dates (for individual industries and trade enterprises), etc.
- Initial balances are entered in quantitative and monetary terms. It makes sense to take an inventory before filling out the relevant tables.
- Maintain chronology in recording transactions. Data on the receipt of products at the warehouse should be entered before the shipment of goods to the buyer.
- Don't hesitate to provide additional information. To draw up a route sheet, the driver needs the shipment date and the name of the customer. For accounting – payment method. Each organization has its own characteristics. A number of data entered into the warehouse accounting program in Excel are useful for statistical reports, payroll for specialists, etc.
It is impossible to unequivocally answer the question of how to maintain inventory records in Excel. It is necessary to take into account the specifics of a particular enterprise, warehouse, and goods. But general recommendations can be made:
- To maintain inventory records correctly in Excel, you need to create reference books. They can take 1-3 sheets. This is a directory “Suppliers”, “Buyers”, “Goods accounting points”. In a small organization where there are not many counterparties, directories are not needed. There is no need to draw up a list of points for registering goods if the enterprise has only one warehouse and/or one store.
- With a relatively constant list of products, it makes sense to create a product range in the form of a database. Subsequently, receipts, expenses and reports are filled out with references to the nomenclature. The “Nomenclature” sheet may contain the name of the product, product groups, product codes, units of measurement, etc.
- Receipt of goods to the warehouse is recorded on the “Receipt” sheet. Disposal – “Expense”. The current state is “Remains” (“Reserve”).
- Results, the report is generated using the “Pivot Table” tool.
To prevent the headers of each warehouse accounting table from running away, it makes sense to fix them. This is done on the “View” tab using the “Freeze Areas” button.
Now, regardless of the number of records, the user will see the column headers.
Excel table “Warehouse accounting”
Let's look at an example of how a warehouse accounting program in Excel should work.
We make “Directories”.
For supplier data:
*The shape may be different.
For customer data:
*Please note: the title bar is frozen. Therefore, you can enter as much data as you like. The column names will be visible.
To audit goods release points:
Let us repeat once again: it makes sense to create such directories if the enterprise is large or medium-sized.
You can make a product nomenclature on a separate sheet:
In this example, we will use drop-down lists in the table for warehouse accounting. Therefore, we need Directories and Nomenclature: we will make references to them.
Let's give the range of the "Nomenclature" table the name: "Table1". To do this, select the table range and enter the corresponding value in the name field (opposite the formula bar). You also need to assign a name: "Table2" to the table range "Suppliers". This will allow you to conveniently refer to their values.
To record incoming and outgoing transactions, fill out two separate sheets.
Making a hat for the “Parish”:
Next stage - automation of table filling! It is necessary to make sure that the user selects the name of the product, supplier, and point of accounting from a ready-made list. The supplier code and unit of measure should be displayed automatically. The date, invoice number, quantity and price are entered manually. Excel calculates the cost.
Let's start solving the problem. First, we will format all directories as tables. This is necessary so that something can be added or changed later.
Create a drop-down list for the “Name” column. Select the column (without a header). Go to the “Data” tab - the “Data Check” tool.
In the “Data type” field, select “List”. An additional “Source” field immediately appears. To take the values for the drop-down list from another sheet, use the function: =INDIRECT("item!$A$4:$A$8").
Now, when filling out the first column of the table, you can select the product name from the list.
Automatically in the “Unit” column change." the corresponding value should appear. Let's do it using the VLOOKUP and UND functions (it will suppress the error resulting from the VLOOKUP function when referring to an empty cell in the first column). Formula: .
Using the same principle, we create a drop-down list and autocomplete for the “Supplier” and “Code” columns.
We also create a drop-down list for the “Accounting point” - where the received goods were sent. To fill out the “Cost” column, use the multiplication formula (= price * quantity).
We create a table “Consumption of goods”.
Drop-down lists are used in the columns “Name”, “Point of registration of shipment, delivery”, “Buyer”. Units of measurement and cost are filled in automatically using formulas.
We make a “Turnover Statement” (“Results”).
At the beginning of the period we set zeros, because warehouse accounting is just beginning to be maintained. If it was previously maintained, then this column will contain remainders. Names and units of measurement are taken from the product range.
The “Receipts” and “Shipments” columns are filled in using the SUMIFS function. We calculate the remainders using mathematical operators.
Download the warehouse accounting program (a ready-made example compiled according to the scheme described above).
So the independently compiled program is ready.
Online system for managing small business companies in the field of trade and services. Modules: trade and warehouse, CRM, mail, SMS, tasks and calendar, employees. Allows you to maintain full-fledged trade and warehouse accounting, work with clients and suppliers, collaborate with employees, control expenses, calculate profits and costs. A large number of printed editable templates of forms and documents. All necessary reports.
An online service for trade management, designed to automate small and medium-sized businesses. Allows you to manage sales and purchases, control mutual settlements, work with the client base, maintain warehouse records, and also print all the documents necessary for running a business.
Online system for recording sales, products and clients. Allows you to quickly carry out basic trading operations - sales, purchases, returns, load goods and balances from Excel tables, enter new products using the built-in barcode scanner, create incoming and outgoing orders, control the amount in the store's cash register, organize work with discounts, view sales statistics and analytics.
Online accounting system for entrepreneurs and small businesses in the field of wholesale trade and services. Designed to maintain a database of counterparties, balances of goods in the warehouse, money in accounts and in the cash register, accounting for income and expenses, and analyzing the health status of the company. Allows you to generate the necessary primary documents without the help of an accountant.
A simple and convenient retail and warehouse accounting system that saves time and increases business profitability. Cashier workstation on any laptop or tablet (barcode scanner, document printing). Warehouse accounting (remains, purchases, label printing). Tools for business control and development: sales for each store, optimization of warehouse balances, amount of money in the cash register, average check amount, margin and net profit. The system works with EGAIS
A simple and functional warehouse automation system based on 1C. Optimization of routes when collecting orders, optimization of storage area during address storage. Full online mode.
SaaS service for store and warehouse management. Management of purchases and suppliers, warehouse balances, prices, sales and clients, marketing campaigns, employees. There are reports, document printing, and a POS application for the tablet. You can run one small store or a chain of stores.
This is undoubtedly the best software for keeping inventory records. The advantages of the program include a simple user interface and ease of learning. The program allows you to keep full records of goods and money from a kiosk to a large wholesale warehouse. For users who value mobility, there is a version created using portable application technology (portable edition). The program in this version can be installed both on the computer’s hard drive and on removable media (USB-flash, etc.) and can be freely moved from computer to computer.
This is a simple, reliable and flexible program for automating trade, warehouse and production. Easy to deploy, the program requires a minimum of effort to implement and has an affordable price. A free trial version is available.
The program is designed for maintaining operational warehouse accounting. The program allows you to track the balances of goods and materials in the warehouse and receive reports on balances as of any date. Commodity accounting is based on maintaining warehouse accounting cards.
The "IP: Trade Warehouse" program is part of the Info-Enterprise program system. "IP: Trade Warehouse" allows you to easily and conveniently automate warehouse accounting at a trading enterprise. Among the users of our program are wholesale and retail trade enterprises, chain stores, and wholesale warehouses. Despite its trade orientation, "IP: Trade Warehouse" allows you to keep records of materials, finished products, small business products, i.e. The program can be used wherever warehouse accounting is necessary.
As with other FOLIO programs, no special knowledge is required to operate, other than familiarity with MS Windows.
Warehouse accounting "Warehouse+" is a very simple and convenient program that has the most necessary capabilities. Formation of incoming and outgoing documents. Printing of invoice, invoice, invoice and receipt order. Calculation of three sales prices with given coefficients relative to the purchase price, followed by the ability to select one of these prices to form sales prices for a specific buyer.
"1C: Accounting 8" is a universal mass-use program for automating accounting and tax accounting, including the preparation of mandatory (regulated) reporting. This is a ready-made solution for accounting in organizations engaged in any type of commercial activity: wholesale and retail trade, commission trade (including subcommission), provision of services, production, etc. In addition, with the help of "1C: Accounting 8" individual entrepreneurs who apply a simplified taxation system or a general taxation regime can keep records.
The "Azhur-SKLAD" program is designed to automate the cycle of accounting for warehouse operations. The system includes a full cycle of accounting for all types of warehouse transactions of receipt, accounting of expenditure transactions and preparation of analytical reporting.
This is a trade and warehouse program for comprehensive control over the activities of wholesale, retail, wholesale-retail and other trading enterprises - from a kiosk to a supermarket. The program allows you to carry out and formalize all types of trade and warehouse operations, account for funds and control mutual settlements with clients, maintain all the necessary documentation and analyze the work of the entire enterprise.
"Microinvest Warehouse Pro" is an industry solution that is an automation system for “network” retail structures (self-service stores and/or counter sales), warehouse facilities and restaurants. "Microinvest Warehouse Pro" meets all the requirements for the movement of commodity resources within the enterprise itself or in a network of enterprises connected by their commercial or production activities.
"Tirika-Shop" is a very simple but very powerful program for a store. It can do everything, but at the same time it is so easy to use that even an inexperienced user will master it in five minutes. The program is suitable for a store of any profile: groceries, building materials, auto parts, clothing, and so on.
"RM-SKLAD Warehouse Accounting" is a program for maintaining warehouse accounting and trade operations in an enterprise of any form of ownership. It is primarily intended for small and medium-sized businesses. The program can be used in small shops, warehouses, and production for operational accounting of goods and materials.
"Warehouse and Trade" is a simple, reliable program for automating trade and warehouse accounting. It allows you to easily and quickly create primary documents (invoices, invoices, invoices, contracts, etc.), control stock balances, keep records of sales and receipt of goods, make reservations, keep records of debts to customers and suppliers, take into account purchase prices and calculate the cost of products during production, calculate the profit received and much more. The program has extensive interface customization options.
Software for warehouse accounting.
↓ New in the "Warehouse accounting" category:
Free
Frigate - Warehouse Light 4.82.0.5 is a free warehouse program for comprehensive automation of accounting in production, trade and services for small businesses. The Fregat - Warehouse Light program has minor limitations that allow the program to be used only in small businesses, since it does not include some modules necessary for large enterprises and does not have functions for maintaining a large number of reports.
Free 
Product Accounting 2.0 is an application for organizing accounting for goods sold in a store that has several departments. The “Product Accounting” application does not have its own fixed structure and will be convenient when working in various stores that have their own accounting scheme, which requires its own modification for the warehouse program.
Free 
TCU Start 3.53 is an entry-level trading and warehouse system. The TCU Start application will help you keep track of trade and warehouse operations, balances in monetary and quantitative terms, and calculate the resulting commodity profit. The application also provides the ability to conduct revaluation and re-accounting.
Free 
Warehouse and trade 2.155 is an application for organizing wholesale and retail trade and warehouse accounting. The application has a unified and highly customizable interface. The application also contains a large database with the ability to customize its subject part for each user.
Free 
Implementer 1.5.1 is an application that will help with accounting in retail trade, control of distributors and cash registers. The “Realizer” program is effectively used in kiosks, small market complexes, cafes, snack bars, bars and similar points of sale.
Free 
Mini-Market 1.3 is a convenient and simple application for accounting for goods in retail trade in pavilions, retail outlets or on the market. The Mini-Market program requires free registration and a password.
Free 
Mini-Opt 1.5 is a compact trading and warehouse program. The Mini-Opt program has the ability to perform functions for the main modes of working with a warehouse and in trade, printing invoices, TORG-12, invoices and price lists. To use the Mini-Opt program, you need to register for free and receive a password.
Free 
Mini-Shop 1.1 will help automate the accounting process in small stores of various profiles and networks of retail outlets in which it is not possible to enter each product into the computer when selling. The Mini Store program requires a password to start, which is easy to obtain by registering.
Free 
Elf (mini-warehouse) 7.01 is a program for maintaining warehouse records, processing and creating primary documents and various reports.
Free 
SLS-Warehouse-Lite 6.117 is a free and fully working version of the operational accounting system of the SLS-Warehouse family. The SLS-Warehouse-Light program is designed to automate accounting work in small wholesale, retail or wholesale-retail companies, as well as in service sector enterprises.
Let's consider most famous and wherein simple software for warehouse accounting in more detail.
Essentially, this program is most popular for carrying out warehouse accounting. The main advantages of the program include an easy and convenient interface to use.
With its help, you can fully carry out full accounting of products and funds, regardless of whether it is a kiosk or a large wholesale warehouse.
For those users who urgently need mobility, a version is available using so-called portable application technology.
Mobile version of the software can be installed not only on a personal computer, but also on removable media.
"Atonex" Warehouse
A simple program and at the same time convenient for full accounting in trade.
She perfect for medium-sized businesses, since in parallel with simplicity it includes the whole required basic functionality, which is important for the implementation of full accounting in trade, namely:
- inventory control;
- sales of products;
- cash register;
- a set of necessary reports for the necessary analysis of financial results, including audit.
Despite being free, there is also a paid version that includes a huge number of various auxiliary functions.
"VVS: Office - Warehouse - Store"
Is very simple and in parallel with this reliable and flexible software, designed for automation of trade, warehouse and production in general.
Includes minimal effort for the implementation process, and has an affordable price. It is possible to use a demo version for evaluation purposes.
"OK-Sklad: Warehouse accounting and trade"
The program is designed exclusively for warehouse accounting, therefore distinguished by power and comfort of use.
It contains all the necessary functionality. In simple words, complex warehouse accounting and the need for a clear understanding of the company’s economics will no longer be the number one problem.
"Product-Money-Product"
The software belongs to the trade and warehouse category, the purpose of which is to exercising full control over the labor activities of a wholesale, retail and other trading company.
The software fully allows not only to carry out, but also to document all trade and warehouse operations without exception, including taking into account financial capital and monitoring mutual settlements with consumers, as well as maintaining the entire necessary package of documents.
Thanks to this program, you can easily analyze the business activities of a company.
"Warehouse accounting of products"
In most cases used for the purpose of maintaining an operational version of warehouse accounting. With its help, you can effortlessly monitor the remaining volume of products and other materials in the warehouse and at the same time generate a corresponding report on the balance for any selected time period.
Accounting is based exclusively on the management of so-called warehouse cards.
"Folio-WinStore. Local version"
Like all previous software, it does not require any special skills or abilities from users. Only enough know how the Windows operating system works. As for the set of functions, the list is standard.
"Microinvest Warehouse Pro version"
Under the terminology there is a kind of industry solution, which acts as an automated system of “network” retail units (for example, counter sales or self-service stores), warehouses and even restaurants.
BY meets all necessary requirements regarding the movement of product resources within a company or in an entire network that is directly related to commercial or industrial work activities.
"Warehouse +"
Very convenient and at the same time comfortable, which includes most needed features, namely:
- development of incoming and outgoing documentation;
- printing of invoices, invoices, invoices and the receipt order itself:
- carrying out calculations of the cost of sales with the specified coefficients relative to the purchase price.
The program allows you to carry out warehouse accounting without any special skills.
"1c accounting"
Essentially this is universal software, which is aimed at automating both accounting and tax accounting, including the possible preparation of all mandatory reports.
We can say with all confidence that this is a ready-made solution for accounting in a company that conducts any type of work activity.
Besides, Individual entrepreneurs are allowed to keep records, which are on a simplified taxation system.
Standard software for inventory control allows to fully any company, regardless of its type of work activity and form of ownership, can implement an impressive list of solutions.
Using programs can be significantly simplified the work of not only the person in charge, but also the entire accounting department as a whole, whose responsibility is to monthly generate consolidated reports regarding the movement of inventory items.
Each and every program developed to account for products in a warehouse has a huge number of functions, which are able to assist the responsible person to carry out accounting for each individual unit of goods, namely:
- keep records regarding the receipt of goods and materials;
- keep records regarding product costs;
- keep records of inventory balances;
- generate turnover sheets, various internal reports and other documentation;
- automatically carry out activities regarding the display of balances for each individual product;
- automatically re-evaluate inventory items, which are directly related to changes in the company’s pricing policy;
- keep records of defective goods;
- automatically detect surpluses or shortages of goods and materials;
- keep records of product margins, including the cost of production;
- keep records of settlements with direct suppliers and consumers.
Such software can easily integrate with the most modern, which often used in various domestic companies.
Advantages and disadvantages of free versions
Regardless of which free program is used, all of them, without exception, have a huge number of advantages, which are as follows:
If necessary, this list can be continued indefinitely.
If speak about shortcomings free programs, we can highlight the following:
- minimum set of required functions;
- It may happen that the interface will be entirely in English;
- lack of any important functional features, for example, it is impossible to print.
In addition, free versions of programs can only work in the demo version. In other words, they work only for a limited number of days intended for evaluation, and after that an overpayment must be processed.
In addition, when working with free software, problems may arise with regulatory authorities, since they are often “broken” and do not have a license, which is mandatory for tax inspectors.
Installation of the SuperWarehouse program is presented in this manual.
