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

image

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 to implement ERP-systems, the 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, it is first necessary to figure out what is the specifics 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 of 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 common misconception), but to control the top management over the allocation of limited resources (investments, staffing levels, lending limits, etc.) between line and functional units. companies within the budget for the next fiscal year.


Unlike regular procedures, such as taxes, salary payments, financial and statistical reporting, the budget development process is carried out once a year and is strictly limited to time frames. 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 9 months fact scheme + expected 4 quarters), planned results for the next year and deviations (absolute and relative) to control the dynamics of indicators. During October-November, the company's divisions develop their own budgets,


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 a 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 revenues are not enough to cover the planned costs. Therefore, in any budget process, at the final stage, budget balancing is always performed, consisting in setting more intense revenue plans and cutting off certain types of expenses to one or another subdivision and articles. 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 associated 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 ensure this efficiency in time management 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 identified 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 several 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 who combines the functions of a task designer, a business analyst, a tester, and an 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, in addition to a more flexible reporting system based on the data model of Excel pivot tables, by and large, there is nothing (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 Microsoft closed system architecture, which since 2012 has provided 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. Such an 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 check mark in the JetCalc against the required sum line on the web interface. At the same time, any string can be marked as not included in the sum, and also 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 re-formed 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 Metallurgical Enterprises Division (MET code), which includes Ural 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 saving the values ​​calculated by the formula once as primary values ​​in the database. In the future, such saved values ​​can be reused by the calculation system when generating various analytical calculations. The same formulas are used to set 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 sets up 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 entered correctly, must produce a zero value. If there are non-zero values ​​at the control points, the document cannot be blocked from entering data, and therefore it cannot officially be considered to be timely submitted to the parent organization. This approach allows paralleling the work of identifying logical errors on hundreds of employees of reporting organizations instead of individual employees of the parent organization.


And of course, JetCalc implements standard features such as printing documents or saving reports to PDF files, displaying these individual documents as 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 storing the created domain models in the MongoDB database, and values ​​in PostgreSQL. Therefore, the domain model is a JSON file that can be easily downloaded into 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 careful fine-tuning 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, working in which you can find like-minded people, get unique knowledge of the product that has no analogues on the market, and realize your most fantastic ideas.


Also popular now: