Enterprise BDR form in Excel. Simplified budget of income and expenses in Excel

In Excel, it is convenient to check the execution of the annual budget, comparing plan and fact, but for this you need to transfer real numbers from 1C or another accounting program. Manual data entry may lead to errors. A special template for collecting actual figures from the balance sheet will help you avoid them. You can ready file

How to work with a file. The template includes three worksheets: “BDR” (budget of income and expenses), “Fact” (turnover balance sheet, exported from the accounting system and adapted for data collection) and “Conditions” (auxiliary sheet that sets the rules for comparing analytical characteristics of planned and actual data).

The “BDR” sheet reflects the company’s budget of income and expenses, and information about its actual execution from the balance sheet is also transferred here. Horizontally in the budget columns the order details are reflected: name and number (must be unique for the template to work correctly), start and end dates of work, financing plan. The details are followed by planned and actual monthly data, a cumulative total as of the reporting period and an expected fact for the year. Along the vertical line there are cost items: wage fund (payroll), contributions from payroll, materials and services, other expenses, total costs, as well as revenue including VAT, cost, profit, VAT.

How to transfer data. In the finished file, the source is two balance sheets for the month - for accounts 20 “Main production” and 90 “Sales”. Both were created in 1C: Accounting 8.1. All you need to do is save them in Excel format, select the tables, excluding headers and summary rows, and transfer them to the “Fact” sheet. You can also transfer data from another program if it supports the upload function to Excel.

Before transferring the source data to the BDR sheet, it is necessary to ensure the comparability of accounting and budget analytics. Namely, for each line of the balance sheet, put special marks in the “Additional characteristics” table, located to the left of the statement, on the same “Fact” sheet. In the first column of the table write the serial number of the actual month. The value is manually entered only into the first cell of the range, and then it can be “stretched” to the end of the turnover sheet for the selected month. The remaining columns are filled in automatically; you just need to copy the existing formulas.

Every month, after the end of the next reporting period, balance sheets are added to the “Fact” sheet and are accompanied by labels in the “Additional characteristics” table. In accordance with the template, actual data on the execution of the budget of income and expenses are entered in place of the planned ones in the “BDR” at the time the fact is collected. To update the budget with information about its actual implementation for the past month, you need to copy the formulas for the desired period and replace the word “Plan” with “Fact” in the heading of the corresponding column.

Budgeting is a universal tool in business process management. With the help of these tools, it becomes possible not only to optimize the use of the organization’s resources, but also to assess the economic activity of an enterprise that is only at the planning stage.

The key element here is the organization of an income and expenditure budget (abbreviated as BDR). On its basis, a forecast is made of the results of activities for the entire period for which the budget is formed.

If you are interested in automation of budgeting, implementation of treasury or accounting according to IFRS, check out our.

Before answering the question about the formation of financial and financial management, you need to understand: the budget of income and expenses is the final part of the formation of the financial budget of the organization. The BDR is based on many indicators, including data on a set of budgets at all levels.

It is accepted that before drawing up the BDR, it is necessary to create a production budget, a budget related to sales, a budget that includes all production costs, a management budget, commerce, etc.

Just a note.Thus, the budget of income and expenses is a certain feature that summarizes all these indicators.

The result of education B&R will calculate net profit at the end of the budget period.

Components of the BDR

The components of the budget of income and expenses are usually divided into two parts:

• profitable;
• consumable

Let's look at each in more detail.

Profitable

It includes the following:

• profit from the sale of main products, the outflow of which is planned in the budget phase;
• profit from other types of sales of any products;
• profit received from non-operating income. This also includes the amount of money received from loans that were provided to other organizations. Profits can also come from changes in currency exchange rates.

Expendable

This part consists of indicators:

• production costs. This data is taken from the costs associated with the production budget;
• expenses related to business and administration;
• expenses for other needs. This includes interest payments on the loan taken, etc.

Just a note.If necessary, related to accounting needs, each individual line of income and expenditure can be detailed and new elements can be added.

OBD report form

The report model can be different and for the most part depends specifically on the specifics of the enterprise where it is compiled. But there is a general principle that must be observed - a real reflection of the reliable order and meaning of the calculation, which is planned as a result for the intended budget period.

The most popular type of report, which is also the simplest, is using Form 2 of the profit and loss report. Everything is drawn up according to the standard, but the basis is all the regulatory indicators of the organization.

Just a note.The report, in which the budget of each level of the company will be formed, allows you to see almost all areas that negatively affect profits.

That is why a correctly drawn up budget of income and expenses, which will take into account all possible aspects of the organization’s activities, not only helps managers make the right decisions in a timely manner, but also changes some of the nuances in the company’s activities. For example, correct the sales plan to a more current one or activate additional resource reserves in a timely manner .

This is what form 2 looks like as an example:

Differences between BDR and BDDS

(cash flow budget), like the cash flow budget, is a financial budget. Moreover, they are also the most popular. Many people think that these concepts are similar, but this is far from the case, and you can notice a decent number of differences that can confirm this.

We know what BDR is. Now you need to find out what exactly a cash flow budget is.

BDDS is a document reflecting the cash flows that exist throughout the organization. That is, only those transactions that are expressed in monetary terms are included here.

Comparison

BDR differs from BDDS not only in the purposes for which they are formed, but also in the indicators that are included in their reporting.

• BDR - is developed to plan the profit that the organization is able to receive during the budget period. This includes cost and revenue data. In terms of the form of the report, the document is very similar to an accounting statement of profit and loss.
• BDDS performs a different function - a budget of this type is formed to distribute cash flow. They can be both incoming and outgoing. The document shows all the activities of the organization that were carried out in cash. Thus, all movements of company funds across various accounts are tracked. The document is similar to a cash flow statement.

It is in these points that the fundamental difference between BDR and BDDS lies.

If you create a report in Excel, it will look exactly like in the example. There is nothing complicated in compiling a BDR, if you look at it, of course.

Moreover, in the 1C system, each of the numbers can be deciphered right down to the primary document.

As you can see in the picture, many factors are taken into account. Such voluminous tables are typical mainly for large enterprises. If you have a small organization, then the number of columns may be reduced several times.

The Ural Lotto company has developed a model in Excel, with the help of which the financial service can quickly draw up an Income and Expense Budget (IBC) and a Cash Flow Budget (CFB). 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 financial plans, a company needs a unified financial accounting model in Excel, in which only one table is allocated for data entry, and all others are generated automatically.

Let's imagine that the financial director, 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, and in what form it will be necessary to obtain information on actual income and expenses from the accounting program. This is exactly what they thought at Ural Lotto LLC. In the first months, we limited ourselves to the formation of separate budgets - sales, purchases, divisions. After some time, we managed to develop (sheets “detailed PL” and “detailed CF”, respectively) created using the Excel “Pivot Tables” tool. Using built-in filters, you can select items of interest, subitems of the first and second levels, and group separately income (receipts) and expenses (payments). To reflect the changes made to the worksheet, you will 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 division. To get it, just select the items “BDR” and “To both budgets” in the “Budget Name” window. If you need a plan for a specific department, you will need to enter its name in the “CFD” window.

The “To both budgets” option is intended in case the numbers are the same for both budgets, that is, for example, when 10,000 rubles in January for some item goes to both the BDR and the BDDS. This is done so as not to enter the same numbers twice.

A detailed BDDS in Excel is created in a similar way: on the “detailed CF” sheet, you need to select “BDDS” and “Both budgets”, and then update the data. There is also filtering by CFD.

Useful documents

BDR and BDDS example in Excel

An example of an income and expense budget and a cash flow budget (CFB) in Excel for the entire company is shown on the sheets “PL Budget” and “CF Budget”. Unlike detailed plans, they are not formed directly from the worksheet, but after intermediate processing of information on auxiliary sheets (“Code of Articles” and “Code of Art.-Subst.”).

The first of the auxiliary tables is “Code of Articles.” – designed to consolidate budget data on the main items of expenditure and income (receipts and payments). It is configured in the same way as detailed budgets - using the “Budget Name” field (see Fig. 1). Since it is used for both budgets, the BDR and BDDS can only be generated in turn by applying a 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 using the file will become more difficult, because each time you would have to update a larger number of summary tables. And the file itself will become “heavier”.

Figure 1. Auxiliary table

The second auxiliary table (“Code of Article-Subst.”) consolidates data with the detail “Article” and “Subarticle of the first level”. The combined names of the latter are given in the “For Report” column in the worksheet. The work proceeds similarly to the previously described “Code of Art.” – you must select the name of the budget depending on which one is being formed.

Budgets are filled in with data from auxiliary tables using the VLOOKUP formula. For example, the calculation of “Revenue from the sale of the lottery for “6 out of 40”” for January (cell E10 on the “PL Budget” sheet) will be specified as VLOOKUP($A10;"Code of st.-subst."!$A:$T; 4;0), where A10 is the cell containing the name of the subarticle (“6 of 40”), “Code of art. same name, 4 – column number with data for January.

Download an example of BDR and BDDS in Excel

Benefits of Budgeting in Excel

A single budget in Excel has significantly 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 made it possible to abandon the creation of separate budgets for sales, purchases, taxes, payroll, etc. The Ural Lotto company is relatively small: one central office (employees - 55 people), no branches. Therefore, it is much easier to fill out just one worksheet.

Table. Structure of a budget worksheet in Excel (work table sheet)

An example of a cash flow budget in Excel, with which you can quickly manage liquidity and forecast the company's cash deficit. The source of information is the budget of income and expenses.

To understand whether my company will have enough cash until the end of the quarter, I use a “light” cash flow budget. We will look at an example in Excel below.

I build a cash flow budget using an indirect method based on the budget of income and expenses (BDR), adding several key indicators. I use the indirect method. My approach differs in two ways. The first is that I do not form a complete BDDS, but only for those items that change significantly. The classic indirect method requires a planned balance sheet. This method does not consider how to do it. Therefore, the second point is that I calculate the necessary balance sheet items through turnover. At the same time, I use the BDR as a source of information. Next, I will describe the sequence of creating a “light” cash flow budget and consider an example in Excel.

How is a cash flow budget formed?

BDDS can be collected by two methods: direct and indirect. The essence of the first is to calculate all planned inflows and outflows of funds based on the company’s accounts, that is, this approach forces continuous accounting, which is why it is labor-intensive. The second method allows you to collect BDDS through BDR and balance.

The classic construction of a cash flow budget using the indirect method is carried out in four steps.

Step 1. Based on the reporting data (Form 2 “Profit and Loss Statement”), the company’s net profit is determined.

Step 2. The amounts of cost items that do not actually cause cash flow are added to net profit (depreciation, if there are reserves for losses, vacations, etc.). As a rule, depreciation and reserves are not highlighted as separate lines in the income statement; their values ​​are determined from the transcripts of these report items.

Step 3. Any increases (decreases) that occurred in items of current assets are subtracted (added) with the exception of the item “Cash”.

Step 4. Any increases (decreases) that occurred in items of short-term liabilities that do not require interest payments are added (subtracted).

Determination of net profit

First, I detail the cash flow in three sections: flow from operating activities, investment and financial activities. At the same time, the sections of the “light” cash flow budget will not contain all the items of the classical indirect method. For example, the “Operating Activities” section consists of the following articles: “Net profit”, “Non-cash adjustments (depreciation)”, “Change in working capital”; “Investment activities” contains one article - “Investments”; “Financial activities” also consists of a single item “Loans and credits”. The line “Loans and credits” will reflect the amounts of loans and credits received, as well as their repayment. Also, using this line, I determine the company’s future need for cash, which is necessary to cover cash gaps (all calculations, as well as an example of a cash flow budget in Excel, can be downloaded from the link at the end of the article).

Forming a “light” cash budget using the indirect method begins with operating activities. To calculate cash flow, you need data: net profit, non-cash adjustments and changes in working capital. I take the net profit indicator from the BDR (Table 1). Non-cash adjustments are all expenses and income that do not involve cash outflows or inflows. I only take into account depreciation. The remaining non-cash transactions (reserves for losses, vacations, offsets) are reflected both in the cash flow statement and in the balance sheet, and when calculating the cash flow through the cash flow statement and the balance sheet, a zero result will be obtained for them. Therefore, I do not include them in the calculations.

Table 1. BDR report form, rub.

Calculation of changes in working capital

Calculation of working capital based on actual data. For any planning, we need a starting point - something that we already know, that is, a fact. So first I analyze the fact. I take the data from the balance sheet. Then I start planning. I calculate the value of working capital using a simplified net working capital formula . I do not take those assets and liabilities that are insignificantly related to operating activities, and therefore have little impact on its value:

OK = Zap + DZ + DC – short circuit (1)

where OK is working capital;

Zap - inventories, including work in progress and finished goods inventories;

DZ - accounts receivable;

DS - cash and cash equivalents;

KZ - accounts payable.

I note that each term can be decomposed into its component parts. For example, inventories include stocks of raw materials, finished goods and work in progress. But, as a rule, the constituent parts behave identically to the aggregate. Therefore, determine the value of such a division so as not to complicate the calculations. In this example I will not divide. If the activity is not related to cash and collection, there is no money on the way, then cash can be excluded from the formula (when calculating the turnover values ​​in the example, cash is excluded, since its value is insignificant. - Author's note.).

Table 2. Balance, rub.

I substitute data from the balance sheet into formula (1) (see Table 2 on page 48). As a result, I get the actual working capital values ​​for each month of the third quarter:

OK Jul.15 = 684,289 + 764,136 – 51,200 = 1,397,225;

OK Aug.15 = 705,052 + 712,842 – 53,215 = 1,364,679;

OK Sep.15 = 924,126 + 798,321 – 54,987 = 1,667,460.

Determination of turnover. I identify the dependence of working capital on changes in the company’s activity. To do this, I represent it in terms of turnover days using the formula:

OK = Zdn. × SS.av.day + DZdn. × Real average day × 1.18 DSdn. × Real average day – KZdn. × SS.av.day × 1.18 (2)

where Zdn. - inventory turnover in days;

SS.wed.day - average daily cost, i.e. cost for the period divided by the number of days of the period;

DZdn. - receivables turnover in days;

Real average day - average daily sales, i.e. sales for a period divided by the number of days of the period;

DSdn. - turnover of cash and cash equivalents in days;

KZdn. - accounts payable.

1.18 - amendment to the VAT rate. If you need to apply different VAT rates, use the weighted average VAT rate in the formulas.

Example

We purchase raw materials in the amount of 150 million rubles, VAT on top. Of these, 30 percent of raw materials are subject to VAT (10%), the remaining 70 percent are subject to VAT (18%). Let's calculate the weighted average VAT rate.

1. 150 million rubles × 30% = 45 million rubles - we determine the volume of raw materials purchased by the company, including VAT (10%);

2. 45 million rubles × 10/100 = 4.5 million rubles - determine the amount of VAT (10%);

3. 150 million rubles – 45 million rubles = 105 million rubles - volume of raw materials including VAT (18%);

4. 105 million rubles × 18/100 = 18.9 million rubles - VAT amount (18%);

5. 4.5 million rubles + 18.9 million rubles = 23.4 million rubles - total VAT amount;

6. 23.4 × 100/150 = 15.6 percent - weighted average VAT rate.

In formula (2), the values ​​of Zdn., DZdn., DSdn., KZdn. are of interest. They are necessary in order to calculate working capital for the future period (IV quarter of 2015) based on data from the actual period (III quarter of 2015). I calculate these values ​​using the formulas:

Now I will make calculations for the actual and planned periods. I will take the data from the BDR (Table 1) and the balance sheet (Table 2):

Real average day July 15 = Sales for July 15 (Table 1) / 31 = 10,295,478 / 31 = 332,112;

SS.wed.day July 15 = Cost for July 15 (Table 1) / 31 = 5,683,774 / 31 = 183,348;

Zdn. Jul.15 = W. Jul.15 (Table 2) / SS.av.day. Jul.15 = 684,289 / 183,348 = 3.7;

DZdn. July 15 = DZ July 15 (Table 2) / (Real average daily July 15 × 1.18) = 764,136 / (332,112 × 1.18) = 1.9;

KZdn. July 15 = KZ July 15 (Table 2) / (SS.average day July 15 × 1.18) = 51,200 / (183,348 × 1.18) = 0.2

The indicator values ​​for August and September 2015 are calculated in the same way. Next, I will summarize all the indicators in Table 3. Data Zdn., DZdn., KZdn. I average for three actual months (Table 3, column 5) and will use them as planned indicators for the next quarter (Table 3, columns 6–8). I will not take into account cash turnover, since their size in the example is minimal; in other cases, you need to use formula (5). If accounts receivable include advances issued, then they must be counted separately, the denominator will be the average daily cost. If available in accounts payable the advance received, it must also be taken into account separately, with the denominator being the average daily sales.

Table 3. Calculation of turnover values

Calculation of changes in working capital. I’ll start by calculating the amount of inventories, receivables and payables, that is, I’ll calculate the components of formula (1) of working capital for the future period. To do this I use the reverse formulas:

Zap = SS.av.day. × Zdn. (7)

DZ = Real average day. × 1.18 × ДЗдн. (8)

KZ = SS.avg.day. × 1.18 × KZdn. (9)

I will make a calculation based on the data in Table 3 (columns 6–8).

W.Oct.15 = SS.Wed.Day. Oct.15 × Zdn.Oct.15 = 271,000 × 3.6 = 975,600;

DZ Oct.15 = Real average day. Oct.15 × 1.18 × DZdn.Oct.15 = 381,211 × 1.18 × 1.8 = 809,692;

KZ = SS.avg.day. oct.15 × 1.18 × KZdn. Oct.15 = 271,000 × 1.18 × 0.2 = 63,956.

I will similarly calculate the indicators for November and December 2015. I will summarize all the calculations in Table 4. Now, substituting the necessary values ​​from Table 4 into formula (1), I will calculate the working capital and its change.

Table 4. Calculation of the amount of inventories, receivables and payables for the fourth quarter of 2015, rub.

The change in working capital is the difference between the values ​​of the billing month and the previous one. I will summarize the data obtained in Table 5. A positive value when changing working capital indicates an infusion of funds into it.

Table 5. Calculation of working capital and its changes, rub.

Determining the amount of operating cash flow. Knowing net profit, non-cash adjustments and changes in working capital, I will calculate operating cash flow:

ODP = PP + A - ∆OK (10)

where ODP is operating cash flow;

PE - net profit;

A - depreciation;

∆OK - change in the value of working capital for the period.

ODP Oct.15 = PE Oct.15 (Table 2) + A Oct.15 (Table 2) – ∆OK Oct.15 (Table 5) = 49,342 + 130,819 – 53,876 = 126,285.

I will calculate the values ​​for November and December 2015 in the same way.

I’ll supplement my example of a cash budget in Excel with expenses for investment activities (RUB 200,000) in each month of the fourth quarter. And I will calculate the cash deficit/surplus (the difference between operating cash flow and investments), which will show how much funds need to be raised or their surplus. The calculation results are presented in Table 6. In the example, a negative result was obtained, which forces us to raise funds to cover it.

Table 6. Cash surplus/deficit, rub.

Cash Flow Budget Example in Excel

Using the received data, I fill out the “easy” BDDS form. The result is a cash flow budget, an example in Excel is shown in Table 7.

Table 7. “Easy” cash flow budget example in Excel (extract), rub.

You can download an example of a cash flow budget in Excel, as well as all the initial data for its construction, from the link at the end of the article.

Thus, by calculating on the basis of the financial statement only three turnover indicators: inventories, receivables and payables and the size of investments, I can predict a cash deficit or surplus for each month of the planning period.

Supplementing the BDR with turnover calculations. You don’t have to create a BDDS at all. For example, I limit myself to supplementing the BDR with the indicated calculations, as presented in the table, which can be downloaded at the end of the article. It is convenient to carry out such calculations in Excel. It is enough to set up the financial statement form once with additional calculated items for changes in working capital, and you can see the whole picture of the state of affairs in the company in the past and the foreseeable future.

In addition, this method makes it easy to manage planned liquidity; just change the turnover values ​​- and you can immediately see the result. For example, suppose that it was possible to agree with suppliers of raw materials on a deferred payment of five days. We equate to this value the accounts payable turnover (KZdn.). In table 8 in the fourth quarter, the value of accounts payable 0.2 is changed to 5, and the values ​​of accounts payable, working capital and all subsequent dependent lines are automatically recalculated. The result is a new cash flow value for the fourth quarter. As you can see, by implementing this strategy, the company will be able to free up almost 1.5 million rubles from working capital as early as October 2015 and will not need to raise borrowed funds.

Table 8. Change in accounts payable

Prepared based on materials from the magazine

To discuss the article, visit our groups on social networks

5. Enterprise budgeting technology

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

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

Let us note that such simplifications are not of a fundamental nature, but serve solely the purposes of ensuring simplicity and clarity of budgeting technology.

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

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

The first block of input data uses the forecast of sales volumes and prices:

The share of payment in cash in a given quarter is 70% of revenue, the remaining 30% is paid in the next quarter. The planned balance of finished goods inventories at the end of the planning period (quarter) is 20% of the sales volume of the future period. Finished goods inventories at the end of the year are planned to be 3,000 units. The required volume of material per unit of production is 5 kg, and the price of one kilogram of raw materials is estimated at 60 kopecks. The balance of raw materials at the end of each quarter is planned to be 10% of the needs of the future period. The estimated required material stock at the end of the year is 7,500 kg. Payment to the supplier for raw materials is made on the following terms: 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 must be repaid in the next quarter. Payment for direct labor is made on the following terms: labor costs of key personnel per unit of production are 0.8 hours. The cost of one hour including accruals is 7.50 UAH.

Overhead costs are represented by variable and fixed parts separately. Variable costs are planned based on the standard of 2 UAH. for 1 hour of work of key personnel. Fixed overhead costs are estimated at UAH 60,600. per quarter, of which UAH 15,000 accounts for depreciation.

The amount of costs for implementation and management is also planned in the form of two parts - variable and constant. The standard for the variable part is 1.80 UAH. per unit of goods sold. The planned volumes of fixed administrative and management 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 amount of dividends planned for payment is 40,000 per year, evenly distributed over quarters. The company has the opportunity to take out a bank loan at 10% per annum. Moreover, interest payments are made simultaneously with partial repayment of the principal amount of the debt, with interest accrued only on the repaid portion of the loan.

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

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

Table 19. Initial balance of the enterprise

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

1. Sales budget.

2. Product production plan (the company produces a single product).

3. Cost budget for basic materials.

4. Cost budget.

5. Overhead budget.

6. Cost budget

7. Budget for administrative and marketing costs.

8. Planned profit report.

9. Cash budget.

10. Planned balance.

Sales budget with a schedule for receiving money from the consumer. This budget is prepared using sales forecasts, prices for finished products and collection ratios. For the example under consideration, the sales budget is presented in table. 20.

Table 20. Enterprise sales budget and money receipt schedule (UAH)

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

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

The production plan is drawn up on the basis of the sales budget, taking into account the dynamics of finished product balances in the warehouse necessary for the enterprise. The production plan is placed in the table. 21. Consider the production plan for the first period. The company plans to sell 10,000 units of products. To ensure an uninterrupted supply of finished products, the company plans the balance of 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 pieces. Thus, the required production volume is 16,000 pieces. But at the beginning of the first period, there are 2,000 units of finished goods in the warehouse. Therefore, the production volume of the first period should be 16,000 – 2,000 = 14,000 products.

Table 21. Product production plan

All other indicators of the production plan are calculated in a similar way. 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 predicted individually and justified when compiling a block of initial data for the entire budgeting process.

The cost budget for basic materials with a payment schedule consists of two parts: calculation of the volumes of raw materials required for production (raw material acquisition plan) and a payment schedule for these purchases. This budget is presented in table. 22.

Table 22. Cost budget for basic materials with payment schedule

This budget is based on a production plan, not a sales plan. Using the standard for raw material consumption per unit of production (5 kg), the planned volume of raw materials for the production of products for a given period is estimated. To ensure an uninterrupted supply of raw materials to production, the financial manager plans a stock of raw materials at the end of the period in the amount of 10% of the need for raw materials in the next period. For the first quarter this value is 160,000x10%=16,000. Knowing the required volume of raw materials to fulfill the plan for the first quarter is 70,000 kg, we estimate the total required volume of raw materials: 70,000 + 16,000 = 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 = 79,000 kg should be purchased. raw materials. Since 1 kg. raw materials cost 60 kopecks, the total cost associated with the purchase of raw materials is 47,400 UAH. Planned costs for raw materials for all other periods are calculated in a similar way. We only note that the planned volume of raw material reserves at the end of the last period is assessed individually, and not as part of a general procedure. In our case, this is 7,500 kg, given as part of the source data block.

The payment schedule is calculated in the same way as the money receipt schedule. In the first quarter, the company plans to repay the accounts payable that existed at the beginning of the year (see opening balance), which amounts to UAH 25,800. In accordance with the agreement with the supplier of raw materials (see the initial data block), the enterprise must pay 50% of the purchase cost 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 repay the 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 is placed in the final balance sheet of the enterprise in the article accounts payable.

Budget for labor costs of key personnel is compiled based on the payment system adopted at the enterprise. No matter how complex the payment system is, its planned calculation can always be formalized in the form of appropriate tables. In this example, the simplest piece-time 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 of time and monetary costs for direct labor is planned, which is presented in table. 23.

In the case where budgeting is carried out on a more detailed time basis, for example, every month, the amount of money that is accounted for 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 of each month, then the amount of wages that is accrued in February will be scheduled for payment in March.

Table 23. Budget for direct labor costs

Manufacturing Overhead Budget compiled using aggregate financial indicators. The fact is that the composition of overhead costs is often large, and planning overhead costs by cost elements requires an unreasonably large amount of analytical work. Therefore, all overhead costs are preliminarily divided into variable and fixed, and planning of variable overhead costs is carried out in accordance with the planned value of the corresponding basic indicator. In the example under consideration, the volume of direct labor costs was used as a base indicator. In the process of preliminary analysis, a standard for direct labor costs was established - 2 UAH. for 1 hour of work of key personnel. This predetermines the composition of the indicators in the overhead budget table presented in table. 24.

Table 24 Budget for production overhead costs (UAH)

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

Product cost estimation necessary for drawing up a profit report and estimating the amount of finished goods stocks in the warehouse at the end of the planning period. Cost is made up of three components: direct materials, direct labor, and manufacturing overhead. The cost calculation for the example under consideration is shown in table. 25.

Table 25. Calculation of unit cost of production

The first two cost components are determined using direct calculation. In particular, if there are 5 kg per unit of production. raw materials at a price of 60 kopecks. per kilogram, then the direct materials component in the unit cost of production is 3 UAH. To estimate the amount of overhead costs in the unit cost of production, you must first “link” to some basic indicator, and then calculate this cost component. Since variable overhead costs were normalized using direct labor costs, we will also “link” the total overhead costs to direct labor costs. It is necessary to estimate total, and not just variable, overhead costs. To do this, let’s 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 costs (80,800, as follows from Table 23). It is not difficult to establish that for one hour of direct labor there are 404,000/80,800 = 5 UAH. overhead costs. And since 0.8 hours are spent on a unit of production, the cost of overhead costs per unit of production is 5x0.8 = 4.0 UAH, which is noted in the table. 25.

The total cost per unit of production was calculated to be 13 UAH. Now it is easy to estimate the amount of finished goods inventories 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 enterprise balance sheet at the end of the year in the item inventory of finished goods.

Administrative and marketing budget. This budget is prepared using the same approach as the manufacturing overhead budget. All costs are divided into variable and fixed. To plan variable costs, the volume of goods sold is used as a base indicator, rather than the cost of direct labor, as was previously the case. The variable cost standard 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 form of the budget is contained in table. 26.

Table 26. Budget for administrative and marketing costs

In a methodological sense, this budget does not pose any problems. The most difficult thing is to justify specific amounts of expenses for advertising, management salaries, etc. There may be contradictions here, since usually each functional department tries to justify the maximum amount of funds in the budget. At the same time, the budget may not be able to handle everything, as they say, “the budget is not flexible.” The methodological problem is how to evaluate the budget result for various options for the volume of financing of all functional departments of the enterprise. This is where scenario analysis comes to the rescue, which, essentially professing the ideology of flexible planning, considers possible budget options for various components of financing. Of all the options, the one that is most acceptable based on the strategy of the enterprise management is selected.

Planned profit report . Previously, two enterprise planning bases were noted: the resource base and the monetary base. Within the resource base, the enterprise’s profit is planned, the presence of which is a necessary condition for the enterprise’s ability to generate money. The income statement is inserted into the general system precisely for the purpose of checking this necessary condition. In addition, the income statement estimates the amount of income tax, which is then used as part of the statement of cash flows. The profit report for the example under consideration is placed in table. 27.

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

Table 27. Planned profit report (without additional financing)

In this income statement, the interest rate on the loan is assumed to be zero. This is fair, since in the process of previous budgeting the issues of lending to the enterprise were not considered. The need for additional financing should be identified when preparing the statement of cash flows. 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, the need to take out a bank loan may become apparent, and then the amount of net profit will decrease.

Cash budget 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. Good news is money coming in, bad news is money being paid. The result is a cash balance, which can be positive or negative. In table Figure 28 shows the cash budget for the example under consideration. The reader is given the opportunity to follow and check how the data from each individual budget ends up in the final cash budget.

Let us 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 as part of the planned income statement is divided into four equal parts, each part is taken into account in the cash budget of each quarter. It does not seem difficult to complicate this scheme and make it adequate to the real state of affairs. This issue is considered as part of the business situation analysis. Secondly, the investment budget declared in the general budgeting procedure is presented in Table. 28 one line of costs for the purchase of equipment.

28. Cash budget (without additional financing)

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

The work of the financial manager to justify the loan amount involves selecting 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 in the context of the example under consideration should not be lower than 30,000 UAH. A special feature of this work is the need to ensure coordination of cash budget data with the planned profit report. This need is due to the fact that the amount of interest payments is estimated in the cash budget and entered into the income statement. At the same time, the amount of tax payments is assessed in the income statement and entered into the cash budget. It is difficult to make such a selection “manually”, and you should use a pre-developed software tool.

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

Table 29. Planned profit report (final version)

30. Cash budget (final version)

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

Table 31. Balance sheet of the enterprise at the beginning and end of the planning period

Let us describe the formation of each balance sheet item. The cash item is taken as the total value of the cash budget. Accounts receivable is defined as 30% of fourth quarter revenue for which the company plans to provide deferred payment. Raw material reserves are defined as the amount of raw material balances from the table. 22 in size 7,500 kg. at the price of raw materials 0.60 UAH, which is 4,500 UAH. Inventories of finished products are defined as the product of balances of finished products at the end of the year (see Table 21) by the cost of finished products: 3,000x13=39,000 UAH. The item “structures and equipment” is calculated by adding the amount of equipment purchases to the initial value of this item. Depreciation according to the original data is 15,000 per quarter, i.e. 60,000 per year. This value should be added to the accumulated depreciation at the beginning of the period.

The amount of accounts payable is obtained as 50% of the cost of purchases for the fourth quarter, which is calculated in the 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 the assets and liabilities of the balance sheet is the same, which confirms the correctness of the budget. For a specialist who is drawing up a budget for the first time, and this budget turns out to be quite complex (several types of products, a complex cost structure), the fact that the amount of assets and liabilities coincides looks like a “financial miracle,” although this miracle is the result of using the double entry system adopted in accounting department

We also note the fact that in the case under consideration, the company plans to end the year without financial debt. This condition is not at all mandatory. An enterprise may end the year with a partially unpaid bank loan, and then a certain value will appear in the balance sheet item “bank loan” that will “not unbalance” the balance sheet.