The aim of this article is not to extensively describe the consolidation of financial statements from a theoretical perspective but to demonstrate how to automate the preparation of consolidated statements as much as possible, perhaps using Excel. It is assumed that if you are reading this article, you have an idea of what it’s about. The issue of the time-consuming nature of preparing consolidated statements (and reporting in general) is quite relevant.

If you are a company that is required to prepare a consolidated financial statement once a year, and you are not too concerned about numbers during the year (monthly reporting), then you probably don’t need to worry too much. Once a year, before the audit, you can go through an exercise in Excel and simply put everything together somehow and enjoy a moment of relief.

However, if you are a company, or rather a (almost) corporation, that does consolidated financial monthly reporting, you have a lot of subsidiary companies, and you have high demands on the granularity of monthly managerial reporting (product breakdowns, cost centers, departments, etc.), then this topic is more relevant to you because preparing reports takes a long time.

An Example of the Consolidation Process from the Perspective of Accounting Data and Monthly Automation

Within consolidation, we perform several tasks for simplification. These tasks vary for different consolidation entities – consolidation policies are aligned with internal accounting methodologies agreed upon with the auditor to comply with accounting laws.

The steps we take to reach the final result can include the following:

1. We create a unified chart of accounts for the consolidated entity.
2. We map the chart of accounts of subsidiary companies to the consolidated chart of accounts (1).

Note: Steps 1 and 2 are done because subsidiary companies may have different accounting methodologies and account numbering.

3. We define exchange rates for currency conversion (foreign branches).
4. Using a mapping bridge of chart of accounts, we are able to compile a general ledger (with full detail) or account balances. Foreign currencies are converted using prepared exchange rates.
5. We eliminate intercompany transactions within the consolidation entity.
6. We make further adjustments (IFRS adjustments, etc.).
7. We prepare and approve a unified format of consolidated financial statements (Balance Sheet, Income Statement, Cash Flow).
8. We prepare the mapping of consolidated chart of accounts to financial statements and populate balances into statements.

If you can think and implement solutions in this way, you are close to automating the consolidation of financial statements without consuming an unreasonable amount of time, and you might even be able to fully automate the entire process, having consolidation at your fingertips, perhaps in Excel or Power BI (Balance Sheet and Income Statement in Power BI).

Full consolidation of approximately 5-10 companies can take several hours with checks (full automation) or it can take several days (manual).

Typical Situations That Pose Challenges to Monthly Consolidation and Extend the Time?

Now, I will describe what needs to be managed from my perspective. It won’t work on its own. It may seem simple and it can be simple. However, in practice, it is often not. Those who are building the infrastructure (systems, reporting) have a significant advantage. It’s harder to jump onto a moving train.

Lack of a Unified Process for Monthly Financial Statements

A prerequisite for accurate monthly reporting is that all accounting cases are accounted for by a certain date. It is not possible to report if everything is not accounted for.

• Define the closing day for financial statements (e.g., no later than the 10th day of the month) so that you can start reporting the next day.
• Accruals (salaries, depreciation, invoices).

Subsidiary Companies of the Consolidation Entity Operate on Different Accounting Systems

It is rare that individual subsidiary companies all operate on the same financial accounting software. If they do, congratulations, you have a lot fewer worries.

Usually, you have to deal with different systems in some way. Accounting systems have different data structures and even different forms of double-entry accounting. In some systems, a double entry in the general ledger means 1 line (Pohoda accounting system) ¹, while in another system, it might be 2 (Business central) ². There can be a lot of specifics. Generally, the more diversity, the more work.

Personally, I deal with this problem by creating a common template for the general ledger (General Ledger) and map the general ledgers of subsidiary companies to it using mapping bridges.

If you want to convert your accounting data into a unified structure, you will need to do at least two things:

• Standardize the structures of exported general ledgers.
• Standardize the structure of chart of accounts – ideally, have a unified chart of accounts ready for the consolidated entity.

We Cannot Identify Transactions with Related Parties in the General Ledger

And that’s a mistake. I encounter situations where defining transactions with related parties takes an unreasonable amount of time, and some accounting cases are manually tracked down from physical documents. Yet, all you need is to agree on a few rules with accountants, and you have an overview of them.

• Account for these transactions on separate accounts (account codes) – ideally with a fixed and agreed-upon sequence (e.g., ending in 999). If you establish this rule within the company, your analyst/controller compiling the managerial report will always know that account code ending with 999 should always be excluded from consolidation. If it’s not like that, they will have to maintain a list of such account codes, which is labor-intensive and prone to errors/communication.
• Create some kind of flag for these transactions in your systems.

You may encounter other difficulties in your company. If you cannot resolve them, it will be difficult to automate consolidated financial statements and eliminate the time-consuming nature. I would like to follow up on this article next time and show some specific examples of how to deal with consolidation, either in Excel or SQL Server / Power BI. I will try to do that soon 😉

1. Pohoda economic system, Home page [online]. [accessed on 2021-12-02]. Available at: https://www.pohoda.cz/ 
2. Microsoft Dynamics 365, Business Central Home Page [online]. [accessed on 2021-12-02]. Available at: https://dynamics.microsoft.com/cs-cz/business-central/overview/