Bdr tables in excel example. Drawing up an enterprise budget in Excel, taking into account discounts

The introduction of budgeting is a fairly important process that is carried out at every enterprise. Its main goal is to draw up and 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 in an enterprise using the example of production consists of the following parts:

1. revenue part – consists of all receipts from the company’s activities and is divided into three parts:

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

2. expenses – includes all expenses that need to be incurred to ensure the normal functioning of the company.

To effectively use Excel in the budgeting process, it is necessary to create tables that must 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 drawn up:

• 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 income and areas of expenses of the company. When drawing up a report in Excel, it is necessary to take into account all three areas of activity: operational, financial, investment. Therefore, the tables need to provide detail for each of them. In addition, visualization of the cash flow statement should clearly demonstrate the volume of receipts and expenses so that cash gaps can be quickly identified and eliminated.

The main goals of preparing the BDDS are:

• optimization of cash flows based on the forecast of cash balances;
• forming a spending plan taking into account future income;
• minimizing cash gaps.

BDDS is formed on the basis of:

• sales plan;
• standards for inventory turnover, accounts receivable and accounts payable (cash gaps are calculated);
• spending plan;
• financial and investment plan.

Considering that when drawing up budgets it is necessary to deal with a large amount of data, financiers use various automation programs to increase the speed and quality of their work. The simplest and most accessible among them is the Microsoft Office Excel application. Using the tables and formulas contained in the editor, you can carry out a large number of calculations, build various diagrams and graphs, etc.

Excel copes well with small amounts of data, but its use in medium and large enterprises is not recommended, since when working with large amounts of data it becomes very slow and clumsy. 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 allow you to quickly and efficiently solve all the tasks: create budgets of any levels, taking into account the specifics of the enterprise, as well as exercise operational control over their implementation.

Budgeting of income and expenses (IB) is a management tool that must be actively used in every enterprise.

Each company has its own budgeting system, which depends on its goals and financial planning strategy. Therefore, the purpose of the existence of BDR is to achieve the goals of the management team of the enterprise, using the own forces and means of the business entity. In addition to this document, there are two more types of budget - BDDS and BDL.

The document is usually drawn up not only for the enterprise as a whole, but also for its individual divisions. The income and expense budget combines the work plan of all structural elements of the company and allows decisions regarding management decisions to be made in a single flow.

This document indicates total financial flows and profits. Thus, BDR can be called the result of coordinated activities of the management of each division of the enterprise, which is aimed at the effective management of the entire company as a whole.

Thanks to the calculations that are used when forming a budget, it is possible to accurately determine the amount of money that is needed to implement a particular project. This specifically concerns the sources of funds (for example, the company’s own funds or loans).

What is their purpose

There are three main goals for the formation of a BDR:

• determining how effective the enterprise’s activities are;
• identifying the most effective divisions of the company over a specified period of time;
• setting limits regarding major expense items.

The profit and loss statement is similar in its functions to the budget, but, unlike the financial statement, it indicates planned losses. In this case, only zero profit can be indicated in the BDR.

The budget of income and expenses allows the management of the enterprise to analyze profits and find ways to increase this indicator. In addition, this document makes it possible to optimize tax and other mandatory payments.

Another goal of BDR is the full disclosure of the company’s income, its expenses and the cost of production. This allows you to accurately determine the profit that the company receives in the selected period.

An exception may be the case when a company launches a new project, the profit from which is expected only in a few years. Then, in the selected period of time, losses will be inevitable, but the budget will ultimately show break-even.

Popular approaches

According to leading experts in the field of budgeting, there are two approaches to developing BDD:

Both approaches have their pros and cons. It is believed that the most optimal budgeting option is iterative. Its essence lies in the use of both approaches: when forming a task by the company’s management for subordinates of the Central Federal District, coordination with managers “on the ground” is necessary.

Of course, approval is a process that takes time and involves several iterations. Each of them includes holding meetings and analyzing data for previous periods. But, in the end, such a document will be more meaningful than a list of numbers that management passed down to their subordinates.

State model

Each sovereign state has its own budget, the preparation of which is entrusted to the authorities. In this regard, the budget is a state document according to which financial resources are distributed in the country.

The Russian model of state budgeting implies the presence of a certain independence among the constituent entities of the Russian Federation. However, the government approves the main financial document in Russia, and the process of its development is subject to a unified legislative framework and common principles.

The state budgeting model implies that some regions require an additional injection of money. Therefore, the size of the federal budget also shows the degree of financial assistance to depressed regions and municipalities.

Main directions

Formation of expenses

Main directions of state budget expenditures:

• healthcare;
• science;
• defense;
• public administration;
• social policy;
• law enforcement agencies;
• international activities;
• agriculture;
• industry.

Profit control

The source of budget revenue is determined in accordance with the tax and budget legislation of the Russian Federation. Funds that have already been transferred to the account of the executive body are considered to have been received as budget revenue. Tax and non-tax revenues of the state budget are distinguished. Control of state expenses and revenues lies with the Ministry of Finance of the Russian Federation.

Classification of budget income and expenses

Revenue classification is a grouping of budget revenues from different levels of government (federal, regional and municipal).

There are four groups of budget revenues of the Russian Federation:

• transfers from state trust funds;
• free transfers;
• non-tax revenues;
• tax revenues.

The classification of budget expenditures is divided into functional, economic and departmental. The first is a grouping by management level and shows where financial resources are directed and how they are spent.

According to economic classification, expenses are divided according to their economic content. They distinguish between capital and current expenses and the provision of loans. The departmental classification of expenses includes three levels: federal, regional and local.

A balanced budget is one in which income and expenses are equal. If there is an excess of the former over the latter, then there is a budget surplus. Deficit is the opposite picture, when expenses exceed income.

Additional details

Analysis technology

BDD analysis is an indispensable part of any budget architecture.

The following types of analysis exist:

Any method is based on the calculation of indicators (groups of indicators) with their further comparison with standards. The object of analysis is selected based on the goals of the enterprise and the characteristics of its activities.

The main indicators are:

The demand for these indicators is explained by the fact that the economic conditions of the enterprise have a significant impact on the costs associated with depreciation, taxes and interest payments. Therefore, there is no possibility of obtaining comparable data that characterizes the effectiveness of decisions made by the company's management. Analysis of these factors makes it possible to adequately compare the activities of enterprises operating in different countries.

Indicators and results

The formation of the budget revenue takes into account the following indicators:

• budget for services and goods sold;
• income related to other activities;
• forecasts of other income, as well as taking into account changes in the exchange rate for a given period of time;
• forecast of interest payments received for loans provided by the organization.

For the expenditure part of the BDR, the following indicators are used:

• a list of semi-fixed and variable costs for production and business activities;
• business expenses;
• interest paid on loans and borrowings;
• other expenses.

The company's financial statements make it possible to assess how efficiently it conducts its activities, as well as to determine reserves for profit growth and determine the cost limit. In the process of forming the expected profit (target indicator), forecast data is constantly changing. At the same time, there is a recalculation of the BDR indicators necessary to achieve the enterprise’s goal.

The purpose of budgeting is to create a final budget form, according to which it is possible to analyze the efficiency of the enterprise. It provides an opportunity to get a clear picture of the current financial situation and expected changes in the future. The assessment is based on three indicators: profit, cash flow results and financial balance.

The standard BDR form includes the following lines:

• revenue is an income item showing the amount of cash that a company receives over a certain period;
• transfers are revenue that is not yet indicated in accounting documents or revenue that is documented in the current period, but actually relates to another;
• net revenue – the sum of the first and second points;
• revenue from the main and other activities of the company;
• non-operating revenue is not related to the main activities of the enterprise. This includes interest on bank deposits, dividends, and so on;
• cost price;
• variable expenses;
• fixed costs;
• staff salaries and accruals;
• basic materials;
• general production and general business expenses;
• business expenses;
• taxes.

Instructions for formatting in Excel

Let's consider the structure of the budget of income and expenses in the form of an Excel table. At the top level (zero), three groups of items (indicators) are indicated: income, expenses and profit of the enterprise. This type allows you to create a budget for any company.

At the first level, income and expenses are detailed by the main areas of activity of the enterprise (for example, income from core activities, from other activities, and so on). At the same time, at this level, the types of profit that need to be analyzed are specified.

It is also important to determine the main direction of the company's work and find out which types of activities belong to others. If we take a metallurgical plant as an example, then the main thing for it will be the production of metal products, and the other will be transport.

Based on the information received, the directions of costs are determined. For this purpose, a study of production processes is carried out. These could be costs of raw materials, wages of workers, energy, etc.

Approximately according to this principle, BDR records are kept in the form of an Excel table.

We present OKFS codes with a decoding of the name of the form of ownership and its affiliation with economic entities

Whether you're managing your company's finances or your home finances, creating a budget is an important first step. Having a budget is necessary to keep track of your current expenses, determine where you can cut costs, and make decisions about what to spend your money on.

While creating a budget can seem like a daunting process, using a budget template can help make the process a little less intimidating. How to choose a template suitable for your purposes from the huge number of existing ones? We've reviewed the best Excel templates and shared them in this article so you can choose the ones that work best for you. We also provide a detailed description of how to use a personal monthly budget template in Excel and Smartsheet.

1. Go to the website and sign in to your account (or take advantage of the free 30-day trial).
2. Go to the "Home" tab, click "Create" and select the "View Templates" option.
3. Enter the word "Budget" in the "Search Templates" field and click on the magnifying glass icon.
4. A list of templates will be displayed. For our example, we will use the “Planning a family budget by month” template. Click on the blue "Use Template" button in the top right corner.
5. Name your template, choose where to save it, and click OK.

2. Enter your budget details

A pre-formatted template will open containing the content for the sample, as well as ready-made sections, categories and subcategories. In Smartsheet, you can easily add or remove rows based on your budget data.

Simply right-click on a row and select "Insert Above" or "Insert Below" to add a row, or "Delete" to delete a row.

1. Update the section and subsection titles in the main column to reflect your details.

*Please note that the Savings and Savings section of this template is included in the Expenses section. You can move this section to a location that is more suitable for you by selecting the lines you want, right-clicking on them and selecting "Cut". Then right-click on the row where you want to paste the selected rows and click "Paste".

1. Enter your income, savings and expenses for your respective budget categories in the Monthly Budget column. Please note that the hierarchy has already been set up for you, and the formulas will automatically calculate totals for the categories based on the data specified in the subsections.
2. On the left side of each row, you can attach files directly to budget items (ideal for attaching bank statements, tax documents, etc.).
3. Add important details in the Comments column, such as invoice details or links to specific invoices.

3. Update your monthly budget actuals

1. Enter the actual ruble amounts for each budget element for the corresponding month. You can set up to receive reminders by opening the Alerts tab at the bottom of the page and selecting New Reminder. Additional information.

1. You can provide access to your budget to interested parties. This will not only help keep others informed about the state of the budget, but will also increase accountability on your part. To share, click the Sharing tab at the bottom of the page. Add the email addresses of the people you want to share, add a message, and click the blue "Share Sheet" button in the bottom right corner of the dialog box.

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 the 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 the 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 days of turnover 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 (200,000 rubles) 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

Without planning and control of payments, not a single enterprise can exist: every day the head of the company needs to make a decision on how to distribute funds and prioritize payments. Can help him with this Cash flow budget(BDDS) - a document that contains all received requests for payment and information about the available funds in the company. The article provides forms for weekly budget planning, examines mechanisms for forecasting revenue from sales in wholesale and retail directions, and provides recommendations for creating budget forms sent to managers of cost items.

Budget control

An essential component of the control system- control of treasury budget execution, that is, control of the receipt and expenditure of funds that are planned in the BDDS. Operational control of the cash flow budget is usually carried out by budget controller. Guided by the approved cash limits, he determines budget items to finance excess expenses. The financial controller evaluates each incoming application for settlements and determines whether it exceeds the limit for the corresponding budget item.

Exceeding limits in the budget period is possible only by special order of an authorized official (financial or general director). When it comes to redistributing expenses between various budget items, these powers are assigned to financial controllers.

Monthly cash flow plan

Planning cash flows for the current month should begin with a general cash flow budget plan, an example of which is presented in table. 1.

In general, the BDDS consists of the following blocks:

1. Cash balance plan at the beginning of the month.
2. Cash flow plan from core activities (sales revenue, bonuses from suppliers, income from sublease of premises, etc.).
3. Operating expenses plan, which consists of two parts:

• payment plan to suppliers for goods;
• payment plan for other operating expenses.

1. Flow plan for financial activities: the balance between loans receivable and repaid minus interest on loans payable.
2. Flow plan for investment activities: the difference between income from the sale of fixed assets and payments for the acquisition and repair of fixed assets.

As a result, we obtain the planned net flow for the period under review and derive a forecast of the cash balance at the end of the period.

If, as a result of planning at the end of the period, negative cash balances, the budget is adjusted by reducing the payment plan. Therefore, to understand the situation, it is better to immediately add information to the BDDS about the current debt to suppliers, planned costs for the coming month and forecast debt at the end of the month, taking into account the budgeted payment amounts.

In our example, the net flow for the month is predicted to be negative (–47.7 thousand rubles), but due to the initial balances of 65 thousand rubles. We is able to fulfill the stated budget for a given month. At the same time, we are increasing receivables from our customers from 185 thousand rubles. up to 290 thousand rubles. and reduce accounts payable to suppliers of goods from 450 thousand rubles. up to 300 thousand rubles. In general, the picture for the month is optimistic.

However, it is worth noting that this month a refinancing of 500 thousand rubles is planned: our loan term in Bank No. 1 is expiring, we expect to receive a loan for the same amount in Bank No. 2. And if we receive a loan in Bank No. 2, we we can a little later than the expiration date at Bank No. 1, then within a month we need to save up in our accounts 500 thousand. rub. (about half of our monthly revenue). That is, for almost half a month we will not be able to spend large sums on operating expenses: all payments on them will begin only after receiving a loan from Bank No. 2.

Of course, there are mandatory payments that cannot be postponed until the second half of the month (payment of rent, utility bills, payment of wages according to the schedule). Therefore, we need a daily or weekly cash flow plan, which in the future we must strictly adhere to so as not to spoil our credit history with Bank No. 1.

We will draw up a weekly cash flow plan for the next month, where we will plan the receipt of revenue and obligatory expenses, after which we will display the amounts that we can allocate for other payments.

Revenue plan by week

The revenue plan for revenue from retail and wholesale is formed according to different principles. Receipts from wholesale customers can be easily predicted through deferred payment. To do this, we will use the standard report “ Gross profit”, which is located in the “Sales” block of the “Reports” tab on the Excel toolbar (Fig. 1).

Let's customize the "Gross Profit" report to our requirements:

1. Go to the report settings, click the checkbox “ Advanced setup».
2. On the tab " General»:

• we set the sales period for which we expect the receipt of funds from customers (usually it is equal to the maximum deferment provided to our customers);
• in the block " Options» click the checkboxes “Output general totals” and “Output detailed records”;
• in the block " Indicators» we leave only “Sales cost, rub.” and “with VAT”, uncheck the remaining indicators (Fig. 2).

1. On the tab " Groups» delete all groupings that are provided by the default report (Fig. 3).

1. On the tab " Selections» establish a selection: we are interested in sales of only the wholesale division (Fig. 4).

1. On the tab " Additional fields» display the fields “Buyer” and “By dates”, for all fields in the “Placement” column we set the type “In separate columns”, in the “Position” column - “Instead of grouping” (Fig. 5).

1. Click on the button " Form" and we get a report that is presented in table. 2.

Let's copy the resulting report into Excel and add the data we need: add a deferred payment and calculate the payment term as the sum of two columns: Date of sale + Deferred payment (Table 3).

Now let’s group payment dates by week using a pivot table:

1. Select the table. 3 along with the header and on the “ tab Insert"click on the icon" Pivot table"(Fig. 6 (a)).
2. In the dialog box that opens, indicate where we want to place the pivot table: on a new sheet or on an existing one (you must specify the cell into which you want to insert the pivot table). To create a new pivot table, it is better to first place it on a new sheet, bring it to a form convenient for us, and then transfer it to the sheet where we will work with it in the future (Fig. 6 (b)).

In the window that appears " List of Pivot Table Fields" let's set its appearance (Fig. 7):

• In the “Line titles” block, drag the “Payment date” field with the mouse;
• drag the field “Sales cost, rub.” into the “Values” block.

1. We get the report presented in table. 4.

1. It can be seen that the table includes payment dates for the previous month. Let's remove them using the pivot table filter. We stand on any cell with a date and call up the context menu with the right button, in it select “Filter” > “Filter by date”, set the filter “After” > “07/01/2016” (Fig. 8).

1. The table now only contains sales due in July. Call the context menu again and select “ Group" In the dialog box that appears, set the range: from 07/04/2016 to 07/31/2016 with the “Days” step, the number of days is 7 (Fig. 9).

1. We got forecast of cash receipts from wholesale sales by week(Table 5).

Now let's do it forecast of cash receipts in retail direction. There are two important points to consider when planning your cash flow:

1. retail sales have a pronounced seasonality by day of the week: customers visit stores more often on weekends (the peak of sales falls on them);
2. We can use the proceeds from retail sales for payments on the current account only after its collection to the bank, which is carried out on business days with a delay of one or two days. That is, revenue from sales on Monday is credited to the current account on Tuesday-Wednesday (depending on collection conditions), revenue for Friday-Sunday will be credited to the current account on Monday or Tuesday. Thus, we will be able to use the proceeds for July 29–31 only in August. But on July 1, we will receive collection of proceeds for June 30.

Let's compose daily sales plan in retail stores, on the basis of which we will form collection plan to current account. You can break down the monthly plan by day of the week in proportion to the previous month or the same month of the previous year, which is more desirable, since in this case we will be able to take into account the monthly seasonality of sales.

When using data from last year, you need to make comparisons not by dates, but by days of the week. So, 07/01/2016 falls on a Friday; in 2015, the first Friday of July was July 3. Therefore, to derive seasonality proportions, we need to take sales from July 3 to 08/02/2015. That is, to get a date from last year that is similar to the day of the week of this year, you need to subtract 364 days (exactly 52 weeks).

Table 6 shows a breakdown of the sales plan by day and the collection plan by day of the week and grouped by week. As a result, we see the following: since the last days of July fall on the weekend, the cash flow plan differs from the sales plan by 75 thousand. rub. Other revenues in our budget are sublease income, which must be paid by the 10th day of each month according to the lease agreement. Therefore, we set these receipts for the second week.

Table 6. Plan for receipt of revenue from retail sales to the current account, rub.

Payment schedule

We have created a weekly cash flow plan. Now let's spread it to BDDS mandatory payments(highlighted in color in Table 7):

• payment of wages: the remaining salary for the previous month must be paid by the 10th, the bonus is paid by the 15th, the advance payment for the current month - by the 25th. We set 50% of the salary to be paid for the second week, 100% of the bonus for the fourth and 50% of the salary for the last week of the month;
• rent payment: according to the agreements, the deadline for paying rent for the current month is the 10th. We set payment for the second week;
• utility bills must be completed by the 25th, we set them for payment on the 25th, that is, for the last week;
• security according to the agreement concluded with the private security company, payment is due by the 20th, we set payment for the fourth week;
• payroll taxes you need to pay by the 15th, which means we will need money for them in the third week;
• personal income tax is paid simultaneously with the payment of wages, so we distribute it by week in the same proportion as the payment of wages and bonuses;
• for other taxes payment deadline is from the 25th to the 31st (last week of July);
• repayment of loans and interest payments- until the 22nd ( attracting loans- after the 25th).

All other payments in the coming month are immediately attributed to the last week (when we can replenish current assets with a new loan, the receipt of which is scheduled for July 25).

As a result, we see that we can only spend 120 thousand. rub., we will be able to close the rest of the debt to suppliers in the last two weeks of July.

If the opinion of suppliers is important to us, we need to notify them in advance of the current situation. You can provide them with a clear payment schedule for this month so that they too can plan their financial options for the coming month.

Creating budget forms for the budget item controller

Now let's consider different ways to get a monthly BDDS plan. If the company is small and there are few contractors, then the economist is able to independently plan upcoming payments for the month. It is enough to collect the current debt to suppliers and contractors on accounts 60, 76 and analyze the monthly accruals for all counterparties.

In our example, there are only two suppliers of goods and nine contractors and service providers (see Table 7), most of them issue the same invoices monthly (rent, security, utilities and taxi services). It is clear that it is quite easy to predict payments for them. The only difficulty that can arise is in tax planning. This means that you need to turn to the chief accountant for help, since he is responsible for timely payment of taxes.

In large enterprises, it is difficult for one economist to correctly plan the budget for all expense items, so in such companies, usually all expense items are assigned to responsible employees, the so-called managers of budget items. They are the ones who plan payments and then submit requests for payment of bills to the financial service. To make it easier for you to collect a general budget based on the budgets submitted by the stewards, it is better to develop unified budget format which they must fill out.

Table 8 presents the budget form for the manager of the building maintenance cost block, from which it is easy to transfer data to the general BDDS form. If there are a lot of articles in the BDDS, then it is better to enter the article code. Then with the help SUMIFS() functions you will be able to automatically transfer data from the controller's budgets to the general budget.

There are a few things to consider when designing forms:

• the controller should not change the number and sequence of columns (otherwise the formulas configured for his budget will not work correctly). If he wants to add additional explanations on the article, let him do it to the right of the approved form;
• the controller can add lines to the report if he has an increased number of counterparties for any cost item. However, adding new rows should not lead to the need to change the resulting rows;
• all cells with calculation formulas must be protected from editing (to avoid accidental overwriting or changing the formula to an incorrect one);
• the final values ​​for the manager’s budget must be verified with the data included in the consolidated BDDS in order to eliminate the possibility of distortion of information.

Let's look at how to implement these requirements using the capabilities of Excel.

1. Cell protection.

To protect cells from editing, click the " Protect sheet" on the " tab Review" Please note that by default Excel will protect all worksheet cells from changes, and we want to prevent stewards from corrupting the resulting rows. Therefore, protection should be removed from cells in which stewards are allowed to make changes. You can remove protection from the cell in the menu “ Cell Format" on the " tab Protection"(Fig. 10).

The controller is allowed to change the number of rows (add and delete), so when installing sheet protection, do not forget to check the “insert rows” and “delete rows” checkboxes (Fig. 11). Be sure to set a password to remove protection. Otherwise, employees who know how to work with sheet protection will easily bypass this limitation.

1. Taking into account in the SUM() formula new rows that the controller can add.

The main rule when developing a free-length budget is to always use function SUM(). Applying this rule does not always guarantee that the resulting rows will contain all the data. Figure 12 shows an example where the budget controller added a new line to the end of the “Rent of premises” block (quite logical from his point of view), but it was not included in the final formula.

Way out of this situation: insert a line between all cost blocks and include it in the summation formula (for recognition, be sure to highlight the line in some color). For the user, this line will become a kind of separator between cost groups, and he will always add new lines just before it (Fig. 13).

The next stage of financial management is collecting payment requests and maintaining a daily payment calendar.

Instead of a conclusion

Proper development of budgets for the cost item controller will allow partial automation of the collection of planned monthly budget data, which will speed up the process of its preparation and reduce the influence of the human factor when consolidating data from different sources.

If the planned budget for the month is in surplus, this does not mean that in the middle of the month there will be no budget deficit (the situation is most likely in the month of repayment of a large loan amount). Therefore, it is extremely important to draw up not only a monthly, but also a weekly cash flow forecast in order to know in advance about possible gaps in the budget and correctly plan payments to avoid such problems.