Wine Gallery Company

Wine Gallery is the first retail project of our team. We are a distributor of local alcoholic products in Vietnam. Our main the target audience are tourists who come to rest in Vietnam.

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

We were prepared for a long period of study as this was our first experience with a program of this type. 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 opened up new opportunities that helped to capture 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 customers 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 at a very different categories goods, for each counterparty. We turned to the specialists of the USU company to resolve this issue. The guys quickly finalized the program for our needs, which greatly improved and facilitated our interactions with clients.

We are glad that we chose USU 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 products and various orthopedic products. Every year the range of products and the number of customers increases. For more effective management accounting needs to be automated. It is automation that enables any enterprise to move to a new level of development!

The choice of the program was done for 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 a store manager. And so we found USU. Just a find for an individual entrepreneur!

Only one thing was embarrassing ... the lack of representation in our city, and then, as it turned out, in Russia as a whole. They could not imagine how it is possible to successfully cooperate, being at different ends 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, analysis of sales by product groups, maintaining a customer base and much more. It is possible to model the program specifically for our type of activity.

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

Sincerely, The head of the store "OrtoBoty" Timerbayeva A.A.

We express our deep gratitude to the entire team of the Universal Accounting System. As the owner of a child thrift store, we know the importance of systematizing data on consignors and incoming goods for sale. On the recommendation of our partners, we decided to try USU for a thrift 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, stock balances, inventory, payments to consignors, returns of goods to consignors and much more.

The developers did a great job, they listened to our wishes and added a contract, retention for the storage of goods, 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.

Finagin M.Yu.

Russian Federation, Leningrad region, the city of Vsevolozhsk

IP Bogubaeva Dinara

Wedding Boutique Malinelli (IP Bogubaeva Dinara) expresses its heartfelt gratitude to the company IP Akulov N.N. for the installation of 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 have mastered the system very quickly.

As a manager, I can at any time see the results of the day's work from the workplace or at home.

We set up a system of visits. Now we can see which of the employees is on vacation or sick. It has become very convenient to calculate vacations and sick days.

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

Maintenance is very timely and to a high standard. professional level. The guys patiently answer all of our many questions and complete the job on time.

Sincerely, Sharipov Darkhan

IP "Buranbaev"

Rating: 5/5

Product: Program for trade and warehouse.

Our company is engaged in the sale of auto parts.

The management and staff of IP "Buranbaev" expresses gratitude to you for providing quality services and a professional approach to supporting and servicing the program USU - universal accounting system.

Taking into account the experience of working with the program developed for us, we can note the effectiveness and stability software. The provided program fully meets the requested parameters. The specialists of your company have shown high professionalism, having qualitatively installed the program and trained employees to use it.

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

Director of IP "Buranbaev" Buranbaev U.A.

IP Kurakova O.N.

Thank You Letter.

We would like to express our deep gratitude to IP Akulov N.N. and his team for help in organizing the business, namely the adaptation of the program USU for retail clothes.

To date, the software market has enough a 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 project launch time.

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

1) democratic price.

2) transparency of calculations.

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

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

Unlike many software products, presented on the market, this software is quite flexible to the requirements of the user and allows you to customize almost any aspect according to the characteristics of the business. Also, the advantages include the absence of requirements for the use of special equipment. Separately, I want to say thank you for the lightning-fast reaction of those. support for inquiries.

We wish your team prosperity and new professional success!!

Sincerely,

Individual entrepreneur Kurakova Olga Nikolaevna.

Institute of Proper Nutrition, Kit LLP

Individual entrepreneur "Universal Accounting System" managed to quickly and efficiently organize the work on the development and implementation of a software system for the Institute of Proper Nutrition in Astana, as a result of which the enterprise 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 the installation work on time, made our adjustments and changes, in strict accordance with the terms of the Agreement. Upon completion of the work, for their part, the employees promptly provided the Certificate of Completion.

Managers and programmers are very punctual and polite.

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

Director Koishegarina A.N.

06/01/2017

ECOSTYLE company

Taking into account the experience of working with the program developed for us, we can note the effectiveness and stability of the software. When ordering a program from our company warehouse accounting, software development was completed on schedule. The presented program fully meets the requested parameters, the company's specialists have shown high professionalism, having qualitatively installed, adjusted the program and trained employees to use it.

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

Trade Organization "Value Shop"

I started my business from my small kitchen. First bouquet. First unrest. Mommy on maternity leave. Standard history. It's time for the volume to grow. Grow customer base. At first I managed to keep everything in my head. Then came Excel. By the way, it turned out to be not so easy to lead. Since all formulas, names, lines, everything had to be thought out independently. Having abandoned Excel, having received even more volume, invoices came to replace. It seemed that this is happiness. But as it turned out. Not at all either. The numbers diverged. Finding out the presence of the remains took a huge amount of time. And then the idea to automate came to mind. Began analysis, calls. And I chose usu. What I don't want to this day. I've been using it for over a month and a half. More specifically: the happiest thing is to shoot reports. The balances are withdrawn instantly, it is possible to make a discount, accrue bonuses, view any diagrams. Today I can't even take a step without this program. If something happens to my laptop, I convulse convulsively.

What difficulties. 1. Get ready if you are the same plushies like me. Then you will have to enter a thousand little things into the database and think over. But it's a matter of 3-4 days of hard work. During this time, you get to know the program better and you begin to feel it. I am grateful to Irina, who at first withstood the attack of calls: what, how, where, what gives. Then there were the guys programmers. By the way, who have golden patience and good brains :-). So, my experience with the program is positive. Of the minuses, I became somewhat dependent on her and fell in love with the reports. Well, how about it, now I’m smartly taking reports and holding meetings). Usu is a new step for me. Which showed my height. Efficiency. Nothing gets past the account. Every penny, every product knows its place. Usu helped me grow as a businessman. Who if knows: oh well, I won’t count it. Okay, I'll give it to you. Passes, the stage of goodness. The stage of growth and respect for one's own work begins. I am grateful. I am grateful to every employee who led me to my personal small victory.

2) It is possible to take a photo of the product, and the sellers see what they are selling when selling. That's great - there's a lot less resorting going on.

3) It is possible to get a check of 2 types and carry out a sale without a check - the goods are written off in any case.

4) Excellent performance analysis. You can see graphically (which is more visual) the result question asked. And in terms of profit, and in terms of goods and sales of each seller, and much more.

5) Responsive and competent employees of the company, which is very nice. They are quick to help and help resolve issues.

I don't think I made the wrong choice.

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

Thanks to its creators for making accounting easier.

Sole proprietor Samarina T.V.

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 at the initial stage choose Excel tables for warehouse accounting. 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 storage facilities The more items, the higher the probability of their occurrence. 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.
• Inconvenience joint work. 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 Program 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 taking into account 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. Institution 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.

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

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 sale 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 item of the item (item name, receipt price, sale price, and quantity in stock). Target 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. In general, an automated book should provide a convenient interface for managers. This interface will provide two sheets - Admission and Shipment.

Direct work with the sheet shown in Fig. 2.16 is undesirable. Of course, the manager, upon receipt or sale of goods, 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 stock and the receipt 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 next we will hide (this feature is easily implemented using the Microsoft Excel application service) so that it is not directly available to the user. Of course, after that, you can enter protection on the structure of the book (with a password). As a result, users can work with the sheet Nomenclature only with the help of the interface implemented on other sheets.

Upon receipt of goods already in stock, but at a new price, you should decide on the recalculation of 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 us proceed 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 items of the nomenclature.

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

" Listing 2.21. Procedure executed when opening a Private Sub workbook_Open() " Clearing the list Worksheets("Incoming").Spk.Clear " Counting the number of items in the infobase N = 0 While Worksheets("Nomenclature").Cells(N + 2, 1).Value<>"" N = N + 1 Wend " Populating the list For i = 1 To N Worksheets("Incoming").Spk.AddItem Worksheets(3).Cells(i + 1, 1).Value Next Worksheets("Incoming"). Spk.ListIndex = -1 End Sub

When the user selects the product name in the combo box, then in cell C5 on the worksheet Admission the price is entered from the corresponding line of the sheet Nomenclature books. To do this, the procedure that is performed when the Combo Box item is clicked should be formatted as shown in Listing 2.22. There is also nothing fundamentally new in it compared to the previously considered 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 the new receipt of the specified product in cell C6. To increase security against accidental actions, we placed another field for a password on the sheet. This is a normal text box. Button click Contribute will update the price and quantity of products on the sheet Nomenclature only if the password is correct. Set the following properties for the text box:

• Name - Pass;
• PasswordChar - *.

This will ensure the display of "asterisks" when typing the password (the word typed by the user will be hidden from prying eyes). Thus, we approached writing a procedure for entering information about a new arrival (an increase in the number of specified goods and a possible price change) on the third sheet. Listing 2.23 shows the procedure that is executed when the button is clicked. Contribute. The combination 357 is considered here as a password.

" 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 "(!LANG: Quantity correction 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 the password 357, the procedure will add the quantity of goods entered by the user to the third sheet and adjust the price. After that, the screen displays Announcement that the data has been entered, and then the password field is cleared. If the password is wrong, no changes on the sheet Nomenclature not produced.

Let us now turn to the consideration of the right side (in contrast to the left, it is distinguished by a darker shade) of Fig. 2.17. This sheet fragment must be used to enter a new product (indicating the quantity). Listing 2-24 shows the procedure that performs this functional action in this case.

" Listing 2.24. Handling a click on the Submit button new product Private Sub CommandButton_Click() " Counting the number of entered items on the Nomenclature sheet 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("Item").Cells(N + 2, 2).Value = Range("G4").Value Worksheets("Item").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 for the right side of the sheet. However, the option proposed here increases the functionality of the development. As property value Name we chose Pass2. Let's 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 new item 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, N is used) Nomenclature. After that, 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 the sheet Shipment there are three controls: a combo box (Name - Spk), a password field (Name - Pass3) and a button (Name - CommandButton1).

To fill in the list, it is necessary to correct the previously considered procedure (Listing 2.21) Workbook_Open. It should now look like Listing 2-25.

" Listing 2.25. Workbook Open Procedure (Option 2) Private Sub Workbook_Open() Worksheets("Incoming").Spk.Clear Worksheets("Shipping").Spk.Clear " Count Items Already Available N = 0 While Worksheets( "nomen").Cells(N + 2, 1).Value<>"" N = N + 1 Wend " Populating Lists For i = 1 To N Worksheets("Incoming").Spk.AddItem Worksheets(3).Cells(i + 1, 1).Value Worksheets("Shipping").Spk .AddItem Worksheets(3).Cells(i + 1, 1).Value Next Worksheets("Incoming").Spk.ListIndex = -1 Worksheets("Shipping").Spk.ListIndex = -1 End Sub

Next in line is the following procedure (Listing 2-26), which is executed when the combo box is clicked. 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 sale price (Fig. 2.19).

" Listing 2.26. Procedure to execute when a combo box is clicked Private Sub Spk_Click() Range("E6").Value = Worksheets("Item").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 orientation and control, using the previous procedure, we entered the available quantity of goods in stock). The procedure shown in Listing 2-27 (performed when the Ship button is clicked) adjusts the number of items in stock in the database.

" Listing 2.27. Submit Procedure Private Sub CommandButton1_Click() If Pass3.Text = "775" Then ColPrais = Worksheets("Item").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 entered into 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, in order to provide the previously discussed functionality, it is necessary to hide the sheet Nomenclature. Users in this case will use the developed sheets Admission and Shipment.

Inventory management in Excel is a great solution for any trading company or production organization for whom it is important to keep records of the quantity of materials, raw materials used and finished products.

Who can benefit from spreadsheets

Large firms acquire ready-made solutions for these purposes in electronic format. However, they are quite expensive, and some complex programs require 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 instrument, second in popularity only to 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 approach the issue of creating their own from the very beginning. computer program. In this case, from the very beginning, adhere to the following rules:

• All directories should initially be created as accurately and in detail as possible. In particular, one should not be limited to simply indicating the names of goods and 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 in Excel spreadsheets, it is imperative to conduct an inventory.
• It should be foreseen what additional information may be needed, and enter it, so that in the future you do not have to specify the data for each of the goods.

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

• It is necessary to compile directories: "Buyers", "Suppliers" and "Points of accounting for goods" (small companies do not need them).
• If the list of products is relatively constant, then we can recommend creating their nomenclature in the form of a database on a separate sheet of the table. In the future, the expense, income and reports must be filled out with links to it. The sheet in the Excel table with the heading "Nomenclature" must contain the name of the product, product codes, commodity groups, units of measurement, etc.
• The report is generated using the PivotTable tool.
• Receipt to the warehouse should be taken into account on the "Incoming" sheet.
• It is required to create sheets "Expense" and "Remains" to track the current state.

We create directories

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

In order for the headers not to “run away”, they need to be fixed. To do this, on the "View" tab in Excel, you need to click on the "Freeze Panes" button.

The "Customers" table looks exactly the same.

It will be able to provide a convenient and partially automated free service if you create an auxiliary directory of goods release points in it. True, it will be required only if the company has several outlets(warehouses). As for organizations that have one point of issue, it makes no sense to create such a directory for them.

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

First of all, we need to create a table for the nomenclature. Its headings should look like "Product name", "Sort", "Unit of measure", "Characteristic", "Comment".

• Select the range of this table.
• In the "Name" field, located directly above the cell with the name "A", enter the word "Table1".
• Do the same with the corresponding range on the "Suppliers" sheet. In this case, "Table 2" is indicated.
• Fixing of income and expenditure transactions is carried out on two separate sheets. They will help you keep inventory records in Excel.

For "Incoming" the table should look like the figure below.

Accounting automation

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

Wherein:

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

To do this, all directories need to be formatted as a table and a drop-down list should be created for the "Name" column. For this:

• select a 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, specify the function "=INDIRECT("item!$A$4:$A$8")".
• check the boxes next to "Ignore empty cells" and "List of valid values".

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

Similarly, auto-completion is created for the "Code" and "Supplier" columns, as well as a drop-down list.

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

You also need to create a drop-down list called "Points of Accounting", which will indicate 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 almost created a convenient tool that allows your company to keep inventory records in Excel for free, it remains only to teach our program to display the report correctly.

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

To facilitate inventory control, the free program should fill in the "Shipments" and "Receipts" columns using the SUMIFS function.

The balance of goods in the warehouse is calculated using mathematical operators.

This is how we got the Warehouse program. Over time, you can independently make adjustments to it in order to make accounting for goods (your products) as convenient as possible.

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 accounting in in electronic format. Today there are a lot of options for various directions 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 you follow the basic rules. 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. Do not disdain 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 series of data entered into the warehouse accounting program in Excel will be useful for statistical reports, charges wages 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, in the table for warehouse accounting, we will use drop-down lists. 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 in the name field (opposite the formula bar) enter the appropriate value. 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 ( finished example drawn up as described above).

Here is the completed program.