Introduction
About Multi-Entity Consolidation
If your business involves multiple locations, branches, and divisions, you may want to use multi-entity consolidation for ease of management and analytical purposes. It involves setting up the individual branches as subsidiaries and assigning them to a consolidating entity.
Treating the branches as separate entities lets you maintain separate ledgers and consolidate their records using the consolidation entity. The key advantages of having a consolidation entity are as follows:
- IC Transactions: Simplified intercompany charging with automatic elimination of intercompany balances.
- GL Journals: Access to a range of consolidation journals, including Elimination of Investment.
-
Reporting:
- Consolidated reporting through the Report Manager, OData Connector, GL Explorer, and dashboards.
- Analyse performance trends and create benchmarks for the group.
- Consolidated Sales and Purchases analysis.
- Currency Management: Consolidated actuals in base currency.
- Budgets: Consolidated budgets in base currency.
- Statistics: Consolidated statistics for actuals and budgets.
Here is an example multi-company consolidation where 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
Prerequisite: Check Settings
All entities, including the non-trading Consolidation Entity, must be set up in the system before consolidation, using the following rules:
-
General Ledger Categories: The following must be the same across both the subsidiaries and the group company:
- 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 and 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 (see next section). 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.
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.
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
Consolidate a Group
Info: Consolidation Entity Features
Before you can create a Consolidation Group, you must create a separate non-trading entity to use as the Consolidation Entity. During consolidation, the Consolidation Entity records the summarised consolidated transactions of the group. Once an entity is selected as a Consolidation Entity, it will no longer have Sales, Purchases, or Bank Ledgers available but will still have the General Ledger, BI Analysis, and Repoty Manager.
All consolidation reporting is performed from the Consolidation Entity. The Consolidation Entity's GL Explorer can also be used to drill down to GL transactions in the individual entities in the group.
Step One: Assign the Group Company and its Entities
- In the group layer Entity tab, open the Consolidation Manager.
- Click New Group
- 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.
- Name: This should be a unique name that can be used to identify the group and the entity companies within it.
- Click Add New Entity to Group.
- 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.
- Click Save. Repeat this process for each subsidiary.
Note, to delete a group, first remove all its assigned entities, then Delete will become available.
Step Two: Set Consolidation and Group Options
- In Entity Groups, select the relevant Consolidation Group.
- Under Consolidation Options, check or uncheck the consolidation options as required (see Guidelines on Consolidation Options below). All options are checked by default and it is recommended to leave them selected.
- Click Save to apply the settings to the Consolidation Group.
Guidelines on Consolidation Options
Consolidate General Ledger:
- 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:
- 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:
- Check this to consolidate Statistical Data in the Group Company.
Manage Currencies at Group Level:
- 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.
Centrally Manage Dimensions and Elements:
- 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.
Step Three: Consolidating the Group
Ensure the correct group is selected and click Consolidate.
This will consolidate the actual and budget amounts for all entities in the Consolidation Group. 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
Consolidation is complete once verification of the data consistency is complete with no errors. If any error is found, you will receive an error message. Click View Details for more information. If there are several anomalies, you can click Print All Errors. Fix any errors. Click Consolidate again. Wait for the consolidation process to complete. On completion, a confirmation message will appear.
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 Financial Year Management
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.
Consolidate a Multi-Currency Group
Info: Revaluation
Prior to consolidating a multi-currency group, run revaluation in all entities in the 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.
Step One: Create or add currencies in Consolidation Entity
- Log into the Consolidation Entity.
- Go to Setup > Code Maintenance > Currencies.
- Ensure that the table contains all the currencies used for each subsidiary base currency in your consolidation group.
See:
Step Two: Add or update Consolidation Exchange Rates
Info: Exchange Rates
The Rate of Exchange is 1 unit of Group Company Currency = xx.xxxxxx units of Subsidiary Company Currency. The rates for future periods will default to the last rate entered. Rates are usually set at the start of the financial year and can be updated as necessary.
- Open the Consolidation Manager.
- In Entity Groups, select the group you want to add or update.
- In the Consolidation Exchanges Rate tab, select the relevant currency from the Currency dropdown.
- Under the Year and Period columns, use the header filters to find the relevant entry.
- 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.
- 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.
- Click Save.
- Run a Revaluation Journal in each subsidiary. See Foreign Exchange Revaluation Journals
- Proceed with the consolidation process as outlined previously (see Consolidate a Group section).
Exchange rate calculation 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).
Consolidate a Mutli-level Group
Step One: Set Up Sub-Consolidation Groups
- Set up all Sub-Consolidation Groups in the same way as a regular Consolidation Group (see Consolidate a Group above).
- 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.
Consolidate a Group with Minority Interest
To calculate and record minority interest in a Consolidation Group, note the following:
- When setting up the Consolidation Group (see Consolidate a Group 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. Minority Interest is calculated and recorded automatically 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.
- 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:
Add an Entity to Multiple Consolidation Groups
The system 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
Check the Consolidation Option Centrally Manage Dimensions & Elements for all Consolidation Group Entities that you are adding the Entity to (see Consolidate a Group section above). This impacts the Dimension and Elements available in your entities in the following ways:
- 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.
Group Consolidation Entity Reporting
Consolidated reporting is available in the following places:
Report Manager
The Consolidation Entity contains fewer reports than its subsidiaries.
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.
