Consolidations in D365 F&O comes with two features to accomplish consolidations. One of the functionalities that can be used to consolidate is what is now called Financial Reporter. Your second option in D365 F&O is a process called “Consolidate online.”
I explain how to use the “Consolidate online” feature in our case.
Consolidation company (Reporting entity)
Navigate to Organization administration > Organizations > Legal entities
- Create a new legal entity and enter the name of the reporting entity
- Activate Use for financial consolidation process
The reporting entity will be a non-transactional company. That means that you cannot post a transaction in it.
- Add the entity into an organization hierarchy that corresponds to the corporate structure
Navigate to Organization administration > Organizations > Organization hierarchies
- Assign the Organization chart purpose
- Select View to open the hierarchy designer
- You can edit the hierarchy and build the corporate structure
Accounts for automatic transactions
Navigate to General ledger > Posting setup > Accounts for automatic transactions and select the appropriate main accounts for the two posting types:
- Balance account for consolidation differences
- Profit and loss account for consolidation differences
Ledger set up
Navigate to General ledger > Ledger setup > Ledger
In the Accounts for reconciliation tab, select the main accounts for unrealized gain and loss postings using the main account defined in the setup form “Account for automatic transactions” as “Balance sheet account for consolidation differences”.
Chart of accounts
Mark Foreign currency revaluation for all main accounts you need to adjust/translate.
When you originally consolidate the data, you will use the Currency translation tab of Consolidate Online feature to select the initial exchange rates for translation during the consolidation process. After a new exchange rate is entered next month, you must revalue the account balances as you do in a transactional company.
Navigate to General ledger > Chart of accounts > Accounts > Additional consolidation accounts
Map the source main accounts to the group account, also called Consolidation account in D365 F&O.
Navigate to General ledger > Chart of accounts > Dimensions > Financial dimensions
For the financial dimensions that are used for group reporting, map the financial dimension values to the group dimension.
Warning: Leave the Group dimension blank for dummy financial dimensions that may be used.
Exchange rate types
Set up the exchange rates that are used in the consolidation / currency adjustment run. This should be at least Average and Closing.
Navigate to General Ledger > Currencies > Currency exchange rates
Fill in the exchange rate pairs that you need (Local currency <-> Package currency).
Run the consolidation
Navigate to Consolidations > Consolidate Online
Consolidate online – Criteria
You define the accounts, the periods, and the type of data that is being consolidated.
Set the Include actual amounts option to Yes
Set the Rebuild balances during consolidation to No and set up a separate batch job for that task
Do not include budget amounts
In the Select consolidation account from field, select Consolidation account group
Consolidate online – Financial dimensions
You need to define which financial dimensions will be populated in the group ledger, i.e. the ledger of the consolidation company.
The legal entity dimension enables the option to see the source company of each transaction in the group ledger.
Local ledger (GOLD)
|Voucher||Date||Ledger account||Account name||TransactionCurrency||Amount in transaction currency||Amount in Accounting currency (USD)|
|APPS000000001||8/19/2020||65030000-D30301- NORTH-HO-SB4106-P010110—CR10900-VEND000011-625120||Travel Expenses||EUR||1000||890|
|APPS000000002||8/20/2020||65030000-D30301- NORTH-HO-SB4106-P010110—CR10900-VEND000011-625120||Travel Expenses||EUR||1000||880|
Group ledger (Reporting entity)
|Voucher||Consolidation Date||Ledger account||Account name||Currency||Amount in transaction currency||Amount|
The transaction currency of the group ledger is either the accounting currency of the source company or the reporting currency of the source company.
The following parameters will be used:
Consolidate online – Legal entities
On the Legal entities tab, you define the companies that should be included in the reporting entity/package.
Consolidate online – Currency translation
There are two methods for currency translation, i.e. the current-rate method and the temporal method. The configuration depends on which method is used.
In the Currency translation tab, you need to set up the main account ranges and rates to translate from the accounting currency (Local currency) of the source company to the accounting currency of the reporting entity. You need to also define the Exchange rate type and Exchange rate date for a range of main accounts in each source company.
The exchange rate type determines the currencies and exchange rates you want to apply to the account and the exchange rate date determines which exchange rate to use for each account range.
In the ‘Apply exchange rate from’ field, there are three options: Consolidation date, Transaction date, and User-defined.
Based on the following table, use Consolidation date for any accounts that should use the Current exchange rate and Transaction date for any accounts that should use the Historical or Average exchange rate.
|Current-rate method exchange rate||Temporal method exchange rate|
|Cash, AR, AP, debt|
|Inventory, Fixed assets, intangible assets…|
|Profit & Loss|
|Revenues and expenses not related to non monetary assets||Average||Average|
|Revenues and expenses related to non monetary assets||Average||Historical|
When you choose a User-Defined exchange rate, you can also force the exchange rate. However, we don’t recommend that as the history is lost each time you run the consolidation.
When you translate Balance sheet accounts & Profit & Loss accounts with different exchange rates, you end up with a Currency Translation Adjustment (CTA). That’s why you need to define which accounts to use for the consolidation differences in the Accounts for automatic transactions (2.1.2).
Re-running consolidations and/or Re-running exchange adjustments
Non consolidated data will be aggregated monthly in the reporting entity. The batch processing recurrence needs to be set up
That’s the end of the article. Please comment if you have any questions.