Cost and income cash template. How I did management accounting in Excel. What is BDS and BDS and how do they differ

5. Technology budgeting company

The technology for budgeting an enterprise is demonstrated below using a very simple example that describes the relationship between individual indicators within budget tables and the ratio of the total indicators of individual budget tables as a single whole budget. The following system of assumptions has been adopted as simplifying factors:

• budget tables are compiled on the basis of dividing the year into quarters,
• the company produces and sells one type of product,
• the company is not a payer of value added tax,
• all indirect taxes that are included in gross costs are automatically accounted for as part of those basic indicators based on which they are determined (for example, wages are planned along with accruals),
• income tax is calculated according to a simplified scheme - profit is estimated according to the results of the company’s work for the year, and then is evenly divided into four parts.

Note that such simplifications are not fundamental in nature, but serve solely the purpose of ensuring the simplicity and clarity of budgeting technology.

So, let the company begin preparing a short-term financial plan for the next planning year. In accordance with the established tradition, the financial manager draws up a budget system on a quarterly basis, having received data on forecast sales volumes from the marketing department.

The totality of the information collected is presented below. It should be noted that the initial data are usually presented in blocks in accordance with the list of budgets that are developed by the financial manager of the enterprise.

The forecast of sales volumes and prices is used as the first block of initial data:

The share of payment in cash in this quarter is 70% of the revenue, the remaining 30% is paid in the next quarter. The planned balance of finished goods stocks at the end of the planning period (quarter) is 20% of the sales volume of the future period. At the end of the year, finished goods inventories are planned in the amount of 3,000 units. The required volume of material per unit of production is 5 kg, and the price of one kilogram of raw material is estimated at 60 kopecks. The balance of raw materials at the end of each quarter is planned in the amount of 10% of the requirements of the future period. Estimation of the required stock of material at the end of the year is 7,500 kg. Payment to the supplier for raw materials is made on the following conditions: the share of payment for materials purchased in a particular quarter is 50% of the cost of purchased raw materials. For the remaining 50% of raw materials, the supplier provides a deferred payment, which should be repaid in the next quarter. Direct labor is paid on the following conditions: labor costs of the main staff per unit of production are 0.8 hours. The cost of one hour along with charges is 7.50 UAH.

Overhead costs are represented by the variable and constant parts separately. Planning for variable costs is based on a standard of 2 UAH. for 1 hour of work of the main staff. Fixed overhead costs are estimated at 60,600 UAH. per quarter, and of these, 15,000 UAH are depreciated.

The value of the costs of implementation and management is also planned in the form of two parts - variable and constant. The norm of the variable part is 1.80 UAH. per unit of goods sold. Planned volumes of fixed administrative and managerial costs are given below:

The company plans to purchase equipment in the amount of 30,000 UAH. in the first quarter and 20,000 UAH. - in the second quarter.

The planned dividend amount is 40,000 per year, evenly distributed over the quarters. The company has the opportunity to take a bank loan at 10% per annum. Moreover, interest payments are made simultaneously with the partial repayment of the principal amount of the debt when interest is accrued only on the repayable part of the loan.

In addition, we assume that the income tax rate is 30%.

The state of assets and liabilities of the enterprise at the beginning of the planning period is presented in the form of a balance (see table. 19).

Tab. 19. The initial balance of the enterprise

Based on the data presented, it is necessary to build a system of enterprise budgets. The calculated budget system includes:

1. Budget sales.

2. Production plan (the company produces a single product).

3. Budget costs for basic materials.

4. Budget costs.

5. Overhead budget.

6. Budget cost

7. The budget of administrative and marketing costs.

8. Scheduled income statement.

9. Budget funds.

10. The planned balance.

A sales budget with a schedule for receiving money from a consumer.   This budget is compiled using sales forecasts, finished product prices and collection rates. For this example, the sales budget is presented in table. 20.

Tab. 20. The budget of the enterprise’s sales and the schedule for the receipt of money (UAH)

The first two rows of the table. 20 are simply overwritten from the source data. The revenue line is obtained by multiplying the sales volume by the price. When scheduling the receipt of money, it is taken into account that the sales proceeds of the current period come in the form of money only by 70%, the rest comes in the next period.

In the first period, the company plans to receive money from accounts receivable in the amount of 90,000 UAH. This value is taken from the initial balance sheet of the enterprise. Additionally, in the first period, the company receives money 70% of the first period revenue in the amount of 200,000 UAH, which is 140,000 UAH. As a result, the expected amount of revenue in the first period is 230,000 UAH. In the second period, the company expects to receive 30% of the revenue of the first quarter and 70% of the revenue of the second period. In total, this is 480,000 UAH. Similarly, all other budget indicators are calculated. In accordance with this budget, the company will not receive in the planning year 30% of the proceeds of the fourth quarter, which is 120,000 UAH. This amount will be recorded in the final forecast balance sheet of the enterprise at the end of the year.

The production plan is compiled on the basis of the sales budget, taking into account the dynamics necessary for the enterprise of the balance of finished products in the warehouse. The production plan is placed in table. 21. Consider the production plan for the first period. The company plans to sell 10,000 units. To ensure an uninterrupted supply of finished products, the company plans to balance the products at the end of the first period in the amount of 20% of the sales volume of the second period (see initial data), which is 6,000 units. Thus, the required volume of production is 16,000 pieces. But at the beginning of the first period, there are 2,000 units of finished goods in stock. Consequently, the output of the first period should be 16,000 - 2,000 \u003d 14,000 products.

Tab. 21. Production Plan

Similarly, all other indicators of the production plan are calculated. It is only necessary to emphasize that the planned reserves at the end of the first period are the planned reserves at the beginning of the second period. As for the amount of reserves at the end of the last period (they are also at the end of the year), this number is individually projected and justified when compiling a block of initial data for the entire budgeting process.

The budget for the costs of basic materials with a payment schedule consists of two parts: calculating the volumes of raw materials needed for the production of raw materials (raw materials purchase plan) and a schedule for paying for these purchases. This budget is presented in table. 22.

Tab. 22. The budget for the costs of basic materials with a payment schedule

This budget is based on a production plan, not a sales plan. Using the standard consumption of raw materials per unit of output (5 kg.), The estimated volume of raw materials for the production of products of this period is estimated. To ensure uninterrupted supply of raw materials to production, the financial manager plans to stock up on raw materials at the end of the period in the amount of 10% of the need for raw materials of the next period. For the first quarter, this value is 160,000x10% \u003d 16,000. Knowing the required volume of raw materials to fulfill the plan for the first quarter of 70,000 kg., We estimate the total required volume of raw materials: 70,000 + 16,000 \u003d 86,000 kg. But at the beginning of the first quarter, there are 7,000 kg in stock. raw materials. Therefore, 86,000 - 7,000 \u003d 79,000 kg should be purchased. raw materials. Since 1 kg. raw materials costs 60 kopecks., the total amount of costs associated with the acquisition of raw materials is 47.400 UAH. Similarly, the planned costs of raw materials for all other periods are calculated. We only note that the planned volume of stocks of raw materials at the end of the last period is evaluated individually, and not as part of the general procedure. In our case, it is 7,500 kg., Given as part of the source data block.

The calculation of the payment schedule is carried out similarly to the schedule for receiving money. In the first quarter, the company plans to pay off accounts payable at the beginning of the year (see the initial balance), which is 25,800 UAH. In accordance with the agreement with the supplier of raw materials (see the source data block), the company must pay 50% of the purchase price for the supply of raw materials in the first quarter, which is 23,700 UAH. As a result, in the first quarter it is planned to pay 49,500 UAH. In the second quarter, the company plans to pay off debt for the purchase of raw materials in the first quarter and pay 50% of the cost of raw materials purchased in the second quarter, etc. When planning the fourth quarter, we assume accounts payable at the end of the year in the amount of 50% of the cost of raw materials planned for purchase in the fourth quarter. This amount is 27,900 UAH. and placed in the final balance sheet of the enterprise in the article accounts payable.

Budget costs for salaries of key personnel compiled on the basis of the payment system adopted at the enterprise. No matter how complicated the payment system, its planned calculation can always be formalized in the form of appropriate tables. In this example, the simplest piece-rate payment system is used. In accordance with this system, there is a standard for direct labor costs per unit of finished product, which is 0.8 hours. One hour of direct labor costs is paid based on the standard of 7.5 UAH. in an hour. As a result, a budget is planned for time and cash costs for direct labor, which is presented in table. 23.

In the case when budgeting is carried out on a more detailed time basis, for example, every month, the amount of money that is recorded as costs (accrued) and the amount of money that will be paid do not necessarily coincide. In particular, if wages are paid, for example, on the 10th day of each month, then the amount of wages accrued in February will be scheduled for payment in March.

Tab. 23. The budget of direct labor costs

Production Overhead Budget   compiled using aggregated financial indicators. The fact is that the composition of overhead costs is often large, and planning overhead costs by cost elements is an unreasonably large analytical work. Therefore, all overhead costs are previously divided into variables and fixed, and the planning of variable overhead costs is carried out in accordance with the planned value of the corresponding base indicator. In this example, the volume of direct labor costs is used as a basic indicator. In the process of preliminary analysis, the norm of direct labor costs was established - 2 UAH. for 1 hour of work of the main staff. This determines the composition of indicators in the table of the budget overhead costs presented in table. 24.

Tab. 24 Production overhead budget (UAH)

Based on the planned amount of direct labor costs and the norm of variable overhead costs, total variable overhead costs are planned. So, in the first quarter, with a direct labor plan of 11,200 hours with a standard of 2 UAH / hour, the amount of variable overhead costs will amount to 22,400 UAH. Fixed costs in accordance with the source data are 60,600 UAH. per month. Thus, the amount of overhead costs in the first quarter will amount to 83,000 thousand UAH according to the plan. When planning a cash payment for overhead costs, from the total amount of overhead costs, you should subtract the amount of depreciation deductions, which are not a monetary form of costs, i.e. the company does not pay anyone for depreciation.

Estimation of the cost of production   necessary to compile a profit report and assess the value of stocks of finished products in stock at the end of the planning period. Cost consists of three components: direct materials, direct labor and production overheads. The calculation of the cost for the considered example is shown in table. 25.

Tab. 25. The calculation of the cost per unit of output

The first two cost components are determined using direct calculation. In particular, if a unit of production accounts for 5 kg. raw materials at the price of 60 kopecks. per kilogram, the component of direct materials in the unit cost of production is 3 UAH. To assess the value of overhead costs in the cost of a unit of production, you must first “attach” to some basic indicator, and then calculate this component of the cost. Since variable overhead costs were normalized using direct labor costs, the total overhead costs will also be “tied” to direct labor costs. It is necessary to evaluate the full, and not just the variables, overhead. To do this, we compare the total amount of overhead costs for the year (it was calculated in Table 2 and amounts to 404,000 UAH) with the total amount of labor (80,800, as follows from Table 23). It is easy to establish that one hour of direct labor accounts for 404,000 / 80,800 \u003d 5 UAH. overhead costs. And since 0.8 hours are spent per unit of output, the cost of overhead in a unit of production is 5x0.8 \u003d 4.0 UAH, which is noted in the table. 25.

The total value of the unit cost of production was estimated at 13 UAH. Now it is easy to estimate the value of stocks of finished products in the final balance sheet of the enterprise. Since the financial manager planned 3,000 balances of finished goods at the end of the year, 39,000 UAH will be planned in the balance sheet of the enterprise at the end of the year in the article inventories of finished goods.

Budget administrative and marketing costs. This budget is prepared using the same approach as the production overhead costs budget. All costs are divided into variables and fixed. For planning variable costs, the volume of goods sold is used as a base indicator, rather than direct labor costs, as it was before. The standard variable costs is 1.80 UAH. per unit of goods sold. Fixed overhead costs are transferred to the budget literally as they are presented in the source data. The final type of budget is contained in table. 26.

Tab. 26. Budget administrative and marketing costs

In a methodological sense, this budget does not cause problems. It is most difficult to justify specific amounts of advertising costs, management salaries, etc. There may be contradictions, since usually each functional unit tries to justify the maximum amount of funds in the budget. At the same time, the budget of everything can “not sustain”, they say “the budget is not rubber”. The methodological problem is how to evaluate the budget result for various options for financing volumes of all functional units of the enterprise. Here scenario analysis comes to the rescue, which, professing essentially the ideology of flexible planning, considers possible budget options for various financing components. Of all the options, choose the one that is most acceptable based on the strategy of the enterprise.

Scheduled Profit Report . Earlier, two bases of enterprise planning were noted: the resource basis and the monetary basis. Within the framework of the resource basis, enterprise profit planning takes place, the presence of which is a necessary condition for the ability of the enterprise to generate money. The profit report is inserted into the general system precisely for the purpose of verifying this necessary condition. In addition, the income statement estimates the amount of income tax, which is then used as part of the cash flow statement. The profit report for this example is placed in table. 27.

In this report, sales revenue is obtained by multiplying the total sales for the year (100,000 units) by the unit price. The cost of sales is determined by multiplying the calculated in table. 25 unit costs per total volume of products sold. General and marketing costs were calculated in table. 26.

Tab. 27. Scheduled income statement (without additional funding)

In this income statement, the interest on the loan is assumed to be zero. This is true, since in the process of previous budgeting the issues of lending to the enterprise were not considered. The need for additional funding should be identified when compiling a statement of cash. In this sense, the version of the planned profit report, placed in table. 27, should be considered preliminary, since in the process of budgeting money, it may be necessary to take a bank loan, and then the amount of net profit will decrease.

Cash budget   It is the final and most important in the entire budgeting scheme. It brings together the final numerical financial indicators of each private budget. The cash budget reflects all the “good and bad news” of the enterprise. The good news is the receipt of money, the bad news is the payment of money. The result is the cash balance, which can be positive and negative. In the table. 28 shows the cash budget for this example. The reader is given the opportunity to track and verify how the data of each individual budget falls into the final budget of funds.

We note a number of features of this budget. This is, firstly, the simplified nature of the income tax payment scheme adopted in this example. The amount of income tax determined in the framework of the planned profit statement is divided into four equal parts, each part is taken into account in the cash budget of each quarter. To complicate this scheme and make it adequate to the real state of affairs does not seem complicated. This issue is considered in the analysis of the business situation. Secondly, the investment budget declared in the general budgeting procedure is presented in table. 28 one line of equipment purchase costs.

28. Cash budget (without additional financing)

As can be seen from the analysis of the data table. 28, the resulting budget is in short supply. Thus, it is necessary to provide additional sources of financing, which in the framework of the considered example are reduced to bank lending. Technologically, an additional block is introduced in the budget table - “financing”, in which the financial manager must provide for a loan and its repayment, as well as payment of interest to the bank. In the table. 29 shows the cash budget, providing for additional financing.

The work of the financial manager to justify the amount of lending involves the selection of data in the “financing” block so as to implement the principle of a reasonable excess of funds, i.e. plan a cash account at a certain acceptable level, which within the framework of the considered example should not be lower than 30,000 UAH. A feature of this work is the need to ensure the coordination of cash budget data with the planned profit statement. This need is due to the fact that the amount of interest payments is estimated in the cash budget and substituted in the income statement. At the same time, the amount of tax payments is estimated in the statement of profit and substituted in the budget of funds. Such a “manual” selection is difficult to make, and you should use a pre-developed software tool.

In the table. Figures 29 and 30 show the agreed profit statement and cash budget as a result of the work of the financial manager in selecting budget indicators.

Tab. 29. Scheduled income statement (final version)

30. Cash budget (final version)

Completes the budget system company balance sheet . It is a description of the state of the assets and liabilities of the company at the end of the planning period. Tab. 31 illustrates the planned balance sheet of the enterprise.

Tab. 31. The balance of the enterprise at the beginning and end of the planning period

We describe the formation of each balance sheet item. The cash item is taken as the total cash budget. Accounts receivable are defined as 30% of the fourth quarter revenue for which the entity plans to grant a deferred payment. Stocks of raw materials are defined as the amount of residual raw materials from the table. 22 in the amount of 7,500 kg. at the price of raw materials 0.60 UAH, which is 4,500 UAH. Stocks of finished products are defined as the product of residues of finished products at the end of the year (see table. 21) on the cost of finished products: 3,000 x 13 \u003d 39,000 UAH. The article “constructions and equipment” is calculated by adding the amount of equipment purchases to the initial value of this article. Depreciation in accordance with the initial data is 15,000 per quarter, i.e. 60,000 a year. This value should be added to the accumulated depreciation at the beginning of the period.

The amount of accounts payable is obtained in the form of 50% of the procurement value of the fourth quarter, which is calculated in budget 22. Finally, the amount of retained earnings at the end of the year is obtained using the following formula:

Retained earnings at the beginning of the year + Net profit for the year - Dividends paid.

Note that the sum of assets and liabilities of the balance sheet coincides, which is a confirmation of the correctness of the budget. For a specialist who draws up a budget for the first time, and this budget turns out to be rather complicated (several types of products, a complicated cost structure), the fact that the sum of assets and liabilities coincides looks like a “financial miracle”, although this miracle is the result of using the double entry system adopted in bookkeeping.

We also note the fact that in this case, the company plans to end the year without financial debts. This condition is by no means mandatory. An enterprise may end the year with a partially unpaid bank loan, and then a certain value appears in the balance sheet item “bank loan”, which “does not unbalance” the balance sheet.

The introduction of budgeting is quite an important process that is carried out at each enterprise. Its main goal is to draw up, plan budgets, depending on the tasks facing the legal entity, as well as control over their implementation. Budgeting is one of the components of the management accounting system.

Budgeting at the enterprise on the example of production consists of the following parts:

1. revenue - consists of all revenues in the course of the company and is divided into three parts:

• income from core activities;
• income from investing activities;
• income from financial activities.

2. expenditure - includes all the costs that must be implemented to ensure the normal functioning of the company.

For the efficient use of Excel in the budgeting process, it is necessary to create tables that should contain all the items of expenses and income necessary for conducting business activities (their unified values).

Depending on what tasks need to be solved, the following budgets are prepared:

• financial;
• operating rooms.

The latter, in turn, is divided into:

• budget of income and expenses;
• cash flow budget.

The cash flow statement shows the main sources of revenue and cost areas of the company. During the preparation of the report in Excel, it is necessary to take into account all three areas of activity: operational, financial, and investment. Therefore, the tables need to provide details of each of them. In addition, the visualization of the cash flow statement should clearly demonstrate the volume of revenues and expenses so that cash gaps could be quickly identified and closed.

The main objectives of the preparation of road safety are:

• cash flow optimization based on forecast cash balances;
• formation of a cost plan taking into account future income;
• minimization of cash gaps.

BDSDS is formed on the basis of:

• sales plan;
• standards of inventory turnover, receivables and payables (cash gaps are calculated);
• expense plan;
• financial and investment plan.

Given that it is necessary to deal with a lot of data during budgeting, financiers use various automation programs to increase the speed and quality of their work. The simplest and most affordable among them is Microsoft Office Excel. Using tables and formulas that are contained in the editor, you can carry out a large number of calculations, build various diagrams and graphs, etc.

Excel does a good job with small data sets, but its use in medium and large enterprises is not recommended, because when working with large data sets it becomes very slow and slow. We can say that Excel is suitable for beginners who do not yet need to analyze large amounts of information.

Using the specialized module "Budgeting" will quickly and efficiently solve all the tasks: to form budgets of all levels, taking into account the characteristics of the enterprise, as well as to carry out operational control over their implementation.

In the conditions of growing competition, unstable economic situation, an increasing number of companies come to the need for budgeting. Budgeting at the enterprise is a process of planning, control and budget execution in the process of financial management. In this article, we will try to analyze using an example how to make an enterprise budget using an example.

Creating a budgeting system in a company or enterprise usually consists of several stages. At the first stage of the company, it is necessary to determine the goals, budgeting methodology, determine the financial structure (structure of financial responsibility centers - CFD), develop a budget model (composition, structure, types of budgets), approve the situation and the regulation of the budget process. At the second stage, you can immediately begin planning the budget of the enterprise. It is convenient to automate the preparation of enterprise budgets on the basis of a special software product.

Regulation on budgeting at the enterprise may contain the following sections:

• Strategic goals and objectives of the enterprise;
• Budget model;
• The financial structure of the company, etc.

Based on the Budgeting Regulation in the company, it is necessary to develop a Budgeting Regulation for the enterprise, which may contain the following sections:

• The procedure for the formation of functional and master budgets, the structure of subordination;
• Assigning responsible and deadlines for the provision of budgets and reporting;
• The procedure for approval and amendment;
• Control and analysis of the budget, etc.

There are several ways to implement a ready-made budget model. The most common and relatively cheap ways are:

• Budgeting in Excel

Excel company budget example

Budgeting in Excel consists in creating budget forms in Excel format and linking these forms using formulas and macros. The forms of budgets, including the budget of income and expenses, the cash flow budget, can be different, with enlarged items or more detailed, divided into long periods (for example, the annual budget for quarters) or for shorter periods (for example, a monthly budget for weeks ) - depending on the needs of financial management in the company.

The following is a Budget of income and expenses (an example of drawing up in Excel) and an example of a Budget for cash flows.

Figure 1. Budget revenue and expenses of the enterprise sample Excel.

Figure 2. Cash flow budget example in Excel.

Drawing up BDR and BDSDS example in Excel

The process of compiling BDR and BDSDS on an example in Excel may look as follows. Let's build budgeting in a company or in an enterprise using the example of a manufacturing company in Excel (details in the files below):

Figure 3. BDSDS example in Excel (Budget cash flow example in Excel).

Figure 4. BDR example in Excel (Income and expense budget example compilation in Excel).

This example is as simple as possible. But even from it it is clear that budgeting to Excel is a rather laborious process, since it is necessary to collect all functional budgets and prescribe formulas and macros to correctly display the final results. If you take a real company, or even a holding structure, you can imagine how complicated the process of budgeting in Excel is.

An example of implementing budgeting based on Excel has many drawbacks: single-user mode, the lack of coordination of functional budgets, there is no differentiation of access to information, the complexity of consolidation, etc. Thus, budgeting to Excel is not an optimal choice for a company.

Budgeting in programs on the 1C platform

Automation of budgeting and management accounting based on 1C, for example, in the WA: Financier system, makes the budgeting process at the enterprise more efficient than budgeting in Excel.

The budgeting subsystem "WA: Financier" includes the ability to form and control operating and master budgets.

The solution implements mechanisms by which users can independently adjust the structure of budgets, their interconnections, methods of obtaining actual data and data for calculations. The implemented mechanism of interaction with external accounting systems makes it possible to use external data both for calculating planned indicators or generating reports, and for reflecting actual data on budget registers.

This system allows you to effectively build a budgeting business process at all its stages:

• budget model development;
• coordination of budgets and their adjustments;
• reflection of actual data on budgeting items;
• budget execution control;
• plan-fact analysis of indicators using developed reporting tools;
• formation of business management solutions.

Figure 5. The WA: Financier: Budgeting interface. Budgeting section.

WA: The Budgeting financier includes the following business processes:

• Modeling - development of a budget model;
• The main budget process is the registration of planned indicators by units. Approval of budgets. Adjustment of plans and coordination of adjustments;
• Subsystem of interaction with data sources - setting to receive data from external sources (as a special case — access to system data).
• System reports - a set of analytical reports.

Entering planned indicators in the system is carried out using a flexible, freely configurable budget document. The budget input form (income and expense budget form, as well as the cash flow budget form) is as close as possible to the format in Excel, which ensures a comfortable transition for the user to work with the system.

Some budget items that depend on another budget item (for example, cash receipts from buyers depend on the revenue item “Revenue”) can be planned using the dependent turnover mechanism, which is presented in the system in the form of documents “Registration of dependencies of turnover by articles”.

If necessary, it is possible to adjust the approved budget with special documents “Budget Adjustment” and track the changes made in reports with the form “Display budget adjustments separately”. It is possible to adjust the distribution of the budget, keep track of budget applications.

Using special documents “Accounting for actual data on budgets”, a fact is obtained from external accounting systems, for example, 1C Accounting.

Various reports allow you to analyze planned and actual data, thus managing budgeting at the enterprise.

Thus, the introduction of budgeting in companies in programs on the 1C platform is the most optimal in terms of time, money and the effectiveness of further work.

Ural Lotto has developed a model in Excel, with which the financial service will be able to quickly draw up an Income and Expense Budget (BDR) and a Cash Flow Budget (BDS). An example in Excel can be downloaded from the link at the end of the article.

To develop a budgeting system, simplify the planning procedure and minimize the risk of errors in the financial plans of the company, you need a unified model of BDR in Excel, in which only one table is reserved for data entry, and all the others are generated automatically.

Imagine that the CFO, together with the economic service, has to develop a budgeting system from scratch. At first, it is difficult to understand how the budget process will be organized, what types of budgets and reports will be required, in what form it will be necessary to receive information on actual income and expenses from the accounting program. This is exactly what was argued at Ural Lotto LLC. In the first months they were limited to the formation of separate budgets - sales, purchases, units. After some time, it was possible to develop (the sheets “detailed PL” and “detailed CF”, respectively) were created using the Excel tool “Summary tables”. Using the built-in filters, you can select articles of interest, sub-articles of the first and second levels, group income (income) and expenses (payments) separately. To reflect the changes made to the worksheet in them, you need to right-click and select "Update" from the menu that appears.

• How to analyze BDR for a certain period and show deviations

The “detailed PL” sheet allows you to create a budget of income and expenses both for the company as a whole and for a separate unit. To get it, just select the items “BDR” and “To both budgets” in the window “Budget Name”. If you need a plan for a specific unit, you will need to enter its name in the "CFD" window.

The option “To both budgets” is intended in case the numbers are the same for both budgets, that is, for example, when 10,000 rubles in January go to either the BDR or the BDS for any item. This is done so as not to drive the same numbers twice.

In Excel, a detailed BDDS is created in the same way: on the "detailed CF" sheet, select "BDDS" and "To Both Budgets", and then update the data. There is also filtering by CFD.

Useful documents

BDR and BDSDS example in Excel

An example of the income and expense budget and cash flow budget (BDS) in Excel for the whole company is shown on the sheets “PL Budget” and “CF Budget”. Unlike detailed plans, they are not formed directly from the worksheet, but after the intermediate processing of information on supporting sheets (“Code of stations” and “Code of stations-subst.”).

The first of the supporting tables, “Code of Art.”, Is intended to consolidate budget data for the main items of expenses and income (receipts and payments). It can be configured in the same way as detailed budgets - using the “Budget Name” field (see Fig. 1). Since it is used for both budgets, BDD and BDDS can only be formed in turn, using the filter in the "Budget Name" field. Simultaneous updating of both budgets in this version of the file is impossible. Of course, you can make two such auxiliary tables - for each budget, but it will become more difficult to use the file, because each time you would have to update a larger number of pivot tables. And the file itself will become "heavier."

Figure 1. Supporting table

In the second auxiliary table (“Code of Articles of Sub-Sub.”) Data with details “Article” and “Sub-article of the first level” are consolidated. The combined names of the latter are listed in the “For Report” column of the worksheet. The work proceeds similarly to the “Code of Articles” described earlier - you must select a budget name depending on which one is being formed.

Budgets are filled with data from auxiliary tables using the VLOOKUP formula. For example, the calculation of “Revenues from the sale of the lottery according to“ 6 of 40 ”” for January (cell E10 on the “PL Budget” sheet) will be set as VLOOKUP ($ A10; "Set of subst."! $ A: $ T; 4; 0), where A10 is the cell containing the name of the sub-article (“6 of 40”), “Code of subst.”! $ A: $ T - the initial range in the second auxiliary table, where the row will be searched with by the same name, 4 is the column number with data for January.

Download example of BDR and BDS in Excel

Benefits of Budgeting in Excel

The unified budget in Excel has greatly simplified the planning process in the company. Budget adjustments have become easier. It is enough to enter a new value in one table, and this will be reflected in all the others. The likelihood of errors is reduced.

The “all in one” format allowed to abandon the creation of separate budgets for sales, procurement, taxes, payroll, etc. The Ural Lotto company is relatively small: there is one central office (55 employees), no branches. Therefore, it is much easier to fill out just one worksheet.

Table.   The structure of the budget worksheet in Excel (sheet "Work table")

I work as an ordinary analyst and it so happened that in the summer of 2014, participating in one e-commerce project, I made management accounting in MS Excel on my knee for 3 weeks. I have long planned and finally decided to put it on Habr. I think it will be useful for small entrepreneurs who understand the importance of managing financial flows, but do not want to spend a significant amount of time and money on managing accounting. I do not pretend to be the ultimate truth and will be happy with other decisions proposed by the community.

The business to which I had a relationship in the summer was the usual online store of premium and higher clothing with a turnover of about 1 million rubles a month. The business worked, not to say that it was very successful, but it worked and continues to work. The owner understood the need for management accounting and, with this understanding, took me as financial director (analyst / manager ...), since the previous one left the business 3 months before my arrival. Actually, a hole of the same duration was also managed by management accounting. Looking ahead, I’ll say that I didn’t eliminate the hole (we decided not to tear the past), but I created a system that successfully works with minimal labor to this day.

My predecessor led a manager in Fyngrad, which turned out to be a very powerful tool. For example, it allowed you to automatically load information from 1C and statements of different bank customers, creating postings according to predefined rules. The thing is certainly useful, however, subject to the double recording system, it increased the operating time by several times. To avoid an increase in work, this tool allowed the generation of “dependent transactions”. The dog was buried in the creation of these additional postings. And then it turned out that behind all the power of Fingrad was unique, which caused a complete lack of expertise in the public domain. Ordinary users (who, incidentally, paid 3,000 rubles a month for accessing the system) had access only to the “User Guide” on the official website, and 6 video lessons there. Youtube, which provided access to a couple of dozen video tutorials, also did not help much. There were no forums with information on how to ... in principle. Support, specific questions about the rules for creating “dependent transactions” and requests for help in my particular case, were frozen with the phrases “we have not concluded a support agreement, therefore we are not ready to answer such specific questions”. Although it would seem - what is specific in such requests, and even with screenshots on my part? It is clear that everything can be beaten with your hands, but the question is, why then generally pay for a tool that greatly increases the time required for managing and does not give any advantages for small businesses?

Having convinced the owner of the inappropriateness of using “Fingrad” with such volumes of business and unloading all the information from the system, I put BIG and fatty cross. Moreover, the decision to leave for MS Excel was not spontaneous. Good googling on the subject of management accounting, found monsters similar to Fingrad, or links to web applications for maintaining personal finances, while the main requirements for the system were:

The ability to maintain BDSDS and BDR based on a revised chart of accounts;
   - simplicity in the further management accounting (including by the forces of "financially illiterate" users);
   - flexibility (the ability to expand / remove functionality on the go);
   - lack of congestion of the tool / interface.

To begin with, we’ll clarify the terms: being a non-financier, I mean “Balance of Cash Flows of Funds” by BDS, BDR - “Budget of Income and Expenses”. We consider the BDDS to be on a cash basis (the day of the operation — the column “Date of Operation”) and use it for operational day-to-day planning, and the BDR — on an accrual basis (column “The accrual period”) for strategic, within a year or more.

So, how it works and how it works (ideally):

1. Management accounting is collected based on information entered by end users using the form in Google Docs. The field names and encoding options are marked in red in the final file of management accounting - a kind of mapping of fields.

2. In the end, it looks like this (the green is transferred to the resulting management file).

3. Management accounting is built on the basis of .xls unloading from Fengrad (hence the strange names for external users and, in general, an excessive number of columns). We kindly request not to take seriously the values \u200b\u200bof the columns “Parish”, “Consumption” - much has been randomly changed.

The filling mechanism is simple: we carefully transfer it to the “General Book” tab from the Google Docs form and bank statements. The rows used for the formation of the BDR are highlighted in red, the BDSDS., Which are pivot tables and are built on the basis of intermediate tabs with speaking names, highlighted in green. The only columns in which the information is not connected with other sources: “Original ID” (unique row values) and “Creation date” (\u003d TAT (), and then copy and paste as a value)

4. Articles of DDS (cash flow) are located on a separate tab "PS_service" and may well be regularly reviewed depending on specific needs (do not forget to update the formulas on the sheets "Data_BDDS", "Data_BDR").

5. In the picture, a BDSDS sample, in the default format, minimized to a weekly “relevance”.

6. Sample BDS (monthly). Pay attention to the thesis mentioned above about the use of lines from the General Book: Budget and Fact for BDR, Plan and Fact for BDS.

7. Work with BDSDS means maintaining the lines of the "Plan" in the most current state. I am quite pedantic in working with primary information and the comments made by me kept the entire history of changes. How will you be - a question for you. My approach allowed me to catch about 1 significant error per week, threatening discrepancies of tens to hundreds of thousands of rubles. By the way, time was eaten a little.

8. Actually himself