Consolidation using a spreadsheet versus consolidation software
You currently do your consolidations on a spreadsheet. When will it become necessary to invest in consolidation software? We’ll provide you with some information for consideration so that you can evaluate your current situation and make a fully informed decision.
Our first recommendation is related to your strategic vision. You don’t know today what might happen to your group tomorrow: a doubling of the scope via the acquisition of a sub-group, a public offering with new shareholders who will demand a fast close, a change in consolidation rules or much more precise information at the consolidation level… This can happen at any time, and given this, it’s strategically prudent to preventively start using suitable management tools to deal with new, unexpected and demanding situations.
From a management standpoint, a consolidation done with a spreadsheet will take more time than it would using an ad hoc tool. Inclusion of the man hours spent doing a consolidation really speaks in favour of investing in a dedicated solution. The time savings can be allocated to creating reports or doing analyses that provide greater added value for the group in question.
Let’s take a look at the risks and difficulties you may be encountering in your current situation.
In our opinion, Excel (or any similar spreadsheet) is a flexible tool that can work well if you are doing the first consolidation for a simple group (single currency, constant scope, a few entities, no minority shareholders, etc.).
At what time should you invest in a dedicated software package? Here are a few aspects to consider:
1) Consolidation rules
First, it’s helpful to remember the rules of consolidation (refer to the 7th European directive): the consolidation process is based on accounting logic. It must, therefore, contain reports for balances, journals, general ledger, etc., which are particularly difficult to create with Excel. This causes a lot of frustration for auditors.
2) What is consolidation?
Many “beginners” see consolidation information as being part of a matrix organisation (companies x accounts) and, therefore, as excellent candidates for entry in a spreadsheet. This idea is wrong. Consolidation information is based on a transactional structure, not a positional one which implies at least four different dimensions: the company, the account, adjustments, flows and, sometimes, analytical aspects. This information isn’t part of a matrix, but of a hypercube, thus the need to use a database.
3) Consolidation calculations with a spreadsheet
All calculations, operations and transactions are done manually with a spreadsheet. Of course, some links and automated processes can be set up. These links and automated processes quickly turn into multiple worksheets, all linked together. Over the years they become an illegible, inflexible and risk-prone document because there are so many links that haven’t really been validated and are “touched up” manually. The flexibility of a spreadsheet, which is basically one of its strong points, becomes a risk in consolidation.
Moreover, a spreadsheet doesn’t allow for consolidation package automation.
Control and interest percentage calculations
Control and interest percentage calculations carried out before the consolidation process can quickly become laborious and the source of many errors, even for small groups, if they are obtained “manually” via worksheets.
There is a clear benefit to using specialised software in this case. The software will do all of the work for you and will, importantly, guarantee the reliability of the data: calculations, definition of the consolidation methods, implementation of various simulations within the scope, and the correction of any errors occurring in the latter.
4) Application of consolidation methods
The application of consolidation methods using a spreadsheet is done manually, by definition, and is therefore very time consuming and can’t guarantee reliable results. In fact, the conversion of corporate data into the consolidation currency and the calculations required for minority shareholders and consolidation reserves are fertile ground for errors.
By automating all of these tasks, consolidation software enables productivity gains and makes it possible to quickly obtain reliable results.
5) Minority shareholders and translation adjustments
Many consolidations done using a spreadsheet omit minority shareholders in the equity or proportionate approach. With this method, it’s also common to forget to allocate to minority shareholders their share in the value of a holding held through another subsidiary that isn’t 100% owned by the parent company.
As for translation adjustments, consolidation reserves and minority shareholders, their reconstitutions and justifications often result in an unbelievable loss of time because they must be reconstituted outside of accounting, starting with the corporate situation of each subsidiary.
If we go a little deeper into the consolidation process, we may still be faced with the problem of managing consolidation entries, the concept of flow, and the creation of restitution reports or financial statements, as well as of cash flow and tax proof tables.
6) Management of consolidation entries
A system for consolidation entries can be set up under Excel. However, ensuring its longevity is another matter. With each new consolidation/period/year, it will be important to review the entries from the previous year to determine if they have to be carried over or not, and if yes, how?
In addition to the fact that this recurring exercise will be very time consuming, other items, such as sales and de-consolidation, must be taken into account which will, no doubt, require that consolidators working with Excel deal with the painful problem of isolating the entries by company. The latter point requires working with linked accounts within each company, which is rarely the case under Excel which provides the “flexibility” needed to post entries from company to company and, consequently, compromises any possibility of obtaining the contributions by company!
Using a spreadsheet to do a consolidation creates difficulties and risks related to updates. If the chart of accounts is deployed using one worksheet per company and the chart of accounts has to be modified, updates must be done for each worksheet (which is tedious), not to mention the risk of using the wrong lines (inconsistency).
8) Consolidation variants
Again, a spreadsheet isn’t the best tool for producing consolidation variants from the same set of data: IFRS consolidation, followed by a version in local GAAP, creation of a sub-group, production of segmented data (IFRS), etc.
9) The concept of flow
The concept of contribution isn’t the only one that causes problems for Excel consolidations. In fact, most consolidations processed using a spreadsheet are done with balances. The notion of flow is usually totally absent. The implementation of a cash flow statement in Excel is always very laborious. It is created by trial and error, without any guarantee of quality despite the time spent, by drawing on information in the financial statements of the various companies in the scope. In addition to the cash flow, the concept of flow is essential to automating the creation of restitution reports and other required or compulsory reports.
10) Intra-group (or interco) transactions
At some point in the consolidation process, the consolidator must check the status of interco positions between subsidiaries one last time, even if a prior exchange procedure was carried out. In practice, this means checking that the interco position of A with B for a certain account actually reflects interco position B with A for another account. This linking can take on combinatorial proportions that are difficult to manage in a spreadsheet. Interco relationships are not account to account but, rather, accounts to accounts relationships.
11) The audit phase
The auditors we have spoken with are clear: they are more confident when they have to audit a consolidation that was done with professional software. It’s a guarantee of quality in their eyes. Consolidation software makes all of the processes secure. In addition, the best performing consolidation software provides detailed audit trails that enable auditors to access the reasons for each entry. The result is a quick and efficient audit.
It’s also very likely that the new European standards imposed on audit companies will lead them to recommend the use of dedicated professional tools to their clients in order to guarantee the quality of the consolidated accounts production process.
12) Sharing of know-how
Lastly, the spreadsheets used and the overall file are generally well understood by the “creator of the model”, but what happens if that person leaves the group? It becomes a matter of the continuity and security of the group’s consolidation. If you need to produce reliable data quickly, save time, and gain credibility during the audit phase, we recommend that you invest in consolidation software. It’s a matter of your time and the quality of information created and provided to your internal and external clients.