Inventory management using MS Excel. Warehouse accounting in Excel. A simple program for automating warehouse accounting

Inventory accounting in Excel is an excellent solution for any trading company or manufacturing organization for which it is important to keep track of the quantity of materials, raw materials used and finished products.

Who can spreadsheets help?

Large companies purchase ready-made solutions in electronic format for these purposes. However, they are quite expensive, and some complex programs require hiring a qualified employee with a high salary to work in the warehouse. This is not possible for start-ups or small companies. Fortunately, there is a way out, and you can use Excel spreadsheets. This electronic tool, which is second in popularity only to the office program Word, has functionality that is quite sufficient for solving warehouse accounting problems.

A few important rules

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

All directories must initially be created as accurately and thoroughly as possible. In particular, you cannot limit yourself to simply indicating the names of goods and you should also indicate articles, codes, expiration dates (for certain types), etc.
Opening balances are usually entered into tables in monetary terms.
You should follow the chronology and enter data on the receipt of certain goods at the warehouse earlier than on shipment to the buyer.
Before filling out Excel tables, it is necessary to conduct an inventory.
You should foresee what additional information may be needed and enter it too, so that in the future you do not have to clarify the data for each product.

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

It is necessary to compile directories: “Buyers”, “Suppliers” and “Points of registration of goods” (small companies do not require them).
If the list of products is relatively constant, then it is recommended to create their nomenclature in the form of a database on a separate sheet of the table. In the future, expenses, income and reports must be filled out with references to it. The sheet in the Excel table with the heading “Nomenclature” should contain the name of the product, product codes, product groups, units of measurement, etc.
The report is generated using the Pivot Table tool.
Receipts to the warehouse should be recorded on the “Receipt” sheet.
It is required to create “Consumption” and “Remaining” sheets to track the current status.

We create directories

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


Suppliers
Least	Legal address	A comment
LLC "Moscow"
LLC "Leto-3"
JSC "Utro"

To prevent headings from running away, they need to be fixed. To do this, on the “View” tab in Excel, you need to click on the “Freeze Areas” button.

The “Buyers” table looks exactly the same.

Freeware can provide a convenient and partially automated service if you create an auxiliary directory of goods release points in it. True, it will only be required if the company has several retail outlets (warehouses). As for organizations that have one point of issue, there is no point in creating such a directory for them.


	Metering points
	Least		A comment

			Store 1

Our own “Warehouse” program: creating a “Receipt” sheet

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

Select the range of this table.
In the "Name" field located directly above the cell called "A", enter the word "Table1".
Do the same with the corresponding range on the “Suppliers” sheet. In this case, indicate “Table2”.
Receipt and expense transactions are recorded on two separate sheets. They will help you maintain inventory records in Excel.

For “Parish” the table should look like the figure below.


Arrival of goods	Provider	Metering point	Unit measured

Automation of accounting

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

Wherein:

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

To do this, you need to format all directories in the form of a table and create a drop-down list for the “Name” column. For this:

select the column (except for the header);
find the “Data” tab;
click on the “Data Check” icon;
in the “Data type” field, look for “List”;
in the “Source” field we indicate the function “=INDIRECT("item!$A$4:$A$8")".
Check the boxes next to “Ignore empty cells” and “List of acceptable values.”

If everything is done correctly, then when filling out the 1st column you can simply select from the list. At the same time, in the column “Unit. change." the corresponding value will appear.

Autocomplete for the “Code” and “Vendor” columns, as well as a drop-down list, are created in the same way.

To fill out the “Cost” column, use the multiplication formula. It should look like “= price * quantity”.

You also need to create a drop-down list called “Accounting points”, which will indicate where the received goods were sent. This is done in exactly the same way as in previous cases.

"Turnover sheet"

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

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

To facilitate warehouse accounting, the free program must fill in the “Shipments” and “Receipts” columns using the SUMIFS function.

We calculate the remaining goods in the warehouse using mathematical operators.

This is how we ended up with the “Warehouse” program. Over time, you can make adjustments to it yourself to make accounting for goods (your products) as convenient as possible.

However, when filling out receipt or expense 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 the tools built into Excel, you can achieve automatic completion 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. Maintaining them separately is convenient for subsequent quick search of 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 assortment item. Next, we will consider a simple example of 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 set up an automatic check of 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

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) 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 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 technologies and research activities; data about technical systems and equipment, including software elements; 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 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, 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 secret all confidential information received by one Party from the other Party during the interaction of the Parties, not to disclose, divulge, 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 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 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;

(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 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.

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 this regard.

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 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 his means of accessing your 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 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

Mailing address:

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

INN: 7714843760 Checkpoint: 771401001

Bank details:

Today there is a large selection of specialized tools for warehouse accounting: from simple free programs to expensive full-featured WMS systems. Despite this, many entrepreneurs at the initial stage choose Excel spreadsheets for warehouse accounting. At first, this seems like an affordable and convenient solution, but as the number of orders in an organization grows, using Excel for warehouse accounting is fraught with many problems. The more warehouse space, the more product items, the higher the likelihood of their appearance. What difficulties do users encounter when working with Excel?

Errors when entering data. Due to a single input error, it becomes necessary to double-check all table data.
Inconvenience of working together. A situation often arises when, when editing data by one user, it is overwritten by another. Therefore, accounting in Excel may not be correct.
Performing many operations manually. Excel is not designed as a solution for warehouse accounting; the program combines many basic functions, and therefore you will often have to spend time on monotonous manual labor.
Difficulty verifying data accuracy. 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 to perform other tasks, and keep warehouse records using more advanced programs.

These problems may not occur very often, but they still take up a lot of work time. Warehouse program in Excel Correcting errors and typos, lengthy verification of the correctness of data on balances and forced manual labor when working with Excel are the main reasons why entrepreneurs eventually come to use specialized solutions for maintaining warehouse records. Even experienced specialists who know how to maintain inventory records in Excel still make a choice in favor of other Excel-based programs or completely new solutions.

How to keep inventory records in Excel?

Regardless of whether you choose Excel spreadsheets, the MyWarehouse service, or another specialized trading solution, you must follow a few basic principles. If you download the program and do not use special rules, over time the accounting work will become much more complicated:

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

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

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, when goods are received or sold, the manager can directly enter data into it. 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 consider the right part (unlike the left, it is distinguished by 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 performed 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.

Wine Gallery Company

Manager Kirichenko D. Product:

Wine Gallery is the first retail project of our team. We are a distributor of local alcohol products in Vietnam. Our main target audience is tourists coming on holiday to Vietnam.

When launching this project, we clearly understood that accounting and analysis of goods and cash flows is the basis of our business. On the advice of friends, we chose the USU (Universal Accounting System) program.

We were prepared for a long learning curve since this was our first experience with this type of program. But the program turned out to be simple and intuitive. The very next day after the acquisition, we put it into practice and every day we discovered new opportunities that helped us record and analyze a huge flow of information without any difficulties.

This program certainly helps in our daily tasks, but with the development and growth of our business, many regular clients have appeared, and we have an individual approach to each of them. Therefore, it was important for us to be able to obtain sales data for very different categories of goods, for each counterparty. We turned to USU specialists to resolve this issue. The guys quickly modified the program to suit our needs, which greatly improved and simplified our interactions with clients.

We are glad that we chose the USU company to implement the accounting system for our first business and continue to cooperate with the guys on the basis of other projects.

Sincerely, Director of “Wine Gallery” Kirichenko D.S.

We sell children's orthopedic shoes, children's rehabilitation equipment and various orthopedic products. Every year the range of products and the number of customers increases. For more efficient management, accounting automation is necessary. It is automation that allows any enterprise to move to a new level of development!

The selection of the program took a long time and carefully. The task was to find a universal program at an affordable price, which would combine all the functions at once: both the position of a cashier and a merchandiser, and when logging in with a different password, it would serve as an excellent analytical program for the store manager. And so we found USU. Just a godsend for an individual entrepreneur!

There was only one thing that bothered me... the lack of representation in our city, and then, as it turned out, in Russia as a whole. We couldn’t imagine how we could successfully collaborate while being on different sides of the world.

But, thanks to the professionalism and attentive attitude of the team USU, we have decided.

Today we have an easy-to-use sales automation program. Accounting for inventory balances, sales analysis by product groups, maintaining a customer base and much more. It is possible to model a program specifically for our type of activity.

Technical support is excellent. Specialists quickly respond to all questions and help with any request.

Sincerely, Head of the OrthoBots store A.A. Timerbaeva

We express our deep gratitude to the entire team of the Universal Accounting System. As owners of a children's consignment store, we know the importance of systematizing data about consignors and incoming goods for sale. On the recommendation of our partners, we decided to try USU for a consignment store, and after using its demo version, we were delighted with what you can do in this program. Accounting for the receipt of goods from consignors, suppliers, barcoding of goods, cost accounting, warehouse balances, inventory, payments to consignors, returns of goods to consignors and much more.

The developers are great, they listened to our wishes and added an agreement, a deduction for storing goods, and our percentage for the agency fee. Despite the fact that the training took place remotely, USU employees were very accessible and quickly explained how to work in the program. In general, working with such a program is a pleasure.

Director of the children's consignment store "Baby to Baby" Finagin M.Yu.

Russian Federation, Leningrad region, city of Vsevolozhsk

IP Bogubaeva Dinara

Sharipov Darkhan Product:

Wedding boutique Malinelli (IP Bogubaeva Dinara) expresses its heartfelt gratitude to the company IP Akulov N.N. for installing the program at our enterprise Universal Accounting System.

Thanks to the program, we forgot about keeping records in notebooks and Excel. All our information is stored on a computer and processed within a few seconds.

The program interface is very simple. All sellers mastered working in the system very quickly.

As a manager, I can view the results of the day’s work at any time from my workplace or while at home.

We have set up a visiting system. Now we can see which employees are on vacation or sick. It has become very convenient to calculate vacations and sick leave.

The accountant now sees the whole picture of the movement of goods and cash flows, and can also reflect payments both in cash and by card or using various payment systems.

Maintenance is carried out in a very timely manner and at a high professional level. The guys patiently answer all our many questions and complete the work on time.

Sincerely, Sharipov Darkhan

IP "Buranbaev"

Director Buranbaev U.A. Product:

Rating: 5/5

Product: Program for trade and warehouse.

Our company sells auto parts.

The management and team of IP “Buranbaev” expresses gratitude to you for the provision of quality services and a professional approach to supporting and maintaining the program USU – universal accounting system.

Taking into account our experience with the program developed for us, we can note the effectiveness and stability of the software. The provided program fully meets the requested parameters. The specialists of your company showed high professionalism, installing the program efficiently and training employees to use it.

We thank the employees of your company for their professional service and look forward to further cooperation.

Director of IP "Buranbaev" Buranbaev U.A.

IP Kurakova O.N.

Individual entrepreneur Olga Nikolaevna Kurakova Product:

Letter of thanks.

We would like to express our deep gratitude to IP Akulov N.N. and his team for assistance in organizing the business, namely adapting the program USU for clothing retail.

Today, the software market offers a fairly large number of business support options, but all of them are either high in price or do not have the required functionality and require additional improvements, which also affects the final price and timing of the project launch.

Our choice fell on USU for a number of reasons, namely:

1) reasonable price.

2) transparency of calculations.

3) the opportunity to try a demo version of the product.

4) high speed of project launch in the stages from product selection to the full launch of the project (less than 5 days.)

Unlike many software products on the market, this software is quite flexible to user requirements and allows you to customize almost any aspect according to the specifics of your business. Another advantage is the absence of requirements for the use of special equipment. Separately, I would like to say thank you for the lightning-fast reaction of those. support for inquiries.

We wish your team prosperity and new professional successes!!

Sincerely,

Individual entrepreneur Olga Nikolaevna Kurakova.

Institute of Proper Nutrition, Keith LLP

Director Koishegarina A.N. Product:

Individual entrepreneur "Universal Accounting System" managed to quickly and efficiently organize the development and implementation of a software system for the Institute of Proper Nutrition in Astana, as a result of which the company systematized the current work of several departments, optimizing it to a minimum, but at the same time the report form remained complete, detailed and transparent.

We would like to note that the employees completed all installation work on time, made our adjustments and changes, in strict accordance with the terms of the Agreement. Upon completion of the work, the employees promptly provided a Certificate of Completion.

Managers and programmers are very punctual and polite.

In general, the program is simple and easy to use.

Director Koishegarina A.N.

06/01/2017

Company "ECOSTIL"

Director Arkaev N.E. Product:

Taking into account our experience with the program developed for us, we can note the effectiveness and stability of the software. When our company ordered a warehouse accounting program, the software development was completed on time. The presented program fully meets the requested parameters; the company’s specialists showed high professionalism, performing high-quality installation, setting up the program and training employees to use it.

Thanks to this software, it was possible to most fully optimize all processes for managing and controlling warehouse accounting.

Trade organization "Valuables Shop"

Director Seitkazieva M.M. Product:

I started my business from my small kitchen. First bouquet. First excitement. Mommy is on maternity leave. Standard story. The time has come for volumes to increase. The customer base is growing. At first I managed to keep everything in my head. Then Excel came. By the way, it turned out to be not so easy to lead. Since all the formulas, names, lines, everything had to be thought out independently. Having abandoned Excel and received even more volume, invoices came to replace it. It seemed like happiness. But as it turned out. Not at all either. The numbers differed. Determining the presence of residues took a huge amount of time. And then the idea of automation came to mind. The analysis and calls began. And I chose usu. What I don’t wish for to this day. I've been using it for over a month and a half. More specifically: the happiest thing is making reports. Balances are withdrawn instantly, it is possible to make a discount, add bonuses, and view any charts. Today I can’t do a single step without this program. If something happens to my laptop, I go into convulsions.

What difficulties. 1. Get ready if you are Plyushkins like me. Then you will have to enter a thousand little things into the database and think through them. But this is a matter of 3-4 days of hard work. During this time, you become more familiar with the program and begin to feel it. I am grateful to Irina, who initially withstood the attack of calls: what, how, where, what gives. Then there were the programmer guys. By the way, those who have golden patience and good brains :-). So, my experience using the program is positive. On the downside, I became somewhat dependent on it and fell in love with the reports. Well, of course, now I take reports and hold meetings with a smart look). Usu is a new level for me. Which showed my growth. Efficiency. Nothing goes unaccounted for. Every penny, every product knows its place. Usu has helped me grow as a businessman. Who knows: oh well, I won’t count it. Okay, I'll give it to you. The stage of calmness is passing. The stage of growth and respect for one’s own work begins. I'm grateful. I am grateful to every employee who led me to my personal small victory.

2) It is possible to take a photograph of the product, and sellers can see what they are selling when selling. This is great - there is much less re-grading.

3) It is possible to issue a receipt of 2 types and make a sale without a receipt - the goods are written off in any case.

4) Wonderful performance analysis. You can see graphically (which is more visual) the result of the question asked. And by profit, and by product, and by sales of each seller, and much more.

5) Responsive and competent company employees, which is very pleasant. They quickly come to the rescue and help resolve the issue.

I think I made the right choice.

Great accounting tool! Convenient, simple, understandable and intelligible program.

Thanks to its creators for making accounting easier.

Individual entrepreneur Samarina T.V.