Create product accounting in excel. Warehouse accounting in Excel

Wine Gallery

Director Kirichenko D. Product:

Wine Gallery is the first retail project of our team. We are a distributor of local alcoholic beverages in Vietnam. Our main the target audience- these are tourists who come on vacation to Vietnam.

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

We were ready 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 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 customers, and we have an individual approach to each of them. Therefore, it was important for us to be able to obtain data on sales for very different categories of goods, for each contractor. 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 have chosen the USU company to implement the accounting system of our first business and continue to cooperate with the guys on the basis of other projects.

Respectfully yours, Director of Wine Gallery Kirichenko D.S.

We sell children's orthopedic shoes, children's rehabilitation products and various orthopedic products. The range of products and the number of customers is increasing every year. For more effective management, accounting automation is required. It is automation that makes it possible for any enterprise to move to a new level of development!

The choice of the program was taken for a long time and carefully. The task was to find a universal program for affordable price, which in itself will combine all the functions at once: both the place of the seller-cashier, and the commodity manager, and when entering under a different password will serve as an excellent analytical program for the store manager. And so we found USU... Just a godsend for an individual entrepreneur!

Confused only by one thing ... the absence of a representative office in our city, and then, as it turned out, in Russia as a whole. Couldn't imagine how you can successfully cooperate from different parts of the world.

But, thanks to the professionalism and attentive attitude of the team USU, we made up our minds.

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

Technical support works at its best. Experts quickly respond to all questions and help with any request.

Respectfully yours, Head of the OrtoBoty store, AA Timerbaeva

We express our deep gratitude to the entire team of the Universal Accounting System. Being the owners of the nursery thrift store, we know how important it is to systematize data about consignors and incoming goods for sale. On the recommendation of our partners, we decided to try the USU for a thrift store, and after using its demo version, we were delighted with what can be done in this program. Accounting for the receipt of goods from consignors, suppliers, bar-coding 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 retention for storing goods, our percentage for an agency fee. Despite the fact that the training took place remotely, the staff of the USU was very accessible and quickly explained how to work in the program. In general, working with such a program is a pleasure.

M.Yu. Finagin, director of the children's commission store "Baby for a baby"

Russian Federation, Leningrad region, city of Vsevolozhsk

IE 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 have forgotten 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 work in the system very quickly.

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

We have 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 leave and sick leave.

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 carried out in a very timely and professional manner. The guys patiently answer all our many questions and do the job on time.

Best regards, Sharipov Darkhan

FE "Buranbaev"

Director Buranbaev U.A. Product:

Rating: 5/5

Product: Software for trade and warehouse.

Our company is engaged in the sale of auto parts.

The management and staff of IE "Buranbaev" would like to thank you for providing quality services and a professional approach to support and maintain the program USU - universal accounting system.

Taking into account the experience of working with the program developed for us, we can note the efficiency and stability of the software. The provided program fully meets the requested parameters. The specialists of your company have shown high professionalism, having completed the installation of the program in a high-quality manner and trained the employees to use it.

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

Director of IE "Buranbaev" Buranbaev U.A.

IP Kurakova O.N.

Individual entrepreneur Kurakova Olga Nikolaevna Product:

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 adapting the program USU for retail clothes.

To date, a fairly large number of business support options are presented on the software market, 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) affordable price.

2) transparency of settlements.

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

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

Unlike many software products available on the market, this software is flexible enough to the user's requirements and allows you to customize almost any aspect according to the specifics of the business. Also, the pluses include the lack of requirements for the use of special equipment. I would also like 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.

Nutrition Institute, Kit TOO

Director Koishegarina A.N. Product:

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

The managers and programmers are very punctual and polite.

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

Director Koishegarina A.N.

01.06.2017 year

EKOSTIL company

Director Arkaev N.E. Product:

Taking into account the experience of working with the program developed for us, we can note the efficiency 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 have shown high professionalism, having completed the installation, setting up the program and training its employees to use it.

Thanks to this software tool managed to fully optimize all processes for the management and control of warehouse accounting.

Trade organization "Shop of values"

Director Seitkazieva M.M. Product:

I started my business with my little kitchenette. First bouquet. The first excitement. Mom is on maternity leave. Standard story. The time has come for the volume to grow. 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 the Excel, having received even more volume, invoices came to replace. It seemed that this was happiness. But as it turned out. Not at all either. The numbers were at variance. Finding out the presence of residues took a huge amount of time. And then the idea of automation came to mind. Analysis and calls began. And I chose usu. What I do not wish to this day. I have been using it for more than a month and a half. More specifically: the most happiness is to shoot reports. The balances are withdrawn instantly, it is possible to make a discount, accrue bonuses, view any diagrams. Today I cannot do without this program even a step. If something happens to my laptop, I am convulsed convulsively.

What are the difficulties. 1. Get ready if you are the same Plyushkin as I am. Then you will have to enter a thousand little things into the database and think over. 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 as if. I am grateful to Irina, who first withstood the attack of calls: what, how, where, what gives. Then there were the programmers guys. By the way, those who have golden patience and good brains :-). So, my experience with the program is positive. On the downside, I became somewhat dependent on her and fell in love with reports. Well, now I am cleverly filming reports and holding meetings). Usu is a new step for me. Which showed my height. Efficiency. Nothing passes by. Every penny, every product knows its place. Usu helped me grow as a businessman. Who if knows: come on, I won't count it. Okay, I'll give it. Passes the stage of daladnost. 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) There is an opportunity to take a photo of the product, and the sellers see when they sell what they are selling. This is great - there is much less re-grading going on.

3) It is possible to knock out a check of 2 types and sell 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 of the question asked. And by profit, and by product and by sales of each seller, and much more.

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

I think that I was not mistaken with the choice.

A great accounting tool! Convenient, simple, understandable and intelligible program.

Thanks to its creators for making accounting easier.

Individual entrepreneur T.V. Samarina

Most small and medium commercial organizations solve accounting problems material values(goods or products) using Microsoft applications Excel. 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 possible mistakes user.

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, prices of receipt and sale). Let's start our development with a sheet called Nomenclature(fig. 2.16). This sheet plays the role of a register or reference book of goods. Each line contains information about a specific item of the item (product name, receipt price, sales price, and quantity on hand). The goal of software development is to create control sheets that will allow you to adjust the quantity of goods when they are received or shipped, as well as to make changes in the price of goods. In general, an automated workbook should provide a user-friendly interface for managers. This interface will provide two sheets - Admission and Shipment.

Directly working with the sheet shown in fig. 2.16 is deprecated. Of course, the manager, when goods are received or sold, 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 price of receipt. 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 we will hide it further (this feature is easily implemented using the Microsoft Excel application service), so that it will not be directly accessible 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 using the interface implemented on other sheets.

Upon receipt of goods that are already in stock, but by new price, you should decide on the recalculation of the receipt price. For definiteness, let us assume that in such a case, the price of the last delivery is set as the price of goods receipt.

Let's move on to a detailed examination of the sheet shown in Fig. 2.17, which allows the manager to enter a new receipt of an existing product in a convenient form, 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 available in the infobase (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 goods present on the sheet Nomenclature... This action is performed when opening workbook(The text of the procedure required for this is shown in Listing 2.21). The code assumes that the property value Name combo box selected Spk.

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

When the user selects the name of the product 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 in comparison with the previously considered examples.

"Listing 2.22. Procedure for handling a click on a Private Sub combo box 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 in cell C6 of the specified item... To increase the security against accidental actions, we have placed another field for the password on the sheet. This is a simple text box. Clicking on a button Submit will allow you to update the price and quantity of goods on the list Nomenclature only if the password is correct. Let's set the following properties for the text box:

Name - Pass;
PasswordChar - *.

This will ensure that "asterisks" 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 receipt (an increase in the number of specified goods and a possible change in price) on the third sheet. Listing 2.23 shows the procedure for clicking the button Submit... The combination 357 is considered here as a password.

"Listing 2.23. Handling a click on the Add Private Sub button CommandButton1_Click () If Pass.Text =" 327 "Then" Updating the receipt price Worksheets ("Nomenclature"). Cells (Spk.ListIndex + 2, 2) .Value = Range (" C5 "). Value" (! LANG: 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 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, an informational message is displayed on the screen stating that the data has been entered, and then the password field is cleared. If the password is wrong, no changes to the sheet Nomenclature not produced.

Let us now turn to the examination of the right side (in contrast to the left, it is highlighted in a darker shade) 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 button Add new item Private Sub CommandButton_Click ()" Counting the number of added items on the sheet Nomenclature N = 0 While Worksheets ("Nomen"). Cells (N + 2, 1) .Value<>"" N = N + 1 Wend "Check the password entered in the right text field If Pass2.Text =" 35791 "Then Worksheets (" Nomenclature "). Cells (N + 2, 1) .Value = Range (" G3 "). Value Worksheets. Cells (N + 2, 2) .Value = Range ("G4"). Value Worksheets. Cells (N + 2, 4) .Value = Range ("G5" ) .Value MsgBox "Data entered" Pass2.Text = "" Else MsgBox "Password error! No data entered "End If End Sub

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

the name of the new product is entered in cell G3;
cell G4 indicates the price;
cell G5 is reserved for the number of units of goods;
the user enters the password, and clicking on the Add 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 goods on the sheet is counted (for this, N is used) Nomenclature... After that, information about the new product is written into the next free line of the sheet.

Now let's move on to another sheet - Shipment(fig. 2.18), which is used in the sale of 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 out the list, you need to adjust the previously described procedure (Listing 2.21). Workbook_Open... It should now look like Listing 2.25.

"Listing 2.25. Procedure for opening a workbook (option 2) Private Sub Workbook_Open () Worksheets. Spk.Clear Worksheets. Spk.Clear" Counting already available items N = 0 While Worksheets ( "nomen"). Cells (N + 2, 1) .Value<>"" N = N + 1 Wend "Populating the lists For i = 1 To N Worksheets. Spk.AddItem Worksheets (3) .Cells (i + 1, 1) .Value Worksheets. Spk .AddItem Worksheets (3) .Cells (i + 1, 1) .Value Next Worksheets. Spk.ListIndex = -1 Worksheets. Spk.ListIndex = -1 End Sub

Next in line is the following procedure (Listing 2.26), which is performed when you click the combo box. As a result, to 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 for clicking the Private Sub combo box 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 orientation and control, using the previous procedure, we entered the available quantity of goods in the warehouse). The procedure in Listing 2.27 (performed when you click the Ship button) allows you to adjust the number of items in stock in the database.

"Listing 2.27. Procedure to execute when the Upload 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 "There is no such quantity 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 "Added information" 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 provide the previously agreed functionality, you need to hide the sheet Nomenclature... Users in this case will use the developed sheets Admission and Shipment.

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

Who can help spreadsheets

Large firms acquire for these purposes ready-made solutions v electronic format... 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 beyond the power of start-ups or small companies. Fortunately, there is a way out, and you can use Excel spreadsheets. This electronic tool, which is second only to office Word program, has functionality that is sufficient for solving warehouse accounting problems.

Several important rules

Those who are interested in the question of how to keep stock records should seriously approach the issue of creating their own computer program... In this case, the following rules should be adhered to from the very beginning:

All directories should initially be created as accurately and in detail as possible. In particular, one cannot be limited to a simple indication of the names of goods, and one should also indicate articles, codes, expiration dates (for certain types), etc.
Opening balances are usually entered into tables in monetary terms.
It is necessary to follow the chronology and enter data on the receipt of certain goods at the warehouse before the shipment to the buyer.
Before filling out the Excel spreadsheets, it is imperative to take an inventory.
It should provide for what additional information may be needed, and enter it, so that in the future you do not have to clarify the data for each of the goods.

Before you start developing spreadsheet to ensure the normal functioning of your warehouse, you should take into account its specifics. General recommendations in this case, the following:

It is necessary to compile directories: "Buyers", "Suppliers" and "Points of accounting of 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, expense, income and reports must be filled out with links to it. A sheet in an Excel table with the heading "Nomenclature" must contain the name of the product, product codes, product groups, units of measure, etc.
The report is generated using the PivotTable tool.
Receipt to the warehouse must be recorded on the "Arrival" sheet.
You want to create sheets "Consumption" and "Remaining" to track the current state.

We create directories

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


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

To prevent headlines from running away, they need to be fixed. For this purpose, on the "View" tab in Excel, you need to click on the "Freeze Areas" button.

The table "Buyers" looks exactly the same.

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


	Accounting points
	Least		A comment

			Shop 1

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

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

We select the range of this table.
In the field "Name", 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, "Table2" is indicated.
Incoming and outgoing transactions are recorded on two separate sheets. They will help you keep inventory in Excel.

For "Parish" the table should look like in the figure below.


Arrival of goods	Provider	Accounting point	Unit meas.

Accounting automation

Warehouse 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 goods and the point of accounting.

Wherein:

unit of measure and supplier code should be displayed in the table automatically, without operator intervention;
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 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 field "Data type" look for "List";
in the "Source" field, specify the function "= INDIRECT (" item! $ A $ 4: $ A $ 8 ")".
put a checkmark in front of "Ignore empty cells" and "List of allowed values".

If everything is done correctly, then when filling in the 1st column, you can simply choose from the list. In this case, in the column "Unit. rev. " the corresponding value appears.

In the same way, autocomplete is created for the "Code" and "Supplier" columns, as well as a drop-down list.

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

It is also necessary to create a drop-down list called "Points of account", which will indicate where the received goods were sent. This is done in the same way as in the previous cases.

"Turnover list"

Now that you have almost created a convenient tool that allows your company to keep warehouse 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 inventory records. If it was carried out earlier, then in this column the balances should be displayed. In this case, 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 "Receipt" columns using the SUMIFS function.

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

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

At the first stage of the business, store owners are trying to save money by choosing Excel for warehouse accounting for free. In this article, we will talk about templates for accounting for goods in Excel and offer a more interesting free option for inventory accounting in a store than standard software.

What you will learn about:

Who can help spreadsheets

The account of goods in Excel is used by those entrepreneurs who have 2-3 sales per day and they have enough time to transfer sales data to spreadsheets.

Let's represent on the diagram the “evolution” of inventory accounting in small business. We drew up the figure after a survey of more than 10 entrepreneurs who are currently users of the “Business.Ru” product accounting program (third stage).

Most of them started working with a “notebook” - they kept the receipt and consumption of goods in a notebook, but when it was necessary to make a report and analytics, they transferred the data from the “notebook” to Excel.

When the business began to generate income of more than 15 thousand rubles per month, and sales were 5-10 per day, it became necessary to use a special inventory service.

After all, Excel has drawbacks: the main thing is that the program cannot be linked to the cash register software(you need to record sales manually). Therefore, when expanding a business or to deal with queues (if seasonality is present), the entrepreneur uses a program for inventory control connected to the checkout ().

With this drawback, Excel has advantages:

you can find on the Internet templates for a warehouse management program in excel for free;

you can also learn how to fill them out for free (using lessons from Youtube or on your own, intuitively);

you can independently make tables for the sales report on separate sheets and keep statistics, analytics and other calculations (however, for this you need to study all the capabilities of Excel, which will take more than one day).

How to keep inventory in Excel?

To maintain warehouse accounting in Excel, sales data are recorded manually in a notebook upon purchase, and then in the evening, after the store closes, the sold goods are found in the table and the quantity sold is noted.

In the same way, the table is updated when the goods arrive.

Warehouse accounting in Excel: features

Warehouse accounting in Excel is suitable for you if you:

you can do without the connection between the accounting table and the cash register;

there are no queues, two or three purchases per day, which allows filling out the accounting table in the “window” between buyers;

focused on painstaking work with tables, articles, etc .;

do not use a scanner to add goods to the database, but are ready to fill in everything manually.

Working with tables in Excel is possible if one or two people are engaged in inventory accounting, no more. Otherwise, confusion can arise - employees can accidentally change the data, and you will not see the previous version.

To fully appreciate all the advantages of warehouse automation, try the Business.Ru program. The program has an intuitive interface with the ability to customize for a specific user. All of the above advantages are already included in the basic functionality of the Business.Ru program.

For inventory control in Excel, you can use ready-made templates... You can develop them yourself.

Before the implementation of accounting, it is necessary to carry out an inventory in order to determine the exact amount of product balances. Why is it better and how to make a night inventory,

Carefully, observing attention to detail, enter the data on the product - name, article. If we are talking about food products, then it should be done in the table "Excel" column "expiration date".

It is necessary to take into account the shipment of goods not earlier than they arrived at the warehouse. The chronology of warehouse operations is important, as otherwise analytics and the final graphs of receipts and sales in the month can be distorted.

If you work with several suppliers, you need to make pages with reference data.

Additional information in the table is important - data about the forwarder or the manager with whom the order was made can save the situation if there is a problem with the order.

The first stage of warehouse accounting in Excel - filling in product data and forming columns in a table - can take a significant amount of time, from three hours to a week. It all depends on the number of products in the store and your skills in working with tables.

You can increase the efficiency of a store warehouse in five steps - about them

Structure of an Excel template for sales analytics

The most important thing for accounting for goods in Excel is to choose a convenient template for entering data.

For example, a sales table template might have the following columns:

Product Name;

quantity in stock;

For the convenience of analytics, it is necessary to create a new page (Sheet) for each month.

The table for accounting for sales is different from that required for inventory accounting.

Excel spreadsheet "Inventory accounting"

Structure Excel template for warehouse accounting should include the following sections:

1. Article - taken according to the manufacturer's data, needed for quick search item in the list.

2. The name of the product. Product name and a brief description of... For example:

dress "Anfisa" blue;

dress "Anfisa" red with flowers;

sundress denim mini blue.

You can come up with the name yourself or take what the manufacturer has in the description.

3. Units of measurement. Usually these are pieces, but there can be running meters, cubic meters, kilograms, etc.

4. Leftovers (available, left, reservation - if reservations are encouraged).

5. Price (two columns - sale price and purchase price).

6. Supplier.

In addition, you can do other columns as well:

pre-order (if a lot of time passes between ordering a product from a supplier and bringing it to your warehouse);

a regular customer (to highlight regular customers and assign them a discount or provide a special offer);

discount number (if a coupon was presented during the sale, etc.).

In a separate sheet of Excel spreadsheet, you need to place a reference - a table with the names of suppliers.

Problems with accounting for Excel items

Inventory accounting in Excel has significant disadvantages:

If someone from the staff accidentally replaced a number in the table, clicked “Save”, and then closed the file, it will be problematic to find the error.

To maintain a database of goods remotely, you can use Excel from Google Tables, however, with an increase in the number of lines to 100 and more, this program for warehouse accounting in Excel “slows down”.

Inability to integrate with a cashier or an accounting program.

It takes a lot of manual work and cannot connect the scanner.

There is no way to plan deliveries, control the balances by expiration date, etc.

The file cannot be used by several users at the same time (except for Google Tables).

You cannot set up an automatic check for the “correctness” of the entered data, except for the type of data (numeric, alphabetic, etc.).

Review of free software for warehouse accounting in Excel

Microsoft Excel is a paid program, due to the prevalence of which some users think of a free installation. However, you can download several online free software, which can replace Excel at the first stage of doing business.

OpenOffice Calc

The most popular Excel-like program that can be used to track items is called OpenOffice Calc. It is part of the Apache OpenOffice freeware package.

OpenOffice Calc supports Russian, takes up less disk space and loads faster than Excel.

The program has an interface similar to Excel 2003, so if you have had experience with just such a program, it will be easier to get used to.

Features:

the ability to create tables, graphs, make calculations using formulas, assign cells;

support for export to PDF, which is important for sending an invoice to the buyer;

the ability to use the "Master" to create special functions.

The only disadvantage is that the program has its own format, ODS. Usually XLS files open in this program, but sometimes it opens with an error.

Also Calc can create files in XLS format, and not more advanced - XLSX.

LibreOffice Calc

After installing LibreOffice Calc, the user may not be able to distinguish it from Open Calc. And not surprising. This program was developed by former OpenOffice programmers. Therefore, the principle and interface were taken similar.

The functionality is similar: the creation of tables, graphs, calculation by formulas.

Unlike OpenOffice, LibreOffice can save documents in EXLX format.

The application is convenient for quick calculations, functional, loads in 7-12 seconds. It is possible to work with functions and macros through the "Wizard".

PlanMaker

The third program in our overview of warehouse programs in Excel (you can also download it for free on the developer's website) is PlanMaker. This is the software that is included in the SoftMaker software package.

Like the previous programs, PlanMaker helps you do calculations using functions (using the Insert Function tool). Unfortunately, free PlanMaker can only support XLS format, while XLSX is available for those who have installed the paid version.

Paid programs - analogues of Excel

The difference between paid warehouse management programs and free ones is in ready-made downloaded templates and support for multiple warehouses. Let us present two such programs in the table below. All of them are desktop, that is, they are loaded into the computer's memory. This is inconvenient if you work from several devices - at home and in the office, and also plan to remotely monitor the work of employees (for example, while on vacation).

Table - Comparison of programs "Excel warehouse" and "Production warehouse"

The name of the program

Main characteristics

Price, rub

Warehouse in Excel

acceptance of the application;

posting;

shipment or write-off;

delivery;

analytics;

work with invoices;

formation of orders to suppliers;

invoice printing;

reference books;

price print;

the ability to log in with different kind access to the program.

Production warehouse

flow control;

posting of raw materials;

equipment;

formation of a table of shipment of products;

analytics of admission;

report on the consumption of raw materials;

analytics.

If you are looking for a free or inexpensive program for inventory control, pay attention to "Business.Ru" - a cloud service for all operations in the warehouse, which greatly facilitates the procedures for posting, ordering goods, working with invoices and inventory.

Opportunities of "Business.Ru" as a free cloud program like Excel

All of the above programs have all disadvantages of Excel... This software is an indisputable assistant for organizing stable accounting in a small point of sale with 2-3 sales per day. However, if there are more buyers, more advanced software is needed.

Today there is a large selection of specialized tools for inventory control: from simple free programs to expensive full-featured WMS systems. Despite this, many entrepreneurs initial stage choose Excel tables for inventory control. 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 storage facilities, the more headings, the higher the likelihood of their appearance. What difficulties do users face when working with Excel?

Data entry errors. Due to a single input error, it becomes necessary to double-check all the data in the tables.
Inconvenience working together... A situation often arises when, when editing data by one user, they are overwritten by another. Thus, the accounting in Excel may be incorrect.
Performing many operations manually. Excel program not created as a solution for warehouse management, the program combines many basic functions, and therefore often you have to waste time on monotonous manual labor.
Difficulty verifying the correctness of the data. When using Excel, a lot of effort is spent on checking the correctness of the entered data and on bringing the information into a suitable 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 very often, however, they are time-consuming. Warehouse program in Excel Corrections of errors and typos, long-term checking of the correctness of the balance data and forced manual labor when working with Excel are the main reasons why entrepreneurs eventually come to use specialized solutions for warehouse accounting. Even experienced specialists who know how to keep stock records in Excel still make a choice in favor of other Excel-based programs or fundamentally new solutions.

How to keep inventory in Excel?

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

Correct design of reference books. A directory with unmarked goods creates guaranteed accounting problems, inconveniences for 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, SKUs, prices, VAT). If the accounting of the goods sold in the warehouse is carried out using the MyWarehouse service, all the necessary data about the goods are 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 work will give adequate reports in the process of work, and those who make up sales with " blank slate”Should not be counted on.
Compliance with the chronology in the paperwork. It is necessary to register the arrival of goods to the warehouse from the supplier strictly prior to their shipment to the buyer. Otherwise, the cost will "go" and you will not be able to calculate exactly how much you earned. In MyStorage for each purchase and sale of goods, you can specify an arbitrary date.
Structured storage of additional information. When keeping records of a warehouse, it is important to collect Additional information sales information (for example, method of delivery and payment, date of shipment, customer name, manager's name) in separate fields. All these data in the future will be useful for drawing up route sheets for couriers, calculating salary for managers, and analyzing statistics. In MySklad, you can easily and without programming create required amount free fields for use in reference books and documents.

Free support by phone and e-mail is available to users of the Internet service MySklad. The support staff not only help with the work with the program, but also share advice on optimal inventory control and setting up the main business processes. Download the free version, try our new solutions.

It might be helpful to read: