Introduction

How does Multi-Company Consolidation work?

With multi-company consolidation, a business can set up a group company as a consolidating entity. Within this group, they can operate locations, branches, or divisions as separate entities. Treating these as separate entities lets the business maintain separate ledgers and then consolidate all their records using the consolidation entity.

Advantages of Centralised Consolidation

The key advantages of having a consolidation entity are as follows:

• Simplified intercompany charging with automatic elimination of intercompany balances.
• Access to a range of consolidation journals, including Elimination of Investment.
• Analyse performance trends and create benchmarks for the group.
• Consolidated Sales and Purchases analysis.
• Consolidated actuals in base currency.
• Consolidated budgets in base currency.
• Consolidated statistics for actuals and budgets.
• Consolidated reporting through the Report Manager, OData Connector, GL Explorer, and dashboards.

Example Multi-Company Consolidation

In this example, the group company, RenuMe Spa Holdings Limited is the consolidating entity for three subsidiary companies:

• RenuMe Spa Products Bristol
• RenuMe Spa Products Leeds
• RenuMe Spa Products Norwich

See:

 Consolidation Overview (12.0) - AIQ Academy

Consolidation Group Setup (12.1) - AIQ Academy

How to run the consolidation process in AccountsIQ (12.2) - AIQ Academy

Consolidated Reporting (12.3) - AIQ Academy 

How do I Set Up a New Company or Corporate Entity?‍ 
How do I Implement Extended Business Analysis?‍ 
Setting up and Administering Group and Entity Users‍ 

Consolidation Pre-requisites

General Ledger Categories

• General Ledger Categories (for example, Operating Revenues, Direct Costs, and Current Assets).
• General Ledger Sub Categories (for example, Payroll Liabilities, Facility Costs, and Capital and Equity).

Financial Year

• All accounting year ends must have the same end date.
• All accounting periods must have the same calendar structure.

Base Currency

• The group company must be set up in the base reporting currency, regardless of the base currencies of the subsidiaries.
• The group company must have the base currency of every subsidiary set up in its currency table. The subsidiary companies can then operate and report in their areas in the local currency but be consolidated into the group company using its base currency.

Extended Business Analysis

• Dimensions and Elements must be the same in the group and all its subsidiaries. For the highest and intermediate levels in a multi-level consolidation, check "Centrally Manage Dimensions and Elements" in the Consolidation Manager. This ensures that Dimensions and their Elements can only be created at the highest consolidation level and all subsidiary companies will inherit them, including any intermediate sub-consolidation companies.
• BI Codes do not need to be replicated across all subsidiaries. However, if more than one subsidiary uses an identical BI Code, it must also have identical Dimensions, Elements, and Descriptions. If the BI Code is not already present, it will be automatically generated in the group company.

See:

Setting up the Consolidation Group 

Prerequisite: Set User Permission

To set up a Consolidation Group and perform consolidation, the user must have Consolidation Manager checked in their account-level permissions‍.

Step One: Assign the Group Company and its Entities

1. Click the Consolidation Manager link or the  icon.
   
   
   
2. In the Entity Groups listing, Click Create New Group to consolidate multiple companies into one.
3. In the Entity Group Detail listing, go to the Setup tab. Enter the following:
   •  Name: This should be a unique name that can be used to identify the group and the entity companies within it.
   • Consolidation Entity: This is the entity that will serve as the group company into which the selected entities' data will be consolidated. Ensure that it has been set up with a base currency that is the reporting currency for the group before continuing. The consolidation entity will have menu and report options that are relevant to consolidation functions only, for example running consolidation reports, entering journals, and viewing transactions. The normal transaction entry screens, such as invoice entry, customer accounts are not available in the consolidation company.
   •  Notes: Enter any notes that are relevant to the consolidation group. For example, you may wish to store a reminder about any period end procedures that need to be done prior to consolidation.
4. To nominate the individual subsidiaries of the group company, click Add New Entity to Group. A designated group company in one group can become an entity company in another group.
   
   
   
   
5. In the Add New Entity to Group screen, select the subsidiary company you want to include from the Entity dropdown. Enter the Ownership % of the subsidiary by the group company and select the Ownership Period that applies.
6. Click Save.
   
   
   
   
7. Repeat steps 4-6 for each subsidiary. Note, to delete an entity group you will need to first remove all the assigned entities from that group, before clicking Delete. 

Step Two: Set Consolidation and Group Options

1. Under Entity Groups, select the relevant Consolidation Group.
2. Under Consolidation Options, check or uncheck the consolidation options as required (see Guidelines on Consolidation Options below).  Usually, it is recommended to check all options.
3. Click Save to apply the settings to the Consolidation Group. 
   
   

Guidelines on Consolidation Options

Consolidate General Ledger: 

• Checked by default.
• Check to consolidate the subsidiary General Ledger data into the Consolidation Entity. 
• GL Categories and GL Sub Categories must match across all subsidiaries and parent companies for the consolidation process to function properly. 
• GL Codes do not have to match. This allows for subsidiaries to have different Charts of Accounts, provided they have a common set of GL Categories and Sub-Categories.

Consolidate Sales & Purchases:

• Checked by default.
• Check to consolidate the Sales and Purchase data into the Group Company. This facilitates the production of Sales and Purchase Analysis Reports at Group level.

Consolidate Statistical Data:

• Checked by default.
• Check this to consolidate Statistical Data in the Group Company.

Manage Currencies at Group Level: 

• Not checked by default.
• Check to maintain currency transaction rates at Group level. This will propagate the currency rates in the Consolidation Entity to the individual entities in the group. 
• Rates can still be updated at an entity level if required. 

How do the Multi-Currency Features of the System Work?‍ 

Centrally Manage Dimensions and Elements: 

• Not checked by default.
• If you use Business Intelligence, check to ensure that Dimensions and Elements can only be set up in the top Consolidation Group, not individual subsidiary entities in the group. They will then be propagated to the individual entities in the Group. 
• It is recommended to check this for all Consolidation groups to ensure consistency of Dimensions and Elements across the entities in the group. This is required for consolidation.
• BI Codes can still be created in individual entities as required. However, if a BI Code is set up in more than one entity, the Description, and Dimensions must be the same in all entities. 

Consolidating the Group

1. In the Consolidation Manager, select the Consolidation Group from the Entity Group listing.
2. Click Consolidate. This will consolidate the actual and budget amounts for all entities in the Consolidation Group. 
3. The Verify Consolidation Data window will open, showing the progress of the financial and statistical data consolidation in the following order:
   • Verifying CTL Account Consistency 
   • Verifying GL Account consistency 
   • Verifying GL Categories consistency 
   • Verifying GL Sub Categories consistency 
   • Verifying Currencies consistency 
   • Verifying Period Consistency 
   • Verifying BI Codes Consistency 
   • Verifying Reporting Information Consistency 
4. Consolidation is complete once verification of the data consistency is complete with no errors. Click Close. 
5. Once the process is complete, you can log into the group consolidation company and run Consolidation reports. You can run the consolidation process as many times as you wish during a period.

Consolidation and Open and Closed Periods

The consolidation process can be run as frequently as required during a period. 

The Consolidation process consolidates all transactions posted in prior periods. When making adjustments to prior periods, re-run consolidation to ensure there are no inconsistencies between the entity and group financial reports. 

Consolidating a Multi-Currency Group

If an entity base currency is different to the Consolidation Group Currency, the consolidation routine translates actual and budget amounts into the Consolidation Group Currency. The translation rates come from the Consolidation Exchange Rate table. 

To do this, the following setup steps must be performed. 

Step One: Create or add currencies in Consolidation Entity

1. Log into the Consolidation Entity for your group.
2. Go to Setup > Code Maintenance > Currencies.
3. Ensure that the table contains all the currencies used for each subsidiary base currency in your consolidation group. 

How do the Multi-Currency Features of the System Work?‍ 

Step Two: Add or update Consolidation Exchange Rates

1. Open the Consolidation Manager.
2. In the Entity Groups listing, select the group you want to add or update exchange rates for.
3. Under Entity Group Detail, open the Consolidation Exchanges Rate tab.
4. Select the relevant currency from the Currency dropdown.
   
   
   
   
5. Under the Year and Period columns, use the header filters to find the relevant entry.
6. Click Edit and make updates to the relevant fields: 
   • P&L Avg. Period Rate: This rate is used to translate foreign currency Profit and Loss GL subsidiary Accounts into the reporting currency of the group. 
   • Bal. Sheet Period End Rate: This rate is used to translate foreign currency Balance Sheet GL Accounts and Foreign Exchange Revaluation Journals within each subsidiary. Note, that the equity section in the balance sheet of any foreign currency subsidiaries will not be retranslated every month. The initial rate used when the equity is posted will be used to translate the equity section and cannot be changed.
   • Budget Rates, Revised Budget Rates: This rate is used for budgets. 
     • Ignore both columns if you do not use consolidated budgets. These will default to the P&L Avg. Period Rate for the same period.
     • Budgets are consolidated automatically by consolidation routine. This allows you to prepare more accurate consolidated group and revised budgets at a pre-defined exchange rate, including forecasted future rates. You can also prepare monthly consolidated budgets at the same rate to ensure consistency. These rates will only be used when consolidating budgets. They are not used when consolidating Actuals.  
7. Click Update after adding or updating exchange rates. The updated rates will have a white background. Updating an average rate will automatically update other rates in the column, so edit as necessary. 
8. Repeat this process for all other currencies in the group.
9. Run a Revaluation Journal in each subsidiary. See Foreign Exchange Revaluation Journals‍ 
10. Proceed with the consolidation process as outlined previously (see Consolidating the Group section).

How is the exchange rate of a currency calculated when a subsidiary has a different base currency to the top consolidation entity?

Example for JPY

The exchange rates set in the top consolidation entity determine the rates for subsidiaries.

Top Consolidation Entity Base Currency: USD

• EUR to USD = 0.987650.
• JPY to USD = 141.000

Sub-Consolidation Entity Base Currency: EUR

• USD to EUR = 1.012504 USD. This is calculated based off the USD to EUR rate from top consolidation entity.
• JPY to EUR = 142.763EUR. This is calculated using the formula USD to JPY * EUR to USD (141 * 1.012504).

Consolidating a Mutli-level Group

Step One: Set Up Sub-Consolidation Groups

1. Set up all Sub-Consolidation Groups in the same way as a regular Consolidation Group (see Setting Up a Consolidation Group above). 
2. Add the Consolidation Entity of each Sub-Consolidation Group to the higher-level consolidation group using Consolidation Manager > Add New Entity to Group. 

 Step Two: Consolidate the multi-level group

• To ensure the accuracy of consolidation reporting at the top-level group, perform consolidation for each group starting in order of the lowest-level sub-consolidation group. 

Reporting in a Multi-level Group

• Consolidation Reporting can be performed at each sub-consolidation group level. 

Consolidating a Group with Minority Interest 

To calculate and record minority interest in a Consolidation Group, perform the following steps. 

Step One: Set the Ownership %

• When setting up the Consolidation Group (see earlier section), ensure the Ownership Percentage of each subsidiary reflects any minority interest (< 50%) share. To do this, set the Ownership % to less than 100% in the entity with a minority interest. For example, if the ownership percentage is set to 90%, a minority interest of 10% will be calculated and recorded during consolidation. 
• If the Ownership % changes, also update the Date of ownership period to avoid including earlier data.
• Ensure the Minority Interest system account has been set up in each entity in the group.

Step Two: Record and Review the Minority Interest 

• Minority Interest is calculated and recorded automatically during consolidation. 
• Minority Interests appear in the Consolidation Reports. If you have more than one entity with a minority interest, you can use the Consolidated Reports – Split by Subsidiary to review the Minority Interest calculated per entity. 

See:

Consolidated Reporting for Management versus Statutory Requirements 

AccountsIQ allows you to add entities into more than one consolidation group. This can be used if you need consolidated reports for the same group, or for certain entities in the group, in a different currency or accounting standard.

Managing Dimensions and Elements

• The Consolidation Entity of the first group the entity was added to will define the Dimensions and Elements in use in the entity.
• If the entity is subsequently removed from the first group, the Dimensions and Elements available in the entity will be those defined in the Consolidation entity of the second group the entity was added to.  
• If the entity is subsequently removed from the second group it was added to, the Dimension and Elements available in the entity will remain as those defined in the Consolidation Entity of the first group the entity was added to. 

Managing Ownership %

• When adding an entity to more than one entity group, the entity's Ownership % must be set separately in each group. 

Consolidated Reporting

Consolidated reporting is available through the:

• Report Manager‍ 
• OData Connector‍ 
• GL Explorer‍ 
• Dashboards‍ 

Report Manager

The are only four report groups and a shorter list of reports than at subsidiary level.

The most commonly used are:

• Consolidated Balance Sheet - Split By Subsidiary: This allows for comparison of the balance in each entity, and lets you drill down to transaction level. All reports that are split by subsidiary let you view data using either the consolidation entity GL categorisation or that of a subsidiary.
• Consolidated Profit and Loss - Split By Subsidiary: Similar to above but with a P&L breakdown.
• Consolidated Trial Balance - Split By Subsidiary: Lists all GL Codes split by subsidiary.
• Aged Debtors/Creditors: All outstanding payments. This can be viewed in the currency of the group or subsidiary.
• Consolidated Cashflow: This summarises the amount of cash and cash equivalents entering and leaving a company.
• Consolidated Bank Reconciliation Report: An overview of the group's bank reconciliation status. You can drill down into the unreconciled items.
• Management Accounts Pack: This includes Profit and Loss Forecast, Balance Sheet, Profit and Loss versus Budget, and Profit and Loss versus last year.
• Intercompany Balance: Shows intercompany balance for all subsidiaries that have processed intercompany transactions during a selected financial period.

Troubleshooting Consolidation Errors 

When you click Consolidate, AccountsIQ performs verification steps to ensure the financial data is consistent across the group. If an inconsistency is found, you will receive an error message. To resolve consolidation issues, click View Details.

Troubleshooting Example

1. There are four BI Code Description inconsistencies in this Consolidation. Normally these inconsistencies only occur the first time you run the verification process as once corrected, they shouldn’t re-occur. If there are several anomalies, you can click Print Errors to produce a hard copy report.
   
   
   
   
2. Fix any errors. In this example, the BI Code descriptions were updated to make them the same in all the entities. Click Consolidate again.
   
   
   
   
3. Wait for the consolidation process to complete. On completion, a confirmation message will appear. Click Close.
   
   
   
   
4. You can now run reports such as the Consolidated Profit & Loss Split by Subsidiary (Summarised by GL Sub-Category).