Konzernabschluss in Excel: Business Central konsolidieren

Consolidating multiple companies in Business Central is successful in Excel if you do three things in this order: map every G/L account in every company to a common account category and subcategory, verify this mapping for completeness, and then retrieve all companies in a single Excel query live from the general ledger. The account category thus becomes the common language for companies with different charts of accounts. If the mapping is complete, the consolidated P&L updates across all companies with a single click, and every consolidated figure can be traced back to the individual company and the individual G/L account. You do not need a data warehouse, an Azure SQL copy, or a Power BI license for this.

This article describes the method step-by-step, compares it fairly to Microsoft's corporate consolidation, and uses a group with three companies—one of which is in Swiss Francs—to show where the boundary between Exsion Reporting and Exsion Corporate lies.

Why is consolidating multiple companies in Business Central so tedious?

If you ask a head of accounting why the monthly consolidated financial statements take four days, you rarely hear anything about addition. Three other things are the problem.

First, the companies almost never share a chart of accounts. The holding company was set up according to SKR04, the Swiss subsidiary was added via an acquisition with its own account numbering, and the third company was copied from a template that has since gone its own way. The G/L account 4310 means something different in each company.

Second, no one can prove that the mapping is complete. Usually, the mapping resides in an Excel file maintained by a single person. A newly opened G/L account silently falls through the cracks, and the consolidated total remains incorrect until someone stumbles over a discrepancy months later.

Third, consolidation destroys traceability. As soon as three companies are condensed into a single number, the CFO asks why other operating expenses increased by 18 percent, and you are back in three separate Business Central sessions using the export button.

What Microsoft's corporate consolidation requires from you

Business Central comes with its own consolidation feature, and you should know it before you decide. Each company is set up as a business unit, and the balances are transferred to a separate consolidation company, which Microsoft describes as a pure container for the consolidated data without its own operating business. For each postable G/L account in each company, you define a consolidation debit account and a consolidation credit account. For foreign currency companies, you also specify a translation method per account, typically the average rate for P&L accounts, the closing rate for balance sheet accounts, and a composite rate for retained earnings. You can find the details in the Microsoft documentation on company consolidation.

For a stable group with a legal consolidation requirement, this can be exactly right. You should know two points beforehand. Microsoft itself writes that processing consolidation eliminations is a manual process. And the consolidation company is a periodic transfer, not a live view; a management report based on this shows the status of the last consolidation run, not the general ledger from this morning.

Anyone who needs a monthly consolidated P&L and not once a year a consolidated financial statement according to HGB often has more machinery than the task requires.

The foundation: Account category and subcategory on the G/L account

On the G/L account card, i.e., in the G/L Account table (Table 15), each account carries an account category and an account subcategory. Business Central has six fixed categories: Assets, Liabilities, Equity, Revenue, Cost of Goods Sold, and Expense. The subcategories are the flexible level because you define them yourself. Microsoft explicitly recommends that every account in the chart of accounts should be mapped to a category and a subcategory.

In most groups, this field is only partially maintained; accounts created in a hurry during the year-end closing often carry no subcategory. Across multiple companies, this is dangerous because an unmapped account in the smallest subsidiary produces a consolidated total that looks plausible but is incorrect. The first task of any consolidation is therefore not building the report, but seamlessly filling in the category and subcategory for every G/L account in every company.

Keep the mapping in Business Central and not in the reporting tool. There, it resides in the leading system, is subject to the same permission model as the rest of the general ledger, and is maintained by the person who opens the account. If the group changes its reporting tool in three years, the corporate structure remains where it is.

How do I consolidate multiple companies in Business Central in five steps?

Step 1: Define corporate structure and create menu sheet

The workbook begins with a menu sheet containing the parameters of the entire consolidation. Before you touch it, align with the CFO on the subcategories the group reports on; this is a business decision, not a technical one. The subcategories become the rows of the consolidated P&L: too coarse, and every meeting becomes a drill-down exercise; too fine, and the report is a trial balance with a nicer font.

Step 2: Check mapping before you trust a number

Before any evaluation, a validation sheet lists every G/L account of all companies with its category and subcategory. One column carries the mapping status, and the validation is simple: filter for everything that is not set to "not applicable." Every account in this filter is an unmapped account in a company you are about to consolidate.

Via Show Details, you jump directly in Business Central to exactly this account in exactly this company and enter the subcategory at the source. Run this filter with every monthly closing, not just during the implementation; new accounts are created constantly.

Step 3: Refresh and confirm mapping table

Back in Excel, a refresh rebuilds the mapping table directly from Business Central, and the correction from just now is already included. The validation filter now yields no matches. From this point on, the structure beneath the numbers is proven, not assumed.

Step 4: Select companies and periods

The postings of all companies come from the G/L Entry table (Table 17). In the Exsion Reporting query definition, which is a readable grid in the workbook, you check the desired companies; the result gets a company column, and the selection also works across multiple environments. A group that reports on multiple levels—such as the entire group, only German companies, only construction—creates each view through a filter instead of three separate reports.

You set the period using Business Central's own filter syntax, for example, 01-01-26..31-03-26 for the first quarter. The current year and the previous year are loaded simultaneously. If your group does not work in calendar months but in four-week periods or a 4-4-5 calendar, you switch from month to accounting period, and the report follows the periods defined in Business Central.

Step 5: From the consolidated figure back to the posting

Next to the consolidated report is a PivotTable with the full detail. A double-click on a number shows the underlying rows: which G/L accounts in which companies formed it. Show Details opens Business Central directly filtered to the account and date range behind a cell. The CFO's question is answered while it is being asked, in the same file, instead of arriving as an action item half a day later.

A calculation example: three companies, one of which is in CHF

Let's take a medium-sized mechanical engineering group: the holding company in Stuttgart (EUR), a sales company in Munich (EUR), and a production subsidiary in Zurich (CHF). All three run as companies in a Business Central environment. The numbers are chosen for illustrative purposes.

In the validation sheet, two gaps appear after the first run: a newly opened account for leasing expenses in Munich and an account for foreign currency differences in Zurich, both without a subcategory. Both are added via Show Details on the G/L account card; after refreshing, the filter yields zero matches.

Then the currency. The Zurich company posts in CHF, and its general ledger remains in CHF. For the consolidated P&L, you pull the exchange rate from the Currency Exchange Rate table (Table 330) in addition to the amount into the same query and convert in Excel. With quarterly revenue of 2,400,000 CHF and an average rate of 0.96, 2,304,000 EUR land in the consolidated revenue line; the balance sheet accounts are converted at the closing rate. Which method you choose per account group is an accounting decision, and it is visibly stated in the workbook.

Finally, intercompany relationships. Munich purchased machines for 850,000 EUR from Zurich during the quarter. This revenue must not appear in the consolidated P&L, otherwise the group would report 850,000 EUR in revenue and 850,000 EUR in cost of sales that never existed externally. With Exsion Reporting, you solve this via a dimension or a dedicated intercompany account that you filter out in the consolidated view, and document the elimination entries in a separate sheet. For the Zurich subsidiary, this leaves a consolidated row that survives every meeting: 2,304,000 EUR revenue minus 816,000 EUR intercompany revenue at the same average rate, resulting in 1,488,000 EUR external revenue, and each of these figures leads back to the G/L entries in Zurich with a double-click.

Which method fits which corporate group?

There are five serious ways to a consolidated P&L from Business Central, and the honest answer is that they fit different situations.

[[TABLE_START]]

Method | Setup | Data Recency | Eliminations and Currency | Suitable For

Manual export per company | None, but new every month | Status of the export | By hand in Excel | Two companies, rare reporting

Business Central corporate consolidation | Business units, consolidation accounts per G/L account, translation method per account | Status of the last consolidation run | Eliminations manual according to Microsoft, currency translation built-in | Stable groups with legal consolidation requirements

Power BI | Data model, gateway or copy, license per user | According to data model schedule | To be built in the data model | Group KPIs alongside operational data for many readers

Exsion Reporting cross-company | Installation from AppSource in about five minutes, mapping of account categories | Live with every refresh | Currency via Table 330 in Excel, eliminations via filters and documentation | Groups in one environment with a common category structure, monthly consolidated P&L

Exsion Corporate | Consolidation layer across multiple databases | With every refresh | Intercompany eliminations and currency translation as a tool feature | Groups with many companies, multiple databases, or non-reconcilable charts of accounts

[[TABLE_END]]

To be fair: Velixo, which supports Business Central alongside Acumatica, Sage Intacct, and other ERP systems, also offers multi-company features, and Jet Reports by InsightSoftware has covered consolidation for a long time. What really separates the tools are more specific questions: Where does the mapping lie? Do I get from the consolidated figure to the company and G/L account in one step? For how many ERP systems does the vendor build?

With Exsion, the mapping lies in Business Central's own account categories, the validation runs before the report, the drill-down delivers the company and account in one click, the permissions are inherited from Business Central, and Business Central is the only ERP system for which the product is built. Regarding speed, here is an indication: 700,000 G/L entries load in under five seconds, and Excel remains responsive during the refresh. Prices are publicly available on the pricing page, with a full 30-day trial.

When do you need Exsion Corporate instead of Exsion Reporting?

The method in this article uses Exsion Reporting and works when all companies are in Business Central and can share a common structure of account categories and subcategories. For the group in the example—three companies in one environment, few intercompany flows, one foreign currency—this is sufficient.

Some groups need more. If companies reside in different databases or systems, the charts of accounts cannot be reconciled via account categories alone, the intercompany relationships grow to dozens of flows with unrealized profits and investments, or the consolidation requires normalization and transformation beyond a common mapping, then Exsion Corporate is the tool for the job. It is built for the consolidation of Business Central groups, with intercompany eliminations, currency translation, and consolidated reporting as part of the product, and it reports across multiple databases.

The decision is simple. If a common category structure is achievable across your Business Central companies, start with the method above. If it is not, or if elimination entries become their own area of work, the group needs its own consolidation layer. An example of how far the cross-company method carries: N+P Group reports across 45 companies without a single export and has grown from 7 to 50 licenses in four years.

Implementation Checklist

1. List all companies in the scope of consolidation with their names in the Business Central environment.

2. Count how many G/L accounts per company are without an account category or subcategory. This number determines the preparation effort.

3. Align with the CFO on the subcategories at the level of detail the consolidated P&L should show.

4. Maintain the category and subcategory for every account in every company and ensure that no exceptions remain.

5. Define the translation method for foreign currency companies per account group and document it in the workbook.

6. List all intercompany relationships and decide whether filters and documented elimination entries are sufficient or if Exsion Corporate is required.

7. Clarify whether the group reports in calendar months or in Business Central accounting periods.

8. Determine who will be responsible for the mapping in the future, and include filling out the subcategory in the process for opening a new G/L account.

Point eight is the one groups skip, and the one that keeps consolidation correct in the second year. If you want to see the method on your own chart of accounts, book a demo or start with the 30-day trial via exsion365.com/de. The English version with a video walkthrough of five companies can be found in the article How to consolidate multiple companies in Business Central using Excel.

Frequently Asked Questions

[[FAQ_START]]

How do I consolidate multiple companies in Business Central in Excel? | Map every G/L account in every company to an account category and subcategory, verify the mapping for completeness, and then retrieve all companies in an Excel query from the G/L Entry table. The common category structure makes totals comparable across different charts of accounts.

Do I need a consolidation company in Business Central for this? | Not for the method described here. Microsoft's corporate consolidation transfers balances to a separate consolidation company and requires business units, consolidation accounts per G/L account, and translation methods per account. Evaluating via account categories directly from the source companies delivers the consolidated view without this setup and reads live data with every refresh.

How does currency translation work for a company in CHF? | The CHF company remains in its posting currency. In the Excel query, you pull the exchange rate from the Currency Exchange Rate table (Table 330) in addition to the amount and convert P&L accounts at the average rate and balance sheet accounts at the closing rate. The chosen method per account group is visibly stated in the workbook.

How do I handle intercompany eliminations? | For a few intercompany flows, you filter out intercompany accounts or a corresponding dimension from the consolidated view and document the elimination entries in a separate sheet. If the relationships grow to many flows with unrealized profits or multiple databases, Exsion Corporate handles intercompany eliminations and currency translation as a tool feature.

Can I see which company and G/L account a consolidated figure came from? | Yes. Next to the consolidated report is a PivotTable with the full detail, and a double-click on a number shows the G/L accounts and companies behind it. Show Details opens Business Central directly filtered to the account and date range behind the cell.

[[FAQ_END]]