Accounting for goods in stock in excel example. Inventory control in Excel is a program without macros and programming. Installation and launch

Today there is a large selection of specialized tools for inventory management: from simple free programs to expensive full-featured WMS systems. Despite this, many entrepreneurs initially choose Excel spreadsheets for inventory management. At first, this seems like an affordable and convenient solution, but as the number of orders in your organization grows, using Excel for inventory tracking is fraught with many problems. The more warehouses, the more commodity items, the higher the probability of their appearance. What difficulties do users face when working with Excel?

• Data entry errors. Due to a single input error, it becomes necessary to double-check all the data in the tables.
• The inconvenience of collaboration. Often a situation arises when, when editing data by one user, they are overwritten by another. Thus, accounting in Excel may not be correct.
• Performing many manual operations. Excel is not designed as a warehouse management solution, the program combines many basic functions, and therefore often you will have to spend time on monotonous manual labor.
• Difficulty in validating data. When using Excel, a lot of effort goes into checking the correctness of the entered data and bringing the information into the appropriate form. If you have a large warehouse, it is better to download Excel for other tasks, and keep inventory records using more advanced programs.

These problems may not occur so often, however, they take a lot of work time. Warehouse program in Excel Corrections of errors and typos, lengthy checking of the correctness of stock data and forced manual labor when working with Excel are the main reasons why entrepreneurs eventually come to use specialized warehouse accounting solutions. Even experienced professionals who know how to keep inventory in Excel still choose other Excel-based programs or completely new solutions.

How to keep inventory records in Excel?

Whether you choose Excel spreadsheets, MySklad or another specialized retail solution, there are a few basic principles to follow. If you download the program and do not use special rules, over time, work with accounting will become more complicated at times:

1. Correct design of reference books. A directory with unmarked goods creates guaranteed accounting problems, inconvenience to the seller and difficulties in reporting. When entering data, it is necessary to fill in as much information as possible in various cells (for example, codes, articles, prices, VAT). If the inventory of the sold goods in the warehouse is carried out using the MySklad service, all the necessary data on the goods are automatically inserted into the documents, which saves a lot of time when processing orders.
2. Entering initial balances in quantitative and total terms. Correct data at the beginning of the work will give adequate reports in the process of work, and those who make sales from a “clean slate” should not count on this.
3. Compliance with chronology in the preparation of documents. It is necessary to register the arrival of goods at the warehouse from the supplier strictly before it is shipped to the buyer. Otherwise, the cost price will “go” and you will not be able to accurately calculate how much you have earned. In MySklad for each purchase and sale of goods, you can specify an arbitrary date.
4. Structured storage of additional information. When maintaining warehouse records, it is important to collect additional information about sales (for example, method of delivery and payment, date of shipment, customer name, manager name) in separate fields. All this data will be useful in the future for compiling route sheets for couriers, calculating salaries for managers, and analyzing statistics. In MySklad, you can easily create the required number of free fields for use in directories and documents without programming.

Users of the Internet service MySklad have access to free support by phone and e-mail. Support staff not only help with the program, but also share tips on optimal inventory management and setting up basic business processes. Download the free version and try our new solutions.

Excel can be an excellent inventory management tool for any manufacturing or trading company that keeps records of materials, finished products, and raw materials used. Spreadsheets can help with this difficult task.

Where can spreadsheets be used? Large organizations for these purposes acquire ready-made solutions in electronic format. However, such solutions are quite expensive. Some complex programs even require the involvement of a qualified employee with a fairly high salary to work in the warehouse. Small start-up companies can't do that. But there is a way out: you can use Excel spreadsheets to keep inventory records. This electronic tool is second in popularity only to the Word office program. The functionality of Microsoft Excel will be quite enough to solve the basic tasks of warehouse accounting.

Important Rules

Users who are interested in issues related to inventory management should seriously approach the issue of creating their own computer program from the very beginning. In doing so, you must adhere to the following important rules. Initially, all directories should be created as detailed and accurate as possible. In particular, one should not be limited to a simple indication of the names of goods. It is also necessary to indicate articles, expiration dates, codes, and so on. Typically, opening balances are entered into tables in monetary terms. It is necessary to follow the chronology and enter information about the receipt of certain goods at the warehouse earlier than about their shipment to the buyer. Before filling in Excel spreadsheets, it is imperative to perform an inventory. It is necessary to foresee what additional information may be required and enter it so that in the future you do not have to clarify the information for each of the goods.

Before you start developing spreadsheets to support the operation of your warehouse, you need to consider its specifics. In this case, the following general recommendations can be made:

1. It is necessary to draw up directories "Suppliers", "Buyers" and "Points of accounting for goods". The last point is optional for small companies.
2. When using a relatively constant list of products, it is recommended to create the nomenclature in the form of a database on a separate sheet. Expense, income and reports in the future are required to be filled out with links to it.
3. The Excel sheet with the heading "Nomenclature" should contain the following data: product name, product groups, product codes, units of measure, and so on.
4. The report is generated using the PivotTable tool.
5. The receipt of goods at the warehouse is recorded on the "Incoming" sheet.
6. To track the current state, you need to create sheets "Balances" and "Expenses".

Creating directories

In order to develop a warehouse accounting program in Excel, you need to create a file with any name. Its name may sound, for example, "Warehouse". Now you need to fill in the directories. In order for the headings not to “run across”, you need to fix them. To do this, on the "View" tab in the Excel program, click on the "Freeze Panes" button. The table "Customers" looks approximately the same. A free program may well provide a convenient and partially automated warehouse accounting if you create an auxiliary directory of goods release points in it. However, it is required only if the company has several outlets or warehouses. As for organizations that have only one distribution point, it makes no sense for them to create such a directory.

Own program "Warehouse": how to create a sheet "Incoming"

First of all, you will need to create a table for the nomenclature. Its headings should look like this: "Product name", "Variety", "Unit", "Characteristic", "Comment". After that, you need to select the range of this table. In the "Name" field, which is located directly above the cell with the name "A", you must enter the word "Table 1". Do the same with the corresponding range on the "Suppliers" sheet. In this case, you need to specify "Table 2". Incoming and outgoing transactions are recorded on two separate sheets. They allow you to keep inventory records in Excel.

Accounting automation

It is possible to make warehouse accounting in Excel more convenient if the user chooses a supplier from a ready-made list, and also chooses a point of account and a product name. The supplier's code and unit of measure should be automatically displayed in the table without the participation of an operator. Date, cost, invoice number and quantity are entered manually. The Warehouse program will automatically calculate the cost using mathematical formulas. All directories for this need to be formatted as a table. For the "Name" column, you need to create a drop-down list. To do this, select the entire column except for the header, find the "Data" tab and click on the "Data Validation" icon. In the field "Data Type" you need to find the word "List". In the "Source" field, you need to specify the function "=INDIRECT("item!$A$4:$A$8"). Opposite the items "Ignore empty cells" and "List of valid values" must be checked. If everything is done as a rule, then when filling out the first column, you can simply select the name of the product from the list. In the column "Unit. rev.» the corresponding value will appear. About the same way, auto-completion is created for the "Supplier" and "Code" columns, as well as a drop-down list. To fill in the "Cost" column, you must use the multiplication formula. It should look like this: "=price * quantity". You also need to create a drop-down list called "Accounting points". It will indicate the place where the incoming goods were sent. This is done in exactly the same way as in the previous cases.

"Turnover sheet"

Now that you have practically created a convenient tool that allows your company to keep inventory records in Excel for free, all that remains is to teach the program to correctly display the report. To do this, you need to start working with the corresponding table. We set zeros at the beginning of the time period, since we are only going to keep inventory records. If we have already carried it out earlier, then this column should display the balances. Units of measurement and names of goods in this case will have to be taken from the nomenclature. To facilitate inventory control, Excel will need to fill in the Receipts and Shipments columns using the SUMIFS function. To account for the balance of goods in the warehouse, mathematical operators should be used. Over time, you can independently make adjustments to the Warehouse program to make the accounting of your products as convenient as possible.

However, when filling out incoming or outgoing documents, some values ​​\u200b\u200bwill still have to be scored by hand.

These include:

• document date;
• quantity of goods;
• Document Number;
• other frequently changing parameters.

Using the tools built into Excel, you can achieve automatic filling of several columns based on the reference data. However, it is unlikely that you will be able to configure this functionality yourself without deep knowledge of the program.

You can add elementary formulas yourself, for example, specify a column with the total cost of goods by multiplying the quantity by the price.

In the above way, the sheets “Income” and “Expense” are also formed. Their separate maintenance is convenient for the subsequent quick search for the desired document.

Formation of the turnover sheet

You can make a turnover sheet yourself, but you will have to understand some of the functions of the spreadsheet editor. Programming skills are not required here.

On a separate sheet, you can draw up information about product balances in order to understand the need to purchase a particular item in the assortment.

Tracking Critical Remains

Keeping records of goods in the store in Excel can be configured in such a way that information is displayed on the sheet with product balances about the need to purchase a particular item in the assortment. Next, we will consider the simplest example of how to arrange this in a spreadsheet editor.

In the proposed example, there are three storage locations for goods, indicating the balances in each of them. Using the IF(OR…) function, you can set up an automatic check of stock quota compliance for each warehouse. The final formula will look like this:

IF(OR(C3<3;D3<3;E3<3);«Необходимо пополнение склада»;«Товара достаточно»).

Thus, if the balance in any storage location falls below three units, the user will see a message about the need to purchase or internally move the goods. In the same way, you can monitor the reverse process - packing with a certain nomenclature position. The formula used is very simple and can always be added to a finished spreadsheet.

The specified method of monitoring values ​​is applicable not only to warehouse balances. Similarly, the following indicators can be analyzed:

• volumes of work performed or sales by employees;
• increase in gross turnover or profit;
• identification of trade declines in certain groups of goods and other indicators.

Excel can provide entrepreneurs with many analytical tools when accounting for retail sales. One of its difficulties lies in the small amount of professional training material and the need to independently compose all the formulas.

Advantages of accounting for goods in a store in Excel

Try all the features of the EKAM platform for free

Read also

Privacy agreement

and processing of personal data

1. General Provisions

1.1. This agreement on confidentiality and processing of personal data (hereinafter referred to as the Agreement) is accepted freely and of its own free will, applies to all information that Insales Rus LLC and / or its affiliates, including all persons belonging to the same group with LLC "Insales Rus" (including "EKAM service" LLC) may receive about the User while using any of the sites, services, services, computer programs, products or services of "Insales Rus" LLC (hereinafter referred to as the "Services") and in during the execution of Insales Rus LLC of any agreements and contracts with the User. The User's consent to the Agreement, expressed by him in the framework of relations with one of the listed persons, applies to all other listed persons.

1.2. The use of the Services means the User's consent to this Agreement and the conditions specified therein; in case of disagreement with these conditions, the User must refrain from using the Services.

"Insales"- Limited Liability Company "Insales Rus", PSRN 1117746506514, TIN 7714843760, KPP 771401001, registered at the address: 125319, Moscow, Akademika Ilyushin St., 4, building 1, office 11 (hereinafter referred to as "Insales" ), on the one hand, and

"User" -

or an individual who has legal capacity and is recognized as a participant in civil legal relations in accordance with the legislation of the Russian Federation;

or a legal entity registered in accordance with the laws of the state of which such entity is a resident;

or an individual entrepreneur registered in accordance with the legislation of the state of which such person is a resident;

which has accepted the terms of this Agreement.

1.4. For the purposes of this Agreement, the Parties have determined that confidential information is information of any nature (production, technical, economic, organizational and others), including the results of intellectual activity, as well as information about the methods of carrying out professional activities (including, but not limited to: information about products, works and services; information about technologies and research works; data on technical systems and equipment, including software elements; business forecasts and information about proposed purchases; requirements and specifications of specific partners and potential partners; information, relating to intellectual property, as well as plans and technologies related to all of the above) communicated by one party to the other party in written and / or electronic form, expressly designated by the Party as its confidential information.

1.5. The purpose of this Agreement is to protect confidential information that the Parties will exchange during negotiations, conclusion of contracts and fulfillment of obligations, as well as any other interaction (including, but not limited to, consulting, requesting and providing information, and performing other assignments).

2.Obligations of the Parties

2.1. The Parties agree to keep secret all confidential information received by one Party from the other Party during the interaction of the Parties, not to disclose, disclose, make public or otherwise provide such information to any third party without the prior written permission of the other Party, with the exception of cases specified in the current legislation, when the provision of such information is the responsibility of the Parties.

2.2. Each Party will take all necessary measures to protect confidential information at least with the same measures that the Party applies to protect its own confidential information. Access to confidential information is provided only to those employees of each of the Parties who reasonably need it to perform their official duties for the implementation of this Agreement.

2.3. The obligation to keep secret confidential information is valid within the term of this Agreement, the license agreement for computer programs dated 01.12.2016, the agreement of accession to the license agreement for computer programs, agency and other agreements and within five years after termination their actions, unless otherwise agreed by the Parties.

(a) if the information provided has become publicly available without violating the obligations of one of the Parties;

(b) if the information provided became known to the Party as a result of its own research, systematic observations or other activities carried out without the use of confidential information received from the other Party;

(c) if the information provided is lawfully obtained from a third party without an obligation to keep it secret until it is provided by one of the Parties;

(d) if the information is provided at the written request of a state authority, other state body, or local government in order to perform their functions and its disclosure to these bodies is mandatory for the Party. In this case, the Party must immediately notify the other Party of the request received;

(e) if the information is provided to a third party with the consent of the Party about which the information is being transferred.

2.5. Insales does not verify the accuracy of the information provided by the User, and is not able to assess its legal capacity.

2.6. The information that the User provides to Insales when registering in the Services is not personal data, as they are defined in the Federal Law of the Russian Federation No. 152-FZ of July 27, 2006. "About personal data".

2.7. Insales has the right to make changes to this Agreement. When making changes in the current version, the date of the last update is indicated. The new version of the Agreement comes into force from the moment of its placement, unless otherwise provided by the new version of the Agreement.

2.8. By accepting this Agreement, the User acknowledges and agrees that Insales may send personalized messages and information to the User (including, but not limited to) to improve the quality of the Services, to develop new products, to create and send personal offers to the User, to inform the User about changes in the Tariff plans and updates, to send marketing materials to the User on the subject of the Services, to protect the Services and Users and for other purposes.

The user has the right to refuse to receive the above information by notifying in writing to the e-mail address Insales - .

2.9. By accepting this Agreement, the User acknowledges and agrees that the Insales Services may use cookies, counters, other technologies to ensure the operation of the Services in general or their individual functions in particular, and the User has no claims against Insales in connection with this.

2.10. The user is aware that the equipment and software used by him to visit sites on the Internet may have the function of prohibiting operations with cookies (for any sites or for certain sites), as well as deleting previously received cookies.

Insales has the right to determine that the provision of a certain Service is possible only if the acceptance and receipt of cookies is allowed by the User.

2.11. The user is solely responsible for the security of the means chosen by him to access the account, and also independently ensures their confidentiality. The User is solely responsible for all actions (as well as their consequences) within or using the Services under the User's account, including cases of voluntary transfer by the User of data to access the User's account to third parties on any terms (including under contracts or agreements) . At the same time, all actions within or using the Services under the User's account are considered to be performed by the User, except for cases when the User notified Insales about unauthorized access to the Services using the User's account and / or about any violation (suspicions of violation) of the confidentiality of their account access.

2.12. The User is obliged to immediately notify Insales of any case of unauthorized (not authorized by the User) access to the Services using the User's account and / or any violation (suspicion of violation) of the confidentiality of their means of access to the account. For security purposes, the User is obliged to independently carry out a safe shutdown of work under his account at the end of each session of work with the Services. Insales is not responsible for the possible loss or corruption of data, as well as other consequences of any nature that may occur due to the violation by the User of the provisions of this part of the Agreement.

3. Responsibility of the Parties

3.1. The Party that has violated the obligations stipulated by the Agreement regarding the protection of confidential information transmitted under the Agreement is obliged to compensate, at the request of the affected Party, the real damage caused by such a violation of the terms of the Agreement in accordance with the current legislation of the Russian Federation.

3.2. Compensation for damages does not terminate the obligations of the violating Party for the proper performance of obligations under the Agreement.

4.Other provisions

4.1. All notices, requests, demands and other correspondence under this Agreement, including those including confidential information, must be made in writing and delivered personally or through a courier, or sent by e-mail to the addresses specified in the license agreement for computer programs dated December 01, 2016, the agreement of accession to the license agreement for computer programs and in this Agreement or other addresses that may be specified in writing by the Party in the future.

4.2. If one or more provisions (conditions) of this Agreement are or become invalid, this cannot serve as a reason for termination of other provisions (conditions).

4.3. The law of the Russian Federation shall apply to this Agreement and the relationship between the User and Insales arising in connection with the application of the Agreement.

4.3. The User has the right to send all suggestions or questions regarding this Agreement to the Insales User Support Service or to the postal address: 107078, Moscow, st. Novoryazanskaya, 18, pp. 11-12 BC "Stendhal" LLC "Insales Rus".

Publication date: 01.12.2016

Full name in Russian:

Limited Liability Company "Insales Rus"

Abbreviated name in Russian:

Insales Rus LLC

Name in English:

InSales Rus Limited Liability Company (InSales Rus LLC)

Legal address:

125319, Moscow, st. Academician Ilyushin, 4, building 1, office 11

Mailing address:

107078, Moscow, st. Novoryazanskaya, 18, building 11-12, BC "Stendhal"

TIN: 7714843760 KPP: 771401001

Bank details:

The program is designed for material accounting of various types of resources. It is possible to implement accounting in value terms. Supports multiple storage locations, production consumption, and scrapping. Reporting forms allow you to track the movement of resources and receive operational information for each accounting point.

Introduction

The program can be used as the simplest warehouse accounting system for resource storage locations at enterprises using a small range of goods, raw materials and materials. The account is kept in natural units of measure. Cost accounting in automatic mode is not implemented, but it is possible to manually enter indicators in monetary units.

The initial data for generating reports are three types of tables:

• Options
• Reference books
• Magazines

At the same time, the reports themselves are also Excel tables.

The openness of the spreadsheet interface provides maximum calculation flexibility. The user has access to all the functions of a powerful computer and the Excel interface. Features of the program implementation require self-configuration of logs and reports for specific user tasks.

Changes in version 2.6

• Starting with version 2.6, the old XLS workbook format is not supported. The program file is saved in the format XLSX/XLSM and works in Excel starting from version 2007 ( 2007-2016 ). Program in format XLS available only in version 2.0, and can only be purchased by special request.
• Workbooks have been updated to match the design of the latest versions of Microsoft Office.
• The second summary report on the places of storage.
• The default view of the first report has been converted to allow copying and pasting balances at the beginning of a new period.
• Drop-down list for searching reference items by the first letters of the word. Activated by double clicking on the cell.
• Highlighting in red the elements of journals not found in the directories.

Changes in version 2.0

• Accounting points and counterparties are divided into different directories.
• The limit on the number of suppliers and buyers has been removed.
• Added resource groups for filtering in reports.
• Initial balances are filled in a separate journal.
• Removed cost indicators in magazines, added an indicator of the average price in directories.
• New report on storage locations in the form of an Excel pivot table.

Opportunities and limitations

Excel spreadsheets have internal limits on the amount of information processed. The program is not designed to replace large accounting systems for hundreds of thousands of stock items. But in most SMBs, the volume and complexity of data is available to Excel for processing.

The main file does not contain a large number of calculations; special algorithms are used for reports, which also do not have formulaic links with the source data. Therefore, large amounts of data provide normal performance even in the automatic calculation mode of Excel. Switching to manual calculation mode is recommended only on older computers.

AT Excel automatic calculation mode We recommend the following settings when working on high performance computer:

• Number of resources (nomenclature) - up to 2000
• Number of metering points - up to 200
• Number of operations (lines) in each journal - up to 10,000
• File size (xlsx) - up to 10Mb

AT manual calculation mode Excel the limitations are mainly related only to the capabilities of the computer. For the normal operation of reports, we recommend the following restrictions:

• Number of resources (nomenclature) - up to 5000
• Number of metering points - up to 500
• Number of transactions (lines) in each journal - up to 30,000
• File size (xlsx) - up to 25Mb

In the case of using additional calculations (full openness of the program allows you to configure any additional calculations), the performance restrictions may be more severe.

To reduce data, you can create a new file at any time and transfer directories and opening balances for all accounting points there.

Installation and launch

The composition of the file is described on the initial page of the program - "Main".

As already noted, the program consists of parameter tables, directories, logs and reports. These objects can be accessed via hyperlinks on the Home page. You can also use the standard ways to navigate Excel sheets and cells.

The easiest way to return to the page with the content is by pressing a special button in the title of any worksheet of the program

The possibilities of moving through the pages are described on the service sheet. preset and in the hidden third column of the "Home" sheet.

Options

The program contains several general parameters that control performance characteristics:

All parameters can be changed at the discretion of the user.

Reference books

Reference books are used for calculations and make it easier to enter data into journals (through selection from lists). Data entry into reference books is carried out as information becomes available.

Buyers and Suppliers

Lists of accounting counterparties. It is allowed to add rows down without restrictions.

List of resource storage locations participating in the accounting system. These can be separate storage cells, storage areas, production sites or premises. It is allowed to add rows down without restrictions.

Groups and Resources

Two interconnected directories for describing the resources of all types stored in accounting points. Resources include: finished products, semi-finished products, raw materials and materials. Grouping is used for the convenience of filtering reports. It is allowed to add rows down without restrictions.

Table fields (all available for editing):

Activity logs

All warehouse transactions required for generating reports are entered in special tables, divided by types of transactions.

Data on stocks at the beginning of accounting. The date is defined in the Parameters.

Arrival from suppliers

Receipts of resources from suppliers are entered into the table. The sequence by date or order number is not important.

Table fields:

header	Description	Input Accessibility	obligatory
the date	Date of operation.	Yes	Yes (start input)
Vendor order code	Arbitrary transaction code.	Yes	Not
Resource	The name of the resource coming from the provider.	List	Yes
Stock		List	Yes
Volume / Quantity		Yes	Yes
EI	The unit of measure for the resource.	Not	Computed
Comment		Yes	Not

the date

The table contains information about the consumption of raw materials and materials in the production process. The sequence by date or order number is not important. For a trade organization, this table is not required to be filled out.

Table fields:

header	Description	Input Accessibility	obligatory
the date	Date of operation. Be sure to start entering new data in this field.	Yes	Yes (start input)
Production order code	Arbitrary transaction code.	Yes	Not
Warehouse for the consumption of raw materials and materials	Accounting point from which the consumption (write-off) of the resource occurs	List	Yes
Raw materials	The name of the resource consumed in the production process.	List	Yes
Volume / Quantity	The amount of the resource in natural units of measurement.	Yes	Yes
EI	The unit of measure for the resource.	Not	Computed
Amount accrued.	The amount of the transaction in the base currency.	Yes	Computed
Comment	Arbitrary comment to the operation.	Yes	Not

In addition to the described fields, the table contains several special service columns.

To delete an entry, simply clear the field. the date using standard Excel tools. It is also possible to delete entire rows.

The table contains information about the production of finished products and semi-finished products. The sequence by date or order number is not important. For a trade organization, this table is not required to be filled out.

Table fields:

header	Description	Input Accessibility	obligatory
the date	Date of operation. Be sure to start entering new data in this field.	Yes	Yes (start input)
Production order code	Arbitrary transaction code.	Yes	Not
Products	The name of the resource formed during the production process.	List	Yes
Stock	The accounting point to which the resource is received from production.	List	Yes
Volume / Quantity	The amount of the resource in natural units of measurement.	Yes	Yes
EI	The unit of measure for the resource.	Not	Computed
Comment	Arbitrary comment to the operation.	Yes	Not

In addition to the described fields, the table contains several special service columns.

To delete an entry, simply clear the field. the date using standard Excel tools. It is also possible to delete entire rows.

Transactions of moving resources between internal warehouses are entered into the table. The sequence by date or order number is not important. This also includes the initial balances stored in warehouses at the start date of accounting.

Table fields:

header	Description	Input Accessibility	obligatory
the date	Date of operation. Be sure to start entering new data in this field.	Yes	Yes (start input)
Order code	Arbitrary transaction code.	Yes	Not
source warehouse	The accounting point from which the resource is moved. Not filled when entering initial balances.	List	Yes (except for initial balances)
Resource	The name of the resource being moved.	List	Yes
Receiving warehouse	The accounting point to which the resource is received.	List	Yes
Volume / Quantity	The amount of the resource in natural units of measurement.	Yes	Yes
EI	The unit of measure for the resource.	Not	Computed

Warehouse accounting in Excel is suitable for any trade or manufacturing organization where it is important to take into account the amount of raw materials and finished products. To this end, the company maintains inventory records. Large firms, as a rule, purchase ready-made solutions for keeping records in electronic form. Today, there are a lot of options for various activities.

Small businesses control the movement of goods on their own. For this purpose, you can use Excel spreadsheets. The functionality of this tool is quite enough. Let's get acquainted with some of the possibilities and independently 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 custom warehouse management solution, whether built on your own or purchased, will only work well if the basic rules are followed. If you neglect these principles at the beginning, then later the work will become more complicated.

1. Fill in the handbooks as accurately and as accurately as possible. If this is a nomenclature of goods, 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.
2. Initial balances are entered in quantitative and monetary terms. It makes sense to conduct an inventory before filling in the relevant tables.
3. Observe chronology in the registration of transactions. Data on the receipt of products at the warehouse should be entered earlier than on the shipment of goods to the buyer.
4. Don't skimp on additional information. To draw up a route sheet, the driver needs the date of shipment and the name of the customer. For accounting, the method of payment. Every organization has its own characteristics. A number of data entered into the warehouse accounting program in Excel will be useful for statistical reports, payroll for specialists, etc.

It is impossible to unequivocally answer the question of how to keep inventory records in Excel. It is necessary to take into account the specifics of a particular enterprise, warehouse, goods. But we can draw general recommendations:

1. For correct warehouse accounting in Excel, you need to create directories. They can take 1-3 sheets. This is the directory "Suppliers", "Buyers", "Points of accounting for goods." In a small organization, where there are not so many counterparties, directories are not needed. It is also not necessary to compile a list of goods accounting points if the enterprise has only one warehouse and / or one store.
2. With a relatively constant list of products, it makes sense to make the product range in the form of a database. Subsequently, income, expenses and reports should be filled in with references to the nomenclature. The "Nomenclature" sheet may contain the name of the product, product groups, product codes, units of measurement, etc.
3. The receipt of goods at the warehouse is recorded on the "Incoming" sheet. Retirement - "Expense". The current state is “Remainders” (“Reserve”).
4. Results, the report is generated using the PivotTable tool.

So that the headings of each warehouse accounting table do not run 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 entries, the user will see the column headings.



Excel table "Warehouse accounting"

Let's look at an example of how the warehouse accounting program in Excel should work.

We make Handbooks.

For supplier data:

* Shape may vary.

For customer data:

* Please note: the title bar is pinned. Therefore, you can enter as much data as you like. The column names will be visible.

For auditing points of issue of goods:

Once again, it makes sense to create such directories if the enterprise is large or medium.

You can make a product range on a separate sheet:

In this example, we will use drop-down lists in the table for warehouse accounting. Therefore, Directories and Nomenclature are needed: we will make references to them.

Name the range of the "Nomenclature" table: "Table1". To do this, select the range of the table and enter the appropriate value in the name field (opposite the formula bar). You also need to assign a name: "Table2" to the range of the "Suppliers" table. This will allow you to conveniently refer to their values.

To fix income and expenditure transactions, we fill out two separate sheets.

We make a hat for the "Arrival":

Next stage - table automation! It is necessary to make it so that the user selects the name of the product, supplier, accounting point from the ready list. The vendor code and unit of measure should be displayed automatically. Date, invoice number, quantity and price are entered manually. Excel calculates the cost.

Let's start solving the problem. First, let's format all directories as tables. This is necessary so that later you can add something, change it.

Create a dropdown list for the Name column. Select a column (without a header). Go to the "Data" tab - the "Data Validation" tool.

In the "Data type" field, select "List". An additional field "Source" immediately appears. To make the values ​​for the drop-down list taken from another sheet, use the function: =INDIRECT("item!$A$4:$A$8").

Now, when filling in the first column of the table, you can select the name of the product from the list.

Automatically in the column "Unit. rev.» the corresponding value should appear. We will do it using the VLOOKUP and UND functions (it will suppress the error as a result of the VLOOKUP function when referring to an empty cell in the first column). Formula: .

By the same principle, we make a drop-down list and autocomplete for the "Supplier" and "Code" columns.

We also form a drop-down list for the "Accounting point" - where the incoming goods were sent. To fill in the "Cost" column, we use the multiplication formula (= price * quantity).

We form the table "Consumption of goods".

Drop-down lists are used in the columns "Name", "Point of accounting for shipment, delivery", "Buyer". Units of measurement and cost are filled in automatically using formulas.

We make a "turnover sheet" ("Results").

We set zeros at the beginning of the period, because warehouse accounting is just beginning to be conducted. If previously conducted, then in this column there will be balances. Names and units of measurement are taken from the nomenclature of goods.

The columns "Receipt" and "Shipments" are filled using the SUMIFS function. The remainders are calculated using mathematical operators.

Download the warehouse accounting program (a ready-made example compiled according to the above described scheme).

Here is the completed program.