Accounting for purchases in excel. Accounting of goods in MS Excel: advantages and disadvantages

However, when filling out incoming or outgoing documents, some values ​​still have to be hammered in by hand.

These include:

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

Using built-in Excel tools, you can achieve automatic filling of several columns based on the data in the reference book. However, it is unlikely that it will be possible to configure this functionality on your own without deep knowledge of the program.

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

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

Formation of the turnover sheet

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

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

Tracking critical balances

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

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

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

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

This method of monitoring values ​​is applicable not only to warehouse balances. Similarly, you can analyze the following indicators:

  • volume of work performed or sales by employees;
  • increase in gross turnover or profit;
  • identification of trade downturns 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 compile all the formulas.

Pros of accounting for goods in the store in Excel

Try all the features of the EKAM platform for free

Warehouse accounting program

  • Online checkout under 54-FZ

Email*

Email*

Try

Warehouse accounting program

  • Setting up automation of turnkey accounting of goods
  • Write-off of balances in real time
  • Accounting for purchases and orders to suppliers
  • Built-in loyalty program
  • Online checkout under 54-FZ

We provide prompt support by phone,
we help to load the commodity base and register the checkout.

Try all the possibilities for free!

Email*

Email*

Get access

Privacy agreement

and processing of personal data

1. General Provisions

1.1. This agreement on confidentiality and processing of personal data (hereinafter referred to as the Agreement) is freely accepted and of its own free will, applies to all information that LLC "Inseils Rus" and / or its affiliates, including all persons belonging to the same group with LLC "Insails Rus" (including LLC "EKAM service"), can receive information about the User during the use of any of the sites, services, services, computer programs, products or services of LLC "Insails Rus" (hereinafter referred to as the Services) and the course of execution of any agreements and contracts with the User by Insales Rus LLC. 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 conditions, the User must refrain from using the Services.

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

"User" -

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

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

or an individual entrepreneur registered in accordance with the legislation 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 projects; information about technical systems and equipment, including software elements; business forecasts and information about prospective purchases; requirements and specifications of specific partners and potential partners; information, related to intellectual property, as well as plans and technologies related to all of the above) communicated by one party to the other in writing and / or electronic form, explicitly 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. Obligations of the Parties

2.1. The Parties agree to keep secret all confidential information received by one Party from the other Party during the interaction of the Parties, not to disclose, disclose, disclose 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 of the Parties will take all necessary measures to protect confidential information at least using the same measures that the Party applies to protect its own confidential information. Access to confidential information is provided only to those employees of each of the Parties who reasonably need it to perform their official duties for the implementation of this Agreement.

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

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

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

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

(d) if the information is provided at the written request of a state authority, other state authority, or local self-government body in order to perform their functions and its disclosure to these authorities 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, the information about which is being transferred.

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

2.6. The information that the User provides to Inseils when registering in the Services is not personal data, as they are defined in the Federal Law of the Russian Federation No. 152-ФЗ dated 07/27/2006. "About personal data".

2.7 Insales reserves the right to amend this Agreement. When changes are made in 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 realizes and agrees that Inseils can 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 related to 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 this in writing to the email address of Inseils -.

2.9. By accepting this Agreement, the User understands and agrees that the Inseils Services may use cookies, counters, other technologies to ensure the performance of the Services in general or their individual functions in particular, and the User has no claims against Inseils in this regard.

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

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

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

2.12 The User is obliged to immediately notify Insails about any case of unauthorized (not authorized by the User) access to the Services using the User's account and / or about any violation (suspicion of violation) of the confidentiality of his means of access to the account. For security reasons, the User is obliged to independently carry out a safe shutdown under his account at the end of each session of work 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 violation by the User of the provisions of this part of the Agreement.

3.Responsibility of the Parties

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

3.2. Compensation for damage does not terminate the obligations of the offending Party to properly fulfill its obligations under the Agreement.

4.Other provisions

4.1. All notices, inquiries, requirements and other correspondence under this Agreement, including those that include confidential information, must be made in writing and handed in personally or through a courier, or sent by e-mail to the addresses specified in the license agreement for computer programs dated 01.12.2016, the agreement of accession to the license agreement for computer programs and in this Agreement or other addresses that may be further indicated by the Party in writing.

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

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

4.3. All suggestions or questions regarding this Agreement, the User has the right to send to the Inseils User Support Service or to the postal address: 107078, Moscow, st. Novoryazanskaya, 18, str. 11-12 Business center "Stendhal" LLC "Inseils Rus".

Date of publication: 01.12.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. Academician Ilyushin, 4, building 1, office 11

Mailing address:

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

INN: 7714843760 Checkpoint: 771401001

Bank details:

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 one is that the program cannot be linked to the cash register software (it is necessary 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 the column “expiration date” should be added to the Excel table.

    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"

The structure of the warehouse excel template should include the following sections:

1. Article - taken according to the manufacturer's data, needed to quickly find a product in the list.

2. The name of the product. Product name and brief description. 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 free programs on the Internet that 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 enter with different types of 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 the disadvantages of Excel. This software is an indisputable assistant for organizing stable accounting in a small retail outlet 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 initially choose Excel spreadsheets 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 warehouse premises, the more commodity items, 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.
  • The inconvenience of teamwork. 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 is not designed as a warehouse management solution, the program combines many basic functions, and therefore you often have to spend 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 software 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:

  1. 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.
  2. Establishment of 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 up sales from a “blank slate” should not count on this.
  3. 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.
  4. Structured storage of additional information. When keeping records of a warehouse, it is important to collect additional information about sales (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 the required number of 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.

Warehouse accounting in Excel is suitable for any trade or industrial organization where it is important to take into account the amount of raw materials and materials, finished products. For this purpose, the company maintains warehouse records. Large firms, as a rule, purchase ready-made solutions for electronic accounting. There are a lot of options today, for various areas of activity.

In small businesses, the movement of goods is controlled on their own. For this purpose, you can use Excel tables. The functionality of this tool is quite sufficient. Let's get acquainted with some of the possibilities and independently compose our own warehouse accounting program in Excel.

At the end of the article, you can, which is disassembled and described here.

How to keep inventory in Excel?

Any custom-made or purchased inventory management solution will work well only if the basic rules are followed. If you neglect these principles at the beginning, then later the work will become more difficult.

  1. Fill out directories as accurately and in detail as possible. If this is a nomenclature of goods, then it is necessary to enter not only names and quantities. For correct accounting, you will need codes, articles, expiration dates (for individual industries and trade enterprises), etc.
  2. Opening balances are entered in quantitative and monetary terms. It makes sense to make an inventory before filling in the appropriate tables.
  3. Observe the chronology in registering transactions. The data on the receipt of products at the warehouse should be entered earlier than on the shipment of the 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 - payment method. Each organization has its own characteristics. A number of data entered into the warehouse accounting program in Excel will be useful for statistical reports, payroll 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 you can deduce general recommendations:

  1. For correct inventory control in Excel, reference books need to be compiled. They can take 1-3 sheets. This is the directory "Suppliers", "Buyers", "Points of account of goods". In a small organization, where there are not many contractors, reference books are not needed. It is not necessary to draw up a list of points of accounting of goods if the company has only one warehouse and / or one store.
  2. With a relatively constant list of products, it makes sense to make the nomenclature of goods in the form of a database. Subsequently, the receipt, expense and reports should be filled out with references to the item. 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. Disposal - "Expense". The current state is "Balances" ("Reserve").
  4. Totals, the report is generated using the PivotTable tool.

So that the headings of each inventory control 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 records, the user will see the column headings.



Excel spreadsheet "Inventory accounting"

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

Making "Directories".

For supplier data:


* The form may be different.

For customer data:


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

To audit the points of issue of goods:


We repeat once again: it makes sense to create such directories if the enterprise is large or medium-sized.

You can make a product list on a separate sheet:


In this example, we will use drop-down lists in the table for inventory control. Therefore, we need Directories and Nomenclature: we will make links to them.

Name the range of the "Nomenclature" table: "Table1"... To do this, select the table range and enter the appropriate value in the name field (opposite the formula line). 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 the incoming and outgoing transactions, we fill in two separate sheets.

We make a hat for the "Parish":

Next stage - automation of filling the table! It is necessary to make sure that the user chooses from a ready-made list the name of the product, supplier, accounting point. 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, we will format all directories as tables. This is necessary so that later you can add, change something.

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

In the "Data Type" field, select "List". An additional field "Source" appears immediately. To get the values ​​for the drop-down list 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 “Unit. rev. " the corresponding value should appear. Let's do it using the VLOOKUP and UND function (it will suppress the error resulting from the VLOOKUP function when referencing 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 "Points of account" - where the received 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 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 kept. If it was previously conducted, then this column will contain leftovers. Names and units of measure are taken from the product range.

Columns "Receipt" and "Shipments" are filled using the SUMIF function. The remainders are calculated using mathematical operators.

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


So the independently compiled program is ready.

Warehouse stock management- one of the most important components in doing business. The quality and success of the organization depends on the situation at the storage facility. That is why entrepreneurs are required to pay sufficient attention to how to properly organize a warehouse management system and monitor it in the future.

Due to the importance of this issue, we have repeatedly touched on the topic in articles and.

Now let's dwell in more detail on how convenient it is to monitor the movement of stocks using the simplest MS Excel program.

Most organizations selling a product have a warehouse for storage. Hence the justified need for record keeping. The use of dedicated software helps organizations to work more efficiently, while reducing costs and increasing profits. MS Excel is great for small shops and other small organizations.

PossibilitiesMSExcel:

  • Shows the amount of stock in the warehouse
  • Structures and analyzes data on available assets
  • Sales reports for any period of time

Accounting for goods inMSExcel: benefits

MS Excel is exactly the program that can simplify warehouse accounting and has a number of functions for controlling the movement of goods.

Accounting for goods in MS Excel: disadvantages

It is important to note that Excel has many drawbacks. For example, a complex series of operations that require work skills and qualifications, as well as large time resources, which we so often lack. Due to errors that sometimes occur in the program, the loss of all existing data is possible.

Excel functions for inventory management

The product consists of tables that are filled with data. You can select individual products or product groups. With this software tool, it is possible to perform analytical functions and operations using formulas. The program requires the necessary knowledge to work with it. Try to make the most of all the capabilities of the computer program. The greater the set of functions you know and are able to handle, the more opportunities you have in conducting inventory control.

With the help of Excel, it is possible to track the arrival and departure of products. You can also calculate the data regarding the actions performed in the warehouse, for example, find out the amount of leftovers for a specific type of footwear or a number of goods. To minimize the amount of leftovers, find out which brand of shoes is the most popular. Read the recommendations for the sale of children's shoes.

Suppose you want to stretch the head of a table over two or more lines. To do this, turn on the word wrap function directly in the cell in advance, and then you will not have any difficulties. Your table will look neat.

The program allows you to insert various fonts, which are very convenient to use when highlighting a number of products or individual categories, based on their parameters.

It is preferable to highlight more important product groups by formatting cells, columns or rows. These techniques will provide an easy perception of the information in the table.

Tips for managing inventory usingExcel

The provision of accounting for products that are in stock is explained by the need to monitor, control store assets, and draw up reports during the sales period. It is recommended that a competent specialist be responsible for this, who perfectly knows the basics of MS Excel and knows how to apply them in practice.

Let's give an illustrative example of how to get started with managing warehouse stocks in MS Excel (all values ​​are approximate):

At the beginning of the table, the flow rate should be kept:

MS Excel inventory table

Going down a few hundred cells below and create a receipt table, for example, in three cells: 1-item, 2-quantity, 3-purchase price.

Goods receipt table in MS Excel

Each item that has just arrived at the warehouse needs to be assigned a price, which is very easy to do in the program. Fill out the table, for example, shoes, sandals, sneakers, sneakers, boots, and others. These are generalized names, in practice, you will fill in the product column with other names. For example, order products that are popular with your audience of buyers for your store. The list is individual for each store.

Important! So that the name of the goods in the consumption table coincides with the name that is indicated in the invoices. Therefore, it is necessary to write the name in full in the table, without shortening it. You can include information down to the size and color of the shoe. The more specific the information is, the more convenient it is.

Quantity and purchase price. These columns are filled in fact.

It is easy to add a new item to the list with just two clicks. Also, immediately set the price, quantity and other parameters.

 

It might be helpful to read: