Sample bdr form in excel. Ready-made enterprise budget template in Excel

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 first example is a management report that Alena Lenart from the Investfond company sent us (report file). It is worth noting that a lot of work has been done on budgets. So, as a first approximation, the report looks very good, but there are a number of significant comments.

First remark

The report immediately highlights three areas

1. Management accounting

2. Accounting

3. Change

Apparently management accounting data appears quickly, but is not very accurate, while accounting data appears later, but is much more accurate. And the report makes an attempt to show the difference between accounting and management accounting data.

The question is - why? To show that the report contains unreliable data? In my opinion, it is most advisable to indicate in the report the most correct data at the moment and not make this bunch of columns. The clutter of data makes the report very difficult to understand!

Second remark

Let's take a closer look at area 3.

The inscriptions circled in red read: Invoices issued but not paid. This suggests that the budget is a hybrid of the budget of income and expenses (invoices issued) and the budget of cash flows (paid invoices). And this leads to great confusion...

Minor issues

Now let's turn our attention to the report design. In the original it looks like this:

The background is too dark. This is very inconvenient when reading from the screen. Not to mention how it will look on paper. When preparing a report, there is a very simple test - try printing your report on a monochrome (black and white) printer. If the report reads normally, then everything is in order. In this case, when printing on a black and white printer, the dark blue and black colors will merge. If you really want to highlight the header, make it dark gray and the font white.

The same. The background is too dark.

Why do you need italics and italics with underlining here? It's beautiful, but it interferes with perception. If you need to make groups of articles and their transcripts, work with font size, indentation and bold highlighting. For example like this:

When you put numbers in the budget, always put a separator for groups of digits - this makes it much easier to understand. And never count pennies - this greatly worsens perception, and the difference in amounts counted with or without pennies, even for an enterprise with a multimillion-dollar turnover, is a maximum of 200-300 rubles!

And finally - if you can play with font sizes in the text part of the budget, then in the numerical part - in no case!!! This will make your budget unreadable!

When numbers are made in different fonts, the brain, instead of perceiving them as components of a single picture, begins to perceive them as different numbers.

Remark three

It also relates to design, but it is already very significant. Let's look at grouping the lines in your budget.

The items marked in block 1 belong to the “Costs of maintaining the company” group, but in order to decipher them, you need to click on cross 2, which is located opposite the article “Utility payments”. This is very bad...

But we can fix everything. Let's redo this block a little. First, let's delete the existing article structure. To do this, select column 1 and click Data – Group and structure – Delete structure.

Now let's change the grouping settings. Go to the menu Data – Group and structure – Settings.

In the window that appears, uncheck the “Totals in rows below the data” checkbox. Now the totals for the group will be displayed at the top of it.

And we re-group the budget lines. Data – Group and structure – Group.

In the window that appears, select “Strings”. As a result, the total for a group of articles is now collected from above. And when you click on an article, it is this article that is revealed, and not the previous one.

Remark four

Now let's get to the bottom of this. Judging by the form of the budget, we have income that is taken into account for the company as a total amount (1) and costs that we can divide by division (2).

All this was laid out in the same form. However, the more numbers, the more complex the budget. The budget should be revealed from top to bottom, and at the top level all this division into departments is not at all necessary. Plan – Fact – Deviation will look much better. It is better to make a breakdown by department in a separate report.

Remark five

If all variable expenses consist of one item (1), there is no need to create a group and then an item. It's better to just use the article. Just write “Variable salary” and that’s it.

Further. Direct costs are not constant (2). This class of costs simply does not exist. Direct costs are those costs that, without allocation, can be attributed to a specific product or service. There is a product - there are costs, without a product - there are no costs. But if we don’t have a product, the rent will remain. And employee salaries too. So these are indirect fixed (they are simply fixed) costs.

I am also confused by the articles “Purchasing furniture” and “Purchasing printers, MFPs and paper shredders.” I would exclude these items from the BDR and include them in the capital budget. Although, it all depends on the amount and economic meaning of the transactions.

What happened in the end?

As a result, after all the transformations, you should get approximately the following budget (all data in this budget are fictitious and are given only as an example)

However, in this budget the division of fixed costs by department was lost. But the breakdown of fixed costs (and even several) is on a separate sheet.

You can download the file with budgets from this link. Try opening this file and playing with periods and scripts. I would especially like to draw your attention to the fact that all the source data is on the “Source Data” sheet, and they are collected on the “BDR” and “Fixed Cost Analysis” sheets. But I will tell you about this Excel feature a little later.

That's all for today. I'm waiting for you at my seminar.

What is a cash flow budget (CFB)? How to create a budget for income and expenses of an enterprise? How to prevent budget expenditures from exceeding its revenues?

If your business has income, it also has expenses. This means you need to budget professionally.

The more money, the more difficult it is to manage. In order to properly distribute funds and manage the solvency of the company, entrepreneurs use budget of income and expenses And cash flow budget .

Denis Kuderin, an expert on economic and financial issues, is with you. In this article I will tell you what the concepts mentioned above are and how to manage a budget to make business more efficient.

Sit back comfortably and read to the end - at the end you will find a review of reliable companies that will help establish budgeting at the facility, plus tips on how to prevent business expenses from exceeding income.

1. What are BDR and BDDS and how do they differ?

Even managing a family budget is not that easy. Anyone who has tried it knows that there is no money for everyday expenses. it always takes more than you expected. You have to adjust expenses, add new items to the budget that you completely forgot about at the time of its preparation.

Imagine how much more difficult it is to manage the budget of a large enterprise. At any commercial facility hundreds of expense items and expenses that need to be made.

The budget is not an abstraction, it is a concrete concept supported by special documents. Each enterprise, even one consisting of 2 employees, maintains an income and expense budget (I&C) and, if possible, a cash flow budget (CFB). This is the basis.

Before moving on to the practical meaning of these concepts, let's define the terminology.

BDR– a method of documenting transactions that form the income and expenses of an enterprise. As a rule, such a document takes the form of a simple table, which takes into account all economic manipulations that lead to the receipt of funds or their expenditure. This takes into account not only monetary, but also any other income and expenses.
BDDS– a way to reflect the movement of cash flows in an enterprise. This document only deals with events that have monetary value.

The primary documents that are used when registering BDR transactions are acts of completed work and services provided, acts of acceptance and transfer of tangible assets, and any other documents confirming the company’s income and expenses. The document is similar to an income statement.

What is the difference between BDR and BDDS?

These budgets are different goals for which they are formed. BDR is being developed for the purpose of profit planning, which the company is able to receive during the budget period. This includes all data about production costs products and revenue.

BDDS is intended for cash flow distribution. It reflects all the activities of the organization that were carried out in cash. With the help of BDDS, all transactions of the enterprise are monitored across various accounts.

The table shows the transactions that are reflected in the budget documents we are considering:

№	Operations	Reflected in the BDR	Reflected in BDDS
1	Depreciation calculation	Yes	No
2	Revaluation of inventory items	Yes	No
3	Shortages of inventory assets	Yes	No
4	Manufacturing defect	Yes	No
5	Credits and loans	No	Yes
6	Acquisition of fixed assets	No	Yes
7	VAT	Yes	Yes
8	Expenses for major repairs	Yes	Yes

Both budgets together provide a clear understanding of the company’s current financial condition and its prospects. As a rule, it begins with the preparation of the BDR, since this document has a more “extended” format.

The BDR includes three groups of financial indicators - income, costs and profit. The latter is calculated by subtracting the second from the first.

BDDS is a plan for the movement of cash in the company's cash register and in current accounts. The document reflects all planned receipts and write-offs of funds as a result of business operations. BDDS protects a business from the main mistake of being left without money to conduct its core business.

In this short video, they will explain to you the difference between BDR and BDDS using the example of buying a refrigerator.

2. What activities underlie the compilation of BDDS - 3 main types of activities

When compiling a report, the BDDS is guided by three types of activities enterprises – operating room(current), investment and directly financial.

Let's look at them in detail.

Type 1. Operating activities

This is the main activity of the company - the work that creates income and expenses of money. This is the production of products, sales of goods, provision of services, performance of work, rental of equipment and other operations related to cash flow.

Type 2. Investment activity

Related to the acquisition or sale non-current assets. Investing, like operating activities, is aimed at making a profit or achieving a beneficial effect for the company. However, in such activities the main working capital is not involved, but is used " free" money.

Example
The Safe Technologies enterprise invests part of its assets in development of alternative energy sources – generators based on solar panels and wind. Money is invested in laboratory research and scientific development. These financial transactions are necessarily reflected in the BDDS report.
Type 3. Financial activities
Leads to changes in the composition and size of the company's fixed capital. For example, this is the attraction and repayment of loans necessary for the enterprise to develop new areas of production.
The DDS budget prevents shortages and excesses of working capital
Dividing the company's activities into types allows us to assess the effect of all three areas on the financial status of the company and the amount of capital at its disposal.
A well-drafted cash flow budget ensures the constant availability of funds necessary to carry out the core work of the company.
BDDS also allows you to effectively use the company's excess money, since the main principle of business is that free funds do not lie idle in bank accounts, but bring even greater profits.
3. How BDD is formed - 5 main stages
BDR is a universal tool for managing business processes. It allows you to optimally use the company's resources, assess the economic condition of the enterprise, and plan further work.
Today most companies use automated budget management and management systems. Special programs reduce the number of errors, reduce the time for calculations and facilitate the work of employees of the financial departments of the enterprise and financial responsibility centers (FRC).
Before drawing up a BDR, you need to create and systematize the company’s local budgets - production, management, sales budget, cost budget, etc. The BDR is a document that summarizes all this data.

The main purpose of BDR is accounting and forecasting the financial condition of the organization. This is the final part of the enterprise budget, the tip of the iceberg, the base of which is the indicators of all company budgets in all areas.
Let us consider step by step how the BDR is formed.
Stage 1. Cost calculation
Without expenses there is no income. Guided by this simple truth, the financial departments of any company pay primary attention to costs.
What is included in the consumables:
production costs;
business expenses;
managerial;
wages and taxes;
other expenses.

The detailing of expense items depends on the goals and capabilities of the company. It is clear that the more details the costs are taken into account, the clearer the economic situation in which a particular object is located.
Stage 2. Calculation of income
Revenues are all inflows into a company's assets.
These include:
sales revenue;
income from services;
rental income;
non-operating income– interest on loans, compensation and other income not directly related to the sale of main products.

Each enterprise has its own sources of income, so the details depend on the profile and specifics of the company.
Stage 3. Determination of profit
Profit– a positive difference between income and expenses. If the difference is negative, it is no longer a profit, but lesion. This means that the enterprise is operating at a loss, and fundamental changes are needed in production and all other processes.
Stage 4. Profit planning
Since profit is the main source of financing for an enterprise, all its activities are aimed at maintaining and increasing working capital . Money invested in production must be returned as quickly as possible– this problem is solved by professional profit planning.
Another goal of planning is to obtain maximum benefits at minimum costs, but not at the expense of quality loss, but through rational organization of labor and reduction of associated costs.
At the same time, the main needs of the company are satisfied:
payment of salaries and incentives to employees;
accumulation of funds for modernization and expansion of production;
payment of obligations, as well as to investors and owners of the company;
increasing the profitability of the enterprise;
increasing competitiveness.

Again, the accuracy of the forecast is directly affected by the most detailed breakdown of the company’s expenses and income.
Stage 5. Report preparation
Only professionals can draw up a competent and objective report. If you are the head of a company and doubt the competence of your CFO employees, then the best option is to delegate budgeting to a qualified outsource company.
Third-party specialists will not only draw up a detailed BDR, but also provide it if necessary. This may take more time, but the result will be more objective.
4. How the BDDS is compiled - 5 main stages
In general, the preparation of a BDDS is similar to the formation of a BDR, but there are certain nuances.
As I already said, only monetary receipts and expenses that are reflected in financial documents.
Stage 1. Setting the cash balance
First, you need to set a required minimum balance. The value of this indicator depends on the specifics of the company’s activities and the likelihood of unforeseen situations. In financial language this is called " closing balance ».
Stage 2. Determination of the revenue part
The preparation of the revenue side of the budget is based on the budget of sales and income from investments, dividends and interest.
There are two options for collecting information:
Bottom - up when plans for material receipts come from various departments and are then compiled into a single report;
Top down, when documents are approved by the central financial service of the company and then communicated to department heads.

Stage 3. Compilation of consumables
The expenditure part is based on direct costs - labor costs, raw materials, overhead, production, and general business expenses. This also includes the costs of investments and other financial transactions for the return of loans, interest and dividends to investors.
Stage 4. Calculation of net cash flow
Net Cash Flow (sometimes the English term is used Cash Flow) is calculated using a formula and shows the difference between positive and negative balance for a specific period of time. This indicator characterizes the current financial status of the enterprise and determines its prospects.
When the expenditure side of the budget exceeds the revenue side, a situation arises that is called " cash gap " The final balance then becomes negative. In such cases, measures are taken to eliminate the disadvantage - they cut costs or (as a last resort) use borrowed And reserve funds for further business.
Enterprises that cannot eliminate their negative balance over a long period heading towards bankruptcy. It is in such companies that wages are delayed, debt obligations are not fulfilled, creditors are pressing, and profits do not cover current expenses.
Stage 5. Adjustment and approval
The final stage is the adjustment of the budget in accordance with current economic realities and its approval by the management of the enterprise. The approved budget is an official document that guides all company personnel, but primarily the managers of the Central Federal District.
5. Where to get help in drawing up BDR and BDDS - review of the TOP-3 companies providing services
The formation of BDR and BDDS is a responsible job that should be carried out by experienced and qualified employees.
If there are none at your enterprise or your specialists lack knowledge, it makes sense to invite third-party organizations. They will do this work professionally, competently and fully, using modern software.
The experts of our magazine studied the market and chose three most reliable and attractive in terms of cost of company services.
"ITAN" is an up-to-date budgeting system for commercial properties based on 1C. The main area of activity is the establishment, implementation and automation of financial planning at the customer’s enterprise, the organization of management accounting, the consolidation of financial information for large holdings and companies with an extensive network of branches.
The company was founded in 1999. Among the achievements is the development of universal and integrated solutions based on the 1C platform. Every year, the company's unique products are improved, becoming simpler and easier to manage. ITAN's mission is to help improve the productivity of financial management of enterprises.
Sales and implementation 1C software products. Areas of activity: budgeting, accounting, warehouse and production accounting, sales, document flow.
The company employs 56 highly qualified and experienced specialists. Employees are financially responsible for the results. Over the past year, the company has gained 250 new clients. Another advantage is metropolitan quality at regional prices. GOODWILL has many finished projects in the field of automation of financial, warehouse, management accounting.
3) First BIT
The First BIT company was founded in 1997 by several young and ambitious specialists in economics and applied mathematics. The main direction of the organization’s activities is business development based on current IT technologies. Now the company has 80 offices in Russia, near and far abroad.
"First BIT" will automate the enterprise in all necessary areas, including budgeting and management accounting. 2,500 thousand customers have already chosen the company's software products and services.
6. How to prevent budget expenses from exceeding its income - 3 useful tips
Maintaining a budget professionally means constantly track financial results activities. One of the goals of budgeting is to prevent expenses from exceeding income.
How to achieve this? Put expert advice into practice.
Tip 1. Discipline your staff in the use of funds
Financial discipline is the basis for the rational distribution of an enterprise’s material assets.

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:

Cash balance plan at the beginning of the month.
Plan for cash receipts from core activities (sales revenue, bonuses from suppliers, income from sublease of premises, etc.).
Operating expenses plan, which consists of two parts:

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

Flow plan for financial activities: the balance between loans receivable and repaid minus interest on loans payable.
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.

Table 1. Cash flow budget, rub.
	Article	Counterparty	Debt as of 1st	Accruals/ Sales/ Supplies	Budget	Debt on the 31st
	Balance at the beginning of the period
	On a current account

	Sales proceeds
	Retail sales
	Wholesale sales
	Other income
	Operating expenses
	Payment to suppliers
	Payment to suppliers	Alpha LLC
	Payment to suppliers	Omega LLC
	Operating costs
	Salary payment
	Cover part	Employees
		Employees
	Personnel costs
		Taxi LLC
		Express LLC
	Medical examination	Clinic No. 1
	Building maintenance costs
	Rental of premises	Terem LLC
	Rental of premises	Teremok LLC
	Utility costs	Gorvodokanal
	Utility costs	HeatElectroStation
		Private security company "Dobrynya"
	Taxes to the budget
	VAT payable
	Income tax
	Property tax

	Salary taxes
	Total flow from operating activities
	Flow from financing activities
	Attracting loans
	Loan repayment
	Payment of interest on loans
	Flow from investing activities
	Income from OS sales
	Purchasing an OS
	OS repair	IP Ivanov P. A.
	Net flow from activity
	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 we 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 it is planned to refinance in the amount of 500 thousand rubles: 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 accumulate in the 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 for 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:

Go to the report settings, click the checkbox “ Advanced setup».
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).

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

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

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).

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

Table 2. “Gross profit” report based on the presented settings
Buyer	By day	Sales price, rub.
Horizont LLC
LLC "Domovoy"
IP Borisov A. G.
IP Osintsev A. N.
IP Osipov A. Yu.
IP Pinyuga I. G.
IP Poluektov D. A.
IP Lovtsova N.V.
IP Khomenko A.V.

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).

Table 3. Calculation of the payment date from the date of sale and the granted deferred payment
Buyer	Date of sale	Sales price, rub.	Deferred payment, days	Payment date
Horizont LLC
LLC "Domovoy"
IP Borisov A. G.
IP Osintsev A. N.
IP Osipov A. Yu.
IP Pinyuga I. G.
IP Poluektov D. A.
IP Lovtsova N.V.
IP Khomenko A.V.

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

Select the table. 3 along with the header and on the “ tab Insert"click on the icon" Pivot table"(Fig. 6 (a)).
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.

We get the report presented in table. 4.

Table 4. Initial view of the pivot table
Payment date	Payments, rub.









Grand total

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).

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).

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

Table 5. Final view of the pivot table
Payment date	Payments, rub.
04.07.2016–10.07.2016
11.07.2016–17.07.2016
18.07.2016–24.07.2016
25.07.2016–31.07.2016
Grand total

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

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);
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.

Day of the week	Last year date	Last year's revenue	Current year date	Current year revenue	Collection	Total for the week



Sunday
Monday





Sunday
Monday





Sunday
Monday





Sunday
Monday





Sunday
Total				1 000 000

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.

Table 7. Weekly payment planning, rub.
Article	Counterparty	Payment deadline	Budget for the month
Balance at the beginning of the period
On a current account

Sales proceeds			1 105 000
Retail sales
Wholesale sales
Other income		Until the 10th
Operating expenses			1 117 700
Payment to suppliers
Payment to suppliers	Alpha LLC
Payment to suppliers	Omega LLC
Operating costs
Salary payment
Cover part	Employees	Salary - until the 10th, advance payment - until the 25th
	Employees	Until the 15th
Personnel costs
	Taxi LLC
	Express LLC
Medical examination	Clinic No. 1
Building maintenance costs
Rental of premises	Terem LLC	Until the 10th
Rental of premises	Teremok LLC	Until the 10th
Utility costs	Gorvodokanal	Until the 25th
Utility costs	HeatElectroStation	Until the 25th
	Private security company "Dobrynya"	Until the 20th
Taxes to the budget
VAT payable		Until the 25th
Income tax		Until the 28th
Property tax		Until the 30th
		Along with salary
Salary taxes		Until the 15th
Total flow from operating activities			–12 700	–15 000		–63 993	–225 860
Flow from financing activities			–25 000		–250 000	–25 000
Attracting loans		After the 25th
Loan repayment		Until the 22nd
Payment of interest on loans		Until the 22nd
Flow from investing activities			–10 000				–10 000
Income from OS sales
Purchasing an OS
OS repair	IP Ivanov P. A.
Net flow from activity			–47 700	–15 000	–55 879	–88 993
Balance at the end of the period

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.

Table 8. Budget form for the manager of cost items
	Article/Counterparty	Payment deadline (if any)	Debt as of the 1st day, rub.	Costs for the current month, rub.	Budget for payment, rub.	Debt as of the 31st, rub.
	Rental of premises
	Terem LLC	Until the 10th
	Teremok LLC	Until the 10th

	Utility costs
	Gorvodokanal	Until the 25th
	HeatElectroStation	Until the 25th

	Security
	Private security company "Dobrynya"	Until the 20th

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.

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.

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 for 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.

Program Finance in Excel. Consolidation of budgets(up to version 2.6 - Finance in Excel. Budgeting) is an Excel workbook containing program code to automate the process of consolidating typical budgets in large and medium-sized enterprises. Analysis and consolidation of budgets is carried out using Excel pivot tables. Source data for departmental budgets can be stored in various files.

Introduction

The task of consolidating and analyzing the budgets of enterprise departments at different levels, despite its apparent simplicity, in practice raises many problems associated with both organizational activities and the lack of special software tools. In the vast majority of cases, Excel is used for work - the initial budgets of departments are prepared and stored in spreadsheets, and in other files this data is collected into a common consolidated report. This uses formulas and links to external files that can be updated upon user request. The disadvantages of such a work scheme are well known:

A large number of summation formulas in the consolidated report. It is necessary to constantly monitor the links to the original budgets of the departments.
Lack of data integrity control. There is no guarantee that all data is included in the consolidated report.
Difficulty in analyzing data at intermediate levels of the hierarchy. The initially built structure of formulas for summing up departmental budgets cannot be reorganized by simple means.
Hard binding of links to cells of external files. With minimal changes to the file structure, incorrect data is included in the consolidation.
Large file sizes. To solve the previous problem, they usually try to collect as much data as possible into one file, linking pages using formulas.

Similar arguments about the problems of using Excel are very popular when discussing specialized budgeting programs, usually based on OLAP technologies (we will not give names so as not to advertise; those interested can find descriptions through search engines). However, these software products, despite active promotion, are not a panacea and, moreover, require large financial investments for implementation and support.

One of the program's objectives “Finance in Excel. Consolidation of budgets" is to eliminate the described shortcomings without using external software, while maintaining all the capabilities of the Excel computer.

The program does not contain formulas for consolidation - the process is provided by the pivot table interface. The hierarchical structure of subordination of enterprise divisions is configured in a special directory, and can be transformed at any time (no formulas will need to be changed). A set of cost items is determined by a single reference book with mandatory coding. Department data can be stored either in the same program file or in external Excel files. These files may contain any additional calculations and breakdowns of cost items that are not included in the consolidated report.

Changes in version 2.6

The name of the program has been changed. Old name Finance in Excel. Budgeting
Starting from version 2.6, the old XLS workbook format is no longer supported. The program file is saved in the format XLSX/XLSM and works in Excel starting from version 2007 ( 2007-2016 ). Program in format XLS Available only in version 2.0, and can only be purchased by special request.
The appearance of workbooks has been updated in accordance with the design of the latest versions of Microsoft Office.
Controls Checkbox in the Strukutra directory they have been replaced with standard fields. Changing the value is possible either by double-clicking on the cell or by entering a value 1 - yes, 0 - No.