An example of compiling an enterprise budget in Excel. Costs for the current month, rub

Budgeting as a management technology is characterized by the fact that almost all departments of the enterprise are involved in it (and very actively). This imposes special requirements on the budgeting automation platform: it must have such qualities as simplicity, versatility, flexibility, protection from user errors, etc. At the same time, it must be integrated with the management accounting system and the treasury system so that the control and analytical functions of budgets are also implemented at the required level. Fortunately, using Excel allows you to organize budgeting in exactly this way, without unnecessary labor and financial costs. We will tell you how to achieve this result in our webinar.

Using Excel as a budgeting automation platform seems even more justified than automating management accounting and reporting. Excel is, in fact, an ideal budgeting platform due to the fact that it is very easy to customize to the specifics of almost any enterprise, regardless of its industry, size and organizational structure. At the webinar, the speaker will discuss the main approaches to budgeting automation that can be used in almost any company. Here are the main topics planned to be discussed at the webinar:

How to organize your budgeting automation process using Excel
How to form budgets for the Central Federal District and organize exchanges with the leaders of the Central Federal District
How to consolidate the budgets of the Central Federal District into the company budget
How to include the possibility of scenario planning in budgets
How to organize storage of different versions of the budget
How to form BDDS, BDR and planned balance
How to link budgets with management accounting
How to organize a plan-fact analysis of budget execution

You can register to participate in the webinar on the webinar page on the website of the Financial Director magazine.

Webinar materials:

Answers to questions from webinar participants

Question: Is it possible to organize everything using pivot tables?

Answer: Theoretically, it is possible, but pivot tables have a rigid structure, so a model built on their basis will have limited functionality. Using the SUMIFS function gives more flexibility and functionality to the budget and accounting model

Question: How to build a sales budget for small manufacturing enterprises when the enterprise works on orders, and it is difficult to predict them for the year? Order information is available only within 1-3 months. Any tips?

Answer: I would rely on statistics from last year, adjusted for the actual sales growth achieved in the first 2-3 months of this year compared to the same period last year. You should not strive to build a budget only on the basis of 100% information about future sales; the budget should contain an element of forecasting, based on extrapolation of observed trends.

Question: How can you implement a budgeting system in retail trade?

Answer: From a budgeting point of view, retail trade is no fundamentally different from other industries; its main nuance is the absence of clearly identified buyers; retail works with an impersonal mass of individuals. Therefore, sales should be budgeted not by clients, but by product groups and points of sale. Otherwise, everything is standard.

Question: How is data entered into the consolidated register? Where is the connection?

Answer: Data is loaded into the consolidated register of planned transactions using the UpdateRegistry macro. Only the registers of the Central Federal District are filled out by hand.

Question: Can you explain the issues of transfer pricing between divisions in the enterprise? To whom will the services of specialists from the planning and economic department be billed, and from whom will the income be received?

Answer: All central federal districts under such a scheme must finance the costs of the administrative and managerial apparatus, which includes the planning and economic department, through the purchase of its services.

Question: Who edits the budget: can the financial department or a department employee make changes independently?

Answer: The budget of the Central Federal District is edited by the Central Federal District itself. In the process of approving the budget of the Central Federal District, it can be adjusted both by a designated employee of the Central Federal District and by employees of the financial department, it depends on how you write this procedure in the budgeting regulations.

Question: And how can I see who made the change?

Answer: This cannot be done using standard Excel tools, although you can use macros to organize user authorization. However, I don’t see much sense in this, since with the correct organization of the budgeting process it is always clear who formed and adjusted the budget, since at each stage of this process a specific responsible person must be allocated.

Question: How can you organize budget adjustments throughout the year? At the same time, in those months when there is no adjustment, the original data was displayed.

Answer: Through the versioning tool, which was discussed in detail in the webinar. This is exactly why this tool was implemented.

Question: Is the fact only manually entered and then combined with a file with planned data?

Answer: The fact is not entered manually, but is loaded from 1C using a loader, the operation of which was discussed in detail at the webinar on automation of management accounting and reporting in Excel. Further, as you rightly noted, these data are compared with the plan.

Question: Is it possible to take into account budget versioning in plan-factual analysis?

Answer: Of course, to do this, it is enough to slightly adjust the SUMIFS formula on the “Plan-Act” sheet so that it extracts planned data taking into account versions.

Question: How to track changes day by day within the plan-fact framework? Is everything done by hand?

Answer: No, you can make a “Plan-Act” sheet broken down not by month, as was shown in the example, but by week or even by day. This is achieved by minimal adjustments to the SUMIFS function.

Question: How is the BDDS and BDR of the Central Federal District linked/filled out?

Answer: In the register of planned operations of the Central Federal District, two fields must be provided, “Accrual date” and “Payment date”. This will make it possible to record transactions for both the BDDS and the BDR within one register.

Question: How much data can Excel handle without freezing?

Answer: Excel easily processes tens of thousands of transactions in registries, so in real work it is very difficult to encounter any restrictions in terms of the amount of data processed (especially in low-cost models).

Question: How are dependent items of various central financial districts filled out? Let's say transportation costs depend on revenue. Income and transportation costs are managed by different central federal districts.

Answer: To do this, you need to build a financial model of the company and describe in it all the relationships between such items. We will talk about building financial models in Excel in the third webinar of the series. Come, it will be interesting.

Question: The approach is good, but inconvenient for true planning. Those. we must ALREADY KNOW which numbers to enter into the register. And often you need to build a trend, take expenses as a percentage of something, etc. While? I often used building models with closed sheets, but open cells for input. What do you say?

Answer: You are confusing modeling and budgeting. A budget is a normative document, but this does not mean that you cannot use data calculated in financial models to fill it out. We will talk about financial modeling at the third webinar at the end of April.

Question: How to import actual transactions from 1C into Excel for variance analysis?

Answer: With the help of a loader, the work of which was demonstrated at the first webinar of the series, dedicated to management accounting and reporting. I encourage you to rewatch this webinar.

Question: Is the fact downloaded from the ERP system or entered manually?

Answer: Both options have a right to exist, but, of course, it is more effective to upload the fact using an automated procedure. An example of a loader was demonstrated in the first webinar. - When adding a new financial center, agreement, account or other analytics, does the macro need to be rewritten? No, the macro doesn't delve into analytics, it just copies rows. Just make sure that the number of registry entries being copied is correct.

Question: How to get green and red triangles, please explain again.

Answer: You need to select the range of cells in which you want to use these icons, then call the menu command Home / Conditional Formatting / Manage Rules, in the dialog box that opens, click the “Create Rule” button, then select the “Icon Sets” format style, select specific icons in the “Icon Style” field (in addition to triangles, you can use circles, arrows, diamonds, crosses, zeros, etc.) and set specific numeric ranges corresponding to each icon.

Question: Should the costs of paying employees of the Central Federal District appear in the budget of this Central Federal District?

Answer: It depends on how you decide to proceed. In my opinion, all labor costs for all departments should be included in the budget of the financial service or personnel service.

Question: What is the role of the “Status” column?

Answer: This column is necessary so that in the register of actual transactions it is possible to record transactions that are only planned to be carried out. The essence of the column is that only transactions with the status “Fact” are included in management reporting, the rest are used for planning and approval of future payments (including monitoring their compliance with the budget).

Question: What about multi-user work when using such a model?

Answer: This model copes well with multi-user work; this is precisely why many files were created, each of which corresponds to a separate CFO. If all budgets were made by one person, then there would be no need to create a separate file for each central financial district.

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.

Go to the website and sign in to your account (or take advantage of the free 30-day trial).
Go to the "Home" tab, click "Create" and select the "View Templates" option.
Enter the word "Budget" in the "Search Templates" field and click on the magnifying glass icon.
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.
Name your template, choose where to save it, and click OK.

2. Enter details for your budget

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.

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

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 categories based on the data specified in the subsections.
On the left side of each row, you can attach files directly to budget items (ideal for attaching bank statements, tax documents, etc.).
Add important details in the Comments column, such as account information or links to specific accounts.

3. Update your monthly budget actuals

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.

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.

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 on the 1st day	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, proceeds from sales on Monday are 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 the 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.

The budget for the next year is formed taking into account the functioning of the enterprise: sales, purchasing, production, storage, accounting, etc. Budget planning is a long and complex process, because it covers most of the operating environment of organizations.

For a clear example, consider a distribution company and draw up a simple enterprise budget for it with an example in Excel (an example budget can be downloaded from the link below the article). In your budget, you can plan expenses for bonus discounts for customers. It allows you to model various loyalty programs and at the same time control costs.

Data for budgeting income and expenses

Our company serves about 80 clients. The range of goods is about 120 items in the price list. She makes a markup on goods of 15% of their cost and thus sets the selling price. Such a low markup is economically justified by intense competition and is justified by high turnover (like many other distribution enterprises).

A bonus reward system is offered to clients. Discount percentage on purchases for large customers and resellers.

The conditions and interest rate of the bonus system are determined by two parameters:

Quantitative limit. The quantity of a specific product purchased that gives the customer the opportunity to receive a certain discount.
Percentage discount. The size of the discount is a percentage that is calculated from the amount the client purchased when overcoming the quantitative limit (bar). The size of the discount depends on the size of the quantitative limit. The more goods purchased, the greater the discount.

In the annual budget, bonuses belong to the “sales planning” section, so they affect an important indicator of the company - margin (profit indicator as a percentage of total income). Therefore, an important task is the ability to set several bonus options with different boundaries at sales levels and the corresponding % bonuses. It is necessary that the margin is kept within certain limits (for example, no less than 7% or 8%, because this is the company’s profit). And customers will be able to choose several options for bonus discounts.

Our budget model with bonuses will be quite simple, but effective. But first, let’s draw up a report on the movement of funds for a specific client to determine whether it is possible to give him discounts. Pay attention to formulas that reference another sheet before calculating the percentage discount in Excel.

Drawing up enterprise budgets in Excel taking into account loyalty

The budget project in Excel consists of two sheets:

Sales – contains the history of the movement of funds over the past year for a specific client.
Results – contains the conditions for calculating bonuses and a simple account of the distributor’s performance, which determines the forecast of the client’s attractiveness indicators for the company.

Cash flow by clients

The structure of the table “Sales for 2015 by client:” on the “sales” sheet:

Enterprise budget model

On the second sheet we set the boundaries for achieving bonuses and the corresponding discount percentages.

The following table is a basic form of an income and expense budget in Excel showing the firm's overall financial performance for an annual period.

Structure of the table “Conditions of the bonus system” on the “results” sheet:

Bonus bar border 1. Place to set the level of the border bar by quantity.
Bonus % 1. Place to set a discount when crossing the first border. How is the discount for the first border calculated? Clearly visible on the “sales” sheet. Using the function =IF(Quantity > limit of 1 bonus bar[quantity]; Sales volume * percentage of 1 bonus discount; 0).
Bonus bar limit 2. A higher limit compared to the previous limit, which makes it possible to get a larger discount.
Bonus % 2 – discount for the second border. Calculated using the function =IF(Quantity > bonus bar limit 2[quantity]; Sales volume * percentage of bonus discount 2; 0).

Structure of the table “General report on the company’s turnover” on the “results” sheet:

Ready-made enterprise budget template in Excel

And so we have a ready-made enterprise budget model in Excel, which is dynamic. If the bonus limit is at the level of 200, and the bonus discount is 3%. This means that last year the client purchased 200 items. And at the end of the year he will receive a bonus discount of 3% of the cost. And if a client purchased 400 pieces of a certain product, it means that he has crossed the second limit of bonuses and already receives a 6% discount.

Under such conditions, the “Margin 2” indicator will change, that is, the distributor’s net profit!

The task of the head of a distribution company is to select the most optimal levels of boundary strips to provide discounts to customers. You need to choose so that the “Margin 2” indicator is at least within the range of 7% -8%.

Download the enterprise budget-bonus (sample in Excel).

In order not to search for the best solution at random, and to avoid making mistakes, we recommend reading the following article. It describes how to make a simple and effective tool in Excel: Data table in Excel and matrix of numbers. Using the “data table” you can automatically visualize the most optimal conditions for the client and distributor.

The Ural Lotto company has developed a model in Excel, with the help of which the financial service can quickly draw up an Income and Expense Budget (IBC) and a Cash Flow Budget (CFB). An example in Excel can be downloaded from the link at the end of the article.

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

Let's imagine that the financial director, together with the economic service, has to develop a budgeting system from scratch. At first, it is difficult to understand how the budget process will be organized, what types of budgets and reports will be required, and in what form it will be necessary to obtain information on actual income and expenses from the accounting program. This is exactly what they thought at Ural Lotto LLC. In the first months, we limited ourselves to the formation of separate budgets - sales, purchases, divisions. After some time, we managed to develop (sheets “detailed PL” and “detailed CF”, respectively) created using the Excel “Pivot Tables” tool. Using built-in filters, you can select items of interest, subitems of the first and second levels, and group separately income (receipts) and expenses (payments). To reflect the changes made to the worksheet, you will need to right-click and select “Update” from the menu that appears.

How to analyze BDR for a certain period and show deviations

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

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

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

Useful documents

BDR and BDDS example in Excel

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

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

Figure 1. Auxiliary table

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

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

Download an example of BDR and BDDS in Excel

Benefits of Budgeting in Excel

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

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

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

Column name	Purpose
For a report, for a reference book	Auxiliary columns serve to link budget model tables
	Indicate the year for which the budget is drawn up
	A center of financial responsibility is assigned, that is, a department that is directly responsible for the corresponding item of expenses or income