Bdds sample in excel. Why Cash Flow Analysis Is Important

Cash flow, cash flow, cash flow (from English Cash Flow) or cash flow is one of the most important concepts of modern financial analysis, financial planning and financial management of an enterprise.

Cash flow is the difference between cash receipts and cash payments to an organization over a specified period of time. Most often, this time period is taken to be a financial year.

To assess the changes and dynamics of the financial position of the enterprise, a cash flow plan for the enterprise is drawn up, taking into account all cash receipts and all payments. used for budgeting the activities of an enterprise, when drawing up a business plan, and developing a cash flow budget.

If the numerical value of cash flow is greater than zero, this is an indicator of cash inflow. If the numerical value of the cash flow is less than zero, then there is an outflow of funds.

Positive cash flow is formed by the funds received by the company for the corresponding period. This may be revenue from the sale of goods, receipt of funds for the performance of work or provision of services. Negative cash flow is formed by the funds spent by the enterprise in the corresponding period. For example, investments, loan repayment, costs of raw materials, electricity, materials, employee compensation, taxes and others.

Proper cash flow management is extremely important because... can reduce capital requirements, accelerating its turnover, as well as identify financial reserves within the enterprise and thereby reduce the volume of external loans. The main goal of cash flow analysis and management is to increase the volume of positive cash flow and reduce the volume of negative cash flow.

Why is cash flow analysis important?

If a company does not pay due attention to the analysis and management of cash flows, then it is very difficult for it to predict possible cash gaps. This leads to the fact that at the end of the month she may not have money to pay current bills for supplies of goods, office rent, employee salaries, and even taxes.

The regular occurrence of cash gaps leads the enterprise to problems both with suppliers of goods and services, and with clients. Suppliers, dissatisfied with payment problems, cancel discounts and suspend shipments of goods. There is a commodity shortage, customers cannot receive the goods in demand, and for this reason they are in no hurry to pay bills for shipments already made and services provided. Accounts receivable are growing, which further aggravates financial problems with suppliers. A "vicious circle" arises. This situation dramatically affects the turnover of the enterprise, reduces its profitability and profitability.

Thus, the insolvency of a company occurs at the moment when cash flow becomes negative. It is important that such a situation can arise even if the enterprise formally remains profitable. This is precisely what causes the problems of profitable but illiquid companies on the verge of bankruptcy.

Cash flow calculation in Excel

What automation tools are best used to analyze and manage an enterprise’s cash flows? Every business owner answers this question for himself.

There is a choice between expensive specialized programs for accounting cash flows and creating an application for accounting and analysis cash flows to your requirements using Excel. Functionally big difference There is no difference between these options.

Specialized programs may have a more beautiful interface, an abundance of buttons and large number different functions, some of which are never used. However, specialized cash flow accounting programs have several big disadvantages. Firstly, development time. Most often, this is several months. Then implementation - another couple of months. And if accounting needs change, which happens quite often, there is a considerable amount of money for adding new reports and processing by programmers.

Cash flow accounting solutions developed in Excel do not have these disadvantages. But the undoubted advantages are the flexibility of the solutions, the ability to quickly change to suit changing accounting conditions, the ability to make small changes by the user himself, and the versatility of the Excel spreadsheet editor. There is no accounting task that cannot be accomplished using Excel!

Pavel Sukharev
Head of the Planning, Budget Control and Financial Reporting Department of Globus Telecom CJSC
Financial Director
No. 6 (95) June 2010

The classic cash flow statement, no matter how strange it may sound to financial director, is not very clear to the CEO and other top managers of the company. The main problem is the abundance of numbers that the uninitiated have to delve into and understand. There is a way to make the company's main report more visual and understandable.

1. Leave only the most important things

The cash flow statement (CFS) is one of the most reliable sources of information about the true state of affairs in the company. At least, many CEOs and owners are convinced of this. But for greater clarity, it makes sense to modify the standard form. The approach to finalizing the ODDS described below was successfully implemented at Globus Telecom.

Most often, non-financiers in ODDS are confused by the following: - too many redundant indicators (items). For example, if a commercial director uses the report, he is not at all interested in what happens to the money for investment and financial activities. It's just not his area of ​​expertise; It is unclear which trends predominate in the report - negative or positive. For the same commercial director, it would be useful to add one or two additional coefficients to the report that unambiguously characterize the operating flow; It is not clear how significant the deviations from the plan under the articles of the general income tax liability agreement are and how they affect the company’s position. You have to spend time figuring out whether the deviation is large and whether something urgently needs to be changed.

So, the first thing you need to do is reduce the number of lines in the report, getting rid of unnecessary items. We reason as follows. What types of activities are covered by the responsibility of a particular top manager? In other words, which section of the report should be kept and which should be hidden? For example, information about operational activities is important for a commercial director, and investment information is important for the head of a capital construction department. Next, which articles from the remaining section of the report will be of interest to a particular manager? Of course, the most detailed articles relating specifically to his department. In the case of the commercial director, these are the items “Receipts from operating activities”, “Payments to personnel”, “Payment for materials” and “Payment for services of third-party organizations” from the section “Operating Activities” (see Table 1 on page 83 and Table 2 on page 85 - full version ODDS and abbreviated for commercial director).

2. Include additional indicators in the report

So, the report has been simplified. Now we have to answer the managers’ question: how are things going in general? To do this, you will have to enter some resulting indicators and coefficients into the ODDS. Their composition depends on the specifics of the activities of a particular company and a particular top manager. We can recommend using the following fairly universal indicators. For example, the CEO will be interested in monitoring the ratio of net profit to cash flow. It characterizes how much net profit is in the form of real money and what part of it still remains nothing more than “paper” records. This coefficient is calculated as follows:

Net profit content ratio in cash flow = Net cash flow from operating activities: Net profit.

Another important indicator is the cash flow to total debt ratio (Cash Flow to Total Debt Ratio), which shows the level of financial stability of the company. Formula for its calculation:

Cash Flow to Total Debt Ratio = Net Cash Flow from Operating Activities: Total Debt.

Table 1. Cash flow statement for the first half of 2010, thousand rubles. (extraction)

Finally, you can also use an indicator such as “cash” return on sales. This ratio shows how many rubles of net operating cash flow account for one ruble of revenue received in the company’s accounts. It can serve as an excellent analogue to the indicator “Operating Income Before Depreciation and Amortization” (OIBDA). Cash return on sales is calculated as follows:

Cash return on sales = Net cash flow from operating activities: Receipts from operating activities.

Determine acceptable deviations

The next step towards a clear and convenient report is to provide users with information about deviations of planned payments and receipts from actual ones. Simply listing them is clearly not enough. It is important to make it clear at first glance whether they are critical or not.

Table 2. Modified cash flow statement for the first half of 2010,

Note that during the adaptation of the report, its articles essentially turn into key performance indicators (KPIs). The actual KPI value is the turnover for one of the items of the capital income tax, the target value is the planned amount of payments or receipts. Finally, the two types of deviations are the status (state) and trend (trend) of the KPI, respectively:

Deviation of the actual value from the planned value (status) = (Actual value - Planned value): Planned value x 100%;

Deviation from the level of the previous month (trend) = (Value for the billing month - Value for the previous month): Value for the previous month x 100%.

Let's explain how it all works with an example. The company has established the same materiality thresholds for cost items for deviations of actual payments from the planned values ​​of the current month and from the values ​​of the previous period:

up to -5 percent - positive deviation (less funds paid);

from -5 to 2 percent - neutral deviation (actually fit into the budget);

more than 2 percent is a negative deviation.

In February, the outflow of funds under the item “Payment for materials” amounted to 580 thousand rubles, while 612 thousand rubles were planned for the same period, in January the same figure was 570 thousand rubles (see Table 2). Accordingly, the deviation of the actual outflow compared to the budget will be equal to -5.2% (580 thousand rubles - 612 thousand rubles): 612 thousand rubles. X 100%). This value fits into the first deviation range (up to -5%), which means that the status of the “Payment for materials” KPI for February is positive. Now let's evaluate the trend. The cash outflow under this item in February compared to January is higher by 1.75% ((580 thousand rubles - 570 thousand rubles): 570 thousand rubles X 100%). Therefore, the trend is neutral (the deviation falls in the range from -5% to 2%).

For additional coefficients, you will also need to set the range of acceptable deviations. For example, for monetary profitability of sales, any deviation exceeding 10 percent will serve as an alarming signal, even if we are talking about a significant overfulfillment of the plan. After all high level incoming payments and a small volume of payments to counterparties in one reporting period may be a consequence of a violation of the rhythm of settlements.

Create a traffic light system for deviations

The last thing that remains to be done with the ODDS in order for this document to become the CEO’s dream is to highlight the most critical deviations for the company in certain items. Positive statuses and trends are highlighted in green (the situation does not require intervention), neutral ones - in yellow (attention should be paid, but no urgent measures are required), and negative ones - in red (something urgently needs to be done to correct the situation). Typically, such reports are compiled in Excel, and color highlighting can be entered manually. But it is better to entrust this work to the program. If the report is generated in Excel version 2003, the algorithm of actions will be as follows:

select cells that contain deviations;

In the “Format” program menu, select the “Conditional Formatting” command;

In the dialog box that appears, set the formatting conditions - font color. Returning to the example described above, for deviations of “up to -5 percent” under the item “Payment for materials” - green font, for “from -5 to 2 percent” - yellow, “over 2 percent” - red.

In Excel version 2007, the possibilities for conditional formatting are much wider - the state of an indicator can be represented as a colored arrow or a circle simulating a traffic light signal. The procedure is largely similar to how conditional formatting is done in Excel 2003. And the main difference is navigation through the program menu (the “Home” tab (the “Style” group) - the “Conditional Formatting” button - the “Create Rule” item from the drop-down list) .

As a result of the work carried out, the cash flow statement, which is unclear to a non-financier, becomes a tool for monitoring the company’s performance indicators. The report contains only the necessary indicators, as well as additional indicators that help you quickly assess the situation with your money.

For the real economy, it is very important that there are no “holes” in current cash planning, that is, all bills must be paid on time and salaries must be paid on time. To do this, the company strives to speed up the process of receiving revenue from clients and customers. The cash flow budget helps the financial service of the enterprise make a decision on financing current activities (if cash gaps arise) or withdrawing funds from circulation.

Financing is achieved through negotiations with counterparties on accelerating payment, with banks on short-term lending, and with suppliers on deferred payment. You can withdraw money from circulation in the following ways: deposits in banks in order to receive interest, providing deferments to counterparties in order to improve partnerships, withdrawing funds from an enterprise for the purpose of investing in securities, expanding production, etc. Also, funds can simply be accumulated in order to create a “cushion”, which is especially important at the beginning of the implementation of planning in an enterprise, when plans differ significantly from the actual situation.

The structure of the cash flow budget is similar to the structure of the Cash Flow Statement for the enterprise (Form No. 4). However, this is not a regulated, but a managerial form, so it has some differences.

In this article we examine the features of cash flow planning in the construction industry.

The first feature of construction is in object accounting, that is, in the management of several (sometimes unrelated to each other) objects. The second feature is that a construction company can build for itself (when the construction project becomes the property) and for the customer. And the third feature is that a construction company often engages contractors for work or part of the work, which also affects planning and budgeting.

In table Figure 1 shows the cash flow budget of a construction company. In this article we will not make any distinction between the contractor organization and the customer organization, since the principles for receiving funds are the same.

Table 1. Structure of the cash flow budget of a construction company

The cash flow budget consists of three large parts: operating activities, investing activities and financing activities.

Operations- construction of objects and their implementation (delivery to the customer). In operating activities, there are cash outflows and inflows.

Investment activities- long-term investments and income from the acquisition of fixed assets and other capital investments. This part of the plan is strategically important, since without investments there will be no development of the company (we are talking specifically about qualitative development associated with equipment upgrades).

Financial activities- the most important part for “aligning” the budget. This is due to the fact that the planning of the first part is ensured by the efforts of many departments of the enterprise that are not coordinated with each other. Investment activities are planned by management and production units. Financial activities are planned by the enterprise’s accounting department, financial department and PEO. But after summarizing the data in the table. 1 it may turn out that the flow from current activities will be unfavorable. This doesn't mean it's negative. There are two lines in the BDDS: the cash balance at the beginning and at the end of the period. Therefore, if there is an excess balance, the owners of the enterprise can decide to repay the loan early or invest funds in fixed capital, and if there is a shortage (negative balance at the end of the month), they can apply for additional financing.

When planning cash flow, in addition to aligning the budget as a whole, it is important to consider one aspect. The flow from operating activities must be positive. The company earns its profit from this part of the budget. In production activities (in construction too, with some restrictions), the enterprise must accumulate profit every month. The flow from investment activities must be negative, that is, the company must invest its cash in the purchase of new equipment. The flow from financing activities balances the other two activities.

Pay attention! If a company has a negative flow from operating activities and a positive flow from investing activities, this is a warning sign. This may be due to the fact that the company finances its current activities through the sale of fixed assets. It is clear that such an enterprise cannot expect success in the future.

Thus, cash flow planning also allows the company to move in a strategically important direction. We can add that the income and expense plan will not show us such a complete picture as the BDDS, because investments in fixed assets are not expenses, but expenses (that is, they do not reduce taxable profit). And if you draw up only a budget of income and expenses, then the acquisition of fixed assets will not be visible at all (if the planning process is correctly structured from an accounting point of view).

Now let's take a closer look at how it's planned cash flow budget. As a rule, this budget is prepared for the year, broken down into quarters and months.

The preparation for the year is usually carried out by management or owners, as they set the strategic priorities of the company. In addition, drawing up a budgetary budget for a year is not as informative as for a shorter period, since this budget does not serve to ensure the efficiency of the enterprise, but to ensure its financing. Therefore, we will consider only the compilation process for a short period of time - a quarter or a month (the so-called adjustment of the BDDS before the start of the month).

The monthly cash flow planning process consists of several stages.

Stage 1- determination of the forecast for the current month. Usually held on the 20th–25th of the month. During this stage, clarifying data is collected from departments in order to determine the balance of funds in the cash register and in accounts at the end of the month (current). This amount in the cash flow budget for the next month will be the opening balance.

Stage 2- collection of information from departments about payments and receipts in the planned month.

From the estimate department we receive planned receipts for closed reporting forms (if the company is a contractor), from the sales department - planned receipts for sold objects, from the production site, suppliers - data on planned payments on invoices to contractors for work performed and materials supplied, from accounting - projected tax payments. Wage payments to employees can be forecasted both by human resources (they maintain the staffing table) and by accounting (based on previous periods). PEOs are often involved in salary forecasting, which is due to the high workload of the accounting department and the simplicity of calculating wages to employees.

It is advisable to form planned receipts and payments broken down by week, or even by day. This is done in order to avoid cash gaps not only at the end of the month, but throughout the entire period. As a rule, this planning method helps unstable companies or companies where financial and economic planning is still crude.

Also, planned payments for equipment supplies (investments) agreed with the company management are received from the production departments, and planned loan payments are received from the financial department (accounting).

Stage 3- balancing the cash flow budget. This process is handled by the PEO. It is advisable to automate the process in MS Excel using macros or consolidation. It is important that by the next stage the PEO has a common BDDS in one information system, as well as applications from all departments in a readable form. For each cost item, you can define your own codes (they can be unique or in accordance with RAS).

Stage 4- discussion of the cash flow budget at the budget committee. In companies (especially young ones) there may not be a budget committee, then the discussion takes place between all participants in the process of drawing up the BDDS and the management of the enterprise.

At this meeting, the PEO submits a cash flow budget broken down by day and week. If required, each cost item can be divided into departments. This is achieved through the use of software, in particular the Excel “Subtotals” function. An example of this budget broken down by week is presented in table. 2. The company in question plans to receive funds from the customer at the beginning of the month, and at the end of the month - from the sale of its own facilities.

Table 2. Example of a cash flow budget for a construction company (thousand rubles)

Thus, despite the fact that the company’s total cash flow is positive, at certain time intervals there is a “cash gap”, that is, a negative cash balance (see figure).

Cash gaps at the enterprise

Seeing this picture, the budget committee can decide on additional financing and speed up the receipt of funds from the sale of objects. According to the sales department, they will be able to speed up the receipt of funds by a week. But this won't be enough. The management decides to take the rest from the bank. Then BDDS will be next (Table 3).

Table 3. Adjusted cash flow budget

We see that in general the flow has not changed, the flow of funds into the enterprise has simply accelerated (changes are in italics).

Such a cash flow budget can be taken as the basis and guide for action by all departments.

Let's briefly look at cash flow control. If there is such a plan, all payment orders must be signed by the head of the PEO, since his department must control payment within the plan. As soon as the PEO sees excess payments or non-receipt of revenue (loans), he sends a signal to the financial director to make a decision or find the culprits. This could be the sales department, the production department, or the accounting department that incorrectly calculated taxes.

With a mature planning system at an enterprise, bonuses are applied for incorrect calculations, and additional remuneration is applied for compliance of the fact with the plan.

The process of implementing cash flow planning includes the following steps.

Stage 1- planning without automation. At this stage, planning is carried out by transmitting notes to departments with data, combining them into one system in the PEO and printing them out to management. The duration of this stage is the longest, since in the process of its implementation it is determined what types of plans to draw up, who is responsible for what, and what the deadlines should be. As a rule, it lasts about three to four months, depending on the size of the enterprise and the interest of the general director and departments in implementing planning.

At this stage, various consultations take place between the general director, the chief accountant (although he should not be involved in this process) and the head of the PEO. At the same time, the general director wants to understand how efficiently the enterprise operates, what is the profit or loss, how much money does he have and who owes whom. There are cases when a budget is drawn up that contains elements of both the BDDS, the budget of income and expenses, and the budget on the balance sheet. Such a budget will never converge, it will not be possible to automate it, and it will also be impossible to organize responsibility for the fact that it is not fulfilled because if there is a lack of funds, they can say: but there is a profit, and vice versa. Therefore, when implementing DDS planning, it is important to separate the flies from the cutlets. Each of the three budgets must be implemented separately, but in the process they, naturally, must be connected.

Stage 2- automated planning. At this stage, an information system is created to collect indicators from departments and consolidate it. Delivery of information to management is also automated. At this stage, budget committee meetings may even be eliminated, since management can decide for themselves which departments can increase cash flow. At this stage, with the help of the enterprise informatization service, the process of drawing up plans is automated. It can be solved using standard office applications, such as MS Excel and MS Outlook. Depending on the size of the enterprise, this stage can take up to two months.

At the automation stage, it is also necessary to ensure that budgets are connected. The main items of the BDDS must be intertwined with two other budgets, for example, the balances on the current account and cash desk in the budget on the balance sheet must be equal to the balances in the cash flow budget.

In general, after passing through two stages, as a rule, the development of cash flow planning ends, since it is believed that “everything works that way.” But at the third stage, motivation is laid, which is especially important for the smooth execution of plans.

Stage 3- formalized planning. At this stage, planning regulations are being developed, which include the following:

· departments involved in planning;

· deadlines for preparing information;

· various development options (if necessary);

· process of drawing up and approving plans;

· responsibility for the implementation of the plan.

Typically, it takes up to six months to implement such a relatively simple planning element as cash flow planning.

The last thing I would like to mention is the will of the general director when implementing cash flow planning. The success of the implementation depends entirely on how interested he is in such implementation. Because the production departments of the enterprise are not willing to plan, they think that it takes a lot of time, while it is of no use, and it is still impossible to plan everything. In this case, the general director must show how important planning is for the development of the enterprise and point out that with proper organization of planning, all enterprises successfully solve this problem.

During planning, the PEO is required to competently draw up the formats of management tables, prescribe regulations and participate in the automation of planning, and after its implementation, in maintaining the planning system in working mode.

It is important to remember one thing golden rule: The fewer planning tables you create, the better. It is better to keep all bulky calculations within the PEO’s office.

Veselov A.I., chief specialist of the budget control department of the economics and finance department of LLC KB "Agrosoyuz" (Moscow), Ph.D. econ. sciences