Techniques for building corporate budgeting systems

What is a budgeting system, why is OLAP so well suited for them, why does a large business spend tens and even hundreds of millions of rubles on their services?

For some reason, there is no article in Runet on the topic of designing budgeting systems written in a popular language. This material is an attempt to fill this gap and in simple words to talk about the functional and technical side of such systems. In order to keep the volume of material within reasonable limits and not rewrite textbooks, I had to omit or simplify some details.

If any statements seem disputable or insufficient, I will be glad to criticism and to communicate in the comments.

Content

1. What is budgeting?
2. Why use OLAP for budgeting?
3. General principles of building budgeting systems
4. Build budget planning functions
5. Construction of performance evaluation functions
6. Conclusion

')

What is budgeting?

Conceptually, budgeting is a process of planning transactions with assets of various kinds. In its simplest form, this means planning cash receipts and payments.

Typically, budgeting occurs in an enterprise at the moment when several employees appear with the right to enter into contracts and make financial decisions at their discretion. Such delegation allows you to enter into much more transactions at the same time and removes the brake from the amount of income, but is paid for by the loss of intuitive control of efficiency and profitability. As a result, three new problems arise:

First, several people who independently earn and spend money from a common boiler need an instrument for coordinating actions: one earns, another pays salary, a third buys materials, and the fourth attracts loans — coordination is needed.

Secondly, the balance of the current account can no longer serve as an instrument of control over affairs - with a large number of unrelated, parallel transactions, it is not informative.
For example, the amount on the current account will grow, even if the company operates at a loss, as long as sales growth covers current expenses.
On the contrary, an untimely large purchase can destroy a highly profitable business if, because of this, the company fails to fulfill other obligations and a bankruptcy lawsuit is filed.

Thirdly, the ability to pay money to the company and make commitments to it, is an irresistible temptation for employees. In the absence of control, abuse is inevitable.

Thus, the main task of the budgeting process is to solve these three problems.

The solution method is as follows:

The first step is to build a mathematical model that will allow you to calculate, balance and coordinate future incomes and expenses, giving each authorized person how much, for what and when he can spend, and how much he needs to earn to ensure these costs.

The second step is to build the process of negotiating contracts and approving invoices for compliance with the established rules.

The third step is to take into account actual financial transactions and adjust plans and limits so that income and expenses continue to match each other.

Why use OLAP for budgeting?

Budgeting systems and BI are usually built using OLAP technology - On-Line Analytical Processing. In fact, OLAP is a close relative of tabular processors: Google.Sheets and MS Excel. In OLAP-cubes, you can also enter data and formulas onto cells, establish relationships between them, quickly count amounts (aggregates), write scripts that will manipulate many cells and ranges, and so on. The main difference is that a table processor cell has three coordinates — a sheet, a row, and a column, while an OLAP cube cell can have several dozen coordinates.

For example: In Oracle Hyperion, six required dimensions, two multi-currency, and twelve user dimensions. Most budget models include from 9 to 14 measurements, but in some cases there may be 20. Such a number of measurements is necessary to always store interconnected numbers in neighboring cells, regardless of the complexity of their structure, thereby reducing most operations with them to arithmetic, and reporting speed reduced to seconds.

Also, BI-systems provide many important services: the ability to write SQL-like queries, make and fill beautiful reports with the mouse, centrally store all data, manage viewing and editing rights, program integration with other databases, etc.

The advantages of OLAP are even more evident in the example of solving a typical corporate governance problem:

Problem: According to the results of the next quarter, the reports showed that the cost price is growing faster than revenues. Objective: Identify specific operations that have most affected the problem, managers responsible for this, and jointly plan measures to normalize the situation.

Solution: Open from OLAP-cube to the report on cost and income. Then, in turn, use the mouse to open the sections: by products, by time periods, sales channels, regions, divisions, categories of clients, types of expenses, etc., to the level of specific accounting entries. To localize the exact deviations in the values ​​and volumes of operations, arrange them by volume. Obtain specific facts and measurable indicators for further work with responsible managers, in order of their contribution to the overall deviation.

Imagine now how much it is necessary to compile and view spreadsheets in order to do the same thing in a company with a couple dozen divisions or more?

General principles of building budgeting systems

For any system, it is true that the absence of unambiguously stated goals leads to the creation of a multifunctional, but completely useless product. Goals are a criterion for prioritizing between requirements. Lack of priorities will lead to the fact that the team will spend most of the resources on the implementation of minor and contradictory functions.

In the case of budgeting, the final goals are:

1. Ensure the expediency and coordination of revenues, expenses, receipts and payments of the company in conditions when financial operations are carried out in parallel by many employees.
2. Organize the control of payments and cash receipts so that at any time you know the limits on the types of costs that a company can afford, taking into account actual income and expenses that happened in the past, and also taking into account the need to keep an adequate supply of funds to ensure future costs and risks.

Attention should be paid to the word “Expediency”. To some readers this phrase may be unclear, others will think that it is a question that all income and expenses should be subordinated to the purpose of making a profit, and they will also be wrong.

Profit is a popular goal of managers, but it cannot be called the most reasonable, therefore most professional managers want not just profit, but an increase in the Company's financial flow. The financial flow consists of the rate of growth of turnover (income), the amount of profit and value of assets. It is necessary to design the system so that it allows you to search for the right balance between profit margins, business turnover growth rates and assets in such a way as to get the maximum benefit today and in the future.

Technically, the budgeting system is simple - it is an array of amounts of financial transactions, in the context of several analytics, one of which is always the calendar period. On the other hand, it is quite complex - a couple of dozen hierarchical directories, hundreds or thousands of forms and reports, and dozens of scripts.

If you are an experienced financial manager and know exactly what you want, then you can easily build yourself the same. However, if this is your first implementation, then the likelihood of difficulties is high, especially if you are a financier and are familiar with management accounting methodologies.

A full-fledged, methodologically correct budget model that takes into account the structure of the business is objectively complex. But very few people from the first attempt can well formulate so many tasks and requirements and set priorities. As a result, most likely, you will receive a system that implements all the requirements of the methodology, but little useful for solving basic problems.

To avoid this, it is necessary to complicate the system gradually, realizing the functions in a causal relationship between the problems that they solve.

In my opinion, the priorities between the functions should be as follows:

1. Provide elementary coordination - make a plan for the types of receipts and payments, by periods.
2. Establish personal responsibility and limits - delegate planning by department, maintain a consolidated plan, and organize the coordination of contracts and accounts.
3. Introduce fact accounting and basic performance evaluation procedures.
4. Provide the possibility of objective control - to move from planning amounts to price and physical indicators.
5. Implement costing and pricing - in turn, introduce procedures and reports, according to the management accounting methods you require.
6. Introduce planning in the context of legal entities and implement master budgets, starting with BDDS and BDR.
7. Complicate: Introduce additional cuts and advanced methods of accounting and business intelligence.

Build budget planning functions

The first task that the company decides when planning a budget is to coordinate and coordinate financial transactions between all authorized employees. In order to do this, at a minimum, it is necessary to have a table with the amounts of receipts and expenditures of funds in the context of types (elements) and calendar periods.

In this way we get the first and most important reference books: “Types of revenues and costs” and “Periods”. In different organizations, the volume of activities, the habits of financiers and terminology are different, so that “Types of income and costs” are often replaced with “Accounts” or “Articles”.


Picture 1 - Examples of simplest budgets

The next step is the establishment of personal limits, and therefore the third most important reference is the “Centers of Financial Responsibility” (CFD).

Having three dimensions with reference books "Articles", "Periods", "CFD", you can already delegate the right to conduct financial transactions to employees and coordinate their actions. But at the same time you can control the validity of the amounts entered only informally, communicating with each, and this is a very time-consuming and lengthy procedure that must be repeated regularly. The second unpleasant consequence is that you cannot quickly make financially sound decisions, for example: at what price to purchase materials, whether to pay the new employee the desired salary, and so on.

To change this, you need to budget not amounts, but indicators: the number of materials purchased, average purchase prices, hourly rates of employees, labor costs, actual production and sales, and so on. In this case, you can recheck data from objective sources, without communicating with employees. In addition, you can conveniently delegate this task to assistants. Planning prices and natural volumes also allows you to quickly determine the effect of deviations of one indicator on another and on the amount, and this will greatly strengthen your arguments in negotiations with partners.

Thus, we come to four reference books: “Articles”, “Period”, “CFD” and “Indicators”, while the number of elements in the “Indicators” reference book depends on the number of used units of measure, coefficients and formulas. In the simplest case: “Price”, “Quantity” and “Sum”.


Picture 2 - Budgeting from natural indicators in the context of the Central Federal District

Efficiency control has now become much better, but you still can not calculate the cost of production and determine the minimum and optimal prices. The easiest way to determine the cost is simply to divide all your expenses by the quantity of products manufactured. However, this will only work if you produce one product, otherwise questions arise to the fair distribution. Therefore, financiers use more complex approaches, such as Marginal Costing, Absorption Costing, Activity Based Costing, and so on.

The simplest of them is Marginal Costing. With this approach, all types of costs are divided into two groups: direct and indirect. Next, for each product, standards for cost elements are calculated, and the calculations are programmed so that when entering the production volume, the amount of direct costs is automatically calculated. Obviously, in this case, the minimum possible price for selling the product will be its marginal cost.

Using the Marginal Costing method, we come to five directories: “Articles”, “Period”, “Central Federal District” and “Indicators”, “Products”, and in the “Indicators” directory another “cost standards” are added.


Picture 3 - Budget after Marginal Costing implementation

Then the following problem arises: in most organizations, it will turn out that there will be more indirect costs than direct ones. If you are releasing more than one type of product and are not ready to intuitively share indirect costs, you will need to apply Absorption Costing and understand the fair cost of production, taking into account all costs. In this approach, it is necessary to divide all costs into two categories: those related to the production of a specific type of product and necessary for the work of the Central Federal District as a whole.

The first category of costs is allocated to specific products, and by dividing by the volume of production costs per unit are obtained, but such simple costs are rare. The second category is more difficult to distribute: not every CFD manufactures products, in addition, they constantly provide services to each other, so that their expenses are mixed.

To solve this problem, you first need to distribute all costs for specific Central Federal Districts, and then divide all Central Federal Districts into Food and Service, on the basis of whether they directly produce products. The costs of all Service Central Federal Districts should be distributed on Food Central Federal Districts, in one or several iterations. The total costs of the Food Distribution Centers are distributed to the types of products they produce.

At the same time, indicators of the “Distribution Base” type appear in the system, allowing to fairly distribute the costs from the Service Central Federal Districts to the Product Central Federal Districts and further by types of products.
Examples of such bases are: “Number of hours for repair of equipment”, “Area of ​​the removed workshops”, “Labor hours for the production of the type of product” and so on.

As a result, we can divide the distributed costs of production and get the cost, and we can also study the mathematical dependence of the cost of production.

Thus, in our model, the “Indicators” reference book becomes even more complicated and the first serious calculation scripts appear.


Picture 4 - Budget after Absorption Costing implementation

Now we can determine prices, discounts and other parameters that are important for doing business. We can continue to complicate the model by introducing additional methods of management accounting, if the situation requires it.

Thus, we will approach professional financial models that will allow us to control not only costs and revenues, but income and expenses, current and non-current assets, capital and debt, and many more important figures.

The complexity of the model and the number of directories increase even more if the company uses several legal entities, operates in several regions, uses several sales channels, factories, types of workflow, etc.


Picture 5 - Professional budgeting model

However, all this will be absolutely useless if we do not compare our plan with the actual results of the work, so we need to go to the next section.

Construction of performance evaluation functions

Efficiency is a relative indicator, which is obtained by comparing two other indicators.

The very first method that is usually implemented is Plan-Fact comparison. For this purpose, the “Scenarios” handbook is entered into the budget model, with the elements “Plan” and “Fact”. Now we can enter actual data into the system and calculate the amounts of deviations, then redo the remainder of the plan. However, if we change the numbers on the “Plan” element, we will delete what was originally entered and lose valuable information. In order to avoid this, in the “Scenarios” reference book one more element is introduced: “Fact-Prognosis”, into which the data of the element “Fact” from the past are uploaded, and the data of the element “Plan” for the remainder of the period.

After that, we can correct the figures on the “Fact-Forecast” element and use them as a new plan, while retaining the ability to compare with the original figures and evaluate the planning accuracy. Usually, companies review their plans once a quarter, or once a month, for this they create three or eleven fact-type elements in the Scenarios directory.

The next task that is usually implemented is a comparison of the results of activities with the same period last year. This allows us to understand how much better or worse we began to work. On the one hand, we can add a new year to the “Period” dimension each time with quarters, months, days, and weeks, but it’s much more convenient to build tables based on directories located in different dimensions of an OLAP cube. So the best solution is to create a separate dimension in the OLAP-cube and place the reference book “Fiscal Year” there. Thus, we can easily make a report, which will contain periods in columns, and financial years in rows, and the difference between the results of similar periods will be very clear.

Additionally, we can introduce the “Version” dimension in order to store different budget options: working, agreed and approved versions, and so on.

Thus, we have added to the system the three most important dimensions necessary for evaluating the effectiveness: “Scenario”, “Fiscal Year”, “Version”.


Picture 6 - Full budgeting system

By making these changes, we can implement more advanced functions and approaches, improving our management and depth of understanding of the situation in the company through the use of a wide range of methods developed by accountants for 400 years of development of their science.

Conclusion

A sound budgeting system significantly improves management, allows you to make informed decisions and provides baseline data for advanced business methods, for example: standard costing, variance analysis, scenario analysis, sensitivity analysis, factor analysis, risk management, demand forecasting, up to linear optimization, data mining and other tools provided by higher mathematics and statistics.

In order to get these benefits, it is necessary to formulate goals and prioritize requirements, if necessary, sacrificing the complexity of the methodology, in favor of implementing basic functions and maintaining a reasonable balance between mathematics, time-consuming input of information and visibility of reports.

I hope this article will help in this.