Is there an alternative to Excel in the field of budgeting and business intelligence
In recent years, the topic of economic planning and analysis is becoming more relevant. But at the same time, it becomes more and more obvious that these functions are not efficiently implemented in expensive and large-scale ERP-systems, in which their presence is initially assumed (this is even said by the letter "Planning" in the abbreviation of such systems). Despite the huge budgets and titanic efforts to implement ERP-systems, economic units of medium and large enterprises both worked and continue to work in spreadsheets, mainly MS Excel.
GitHub . There are also links to the documentation, working demo version and other additional resources. The system is distributed under the license of MIT and is open to any proposals for participation in its further development for all interested persons.
Before proceeding to the peculiarities of the JetCalc architecture, it should be said that JetCalc is a free version of the system implemented in the jаvascript ecosystem based on the closed system architecture implemented on Microsoft technologies, which since 2012 provides the processes of 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 the JetCalc system, as in Excel, all calculations are performed based on the formulas that the end user develops and tests. In this case, the JetCalc computation system has a number of unique properties that make it easy to modify the data models used and to 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 prescribed for each cell, then in JetCalc, the formulas are written for a row or column, and at the cell level, the formulas are generated dynamically by the system in the context of an open document. This approach drastically reduces the time for changing the formulas and completely eliminates the appearance of arithmetic errors. Moreover, individual columns are combined into headings (caps) for certain kinds of documents, which allows you to change the column formulas simultaneously for several documents in one place.
Another feature of JetCalc is the availability of a specialized mechanism for summing the values of cells along the lines of the document, which is based on the tree of rows, in which the summation is performed on the child rows for each parent row. Therefore, instead of enumerating the cells in Excel, which must enter as arguments to the SUM formula (A1; A2; ), in JetCalc it is enough to tick off the required sum line on the web interface. Thus any line can be marked, as not entering into the sum, and also as summable with opposite sign (that is subtracted). When you add new rows, unlike Excel, in JetCalc you do not need to change any settings, because in the context of an open document the cell formulas will be automatically re-formed.
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 performing complex calculations on aggregation and filtering by writing simple and understandable formulas.
For example, for a division of Metallurgical Enterprises (MET code), which includes Ural Metallurgical Plant JSC (code 201) and Ural Rolling Plant JSC (code 202), to calculate the total by divisional formula of any primary cell in the context of the document will be converted to the form:
$ string @ column # 201? + $ string @ column # 202?
The same expression can be represented as a formula with the consolidation function, which will be automatically extended when one or more enterprises are added to the MET group:
$ string @ column (D: MET)?
Also in the core of the JetCalc system is built-in mechanism for the auto-pumping of values in the form of data entry, which allows to significantly reduce the load on the calculation system by storing values calculated in the database in the database as primary values in the database once. In the future, such stored values can be reused by the accounting system when forming various analytical calculations. To configure auto-inflated values, the same formulas are used as for configuring dynamically calculated values.
The choice between using dynamic formulas and auto-pumped values is completely determined by the user configuring the domain model, and consists in choosing between the ease of administration and the speed of calculating the document's indicators:
Dynamic formulas need only be adjusted once, but as the model becomes more complex and the amount of data increases, the speed of report generation will gradually slow down;
auto-pumping formulas allow you to replace the calculated values with primary ones, which dramatically increases the performance of the reporting system, but requires greater discipline in the modification of the document structure, since previously pumped values can be re-pumped after making changes to the document settings.
More details about the JetCalc calculation system can be found in at .
Another interesting mechanism for increasing the productivity of economists in JetCalc is the checkpoint mechanism, which is a special class of formulas that are also customizable by users, which, if the primary data is correctly entered, should yield a value of zero. If there are nonzero values in the checkpoints, the document can not be blocked from entering data, which means it can not be officially considered timely submitted to a higher-level organization. Such approach allows to parallelize work on revealing of logical errors on hundreds of employees of reporting organizations instead of single employees of the higher organization.
And of course, the JetCalc system includes standard features such as printing documents or saving reports to PDF files, displaying the data of individual documents in the form of graphs, creating subject documentation for each document, and much more.
Of the promising things that have proved their realizability in practice, one 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 the values in PostgreSQL. Therefore, the domain model is a file in the JSON format, which is easy to load into the MongoDB database from any source.
In conclusion, I would like to say that at present the project is developing within the framework of the personal initiative of its participants and is ready for application in real "combat" conditions by approximately 90%. But these remaining 10% require thorough debugging of the system to the commercial level in all directions - from testing deployment scripts, improving the functionality of the calculation system, improving the ergonomics of the web interface to writing documentation, creating demos, developing formats for saving models and protocols for exchanging data with external systems and much more.
Therefore, all those who are interested in the development of the project are invitedI'm going to participate in the development team, today consisting of two people, working in which it will be possible to find like-minded people, to get unique knowledge of a product that has no analogues in the market, and to realize their most fantastic ideas.
It may be interesting