Consolidation in D365 F&O

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. 

Exchange rates

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. 

Example: 

Local ledger (GOLD)

VoucherDate Ledger account Account name TransactionCurrencyAmount in transaction currencyAmount in Accounting currency (USD)
APPS0000000018/19/202065030000-D30301- NORTH-HO-SB4106-P010110—CR10900-VEND000011-625120Travel ExpensesEUR1000890
APPS0000000028/20/202065030000-D30301- NORTH-HO-SB4106-P010110—CR10900-VEND000011-625120Travel ExpensesEUR1000880

Group ledger (Reporting entity) 

VoucherConsolidation DateLedger account Account name CurrencyAmount in transaction currencyAmount 
IRIS0000018/30/20206503.00-GOLD-SB41-P0101-D30-CANADATravel ExpensesUSD17701770

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 rateTemporal method exchange rate
Balance sheet
Monetary accountsCurrentCurrent
Cash, AR, AP, debt
Non-monetary accountsCurrentHistorical
Inventory, Fixed assets, intangible assets…
EquityHistoricalHistorical
Profit & Loss
Revenues and expenses not related to non monetary assetsAverageAverage
Revenues and expenses related to non monetary assetsAverageHistorical

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: