How often is a management accounting report prepared? Investment Activity Reports

Management reporting is one of the most important sources of obtaining information about the company’s performance, based on a set of financial, sales, marketing, production and other indicators.

Information in management reporting should be economically interesting and actively used by managers, founders and business owners. The data disclosed in management reporting is necessary for the analysis of all activities. This helps to timely identify the reasons for possible deviations from the parameters set by the business strategy, as well as show reserves (financial, material, labor, etc.) that have not been used by the company until this time. The process of setting up and implementing management reporting can be divided into 7 stages.

Step 1. Diagnostics of the existing management system in the company.

This stage is necessary for analysis organizational structure company, the process modeling format is determined. If the company has business process diagrams and their descriptions, these documents are analyzed and the main problem areas that require optimization are identified.

Diagnostic goals	Search for systematic approaches to increasing the efficiency of management reporting
Classification and analysis of existing reporting forms	By presentation form - tabular, graphic, text; By business segments – procurement reports, sales reports, tax reports; By targeting of presentation - reports for management, reports for heads of the Central Federal District, reports for managers; By the volume of information - operational reports on current projects, investment reports, final financial reports, summary (master) reports; Content - comprehensive reports, analytical indicators, reports on key performance indicators KPI.
Improving the quality and reducing the time required to obtain output analytical information necessary for making quality management decisions.	Analytical reports are of high value when they can be obtained in a short time and contain information in a form that best meets the needs of the employee who makes decisions based on this report.
Increasing the reliability of stored information.	To make decisions, you must rely only on reliable information. It is not always possible to understand how reliable the information presented in the reports is; Accordingly, the risk of making poor-quality decisions increases. On the other hand, if an employee does not bear official responsibility for the accuracy of the information entered, then with a very high degree of probability he will not treat the information with due care.
Increasing the analytical value of information.	A non-systematic approach to entering and storing information leads to the fact that, despite the fact that large amounts of information are entered into the database, it is almost impossible to present this information in the form of reports. Non-systematicity here means the input of information by employees without the development of general rules, which leads to a situation where the same information is presented to different employees in a different form from each other.
Elimination of inconsistency and inconsistency of information	If there is unclear clarity regarding the division of responsibilities and rights between employees to enter information, the same information is often entered multiple times in different departments of the company. In combination with a non-systematic approach, the fact of duplication of information may even be impossible to determine. Such duplication makes it impossible to obtain a complete report based on the entered information.
Increasing the predictability of obtaining a certain result	Decision making is almost always based on assessing information from past periods. But it often happens that necessary information It was just never introduced. In most cases, it would not be difficult to store missing information if someone assumed in advance that it would someday be needed.
Result	Based on diagnostics and decisions made are being finalized job descriptions, existing business processes are reengineered, reporting forms that do not provide information for data analysis are eliminated, KPI indicators are introduced, accounting systems are adapted to obtain actual data, the composition and timing of management reporting are fixed.

Step 2. Creating a management reporting methodology

This stage is necessary for delegating authority in terms of drawing up operating budgets and determining the responsibility of specific financial responsibility centers (FRCs) for drawing up certain budget plans (segments of management reporting).

Goals and objectives solved as a result of the implementation of management reporting in the company:

• Establishing and achieving specific key performance indicators (KPIs);
• Identification of “weak” links in the organizational structure of the company;
• Increasing the performance monitoring system;
• Ensuring transparency of cash flows;
• Gain payment discipline;
• Development of an employee motivation system;
• Prompt response to changes: market conditions, sales channels, etc.;
• Revealing internal resources companies;
• Risk assessment, etc.

The composition of management reports depends primarily on the nature of the company's activities. As practice shows, the composition of management reporting (master report) usually includes:

• Traffic report Money(direct method);
• Cash flow statement (indirect method);
• Gains and losses report;
• Forecast balance (managerial balance);

Consolidation of budgets

The preparation of consolidated management reporting is a rather labor-intensive process. Consolidated financial statements treat a group of related entities as a single entity. Assets, liabilities, income and expenses are combined into a common management reporting system. Such reporting characterizes the property and financial position of the entire group of companies as of the reporting date, as well as the financial results of its activities for the reporting period. If the holding consists of companies that are not connected with each other at the operational level, then the task of consolidating management reporting is solved quite simply. If business transactions are carried out between the companies of the holding, then in this case not everything is so obvious, because it will be necessary to exclude mutual transactions so as not to distort the data on income and expenses, assets and liabilities at the holding level in the consolidated statements. The company's budget policy needs to consolidate the rules and principles for eliminating VGOs.

For this purpose it is more expedient to use specialized Information Systems, for example, "WA: Financier". The system allows you to eliminate intra-company turnover at the level of processing primary documents and quickly obtain correct information, which simplifies and speeds up the process of generating management reporting and minimizes errors associated with the human factor. At the same time, the reconciliation of intragroup turnover, their elimination, the execution of corrective entries and other operations are carried out automatically.

Example: Company A owns Company B 100%. Company A sold goods for the amount of 1,500 rubles. The purchase of this product cost company A 1000 rubles. Company B paid for the goods delivered in full. At the end of the reporting period, Company B did not sell the product, and it is included in its reporting.

As a result of consolidation, it is necessary to eliminate the profit (500 rubles) that the company has not yet received and reduce the cost of inventories (500 rubles). To exclude VGOs and profits that Company B has not yet earned. Adjustments need to be made.

Result of management reporting consolidation

Determination of key performance indicators (KPI – Key performance indicators)

The introduction of key control indicators allows you to manage financial responsibility centers by setting limits, standard values or the maximum limits of accepted indicators. The set of performance indicators of individual central financial districts significantly depends on the role of this center of responsibility in the management system and on the functions performed. The indicator values ​​are set taking into account the company’s strategic plans and the development of individual business areas. The system of indicators can take on a hierarchical structure, both for the company as a whole, and with detail down to each center of financial responsibility. After detailing the top-level KPIs and transferring them to the levels of the Central Federal District and employees, staff remuneration, etc. can be linked to them.

Monitoring and analysis of the execution of management reporting.

For the execution of budgets included in management reporting, three areas of control can be distinguished:

• preliminary,
• current (operational)
• final.

The purpose of preliminary control is to prevent potential budget violations, in other words, to prevent unreasonable expenses. It is carried out before business transactions are carried out. The most common form of such control is the approval of requests (for example, for payment or shipment of goods from a warehouse).

Current control over budget execution involves regular monitoring of the activities of financial responsibility centers to identify deviations in the actual performance indicators from those planned. Conducted daily or weekly based on operational reporting.

Final control of budget execution is nothing more than an analysis of the implementation of plans after the closure of the period, an assessment of the financial and economic activities of the company as a whole and by facility management accounting.

In the process of executing budgets, it is important to identify deviations at the earliest stages. Determine what methods of preliminary and current budget control can be used in the company. For example, introduce procedures for approving requests for payment or release of materials from the warehouse. This will allow you to avoid unnecessary expenses, prevent budget failure and take action in advance. Be sure to regulate control procedures. Create a separate budget control regulation. Describe in it the types and stages of inspections, their frequency, the procedure for revising budgets, key indicators and ranges of their deviations. This will make the control process transparent and understandable, and will increase executive discipline in the company.

STEP 3. Design and approval of the company's financial structure.

This stage includes work on the formation of classifiers of budgets and budget items, the development of a set of operating budgets, planning items and their relationships with each other, and the imposition of types of budgets on the organizational units of the company’s management structure.

Based on the organizational structure of the company, a financial structure is developed. As part of this work, financial responsibility centers (FRCs) are formed from organizational units (divisions) and a model of the financial structure is built. The main task of building the financial structure of an enterprise is to get an answer to the question of who should draw up what budgets in the enterprise. A correctly constructed financial structure of an enterprise allows you to see the “key points” at which profits will be formed, taken into account and, most likely, redistributed, as well as control over the company’s expenses and income.

The Financial Responsibility Center (FRC) is an object of the company’s financial structure that is responsible for all financial results: revenue, profit (loss), costs. The ultimate goal of any central financial institution is to maximize profits. For each central financial district, all three main budgets are drawn up: a budget of income and expenses, a cash flow budget and a forecast balance (managerial balance sheet). As a rule, individual organizations act as central financial districts; subsidiaries of holdings; separate divisions, representative offices and branches large companies; regionally or technologically isolated types of activities (businesses) of multi-industry companies.

Financial accounting center (FAC) is an object of the company’s financial structure that is responsible only for some financial indicators, for example, income and part of the costs. For the DFS, a budget of income and expenses or some private and functional budgets (labor budget, sales budget) are drawn up. The DFS can be the main production workshops participating in unified technological chains at enterprises with a sequential or continuous technological cycle; production (assembly) shops; sales services and divisions. Financial accounting centers may have a narrow focus:

• marginal profit center (profit center) - a structural unit or group of units whose activities are directly related to the implementation of one or more business projects of the company that ensure the receipt and accounting of profits;
• income center - a structural unit or group of units whose activities are aimed at generating income and do not include profit accounting (for example, a sales service);
• investment center (venture center) - a structural unit or group of units that are directly related to the organization of new business projects, profits from which are expected in the future.
• cost center is an object of the financial structure of an enterprise that is responsible only for expenses. And not for all expenses, but for the so-called regulated expenses, the expenditure and savings of which the management of the Central Bank can control. These are departments that serve the main business processes. Only some auxiliary budgets are drawn up for central planning. The auxiliary services of the enterprise (housekeeping department, security service, administration) can act as a central protection center. A cost center may also be referred to as a cost center (cost center).

STEP 4. Formation of a budget model.

There are no strict requirements for the development of a classifier of internal management reporting. Just as no two companies are exactly alike, no two budget structures are exactly alike. Unlike formal financial statements: profit and loss statement or balance sheet, management reporting does not have a standardized form that must be strictly followed. The structure of internal management reporting depends on the specifics of the company, the budget policy adopted by the company, the wishes of management regarding the level of detail of articles for analysis, etc. You can only give general recommendations, how to create an optimal management reporting structure.

The structure of management reporting should correspond to the structure of the company's daily activities.

Classification of articles using the example of the Cash Flow Statement.

STEP 5. Approval of budget policy and development of regulations.

Budget policy is formed with the aim of developing and consolidating the principles for the formation and consolidation of indicators for these items and methods for their assessment. This includes: determination of the time period, planning procedures, budget formats, action program of each of the participants in the process. After developing the budget model, it is necessary to move on to regulating the budget process.

It is necessary to determine which budgets are formed in the company and in what sequence. For each budget, it is necessary to identify a person responsible for preparation (a specific employee, a central federal district) and someone responsible for the execution of the budget (the head of a department, a head of a central federal district), and set limits, standard values ​​or maximum boundaries for the performance indicators of a central federal district. It is imperative to form a budget committee - this is a body created for the purpose of managing the budget process, monitoring its execution and making decisions.

Step 6. Audit of accounting systems.

At the stage of development and approval of the composition of the company’s management reporting, it is also necessary to take into account that the classifier of budget items must be sufficiently detailed to provide you useful information about the company's income and expenses. At the same time, you need to understand that the more levels of detail are allocated, the more time and labor costs will be required to draw up budgets and reports, but the more detailed analytics can be obtained.

It is also necessary to take into account that as a result of developing a management reporting methodology, adaptation of accounting systems may be required, because To analyze budget execution, planned indicators will have to be compared with available actual information.

Step 7. Automation.

This stage includes work on selecting a software product, creating technical specifications, implementation and maintenance of the system.

What is management reporting? Speaking in simple language, management reporting- information that managers use, and in order to use it, they ask someone for it. For example, you can ask an accountant for management reporting. Or they themselves look at bank statements and accounting program. And then, it happens, they copy the management reporting into their notebook and recalculate it on a calculator.

At first, such a set of information (future management reporting), that is, management reporting, appears spontaneously: if you need it, you ask. Then the manager realizes that it is necessary to create a fixed form for reporting, which should be asked in completed form at a certain frequency. Then one form is not enough, several appear and fill them out different people. Then the manager begins to “wander” at thirty different tables oh, where the same data, for example, on sales by region, are written in different orders and for some reason have different meanings, throws everything into the basket, and calls the accountant: “make me one form, but so that it’s clear!” And the fairy tale about creating management reporting begins all over again.

How to make internal management reporting convenient, relevant and reliable? Just don't think it's easy. The development of management reporting is an organizational, not an economic, task, and it requires a systematic approach.

Management reporting step by step

In order to competently draw up management reporting, here's what you need to do.

1. Make a list of people who need to use management reporting.

For example: the general director, commercial director, sales managers, head of OMTS - they will all need management reporting at some point.

2. Collect existing management reporting as is. If financial statements are used for management purposes (the director is well versed in turnover), include them in the kit as well.

As examples of various reports, let’s take: a sales report by branches, an analysis of account 10 by subconto, a report on current payments (prepared by an economist in Excel), a report on accounts receivable (prepared by the deputy chief accountant), etc.

3. Create a matrix for management reporting: report users / types of reports, at the intersection write down what exactly each user looks at in the report (literally where he looks, in which cell, at what result - in order to detect useless information or information in an inconvenient form, when the user of the report recalculates something else on the calculator). We also need to collect “wants” for our reporting: ask questions and write down what people are missing in existing reporting. If the enterprise has indicators that are used as targets and control indicators, i.e. by which employees are evaluated and by which the owner controls the general director, then it is imperative to include them in the table in the cells of the reports from which their values ​​are taken. This point is important when preparing management reporting.

For example:

At this stage, you get an “as is” picture; everything rational needs to be taken out of it, so that everything that was previously used will be included in the new management reporting.

4. Compile a management classifier of income and expenses (BDR), cash flows (CBDS), investment budget and items to account for turnover between balance sheet items. A sample classifier can be found here.

5. Next, to create management reporting, you need to decide what other analytical reference books are needed to prepare management reporting. For example, looking at the table above, you can immediately say that the development of reporting will require directories of divisions (branches), regions, product groups, sales managers, and possibly also cost items and contractors. For cash reports (cash management reports), you usually need analytics on the places where funds are stored: current accounts, cash desks, reporting accounts. These directories must either be taken from existing databases (for example, a directory of counterparties can be taken from an accounting database), or compiled by ourselves and agreed upon with everyone who uses reporting in the context of such a directory. When preparing management reporting, it is also recommended to pay attention to this point.

6. Next, to create management reporting, you need to make the main reporting forms: BDR, BDDS, Balance sheet. Make sure that the reporting includes the information that managers used before. Create these forms using real data. If management reporting forms are made in Excel, then immediately include the necessary analytics in this form, for example, display regions or product groups by turnover item, and make columns by month. If management reporting forms are made in the “Business Planning” program, then it is enough to fill out the “Budget Forms” reference book and check that the report contains a breakdown of all the necessary analytics. Examples of reports (management reporting) can be viewed here.

7. Create other forms of management reporting based on turnover articles and analytical reference books. First of all, to create management reporting, make forms similar to those already used in the enterprise. Then look at materials from similar enterprises, look at the collected “wish lists” of users and suggest additional forms management reporting.

8. Add formulas and calculated values ​​to the generated reports. At this step, our management reporting is already beginning to take on the proper form for management reporting.

9. Generate (or fill out) management reporting forms with real data for the same month. Coordinate the resulting completed forms with the performers who previously compiled management reporting.

10. Coordinate the completed reporting with users according to the list from clause 1. The process may be iterative, i.e. management reporting will need to be completed based on comments.

Management reporting, what's the output?

You will be surprised how quickly people get used to convenient forms; after a week no one remembers how it was before. But that’s if they turned out to be comfortable. If the manager still recalculates something in notebook, or calls and asks what a certain line means, or compares duplicate information from different tables - keep working, the management reporting system has not yet worked out.

Thus, management reporting is prepared in stages. It is important, when preparing management reporting, to ensure that the reporting is completed correctly at every stage. If all “tasks” are carefully completed, management reporting will be drawn up correctly.

The article will touch upon the main issues related to management reporting. What the document is, why it is needed, and how to fill it out correctly - below.

Legislation requires organizations to submit reports to regulatory authorities. This concerns financial statements; some enterprises also conduct management ones.

It is not necessary to compile them, but they must be attached to the accounting documentation. What is management reporting?

General points

Management reporting is necessary for making the right decisions, as it contains information about the state of affairs at the enterprise.

Problems that reporting solves:

• providing the necessary information;
• preparation of documentation necessary for regular reporting;
• forecast and analysis of the activities of the organization and its branches;
• approval of correct decisions based on truthful data;
• increasing financial discipline.

The management accounting and reporting system allows you to solve the following problems:

• create a management that will be aimed at increasing the effectiveness of decisions made;
• evaluate the management of all aspects of financial activity and its results;
• make the costs of preparing and filing reports minimal;
• link analytics and accounting rules;
• create a basis for motivating employees.

If an organization is being restructured, reporting forms cannot be developed. Has the following characteristics:

Concepts

Management reporting	Documentation that includes information necessary to exercise control over the activities of the organization. Indicates the financial position of the enterprise
Consolidated reporting	Financial reporting of several interrelated organizations that are treated as one. The document characterizes the property and financial position of the group as of a certain date, which is the reporting date
Management reporting	A set of documentation within an organization containing figures showing aspects of activity. Is voluntary. the main objective– provide the governing bodies of the enterprise with truthful information about the results of activities
Subsidiary	A society formed on the basis of another, which exercises control over it and makes decisions
Parent company	A company that has a large amount of capital from another company. Controls the activities of companies dependent on it through their shares
Consolidation	A type of legal formation, the purpose of which is to create a document (normative legal act) that would not have an impact on existing acts and would not change their essence
Limited liability company	A company that was created by one or more persons (both individuals and legal entities) and whose capital is divided into parts

Main types

Management reporting is divided into several types:

• complex;
• according to the final results;
• analytical.

A comprehensive report covers the activities of the enterprise, its branches and affiliates in full.

It is provided at a certain time - a report for the day, for the month, etc. Such a report displays the results of the organization’s activities as a whole and for each segment, as well as costs, debts, etc.

Reporting on final ratios can be provided at any time. Contains the most important data for the enterprise on the number of orders received for certain products.

Characterizes the degree of their implementation, whether there was a defect and in what quantity, what were the sales volumes and resources used.

The analytical type of reporting is prepared at the request of governing bodies. The report may contain information about the reasons for the increase in inventories, the number of irregular work, a drop or increase in sales.

Normative base

Regulatory acts that must be used when preparing management reporting:

Formation of management reporting for the organization

The reporting generation algorithm is as follows:

1. Find out from or him what information he needs to submit and with what frequency.
2. Conversation with an accountant to clarify details.
3. Creation of documentation that will highlight the indicators and their interpretation. The person responsible for reporting may prepare the reporting forms separately for each governing body.
4. Generating a report.

What does it include (forms)

Forms include, income and expense report,. The balance displays the numbers and their interpretation.

It is the basis for the formation of financial statements. It records the results of activities for a specific period of time.

Forms are developed based on the following principles:

Basic principles of formation

The main principles when compiling are:

• efficiency;
• reliability;
• consistency of coefficients;
• content;
• truthfulness.

The reporting procedure requires the following principles:

• rapidity;
• direction;
• concreteness;
• non-disclosure;
• neutrality.

Internal

Internal management reporting is the main part on which the management structure. It contains the main coefficients and basic information.

Compilation requirements:

• the information provided in the report must correspond to the purpose for which it is created;
• internal reports should not contain subjective opinions or biased assessments;
• documentation must be submitted within the time frame when a decision needs to be made;
• There should not be any unnecessary information. The smaller the report, the more quickly a decision is made and the content of the document is comprehended;
• the report must be compared with the plans;
• the document must go to the responsible person manager, who will not disclose information.

Internal management reporting is used to provide personnel at any level with the necessary information.

Developing reports of this type is not easy; they must contain a flexible structure, clarity of data, optimal frequency provision.

Each reporting form should contain the information that will be useful. Do not abuse or overload the document with numbers.

The most common examples of errors:

Internal reporting can be annual, quarterly, monthly, etc. It is necessary to provide the document when the decision is made. Frequency will not affect the adoption rate.

Comprehensive reporting	Includes information about the results of actions for a certain period of activity. It must be provided regularly and in a timely manner, since the report displays costs and profits, financial movements and other important indicators
Thematic	Which is provided as deviations of important coefficients occur. These include losses due to defects, planned indicators, sales volumes
Analytical reporting	It is issued when requested by the governing bodies of the organization. Contains data about the reasons why a particular result occurred

According to the level of management, reporting can be operational, current and consolidated. An operational report is prepared every month or every week.

Contains the data necessary to make a decision. The current report is issued every month or quarter. Includes profit information.

The summary can be compiled either once a month or once a year. Contains information about the most significant data that influences strategic decision making.

Based on the volume of data, internal reporting is divided into summaries, final reports and general reports. Summaries – brief information about individual coefficients for a short period of time, for example, per day.

The final report is compiled every month and contains summarized information. General reporting is compiled for the enterprise as a whole.

Internal reporting can be presented in the form of tables, graphs or plain text. The most convenient form is a tabular one, since the report contains many digital indicators.

What does the bank’s management documentation allow?

Bank management documents include:

• non-cash payments;
• operational activities of the bank;
• documentation related to loans;
• control documents on labor costs;
• output documents;
• deposit documents;
• forms of documents used within the bank and others.

The listed documents make it possible to analyze activities and draw conclusions.

If consolidated

Consolidated financial statements include:

• balance;
• financial results report;
• financial flow reporting;

Such documentation includes reporting indicators of members of the consolidated group for the year. The bank prepares consolidated statements based on data received from group members.

In this case, one of the methods is used - full consolidation or participation in capital. Groups have different structures:

Consolidated financial statements are prepared by the bank for presentation to its shareholders.

The document must contain the following information:

• the nature of the relationship between subsidiaries and parent organizations;
• for what reasons does the investor who owns the majority of shares not exercise control over reporting;
• the date when the reporting period ends. The one for which reporting is prepared;
• restrictions that apply to the subsidiary (if any);
• situations where control over subsidiaries was lost.

The consolidated method involves collecting and processing a huge amount of data. Has the following stages:

• Preparation of reporting by all organizations that are members of the group.
• Making adjustments during consolidation (if necessary).
• Preparation of reports and provision.

Group characteristics:

• possession of the number of shares of a subsidiary created in the form of an LLC;
• exerting influence on the activities of the organization on the basis of a concluded agreement;
• the ability to appoint or recall a verification commission;
• participation in management bodies.

If at least one of the listed signs is present, then a group can form. Requirements for the preparation of consolidated financial statements:

The first reporting must be generated when a subsidiary is formed. Such reports must be published.

Filling example

It is within the capabilities of any company to build an effective and simple system for generating basic management reports in a relatively short period of time. After all, such reports are based on the information that, as a rule, each enterprise has.

As a business develops, management’s ability to control the main parameters of the company’s activities begins to play a fundamental role in its sustainability and the possibility of further development. The most clear and complete picture of the state of the enterprise is provided by management reports - on cash flows, profits and losses and the management balance sheet.

Download useful documents:

Initial information for generating management reports

The generation of basic management reports is based on the information that, as a rule, any company possesses.

Firstly, every enterprise has full information about cash flow. This can be both accounting data (statements on ruble and foreign currency accounts, cash reports, settlements with accountable persons), and information that may not be in the accounting data, for example from the register of settlements between individual businesses within the holding, etc. Secondly, any enterprise in one form or another has in its arsenal reports that characterize the state and dynamics of the most important assets and liabilities. Thus, we can say with confidence that each enterprise keeps records of inventories, mutual settlements with buyers and suppliers of products, or other assets and liabilities that are significant for this type of business.

Often this information is contained in several software products, which is why the data in the reports received does not always correspond to each other. Despite this, the availability of this information is sufficient to begin generating basic management reports.

At the same time, in the process of forming the management balance, all inconsistencies in the reports will be automatically identified, and, accordingly, sources of costs that were previously simply ignored will be discovered.

The most convenient way to prepare management reports is in Excel. This software product has excellent means analysis and processing of data, including when it comes to large amounts of information. By the way, there is a convenient service for maintaining management accounting in the cloud and you will no longer need any reports in Excel. .

Personal experience
Sergey Dmitriev,

Management accounting implementation plan

Before generating a cash flow statement, it is necessary to carry out the following procedures, which will subsequently ensure that information is obtained in the required context and with the required degree of detail.

1. Analysis of the enterprise structure. If an enterprise conducts several independent areas of activity, then it is advisable to maintain management accounting for them separately. It is necessary to highlight for each direction those cash flow accounts that serve it. If you have accounts servicing several types of businesses at once, the easiest way is to create an intra-company cash settlement center (RCC) and include all such accounts in it. At the same time, to generate a cash flow statement for each type of business, you should use extracts from the cash flow center for transactions related to this area.

2. Analysis of the structure of a separate line of business. If necessary, you can highlight the divisions for which the company’s management would like to see a report. This detail plays important role when drawing up company cash flow budgets by division. If on initial stage If such an analytical section of information is not provided, then in the future there will be no mechanism for monitoring the execution of budgets by each of the divisions.

3. Formation of a plan for cash flow items. This is also an important step on which the visibility of the final report will depend. However, constructing an outline of articles is a fairly simple and standard procedure, so within the framework of this article it makes no sense to dwell on its description.

If all the preliminary steps described above have been completed, then the further generation of the cash flow statement is quite simple. technical work. In MS Excel you need to create the form of the required report. Then you need to import cash flow account statements from the appropriate programs and, having written the appropriate formulas, summarize the data for each cash flow item on the summary sheet of the turnover report. It is also quite simple and useful to make separate reports with breakdowns of each cash flow item in the context of elementary transactions. An example of such a report is given in table. 1.

Table 1 Explanation of Article 15. Rental of premises

date	Cash flow account	Income (rub.)	Expense (RUB)	Description		Article	Balance by item (RUB)
				Counterparty	Note	ODDS
							0,00
12.03.06	Calc. check		152 000,00	LLC "Warehouse services"	Rent for May	15.1.	152 000,00
14.03.06	Calc. check		359 700,00	LLC "Rent of Offices"	Rent for May	15.1.	511 700,00
15.03.06	Calc. check		87 705,53	OJSC "Mosenergo"		15.2.	599 405,53
18.03.06	Cash register		140 000,00	Private security company "Granit"		15.3.	739 405,53
18.03.06	Calc. check		359 700,00	LLC "Rent of Offices"	Rent for April	15.1.	1 099 105,53
21.03.06	Calc. check		221 670,73	OJSC "Heat Networks"	Heat energy for March	15.2.	1 320 776,26
28.03.06	RCC		12 000,00	LLC "Equipment Rental"		15.1.	1 332 776,26
TOTAL:		0,00	1 332 776,26				1 332 776,26
			Summary of sub-items
			15.1.	Rental of premises	883 400,00
			15.2.	Communal payments	309 376,26
			15.3.	Security	140 000,00
			TOTAL:		1 332 776,26

These simple procedures that can be implemented in very little time short period time (from one week to a month, depending on the structure of the enterprise), allow you to establish complete control over the receipt and expenditure of funds. In addition, the generation of a cash flow report allows you to begin creating a cash flow budget in the context of selected divisions and items, as well as monitor the execution of these budgets, which significantly increases financial discipline in the enterprise.

Personal experience
Nikolay Sinitsyn,

At the initial stage of creating a company, it was necessary to quickly organize management accounting and generate basic management reports in order to ensure the management company’s control over the commercial and financial activities of regional divisions. Initially, it was decided to develop and implement a unified automated enterprise management system, including management, accounting and tax accounting based on 1C.

However, it was clear that developing the program would take considerable time. In this regard, at the first stage of the company’s development, management accounting in regional trade and production divisions was carried out according to the methodology described by the author in this article. This allowed the management company, during the development of an automated accounting system, to work with regional divisions on planning, accounting and control of their activities and, in the process, refine the principles and reporting algorithms to be automated.

Management balance sheet and profit and loss account

Let us immediately note that the formation of these two management reports is a single inseparable process. It is almost impossible to draw up a correct profit and loss account if you do not prepare a management balance sheet in parallel with it.

Reporting will require a cash flow statement and statements describing changes in the company's main assets and liabilities. Based on the data taken from these reports, the main entries will be made to form the income statement and changes in the management balance sheet.

Before starting to compile reports, it is necessary to analyze the structure of assets, liabilities, income and expenses of the enterprise and draw up a chart of accounts and items of income and expenses to construct a profit and loss statement.

For clarity, let’s consider the methodology for creating a management balance sheet and profit and loss statement for specific example. Let’s assume that an enterprise is engaged in trade and purchasing activities, pays taxes to the budget and salaries to employees. All other aspects of the enterprise's activities in this example we will not consider them, since they do not in any way affect the reporting methodology.

For such an enterprise, a simplified chart of accounts of the management balance sheet and a chart of items in the profit and loss statement can be presented in the form of a table. 2 and table. 3.

table 2. Account structure

Table 3 Structure of income statement items

It should be noted that, at its core, the profit and loss statement is a transcript of changes in the balance sheet item “Profit” for the reporting period. It is for this reason that generating a profit and loss statement without drawing up a balance sheet usually leads to incorrect results.

The inclusion of a auxiliary account (04) in the chart of accounts of the management balance sheet is associated with the need to highlight all discrepancies between the data of various reports for their further analysis and elimination (or write-off to the financial result of the reporting period).

Now let’s look at the management reports we need for our work and compare each of the values ​​in the reports with a certain posting on the balance sheet accounts, and if the posting concerns the “Profit” account, then also the posting on the items of the profit and loss statement (Table 4, table 5, table 6, table 7).

Table 4. Cash flow statement

Article title	Sum	Example in numbers	Wiring
Balance at the beginning of period	Coincides with item 01 of the balance sheet at the beginning of the period	35	–
Sales proceeds	A'	1000	No. 1 Dt 01. Kt 04.
Payment to suppliers	B'	800	No. 2 Dt 04. Kt 01.
Wage	C'	105	No. 3 Dt 06. Kt 01.
Taxes	D'	110	No. 4 Dt 07. Kt 01.
Balance at the end of the period	Coincides with item 01 of the balance sheet at the end of the period	20	–

Table 5 Report on settlements with customers

In this and subsequent examples, when making entries on account 08. “Profit”, we will indicate the income statement item as an additional analytics, which will allow us to correctly generate this report.

Please note that A' in the statement of cash flows and A'' in the accounts receivable statement represent the same parameter. However, it is not always possible to achieve complete coincidence of these values ​​in practice. In this example, A’ and A’’ are equal to 1000 and 1002, respectively. Such a discrepancy may be due to various reasons - the presence human factor, generation of reports in different currencies without correct accounting of exchange rate differences, etc.

Postings associated with these amounts are made in transit through auxiliary account 04. At the same time, the difference between A’ and A’’ remains for now in account 04. The same should be done with all similar parameters that are present in the two reports. In this example, this applies to parameters A, B and F.

Table 6. Goods movement report

Table 7. Accounts payable report

After the entries have been made in accordance with the data obtained from the management reports discussed above, we receive a partially completed management balance sheet. At the same time, balance sheet items 01, 02, 03 and 05 are finalized, since the balance on these accounts was calculated on the basis of available reports. Articles 06 and 07 (and, accordingly, 08) require additional entries related to the accrual of wages and taxes. This is easy to implement by making the following transactions indicated in table. 8.

Table 8. Additional transactions on balance sheet items that do not have specialized reports

Now all that remains is to deal with the balances on account 04, which is the sum of deviations between similar data in various reports. If such deviations are significant and the reason for their occurrence is not obvious, you should analyze the report data, identify the source of the discrepancies and, if necessary, make adjustments to eliminate the cause of their occurrence. If the amount of these deviations is insignificant or their cause is known, then account 04 should be reset by assigning these amounts to the corresponding items of the profit and loss statement.

Let’s say that the deviations A’ from A’’ and B’ from B’’ in the example we are considering are associated with exchange rate differences (the reports were generated in different currencies). The deviation of F' from F'' is due to the fact that the goods movement report does not take into account the arrival of any insignificant part of the assortment (for example, packaging material). In this case, you can reset the auxiliary account 04 with the transactions indicated in the table. 9.

Table 9 Additional Sub-Balance Account Transactions

Thus, we were able to build a system of entries that form the profit and loss statement and changes in the management balance sheet for the reporting period. In this case, data taken from standard reports generated at any enterprise were used. And, despite the fact that in reality the structure of the balance sheet, profit and loss statement and reporting forms that are used with this approach are more complex than in the example given, this technique can be easily applied in almost any enterprise.

Personal experience
Nikolay Sinitsyn, Head of Planning and Accounting Department of JSC " Trade company"Alco-Trade"

TO strengths of such a methodology, I would attribute the possibility of fairly quickly organizing management accounting at an enterprise and independence from the accounting records used software products. The disadvantages are that this mechanism is more focused on the financial part of management reporting.

In practice, operational management of an enterprise requires other important information necessary for in-depth analysis, control and management of sales, warehouse balances, mutual settlements with customers, etc. In other words, the express management accounting described by the author does not penetrate deeply enough into the business processes of enterprises. If such problems are solved within the framework of existing accounting systems, then the use of the described reporting methodology as a temporary measure is quite justified.

In addition, the disadvantages of this approach include the fact that employees of company branches in which management accounting is being implemented face additional labor costs - they have to keep records for themselves and records for the parent company, which, naturally, remains secondary for them. This often leads to discrepancies between accounting data and the actual situation at the enterprise.

Sergey Dmitriev, Financial Director of Aludeko-K LLC (Kostroma)

Opportunity in as soon as possible creating a visual enterprise financial management system without spending significant resources and without making significant changes to existing accounting programs is certainly strong point the technique under consideration. As for the disadvantages, in practice one has to face certain difficulties when analyzing discrepancies in the data of various reports. Naturally, the scale of this problem greatly depends on the quality of primary documentation at the enterprise.

In table 10 shows an example of calculating changes in the accounts of the management balance sheet and items of the profit and loss statement in accordance with the entries made above.

Table 10. Calculation of changes in management balance sheet accounts and profit and loss statement items for the reporting period

Wiring number	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	Total
Assets
01. Cash	1000	–800	–105	–110												–15
02. Settlements with customers					1300	–1002										298
03. Products							950	–968								–18
04. Sub account	–1000	800				1002	–950		947	–804			–2	4	3	0
Liabilities
05. Settlements with suppliers									947	–804						143
06. Settlements with personnel			–105								127					22
07. Calculations with the budget				–110								118				8
08. Profit					1300			–968			–127	–118	–2	4	3	92
Income statement items
08.01. Sales revenue					1300											1300
08.02. Cost of products sold								968								968
08.03. Wage											127					127
08.04. Taxes												118				118
08.05. Exchange differences													2	–4	–3	–5

Practice opinion
Irina Karavaeva, head of department financial control and analysis of JSC "Russian Electronics"

In my opinion, the main goal of organizing management accounting at enterprises is to provide managers with transparent and timely information for making management decisions. This allows you to solve the main accounting problems:
- lack of operational reporting (quarterly reporting is required by law);
- lack of transparency in information (methodologically it is recommended to distinguish only 5 groups of costs).

Thus, when forming a management balance sheet and profit and loss statement, the emphasis is on the introduction of additional accounting for items of expenses and income that are vital for the management of the enterprise, areas of activity, that is, ensuring the principle of reporting transparency.

It should also be noted that, in accordance with the legislation of the Russian Federation, all enterprises are required to maintain accounting records and prepare financial statements (the exception is enterprises that have switched to a simplified taxation system, but even in this case, they have simplified financial statements). Therefore, on the one hand, we can agree with the author in that all enterprises initially have all the necessary reports on financial and economic activities for organizing management accounting and reporting, but, on the other hand, the proposed algorithm does not take into account the fact that the implemented forms already available at the enterprise.

If we regard the article as an algorithm for setting up management accounting for enterprises with a simplified taxation scheme, which are exempt from the preparation of a cash flow statement and balance sheet (except for a report on fixed assets and intangible assets), then, in my opinion, the proposed methodology contains the following inaccuracies:

1. Generating a cash flow report. The algorithm does not cover the activities of auxiliary, support, and administrative departments; attention is also not focused on the formation of cash flow for the operational, investment and financial activities of the enterprise. In other words, the main goal of introducing a cash flow statement - building a system for managing the enterprise's cash flows and optimizing them - will not be achieved. Thus, the accumulation of flows in the main areas of activity, and then the detailing of these flows by divisions by cash flow items will not allow us to identify the net cash flow for all operating activities, as well as for investing and financing activities.
2. Formation of the management balance sheet and profit and loss account. The article presents an algorithm for generating a balance sheet, while notional accounts and codification are used to describe the process. This, in my opinion, creates some confusion, since in accordance with the regulations there is an accounting plan that would be logical to use.

Excel spreadsheet systems
with convenient analytics

Setting up initial (managerial) accounting is the creation of tools for obtaining information about the actual state of affairs in a business. Most often this is a system of tables and reports based on them in Excel. They reflect convenient daily analytics about real profits and losses, cash flow, salary arrears, settlements with suppliers or customers, costs, etc. Experience shows that a system of 4-6 easy-to-fill tables is enough for a small business.

How it works

The specialists of the My Financial Director company delve into the details of your business and create an optimal system of management accounting, reporting, planning, and economic calculations based on the most available programs (usually Excel and 1C).

The work itself consists of entering initial data into tables and takes no more than 1-2 hours a day. To carry it out, 1-2 of your existing full-time specialists who do not have accounting skills are enough.

A system of tables can be organized with division of access to information. Only the director (owner) of the business will see the overall picture and the secret part of the data, and the performers will each see their own part.

The resulting automatic reports provide a picture with the required level of detail: cost and profitability separately by product line, cost summaries by expense groups, profit and loss statement, cash flow statement, management balance sheet, etc. You make decisions based on informed, accurate and operational management information.

In the Questions and Answers section you will find examples of a cash flow plan and a cash flow accounting system with accompanying reports.

IMPORTANT! You receive services at the level of an experienced financial director at the rate of an ordinary economist.

Teaching or conducting

We make sure to train your employees independent work with tables. If you have no one to entrust this work to, we are ready to outsource your accounting. This is 2-3 times cheaper than hiring and maintaining an individual.

Guaranteed support and support 24/7

The work performed is covered by a guarantee. The initial accounting system is maintained in working order as long as you use it. If desired, you receive all the necessary consultations and clarifications.

If you want to change or add something, our specialists will make the necessary modifications, regardless of how long ago the service was provided. Contact us, the support service is available 24/7.

Where to begin

Call +7 950 222 29 59 to ask any questions and receive additional information.

Download analyzes and reports in Excel format

An archive of example files for site articles on performing various tasks in Excel: analyses, reports, document forms, tables with formulas and calculations, graphs and charts.

Download examples of analyzes and reports

Telephone directory template.
Interactive contact directory template for business. Convenient management of a large contact database.

Inventory accounting in Excel free download.
The warehouse accounting program is created exclusively using functions and standard tools. No macros or programming required.

Return on equity formula "ROE".
A formula that displays the economic meaning of the financial indicator “ROE”.

Management accounting in an enterprise - examples of Excel tables

An effective tool for assessing the investment attractiveness of an enterprise.

Supply and demand schedule.
A graph that displays the relationship between two main financial quantities: supply and demand. As well as formulas for finding the elasticity of supply and demand.

Complete investment project.
A ready-made detailed analysis of the investment project, which includes all aspects: financial model, calculation economic efficiency, payback periods, return on investment, risk modeling.

Shortened investment project.
A basic investment project, which includes only the main indicators for analysis: payback periods, return on investment, risks.

Analysis of the investment project.
Full calculation and analysis of the profitability of an investment project with the ability to model risks.

Gordon's formula graph.
Plotting a graph with an exponential trend line using the Gordon model to analyze investment returns from dividends.

Bertrand model diagram.
A ready-made solution for constructing a graph of the Bertrand model, which can be used to analyze the dependence of supply and demand under conditions of price dumping in duopoly markets.

Algorithm for decoding TIN.
Formula for deciphering the Tax Indicator Number for: Russia, Ukraine and Belarus.

All types of TIN (10 and 12 digit numbers) of individuals and legal entities, as well as personal number.

Factor analysis of variances.
Factor analysis of deviations in the marginal income of an enterprise taking into account the following indicators: material costs, revenue, marginal income, price factor.

Time sheet.
Download a time sheet in Excel with formulas for auto-filling the table + maintaining reference books for ease of work.

Sales forecast taking into account seasonality.
A ready-made sales forecast for the next year based on the previous year’s sales figures, taking into account seasonality. Forecast and seasonality charts are attached.

Forecast of enterprise performance indicators.
Form for forecasting the activity of an enterprise with formulas and indicators: revenue, material costs, marginal income, overhead costs, profit, return on sales (ROS)%.

Work time balance.
A report on planning the working time of enterprise employees according to such time indicators as: “calendar time”, “time”, “maximum possible”, “attendance”, “actual”.

Sensitivity of the investment project.
Analysis of the dynamics of changes in results in relation to changes in key parameters is the sensitivity of the investment project.

Calculation of the store's break-even point.
A practical example of calculating the timeframe for breaking even for a store or other type of retail outlet.

Table for holding financial analysis.
The software tool is made in Excel and is designed to perform financial analyzes of enterprises.

Enterprise analysis system.
An informative financial analysis of an enterprise can be easily carried out using the analytical system from professional specialists in Economics and Finance.

An example of a financial analysis of business profitability.
A table with formulas and functions for analyzing business profitability based on the financial indicators of the enterprise.

An example of how to maintain management accounting in Excel

Management accounting is intended to represent the actual state of affairs at the enterprise and, accordingly, make management decisions based on these data. This is a system of tables and reports with convenient daily analytics on cash flow, profits and losses, settlements with suppliers and customers, product costs, etc.

Each company chooses its own method of maintaining management accounting and the data needed for analytics. Most often, tables are compiled in Excel.

Examples of management accounting in Excel

The main financial documents of the enterprise are the cash flow statement and balance sheet. The first shows the level of sales, costs of production and sale of goods over a certain period of time. The second is the assets and liabilities of the company, equity capital. By comparing these reports, the manager notices positive and negative trends and makes management decisions.

Directories

Let us describe the accounting of work in a cafe. The company sells its own products and purchased goods. There are non-operating income and expenses.

An Excel management accounting spreadsheet is used to automate data entry. It is also recommended to compile reference books and journals with initial values.

If an economist (accountant, analyst) plans to list income by item, then the same directory can be created for them.

Convenient and understandable reports

There is no need to include all the figures for the cafe’s work in one report.

Let them be separate tables. And each one takes up one page. It is recommended to widely use tools such as “Drop-down lists” and “Grouping”. Let's look at an example of management accounting tables for a restaurant-cafe in Excel.

Income accounting

Let's take a closer look.

accounting, reports and planning in Excel

The resulting indicators were found using formulas (usual mathematical operators were used). Filling out the table is automated using drop-down lists.

When creating a list (Data – Data Verification), we refer to the Directory created for income.

Expense accounting

The same techniques were used to fill out the report.

Gains and losses report

Most often, for management accounting purposes, the income statement is used rather than separate income and expense statements. This provision is not standardized. Therefore, each enterprise chooses independently.

The created report uses formulas, auto-completion of articles using drop-down lists (links to Directories) and data grouping to calculate results.

Analysis of the cafe property structure

The source of information for analysis is the Balance Sheet asset (sections 1 and 2).

To better perceive the information, let's make a diagram:

As the table and figure show, the main share in the property structure of the analyzed cafe is occupied by non-current assets.

Download an example of management accounting in Excel

The Balance sheet liability is analyzed using the same principle. These are the sources of resources through which the cafe operates.

Cost items

So we need a project budget, which consists of cost items. First, let’s create a list of these same cost items in Micfosoft Project 2016.

We will use custom fields for. We create a substitution table for a custom field of the Text type for the Resources table, for example, as in this figure (of course, you will have your own cost items, this list just as an example):

Rice. 1. Formation of a list of cost items

Working with custom fields was covered in the Microsoft Project 2016 Project Management Tutorial (see section 5.1.2 Milestone). For convenience, the field can be renamed to Cost Items. After generating a list of cost items, they must be assigned to resources. To do this, add the Cost Items field to the Resources view and assign each resource its own cost item (see.

Management accounting in an enterprise: example of an Excel table

Rice. 2. Assigning cost items to resources

Microsoft Project 2016 features allow you to assign only one cost item per resource. This must be taken into account when creating a list of cost items. For example, if you create two cost items (1. Salary, 2. Social security contributions), then they cannot be assigned to one employee. Therefore, it is recommended to group cost items so that one item can be assigned to one resource. In our example, you can create one cost item - payroll.

To visualize the budget in terms of cost items and time periods, the Resource Usage view is well suited, which needs to be slightly modified as follows:

1. Create a grouping by cost items (see. Tutorial on project management in Microsoft Project 2016, section 2.5 Using groupings)

Rice. 3. Creating a grouping of Cost Items

2. On the left side of the view, instead of the Labor Costs field, display the Costs field.

3. In the right part of the view, instead of the Labor Costs field, display the Costs field (by clicking on the right side of the mouse):

Rice. 4. Select fields on the right side of the Resource Usage view

4. Set a convenient scale for the right side, for example, by month. To do this, right-click on the table header on the right side.

5. Project budget example

As a result of these simple steps, in Microsoft Project 2016 we obtain the project budget in the context of specified cost items and time periods. If necessary, you can detail each cost item down to specific resources and tasks by simply clicking on the triangle on the left side of the Resource Name field.

Rice. 6. Details of project costs

Project S-curve

To graphically display changes in costs over time, it is common to use a project cost curve. The shape of the cost curve is typical for most projects and resembles the letter S, which is why it is also called the S-curve of the project.

The S-curve shows the dependence of the amount of costs on the timing of the project. So, if work starts “As early as possible,” the S-curve shifts to the beginning of the project, and if work starts “As late as possible,” accordingly, to the end of the project.

Rice. 7. Project cost curve depending on task deadlines

By scheduling tasks “As early as possible” (this is set automatically in Microsoft Project 2016 when planning from the beginning of the project), we reduce the risks of missing deadlines, but at the same time it is necessary to understand the project’s financing schedule, otherwise there may be a cash gap on the project. Those. the costs of our tasks will exceed those available financial resources, which threatens the risk of stopping work on the project.

By scheduling tasks “As late as possible” (this is set automatically in Microsoft Project 2016 when planning from the end of the project), we expose the project to greater risks of missing deadlines.

Based on this, the manager must find a “golden mean”, in other words, a certain balance between the risks of missing deadlines and the risks of the project’s cash gap.

Rice. 8. Project cost curve in MS-Excel by downloading information from MS-Project

Drawing up an enterprise budget in Excel, taking into account discounts

The budget for the next year is formed taking into account the functioning of the enterprise: sales, purchasing, production, storage, accounting, etc. Budget planning is a long and complex process, because it covers a large part of the operating environment of organizations.

For a clear example, let’s consider a distribution company and draw up a simple enterprise budget for it with an example in Excel (an example budget can be downloaded from the link below the article).

Management accounting in an enterprise using Excel tables

In your budget, you can plan expenses for bonus discounts for customers. It allows you to model various loyalty programs and at the same time control costs.

Data for budgeting income and expenses

Our company serves about 80 clients. The range of goods is about 120 items in the price list. She makes a markup on goods of 15% of their cost and thus sets the selling price. Such a low markup is economically justified by intense competition and is justified by the large turnover (as in many other distribution enterprises).

A bonus reward system is offered to clients. Discount percentage on purchases for large customers and resellers.

The conditions and interest rate of the bonus system are determined by two parameters:

1. Quantitative limit. The quantity of a specific product purchased that gives the customer the opportunity to receive a certain discount.
2. Percentage discount. The size of the discount is a percentage that is calculated from the amount the client purchased when overcoming the quantitative limit (bar). The size of the discount depends on the size of the quantitative limit. The more goods purchased, the greater the discount.

In the annual budget, bonuses belong to the “sales planning” section, so they affect important indicator firm - margin (profit indicator as a percentage of total income). Therefore, an important task is the ability to set several bonus options with different boundaries at sales levels and the corresponding % bonuses. It is necessary that the margin be kept within certain limits (for example, not less than 7% or 8%, because this is the company’s profit). And customers will be able to choose several options for bonus discounts.

Our budget model with bonuses will be quite simple, but effective. But first, let’s draw up a report on the movement of funds for a specific client to determine whether it is possible to give him discounts. Pay attention to formulas that reference another sheet before calculating the percentage discount in Excel.

Drawing up enterprise budgets in Excel taking into account loyalty

The budget project in Excel consists of two sheets:

1. Sales – contains the history of the movement of funds for last year for a specific client.
2. Results – contains the conditions for accruing bonuses and a simple account of the distributor’s performance, which determines the forecast of the client’s attractiveness indicators for the company.

Cash flow by clients

The structure of the table “Sales for 2015 by client:” on the “sales” sheet:

Enterprise budget model

On the second sheet we set the boundaries for achieving bonuses and the corresponding discount percentages.

The following table is a basic form of an income and expense budget in Excel showing the firm's overall financial performance for an annual period.

Structure of the table “Conditions of the bonus system” on the “results” sheet:

1. Bonus bar border 1. Place to set the level of the border bar by quantity.
2. Bonus % 1. Place to set a discount when crossing the first border. How is the discount for the first border calculated? Clearly visible on the “sales” sheet. Using the function =IF(Quantity > limit of 1 bonus bar[quantity]; Sales volume * percentage of 1 bonus discount; 0).
3. Bonus bar limit 2. A higher limit compared to the previous limit, which makes it possible to get a larger discount.
4. Bonus % 2 – discount for the second border. Calculated using the function =IF(Quantity > limit 2 of the bonus bar [quantity]; Sales volume * percentage of 2 bonus discount; 0).

Structure of the table “General report on the company’s turnover” on the “results” sheet:

Ready-made enterprise budget template in Excel

And so we have a ready-made enterprise budget model in Excel, which is dynamic. If the bonus limit is at the level of 200, and the bonus discount is 3%. This means that last year the client purchased 200 items. And at the end of the year he will receive a bonus discount of 3% of the cost. And if a client purchased 400 pieces of a certain product, it means that he has crossed the second limit of bonuses and already receives a 6% discount.

Under such conditions, the “Margin 2” indicator will change, that is, the distributor’s net profit!

The task of the head of a distribution company is to select the most optimal levels of boundary strips to provide discounts to customers. You need to choose so that the “Margin 2” indicator is at least within the range of 7% -8%.

Download the enterprise budget-bonus (sample in Excel).

In order not to search for the best solution at random, and to avoid making mistakes, we recommend reading the following article. It describes how to make a simple and effective tool in Excel: Data table in Excel and matrix of numbers. Using the “data table” you can automatically visualize the most optimal conditions for the client and distributor.