Is there an alternative to Excel in budgeting and business intelligence?

In recent years, the topic of economic planning and analysis has become increasingly relevant. But at the same time, the inefficiency of the implementation of these functions in expensive and large-scale ERP-systems, in which their presence is initially assumed, is becoming increasingly obvious (this is even indicated by the letter P “Planning” in the abbreviation of such systems). Despite the huge budgets and titanic efforts on the implementation of ERP-systems, economic units of medium and large enterprises both worked and continue to work in spreadsheets, mainly MS Excel.

What is the reason for this situation? Is it really the inertia of economists? Or did Microsoft manage to create a truly unique product in terms of convenience and efficiency of its use in a real business environment? Well, and a timid question at the end, made in the title of the article - is there an alternative to Excel?

To answer these questions, you first need to figure out what is the specificity of economic planning and analysis, which Excel holds a monopoly on the maintenance. In short, the answer to this question lies in one word - TIME. This is the time that executives of companies at any level need to make a management decision, which, depending on the scale of the problem, takes from several minutes to several days.

The easiest way to demonstrate this thesis is by the example of the process of developing and approving an annual plan (budget) for the next financial year. This process is implemented in any modern company and is not designed to guess their future (a widespread misconception), but to control the top management over the allocation of limited resources (investments, staffing levels, credit limits, etc.) between line and functional units. companies within the budget for the next fiscal year.

Unlike regular procedures, such as taxation, salary payments, financial and statistical reporting, the budget development process is carried out once a year and is strictly limited to the time frame. The beginning of the budget process usually begins in October, when information is available (at least preliminary) about the actual results of work for the first 9 months of this year. A typical scheme of any budget table usually contains information about the expected results for the current year (according to the scheme of 9 months + expected 4 quarters), planned results for the next year and deviations (absolute and relative) to control the dynamics of indicators. During October-November, divisions of the company develop their own budgets, and then in December they are reviewed and approved by the management of the company or parent organization.

In the process of developing budgets, several options can be developed for different scenarios of the external environment, taking into account the internal goals and objectives of the company. However, in the end, the main (baseline) scenario is chosen, according to which the budgets of all departments are calculated. The end result of this process is a consolidated budget for the company as a whole (group of companies), in simplified form, which is the sum of the budgets of all the departments within the company.

In practice, when developing a budget, each unit is guided by the principle: “Ask for more, you will get just right”. The consequence of this principle is the deficit of the consolidated budget, when the planned income is not enough to cover the planned costs. Therefore, in any budget process at the final stage, the budget is always balanced, consisting in setting more intense income plans and cutting off certain types of expenditures to one or another subdivision and items. Naturally, with the subsequent recalculations of all plans and the formation of a consolidated budget. And there can be as many such recalculations with the subsequent consolidation, as needed to reduce revenues and expenses to zero.

But that is not all. The time-consuming, but technically solvable, task of multiple recalculation and data consolidation is superimposed by a much more complicated task connected with a change in the data model, according to which the recalculation and consolidation of the planned indicators takes place. For example, it may be decided to centralize the sales of the main types of products in the next year and to outsource individual production processes with the creation of new legal entities. And these decisions should not just be described in the form of text or a diagram, but changes should be made to all accounting documents related to the formation of cost and financial results. Moreover, such decisions may arise both at the stage of developing preliminary budgets and at the stage of balancing the consolidated budget.

From the point of view of software that implements support for the budgeting process, in the above process, the key resource becomes TIME, during which changes can be made to the company's business model and calculations are already made on this new model. Obviously, in these conditions, Excel is out of competition, as it allows for the shortest possible time from setting a task to change the business model to issuing recalculated values. ERP-systems can only promise to take into account the decisions taken in the preparation of financial statements for the 1st quarter of the next fiscal year.

What does Excel do to provide this kind of time management efficiency in supporting decision making? The answer to this question is also quite simple - when using Excel, each of its users simultaneously combines the task designer, business analyst, tester and end user, whose functions in ERP systems are distributed not only between different people, but also different departments. And most importantly, if Excel users, even performing different functions, speak one language that is understandable to each language, then ERP system users (in a broad sense) speak many languages ​​that require either talented “translators” (which are few), or strict formalization of the communication process, which is often delayed indefinitely.

Therefore, with all the limitations of Excel on the speed of data processing, as well as the inevitable presence of processes implemented partially in manual mode, the most complex of which is the budgeting process, Excel will always remain out of competition compared to ERP systems.

Having defined the key advantage of Excel related to the efficiency of its handling of a temporary resource, consider its weaknesses, which must be implemented in alternative software in order to make it worthy of competition.

Excel's obvious weakness when working with large and complex models is the file storage model, which:

1. Requires interaction in external relational databases for processing large amounts of data across multiple attributes;

   
   

2. It is fraught with subtle logical errors when changing a data model consisting of several related files or sheets.

   
   

These problems are quite easily solved by developing software modules in the embedded programming language, or by integrating with external software solutions. But in this case, Excel loses its strategic advantage - the presence of a user, combining at the same time the functions of task designer, business analyst, tester and end user. Instead, at least two appear - an economist and a programmer who speak their own languages, each with a superior. As a result, any simple task, usually solved in the head of one person, turns into a long bureaucratic procedure.

Thus, any alternative software system can compete with Excel only if it can expand the list of tasks solved by Excel with standard formulas without additional programming.

Business intelligence systems that have emerged recently, despite loud statements, besides a more flexible reporting system, which are based on the data model of Excel pivot tables, by and large, there is nothing (the Excel pivot table is simply a select query to one flat table with attribute breakdown and data aggregation across several fields horizontally and vertically). The central element of any budgeting process is the calculation of production costs and the formation of financial results, for which the data model of summary tables is practically not applicable.

The only alternative to Excel today is the open-source software platform for modeling complex economic systems JetCalc, the source code of which is available on GitHub . It also contains links to documentation, a working demo version and other additional resources. The system is distributed under the MIT license and is open to any proposals for participation in its further development for all interested parties.

Before turning to the features of the JetCalc architecture, it should be noted that JetCalc is a free version of the system implemented in the JavaScript ecosystem based on the architecture of a closed system implemented on Microsoft technologies, which since 2012 provides budgeting, economic analysis and consolidation of management and financial reporting , including for the preparation of consolidated financial statements in accordance with IFRS, in a large metallurgical holding with an annual turnover of more than $ 10 billion.

In JetCalc, as in Excel, all calculations are performed based on the formulas that the end user develops and tests. At the same time, the JetCalc calculation system has a number of unique properties that make it possible to easily modify the data models used and generate complex consolidated reports in real time.

A key feature of the JetCalc data model is the way to create cell formulas. If in Excel formulas are written for each cell, then formulas are written in JetCalc for a row or column, and at the cell level formulas are formed by the system dynamically in the context of an open document. This approach drastically reduces the time to change formulas and completely eliminates the appearance of arithmetic errors. Moreover, separate columns are combined into headings (caps) for certain types of documents, which allows changing formulas of columns simultaneously for several documents in one place.

Another feature of JetCalc is the presence of a specialized mechanism for summing cell values ​​along lines of a document, which is based on a tree of lines, in which summation is performed on child lines for each parent line. Therefore, instead of listing the cells in Excel, which should be included as arguments in the SUM formula (A1; A2; ...), it is enough to put a checkmark in the JetCalc against the required sum line on the web interface. At the same time, any line can be marked as not included in the sum, as well as as summable with the opposite sign (that is, deductible). When adding new lines, unlike Excel, in JetCalc you do not need to change any settings, since in the context of an open document, cell formulas will be rearranged automatically.

The third important feature of JetCalc is the collection of information in the context of accounting objects organized in the form of a tree, with a number of attributes that allow you to perform complex calculations on aggregation and filtering by writing simple and understandable formulas.

For example, for the Division “Metallurgical enterprises” (MET code), which includes Urals Metallurgical Plant JSC (code 201) and Ural Rolling Plant JSC (code 202), the formula for any primary cell in the context of the document will be converted to:

$@#201? + $@#202? 

The same expression can be represented as a formula with the consolidation function, which will automatically be expanded when one or several enterprises are added to the MET group:

 $@<<<(D:MET)? 

Also, the JetCalc system has a built-in mechanism for automatic transfer of values ​​in the data entry form, which allows to significantly reduce the load on the computational system by once saving the values ​​calculated by the formula as primary values ​​in the database to the database. Subsequently, such stored values ​​can be reused by the calculation system when generating various analytical calculations. The same formulas are used for setting up autoping values ​​as for setting dynamically calculated values.

The choice between the use of dynamic formulas and auto-pumped values ​​is completely determined by the user, who customizes the domain model, and consists in the choice between ease of administration and the speed of calculating document indicators:

1. it is enough to set up dynamic formulas once, but as the model becomes more complex and the amount of data increases, the speed of reporting will gradually slow down;

   
   

2. Autopump formulas allow you to replace the calculated values ​​with the primary ones, which dramatically increases the performance of the reporting system, but requires more discipline when modifying the document structure, since previously pumped values ​​may require re-pumping after making changes to the document settings.

   
   

More information about the settlement system JetCalc can be found at .

Another interesting mechanism for improving the productivity of economists in JetCalc is the control points mechanism, which is a special class of formulas that are also customizable by users, which, when the primary data is correctly entered, must produce a zero value. If there are non-zero values ​​at the control points, the document cannot be blocked from data entry, and therefore it cannot officially be considered timely submitted to the parent organization. Such an approach allows parallelizing the work of identifying logical errors on hundreds of employees of reporting organizations instead of individual employees of a higher organization.

And of course, JetCalc implements such standard features as printing documents or saving reports to PDF files, displaying these individual documents in the form of graphs, creating subject documentation for each document, and much more.

From promising things that have proven their feasibility in practice, we can single out the possibility of distributing once created models to an unlimited number of subscribers via GitHub. This feature is based on the storage of created domain models in the MongoDB database, and values ​​in PostgreSQL. Therefore, the domain model is a JSON file that can be easily downloaded to the MongoDB database from any source.

In conclusion, I would like to say that at present the project is developing in the framework of the personal initiative of its participants and is ready for use in real “combat” conditions by about 90%. But these remaining 10% require a thorough adjustment of the system to the commercial level in all areas - from testing deployment scripts, refining the functionality of the calculation system, improving the ergonomics of the web interface to writing documentation, creating demo models, developing formats for storing models and communication protocols with external systems and more.

Therefore, all those interested in the development of the project are invited to participate in the development team consisting of two people today, in which it will be possible to find like-minded people, get unique knowledge of the product, which has no analogues in the market, and realize their most fantastic ideas.