Introduction
Advantages of 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 including non financial metrics.
- 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.
Consolidation Group Structure
Here is an example multi-company consolidation where the Consolidation Entity, RenuMe Spa Holdings Limited is the consolidating entity for three subsidiary companies:
- RenuMe Spa Products Bristol
- RenuMe Spa Products Leeds
- RenuMe Spa Products Norwich
Note: Feature Access
Please contact support@accountsiq.com if you cannot see the Consolidation option. This might require enabling the Group Accounting add-on.
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 Consolidation Entity:
- 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 Consolidation Entity 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 Consolidation Entity using its base currency.
-
Dimensions: See Using Dimensions for further details.
- Dimensions and Dimension Codes are controlled in the Consolidation Entity. They will be read-only in the subsidiaries and the Save button will be greyed out. To edit Dimensions and Dimension Codes, log into the Consolidation Entity and make any updates. These updates will flow down to the subsidiaries automatically.
- Dimension Tags do not need to be replicated across all subsidiaries. Create in the entities they are needed in and they will appear automatically on consolidated reports. If you need the same tag in multiple entities, ensure that the tag name is identical for smooth consolidation.
Prerequisite: Set User Permission
To set up a Consolidation Group and run Group 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
Set up a Consolidation Group
About the Consolidation Entity
The Consolidation Entity is a separate non-trading entity. When a group consolidation is created, it will be transformed into a Consolidation Entity and will no longer have Sales, Purchases, or Bank Ledgers available. However, it will still have the General Ledger, Dimensions, and Report Manager. See Create and Manage Entities for more details on creating an entity.
The Consolidation Entity:
- Records the summarised consolidated transactions of the group when consolidation is run.
- Contains all Consolidation Reports in the Report Manager, Transaction Browser, GL Explorer, and the listing grids. The GL Explorer can also be used to drill down to GL transactions in the subsidiaries.
Step One: Create a Consolidation Group
- 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 Consolidation Entity 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 Entity.
- 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.
- 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 Consolidation Entity. Then select the Ownership Period. This is the final period that their ownership will be recognised.
- Click Save.
- Continue adding further subsidiaries as required.
Step Two: Set Consolidation 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.
Check the following as required:
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 Consolidation Entity. This facilitates the production of Sales and Purchase Analysis Reports at Group level.
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.
Delete a Consolidation Group
Impact on Entities and Transaction Data
Deleting a group:
- Will not delete any of the entities that form part of that group.
- Will not remove the entity indicated as the Consolidation Entity. Instead, it unlinks the entities.
- Will not impact any transaction data. The Consolidation Entity pulls data from the linked entities during consolidation but doesn't actually hold any transaction data other than journals posted there.
Impact on Dimensions
As Dimensions and Dimension Codes are centrally managed in the Consolidation Entity by default, deleting a group will remove them from the formally linked entities. Therefore, they must be re-created in the unlinked entities. The Dimension Tags will still exist as they were created in the entities. However, they will need to be re-linked to the re-created Dimension Codes.
To delete a group:
- Remove all its assigned entities by clicking on Remove.
- The Delete will then become available to click.
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 Dimension Codes
Note the following:
- The Consolidation Entity of the first group the entity was added to will define the Dimensions and Dimension Codes in use in the entity.
- If the entity is subsequently removed from the first group, the Dimensions and Dimension Codes 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 Dimensions and Dimension Codes 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.
Multi-Currency Consolidation
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: Update Consolidation Exchange Rates
Info: Exchange Rates
The Rate of Exchange is 1 unit of Consolidation Entity 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.
- In the Consolidation Manager select the group you want to update.
- Go to the Consolidation Exchange Rate tab and select the relevant currency from the Currency dropdown.
- Use the Year and Period column filters to find the relevant period.
- 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. 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 "Set up a Consolidation 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.763 EUR. This is calculated using the formula JPY to USD * USD to EUR (141 * 1.012504).
Multi-level Consolidation
Step One: Set Up Sub-Consolidation Groups
- Set up all Sub-Consolidation Groups in the same way as a regular Consolidation Group (see "Set up a Consolidation 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.
Minority Interest Consolidation
To calculate and record minority interest in a Consolidation Group, note the following:
- When setting up the Consolidation Group (see "Set up a Consolidation 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.
