How to Use Consolidations in Microsoft Dynamics 365 Business Central
The origins of Microsoft Dynamics 365 Business Central (MSD365 BC) come from Europe, so it has always been strong when it comes to multinational and complex accounting functionality, including consolidations.
If your organization needs to consolidate financial data for multiple business units or legal entities or have subsidiaries that report to a parent organization, Business Central comes with out-of-the-box tools to streamline those processes.
This blog clarifies what Consolidations in Business Central offers and reviews how to use the functionality.
What Consolidations in Business Central does
In a nutshell, Microsoft Dynamics Business Central does a lot when consolidating subsidiaries for financial reporting. Here are the specifics:
- Allows you to map different Charts of Account and Dimension structures from the subsidiary companies to the master Consolidation company
- Allows you to identify the ownership percentage for a subsidiary and automatically applies this percentage to the consolidated financial data
- Consolidates financial data from subsidiary companies within the same BC database
- Allows you to consolidate financial data using a file created from another BC database or any other financial software capable of producing the required file format
- Establishes a G/L Entry in the Consolidation company for the net change in the mapped G/L Account(s) balance for the consolidation period for each subsidiary
- If desired, brings forward the related Dimension detail for the net change G/L Entry that is established
- Consolidates foreign currency subsidiaries in accordance with standards set in FAS 52-Foreign Currency Translation
- Allows you to re-consolidate past periods if G/L balances in a subsidiary company have changed
- Allows financial reporting at both the consolidated and subsidiary level from the Consolidation company
What Consolidations in Business Central does NOT do
The list is short, but there are some things BC does not do when processing consolidations:
- Establish detailed G/L entries from the subsidiary’s daily transactions in the consolidation company
- Bring forward posted sub-ledger detail from the subsidiary company (e.g., Customer Ledger, Vendor Ledger, Bank Account Ledger)
- Establish posted documents from the subsidiary in the consolidation company
- Establish G/L budgets in the consolidation company
- Automatically identify or process elimination entries
The “Does” far outweigh the “Does not” when it comes to consolidation in MSD365 BC.
How to set up consolidations in Business Central
Before performing a consolidation, the consolidated company must be set up. This company is set up the same way that other companies are setup, with its own chart of accounts and, if used, dimensions. Our example assumes the companies are all in the same database.
Create a new company to be the consolidation company. In the example in Figure 1, the name of the company is “Cronus Consolidated”.
Set up a consolidation Chart of Accounts in the Consolidation and Subsidiary companies:
- The consolidation company is set up like any other BC company, except it will only hold consolidation data
- Depending on business requirements, the consolidation chart of accounts may or may not be the same as the chart of accounts for each of the subsidiaries. The subsidiaries could also have different charts of accounts.
- Each posting account in a subsidiary company must specify the G/L Account of the Consolidation company to use for consolidation. This is what allows the companies to have different charts of accounts. These accounts are set on the Consolidation tab of the G/L Account card as seen in Figure 2.
Set up Business Units in the consolidation company only for all the individual business units, as shown in Figures 3 and 4. Note that the Consolidate button must be set to Yes and the Exchange Rate Accounts are only required if the Business Unit operates in a different currency.
In this example, business units are to be consolidated 100%, which is specified in the Consolidation % field. You can also set the exchange rate options per business unit.
Testing a consolidation in Business Central
In the consolidated company, after the business units are set up, we can test the consolidation. The testing, consolidation, and import/export functions are located under Actions > Functions, as shown in Figure 5.
Set the options for the date and business units to test the consolidation on the request page, as shown in Figure 6. Make sure to select the dimensions you wish to test as well as all relevant business units.
The report shows the G/L Accounts for the selected Business Units. If there are any errors, they will be listed here.
Running the consolidation process in Business Central
In the Consolidation Company, find the action “Run Consolidation”. To exclude a business unit from consolidation, open that business unit card and clear the “Consolidate” check box.
Set the Consolidation options, starting date and end date for the consolidation, and click “OK”. The Document No. is the value used when creating consolidation G/L entries in the company running the consolidation. You will be asked to confirm that you want to run the consolidation.
After calculating the consolidation, you will be prompted to run the “Consolidation Trial Balance” report.
You can now review the general ledger entries and run financial reports with filters for the appropriate Business Unit Code and dimensions.
NOTE: Consider if you will use Intercompany functionality to facilitate tracking elimination entries.
Frequently asked questions about consolidations in Business Central
What do I need to do when adding a new GL account?
Complete the Consol. Debit Account, Consol. Credit Account, and Consolidation Translation Method fields.
What are the Consolidation Translation Methods and when are they used?
- Average Rate (Manual): typically used for Income Statement accounts; the rate is specified during consolidation process
- Closing Rate: typically used for Balance Sheet Accounts, with the exception of Equity accounts; the rate is specified during consolidation process, the beginning balance is restated
- Historical Rate: typically used for Retained Earnings, Intercompany Payables and Receivables, foreign currency loans from third parties; use the exchange rate in effect on the transaction’s posting date
- Composite Rate: used for Equity accounts, the beginning balance is not restated, the average rate specified during consolidation is used for the current period; indicate accounts for posting on the Business Unit page
- Equity Rate: similar to Composite; indicate accounts for posting on the Business Unit page
NOTE: Methods should agree to those being used currently (manually) and confirmed through testing and with outside auditors.
How do I select Dimensions for consolidation?
From the Business Unit, select Functions > Import Database. On the Options tab, click the ellipsis in the Dimensions field and place a check in front of the dimensions to import.
How do I record eliminating entries?
Enter the eliminating entries in a journal batch. To view the entries prior to posting, run the Eliminations report from the Business Unit page and on the Options tab, select the Journal and Batch for the elimination entries.
Do I need to close the year in the consolidated company?
Yes, the year must be closed to reflect retained earnings and the income statement balances properly.
What are the steps involved in processing a consolidation for multicurrency?
From the Business Unit:
- Click the Exchange Rates button > Average Rate and enter the average exchange rate for the period.
- Click the Exchange Rates button > Closing Rate and enter the closing exchange rate for the period.
- Process as normal for other consolidations
What exchange rate is used for processing a consolidation?
In the Currency Exchange Rate Table, you can select the option to use the Local (Parent) or Business Unit (Subsidiary) as the source; alternatively, you can enter the exchange rates from the business unit card.
Are there special considerations when reconsolidating with multiple currencies?
Yes. Enter the Average Rate and Closing Rate on the Business Unit card.
Can I use an Additional Reporting Currency for consolidations?
Yes. In the Data Source field on the Business Unit card, select the Additional Reporting Currency option.
Do I need to run the Adjust Exchange Rate batch in the consolidated company?
If you use the Additional Reporting Currency (ACY) as the consolidation currency, you need to run the Adjust Exchange Rate batch job in the consolidated company for the ACY. If you use multiple currencies in any of the subsidiary companies, you need to run the Adjust Exchange Rate Batch job for your customers, vendors, and bank accounts prior to consolidating.
Are there special steps when processing a consolidation for multicurrency?
- To process the consolidation with a foreign currency, from the Business Unit:
- Select the Exchange Rates button > Average Rate and enter the average exchange rate for the period.
- Select the Exchange Rates button > Closing Rate and enter the closing exchange rate for the period.
- Process as with other consolidations
What exchange rate is used when processing a consolidation?
In the Currency Exc ange Rate Table, you can select the option of using the Local (Parent) or Business Unit (Subsidiary) as the source; alternatively enter the exchange rates from the business unit card as indicated above.
Are there special considerations when reconsolidating and multiple currencies are involved?
Yes, enter the Average Rate and Closing Rate on the Business Unit card.
Can I use an Additional Reporting Currency for consolidations?
Yes, in the Data Source field on the Business Unit card, select the Additional Reporting Currency option.
Do I need to run the Adjust Exchange Rate batch in the consolidated company?
If you use the Additional Reporting Currency (ACY) as the consolidation currency, then you need to run the Adjust Exchange Rate batch job in the consolidated company for the ACY.
Also, if you use multiple currencies in any of the subsidiary companies, you need to run the Adjust Exchange Rate Batch job for your customers, vendors, and bank accounts prior to consolidating.
Need help with Consolidations in Business Central? Ask the experts
At ArcherPoint, our accounting and finance experts have years of experience helping companies make it easier to manage multiple legal entities, subsidiaries, or business units within the US and multi-nationally.
Contact us for help with setting up your consolidation process.