Warehouse accounting in Excel. A simple program for automating warehouse accounting. Inventory accounting in an alternative to excel

Confidentiality 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) was accepted freely and of its own free will, and applies to all information that Insales Rus LLC and/or its affiliates, including all persons included in the same group with LLC "Insails Rus" (including LLC "EKAM service") can obtain information about the User while using any of the sites, services, services, computer programs, products or services of LLC "Insails Rus" (hereinafter referred to as the Services) and in during the execution of Insales Rus LLC any agreements and contracts with the User. The User's consent to the Agreement, expressed by him within the framework of relations with one of the listed persons, applies to all other listed persons.

1.2.Use of the Services means the User agrees with this Agreement and the terms and conditions specified therein; in case of disagreement with these terms, the User must refrain from using the Services.

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

"User" -

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

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

or individual entrepreneur registered in accordance with the laws 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 methods of implementation professional activity(including, but not limited to: information about products, works and services; information about technologies and research works; information about technical systems and equipment, including elements software; business forecasts and information about proposed purchases; requirements and specifications of specific partners and potential partners; information related to intellectual property, as well as plans and technologies related to all of the above) communicated by one party to the other in writing and/or electronic form, clearly 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, concluding contracts and fulfilling obligations, as well as any other interaction (including, but not limited to, consulting, requesting and providing information, and performing other instructions).

2. Responsibilities of the Parties

2.1.The parties agree to keep all confidential information received by one Party from the other Party during the interaction of the Parties, not disclose, disclose, make public or otherwise provide such information to any third party without the prior written permission of the other Party, except for the 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 using at least the same measures that the Party uses to protect its own confidential information. Access to confidential information is provided only to those employees of each Party who reasonably need it to perform their official duties under this Agreement.

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

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

(b) if the information provided became known to a 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 received 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 government authority, other government agency, or body 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 received request;

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

2.5.Insales does not verify the accuracy of the information provided by the User and does not have the ability to assess his legal capacity.

2.6.The information that the User provides to Insales when registering in the Services is not personal data as defined in Federal law RF No. 152-FZ dated July 27, 2006. “About personal data.”

2.7.Insales has the right to make changes to this Agreement. When changes are made to the current edition, the date is indicated last update. The new version of the Agreement comes into force from the moment it is posted, unless otherwise provided by the new version of the Agreement.

2.8. By accepting this Agreement, the User understands and agrees that Insales may send the User personalized messages and information (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 Tariff plans and updates, to send the User marketing materials 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 email address Insales -.

2.9. By accepting this Agreement, the User understands and agrees that Insales Services may use cookies, counters, and other technologies to ensure the functionality 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 understands 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 establish that the provision of a certain Service is possible only on the condition that the acceptance and receipt of cookies is permitted by the User.

2.11. The user is independently responsible for the security of the means he has chosen to access his 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 account the User, including cases of voluntary transfer by the User of data to access the User’s account to third parties under any conditions (including under contracts or agreements). In this case, all actions within or using the Services under the User’s account are considered to be carried out by the User himself, except in cases where the User notified Insales about unauthorized access to the Services using the User’s account and/or about any violation (suspicion of violation) of the confidentiality of their means of access to the account.

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 of 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 safely shut down work under his account at the end of each session of working with the Services. Insales is not responsible for possible loss or damage to data, as well as other consequences of any nature that may occur due to the User’s violation 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 transferred under the Agreement is obliged, at the request of the injured Party, to compensate for the actual damage caused by such violation of the terms of the Agreement in accordance with the current legislation of the Russian Federation.

3.2. Compensation for damage does not terminate the obligations of the violating Party to properly fulfill its 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 in writing and delivered personally or via courier, or sent by email to the addresses specified in the license agreement for computer programs dated December 1, 2016, the accession agreement to the license agreement for computer programs and in this Agreement or other addresses that may subsequently be specified in writing by the Party.

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

4.3. This Agreement and the relationship between the User and Insales arising in connection with the application of the Agreement are subject to the law of the Russian Federation.

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

Publication date: 12/01/2016

Full name in Russian:

Limited Liability Company "Insales Rus"

Abbreviated name in Russian:

LLC "Insales Rus"

Name in English:

InSales Rus Limited Liability Company (InSales Rus LLC)

Legal address:

125319, Moscow, st. Akademika Ilyushina, 4, building 1, office 11

Postal address:

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

INN: 7714843760 Checkpoint: 771401001

Bank details:

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 accounting in electronic form. 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 your own program warehouse accounting 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 inventory 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 be disdainful 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 will be 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 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.

Can be done on separate sheet product range:

IN in this example In the table for warehouse accounting we will use drop-down lists. Therefore, Directories and Nomenclature are needed: 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 range of the table "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”:

The next stage is automation of table filling! We need to make sure that the user chooses from ready list name of the product, supplier, point of accounting. 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 with VLOOKUP functions and UND (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 ( ready-made example compiled according to the scheme described above).

So the independently compiled program is ready.

However, when filling out receipts or expenditure documents, you will still have to enter some values by hand.

These include:

document date;
quantity of goods;
document number;
other frequently changing parameters.

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

You can add basic formulas yourself, for example, indicate a column with the total cost of the product, multiplying the quantity by the price.

In the above manner, the “Incoming” and “Expense” sheets are also generated. Keeping them separate is convenient for later quick search the required 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 table editor. No programming skills are required here.

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

Critical balance tracking

Keeping records of goods in a store in Excel can be configured in such a way that the sheet with product balances displays information about the need to purchase a particular item in the assortment. Next we will consider simplest example, how to arrange this in a table 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 configure automatic check compliance with stock standards 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 goods. In the same way, you can monitor the reverse process - packaging of a certain item. The formula used is very simple and can always be added to a ready-made table.

This method of monitoring values is applicable not only to warehouse balances. The following indicators can be analyzed similarly:

volumes of work performed or sales by employee;
increase in gross turnover or profit;
identifying declines in trade for certain groups of goods and other indicators.

Excel can provide entrepreneurs with a variety of 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 ECAM platform for free

Confidentiality Agreement

and processing of personal data

1.General provisions

"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 person is a resident;

or an individual entrepreneur registered in accordance with the laws of the state of which such a 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 technical systems and equipment, including business forecasts and information about proposed purchases; information about specific partners and potential partners; related to intellectual property, as well as plans and technologies related to all of the above) communicated by one party to the other in written and/or electronic form, expressly designated by the Party as its confidential information.

2. Responsibilities of the Parties

2.1. The Parties agree to keep confidential 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.3. The obligation to keep confidential information secret is valid within the validity period of this Agreement, the license agreement for computer programs dated December 1, 2016, the agreement to join the license agreement for computer programs, agency and other agreements and for five years after termination their actions, unless otherwise separately agreed by the Parties.

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

(c) if the information provided is lawfully received 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 government agency, other government agency, or local government body 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 received request;

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

2.5.Insales does not verify the accuracy of the information provided by the User and does not have the ability to assess his legal capacity.

2.6. The information that the User provides to Insales when registering in the Services is not personal data, as defined in 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 changes are made to the current edition, the date of the last update is indicated. The new version of the Agreement comes into force from the moment it is posted, unless otherwise provided by the new version of the Agreement.

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

Insales has the right to establish that the provision of a certain Service is possible only on the condition that the acceptance and receipt of cookies is permitted by the User.

2.11. The user is independently responsible for the security of the means he has chosen to access his 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 under any conditions (including under contracts or agreements) . In this case, all actions within or using the Services under the User’s account are considered to be carried out by the User himself, except in cases where the User notified Insales of unauthorized access to the Services using the User’s account and/or of any violation (suspicion of violation) of the confidentiality of their means of accessing your account.

3. Responsibility of the Parties

3.2. Compensation for damage does not terminate the obligations of the violating Party to properly fulfill its 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 in writing and delivered personally or via courier, or sent by email to the addresses specified in the license agreement for computer programs dated 12/01/2016, the agreement of accession to the license agreement for computer programs and in this Agreement or other addresses that may subsequently be specified in writing by the Party.

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

4.3. This Agreement and the relationship between the User and Insales arising in connection with the application of the Agreement are subject to the law of the Russian Federation.

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, building 11-12 BC “Stendhal” LLC “Insales Rus”.

Publication date: 12/01/2016

Full name in Russian:

Limited Liability Company "Insales Rus"

Abbreviated name in Russian:

LLC "Insales Rus"

Name in English:

InSales Rus Limited Liability Company (InSales Rus LLC)

Legal address:

125319, Moscow, st. Akademika Ilyushina, 4, building 1, office 11

Postal address:

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

INN: 7714843760 Checkpoint: 771401001

Bank details:

Most small and medium-sized commercial organizations solve the problems of accounting for material assets (goods or products) using the Microsoft Excel application. In this article we will look at an example on this topic from real practice. The completed automation of the Microsoft Excel workbook allows you to reduce the number of routine actions in office work, as well as provide control over possible user errors.

Let's consider a fairly standard situation when our organization is engaged in trade - the purchase of goods and their subsequent sale. In this case, it is necessary to keep accurate records of the nomenclature (quantity of goods, receipt and sales prices). Let's start our development with a sheet called Nomenclature(Fig. 2.16). This sheet plays the role of a register or directory of goods. Each line contains information about a specific product item (product name, receipt price, sales price, and quantity in stock). The purpose of software development is to create control sheets that will allow you to adjust the quantity of goods upon receipt or shipment, as well as make changes to the price of the goods. Overall, the automated ledger should provide a user-friendly interface for managers. This interface will be provided by two sheets - Admission And Shipment.

Direct work with the sheet shown in Fig. 2.16, undesirable. Of course, the manager can directly enter data into it when goods are received or sold. For example, when a new batch of “Comfort” walls arrives in the amount of 5 pieces at a new price of 25,770 rubles, you can simply adjust both the number of units in the warehouse and the arrival price. However, this is not very convenient, and is a “springboard” for possible errors on the part of the user. It is more convenient to create a control sheet (Fig. 2.17) with elements that provide a convenient interface. And the leaf itself Nomenclature further we will hide it (this feature is easily implemented using the Microsoft Excel application service), so that it will not be directly available to the user. Of course, after this you can introduce protection on the structure of the book (with a password). As a result, users can work with the sheet Nomenclature only using the interface implemented in other sheets.

When you receive a product that is already in stock, but at a new price, you should decide to recalculate the receipt price. For definiteness, we assume that in such a case the price of the last delivery is set as the price of receipt of goods.

Let's move on to a detailed examination of the sheet shown in Fig. 2.17, which allows the manager to conveniently enter a new arrival of an existing product, as well as add new product items.

The left half of the Receipts sheet is intended for new receipts of goods that are already available in the information base (on the sheet Nomenclature). To do this, the “Combo Box” control (located in the area of cell A3) is filled with the names of the products present on the sheet Nomenclature. This action is performed when opening a workbook (the text of the procedure required for this is given in Listing 2.21). The program code assumes that the property value Name combo boxes selected Spk.

"Listing 2.21. Procedure performed when opening a book Private Sub Workbook_Open() " Clearing the list Worksheets("Receipt").Spk.Clear " Counting the number of products in the infobase N = 0 While Worksheets("Item").Cells(N + 2, 1).Value<>"" N = N + 1 Wend " Filling the list For i = 1 To N Worksheets("Receipt").Spk.AddItem Worksheets(3).Cells(i + 1, 1).Value Next Worksheets("Receipt"). Spk.ListIndex = -1 End Sub

When the user selects the product name in the combo box, then in cell C5 on the sheet Admission the price is entered from the corresponding line of the sheet Nomenclature books. To do this, the procedure performed when you click on the Combo Box element should be formatted as shown in Listing 2.22. There is also nothing fundamentally new in it compared to the previously discussed examples.

" Listing 2.22. Procedure for handling a click on a combo box Private Sub Spk_Click() Range("C5").Value = Worksheets(3).Cells(Spk.ListIndex + 2, 2).Value Range("C6").Value = "" End Sub

Now the user must enter the number of units of new arrival of the specified product in cell C6. To increase security against accidental actions, we have added a password field on the sheet. This is an ordinary text window. Clicking a button Contribute will allow you to update the price and quantity of goods on the sheet Nomenclature only if the password is correct. Set the following properties for the text window:

Name - Pass;
PasswordChar - *.

This will ensure that “stars” are displayed when typing a password (the word typed by the user will be hidden from prying eyes). Thus, we have come to writing a procedure for entering information about a new arrival (an increase in the number of specified goods and a possible change in price) on the third sheet. Listing 2.23 shows the procedure that is executed when the button is clicked Contribute. The combination used here is 357.

"Listing 2.23. Handling a click on the Submit button Private Sub CommandButton1_Click() If Pass.Text = "327" Then "Updating the receipt price Worksheets("Item").Cells(Spk.ListIndex + 2, 2).Value = Range(" C5").Value " Correction of quantity Col = Range("C6").Value Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value = _ Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value + Col MsgBox "Данные внесены" Pass.Text = "" Else MsgBox "Ошибка пароля! Данные не внесены" End If End Sub!}

Here, when entering password 357, the procedure will add the user-entered quantity of goods to the third sheet and adjust the price. After this, an information message is displayed on the screen indicating that the data has been entered, and then the password field is cleared. If the password is incorrect, no changes are made to the sheet Nomenclature is not produced.

Let us now move on to examining the right part (unlike the left, it is highlighted in a darker shade) of Fig. 2.17. This fragment of the sheet must be used to enter a new product (indicating the quantity). Listing 2.24 shows a procedure that performs this functional action in this case.

" Listing 2.24. Handling a click on the Add a new product button Private Sub CommandButton_Click() " Counting the number of added products on the sheet Nomenclature N = 0 While Worksheets("Nomen").Cells(N + 2, 1).Value<>"" N = N + 1 Wend " Checking the password entered in the right text field If Pass2.Text = "35791" Then Worksheets("Nomenclature").Cells(N + 2, 1).Value = Range("G3"). Value Worksheets("Nomenclature").Cells(N + 2, 2).Value = Range("G4").Value Worksheets("Nomenclature").Cells(N + 2, 4).Value = Range("G5" ).Value MsgBox "Data entered" Pass2.Text = "" Else MsgBox "Password error! Data not entered" End If End Sub

On the right side of the sheet there are two controls - a button and a text box for the password. It would be possible to leave one password field for the left and right parts of the sheet. However, the option proposed here improves the functionality of the development. As a property value Name we chose Pass2. Let us formulate the procedure for user actions when working with the right side of the sheet:

the name of the new product is entered into cell G3;
cell G4 indicates the price;
cell G5 is reserved for the number of units of goods;
the user enters a password, and clicking on the Add a new product button fixes (if the password is correct) the entered information on the sheet Nomenclature.

At the beginning of the procedure CommandButton2_Click the number of cells with information about products on the sheet is counted (for this, sweep N is used) Nomenclature. After this, information about the new product is recorded in the next free line of the sheet.

Now let's move on to another sheet - Shipment(Fig. 2.18), which is used when selling goods. On a sheet Shipment There are three controls: a combo box (Name - Spk), a password field (Name - Pass3) and a button (Name - CommandButton1).

To fill out the list, you need to adjust the previously discussed procedure (Listing 2.21) Workbook_Open. It should now look like Listing 2.25.

"Listing 2.25. Procedure performed when opening a book (option 2) Private Sub Workbook_Open() Worksheets("Receipt").Spk.Clear Worksheets("Shipment").Spk.Clear " Counting existing items N = 0 While Worksheets( "nomen").Cells(N + 2, 1).Value<>"" N = N + 1 Wend " Filling lists For i = 1 To N Worksheets("Receipt").Spk.AddItem Worksheets(3).Cells(i + 1, 1).Value Worksheets("Shipment").Spk .AddItem Worksheets(3).Cells(i + 1, 1).Value Next Worksheets("Receipt").Spk.ListIndex = -1 Worksheets("Shipment").Spk.ListIndex = -1 End Sub

Next in line is the next procedure (Listing 2.26), which is executed when you click on the combo box. As a result, in the corresponding cells of the sheet Shipment the number of units of the specified product in the warehouse will be entered, as well as its selling price (Fig. 2.19).

"Listing 2.26. Procedure executed when a combo box is clicked Private Sub Spk_Click() Range("E6").Value = Worksheets("Nomenclature").Cells(Spk.ListIndex + 2, 4).Value Range("E7 ").Value = Worksheets("Nomenclature").Cells(Spk.ListIndex + 2, 3).Value End Sub

Now the user must indicate (in cell E6) how many units are being shipped (for guidance and control, we entered the available quantity of goods in the warehouse using the previous procedure). The procedure presented in Listing 2.27 (performed by clicking the Ship button) allows you to adjust the number of units of an item in stock in the database.

"Listing 2.27. Procedure executed when the Submit button is clicked Private Sub CommandButton1_Click() If Pass3.Text = "775" Then ColPrais = Worksheets("Nomenclature").Cells(Spk.ListIndex + 2, 4).Value Col = Range ("E6").Value If Col > ColPrais Then MsgBox "This quantity is not in stock" Exit Sub End If Worksheets("Nomenclature").Cells(Spk.ListIndex + 2, 3).Value = Range("E7") .Value ColPrais = ColPrais - Col Worksheets("Nomenclature").Cells(Spk.ListIndex + 2, 4).Value = ColPrais MsgBox "Information has been entered into the database" Pass.Text = "" Spk_Click Else MsgBox "Password error End!" If End Sub

After making changes, the information on the screen will be updated - the user will see the already adjusted value (by calling the procedure Spk_Click, which we have previously developed). Now, to ensure the previously agreed functionality, you need to hide the sheet Nomenclature. Users in this case will use the developed sheets Admission And Shipment.

Goods accounting requires the use of reliable and modern software that is capable of fully providing all the necessary operations for maintaining statistics on the arrival and departure of goods to the warehouse.

A convenient cloud-based system for automating trade and warehouse accounting that has replaced Excel!
Improve your work efficiency and increase your profits!

In particular, MS Excel has a set of functions to make product accounting more complete. Learn how to keep track of store items in Excel. Let's consider the main advantages and disadvantages of this program as a tool for accounting for goods.

Features of accounting for goods in Excel

Accounting for goods in Excel is carried out in special tables that allow you to identify a number of separate groups of goods depending on their purpose, consumption and some other features. In particular, thanks to this software product You can perform a range of analytical functions and sequential operations, including the use of formulas.

At the same time, it is important to be able to work in Excel in order to fully use all the functions necessary for accounting for the sale of goods and services, structure and analyze data to solve various business problems.

Control of the movement of goods in the warehouse

The Excel program allows you to control the movement of goods in the warehouse, their receipt and departure, through the use of special tables.

In particular, you can create a table in the program with various columns, depending on the user’s needs for the necessary resources and options that the program provides. In the table, it is possible to display data on operations performed, including indicating the balance in the warehouse, both for a specific group of goods and for the entire warehouse as a whole.

For each group of goods, a supplier is indicated so that, if necessary, you can quickly find out where a particular product or equipment came from in the warehouse. At the same time, all work is carried out sequentially, taking into account possible adjustments. In parallel with table markup, it is also necessary to take into account the inclusion and creation of additional documents, footnotes and appendices, for which free space must be allocated.

As a rule, detailed accounting of products available in a warehouse is due to the need to monitor the movement of inventory during trade and, in particular, the requirement for strict reporting, so accounting must be carried out by a qualified specialist who is fluent in MS Excel.

Useful tricks when maintaining inventory records in Excel

The first thing we can recommend for working in the program is to use all cell formatting elements in tables. For example, when it is necessary to place the table header in two lines, it is important to first ensure word wrapping in the cell itself. This technique will provide clarity and excellent appearance tables.

The program also allows you to insert special fonts. Their use is appropriate when it is necessary to highlight a number of products or categories depending on certain parameters and values.

Overall the most important groups Items must be selected through appropriate operations on a cell or row. You can also format columns in which various groups of goods can be located, which are supposed to be processed through the use of tables.

A modern solution for simple accounting - online program Class365

Unfortunately, Excel also has a number of shortcomings, which sometimes radically affect the work with goods in the warehouse. These can confidently include: an overly complex set of operations that will require certain work skills and qualifications, as well as too much time spent, sometimes simply unbearable under time pressure.

Moreover, imagine how catastrophic the loss of data would be due to an accidental error, which when working with Excel program occur quite often. In this regard, it is necessary to choose a decent, and most importantly free specialized program to maintain all necessary records.

The online program for accounting for goods in a warehouse, Class365, allows you to automate warehouse and trade accounting, work with online stores, and the customer base, without wasting time on implementation and training of personnel. The program's simple and well-thought-out interface allows you to master it in no more than 15 minutes.

Warehouse accounting: acceptance, write-offs, revaluation, etc.
Automatic issuance of accounting and accompanying documentation
Work with an unlimited number of warehouses
Storage period control
Inventory planning taking into account sales rates
Reports for any period and area of work in 1 click

It is not difficult to guess that accounting in Excel tables becomes a thing of the past and is replaced by a more convenient and functional program - automated system business management Class365. A free online solution allows you to work in any convenient place, without being tied to a work computer. Use modern solutions for your business and get stunning results!