Consolidation in AX 2012

Perform Consolidation in AX 2012

Consolidation functionality in AX 2012 can be used to combine the financial results for several subsidiaries into a single, consolidated company.

Setup a Consolidation company

1.       Setup  General Ledger Parameters

2.       Setup chart of account.

3.       Setup default currency and exchange rates

4.       Specify system account for consolidation exchange differences

5.       Setup Elimination Rules

Note: The consolidation company will only be used to gather transactions of several subsidiaries and can not be used for daily transactions.

To prepare a consolidation Company (Parent) perform the following steps:

1.       In the Organization Administration module click Setup à organization à Legal Entities

2.       Click the NEW button to create a new legal entity.

3.       In the general FastTab Click the use for financial consolidation process check box.

4.       Mark the use for financial elimination process check box to allow elimination transaction to be created in the ledger of the legal entity.

5.       Open up the newly created consolidation company and go to General Ledger à Setupà Ledger

6.       Select the default information such as Chart of Accounts / Accounting Currency / Exchange rates / Fiscal Calendars etc.

 Currency and Exchange Rates

If some subsidiaries have foreign currencies as their company currencies, click General ledger, click Setup, click Currency, and then click Currencies.

·         Create the foreign currencies.

·         To Set up exchange rates click General Ledger > setup > Currency > currency exchange rates .

Setup Consolidation System Account

You must setup  a consolidation system account to automatically post differences resulting from using different exchange rate types for different accounts when consolidating subsidiaries into the consolidation company.

Go to General Ledger à Setup à posting à Accounts for automatic transactions

In the Posting type field use the arrow to select one of the following:

Balance account for consolidation differences if you are consolidating an integrated foreign subsidiary.

Profit & loss account for consolidation differences if you are consolidating a self-sustaining subsidiary or a company that contains the results of several self-sustaining subsidiaries and are using translation methods to consolidate the data.

Ledger Elimination Rule

In the start we have marked “use for financial elimination process” check box in the consolidation company because the consolidation company should only contain transactions that occur between parent company and the company outside the group and all the intercompany transaction should be eliminated.

Let’s setup ledger elimination rule in the consolidation company

Go to General Ledger à Setup à Posting à Ledger Elimination Rules

• Click on new button to create a new rule.
• In the general FastTab specify the Consolidation company in the destination company field
• Mark the active check box
• In the journal settings field group specify the Elimination Journal
• Now click on lines button

 In the elimination method field there two options

Net Change: eliminates an accounts net change for the specified period(most common method)

Fixed amount: eliminates specified amount for an account.

In the source account field select intercompany accounts such as sales /Expense  / purchases / receivable payables

Select “Source” in the account specification and dimension specification fields.

Setup in Subsidiary Company

Map Chart of Accounts

The best case would be that the Consolidating company has the same chart of accounts as subsidiary accounts. If that is the case no need to perform the accounts mapping.

If the chart of account of subsidiary is not similar with the parent company perform the following steps to map accounts

In the subsidiary company go to General ledger > Common > Main Accounts > select ledger account in the general FastTab enter the corresponding account number of the Consolidated company in default consolidation account field 

Map Financial Dimension

Follow these steps to map a dimension in the subsidiary to the consolidated company.

•        Click General Ledger à Financial Dimension à Financial Dimensions.
•        Select a Dimension and click financial dimension values button
•                In the group dimension field on the general FastTab, enter the dimension which will be used for consolidation.

Perform Online Consolidation

Microsoft Dynamics AX supports consolidation 

•   Online Consolidation – Subsidiary company resided within the same database
•  Import / Export – If subsidiary company resides in a different database than an export / import option may be used to get the data in the specified template (delimited file)

Here I am going to perform an online consolidation

Click on General ledger à Periodic à Consolidate à Consolidate online

Criteria Tab

Financial dimension Tab

In the specification field you can specify how the dimension should consolidate from the subsidiary and specify the segment order

Legal Entities Tab

Elimination Tab

In the currency translation tab set the translation exchange rate type if subsidiary company has different accounting currency. And click OK.

You can view the consolidation transaction by clicking General Ledger à Inquiry àConsolidation

Click transaction button then click on actual.

Allocation rules’ “Specific” and “Unspecific” criteria with multiple sets of allocation rules in AX 2012

Allocation rules on a ledger account can be configured to behave many ways, and here’s some explanations on how to use “Specific” and “Unspecific” in the current design for allocations in AX 2012. 

For testing, we’ll setup 2 main accounts, “A” and “B” to serve as our destination accounts, with the source being 110180 “Petty cash”.On the “Allocation rule” form (accessed from the "Main accounts" form), we define the percentage to be allocated, the dimension criterion for the source account, the destination ledger account, and how to handle the destination’s dimensions. For each dimension in the source criteria, you can select either “Specific” or “Unspecific”. If we set a source dimension to “Specific”, we tell AX that the transactions to the ledger account with that value apply to this rule. If the dimension is set to “Specific” and the dimension field is blank, the rule will only apply if the transaction’s corresponding dimension is blank. For example, if all dimensions in the source criteria are set to “Specific”, and Department is set to “OU_1” with the other dimensions blank, the only transaction that would be allocated by this allocation rule would be transactions with the ledger dimension combination of 110180-OU_1- - . If there are any other dimensions on the transaction, the transaction will not apply to this rule.

If we set dimensions to “Unspecific”, we tell AX that the rule applies for any dimensionincluding blanks. For example, If we set Department to “Specific” and “OU_1”, while the rest of the dimensions are “Unspecific”, all transactions to account 110180 with Department “OU_1” will allocate, no matter what the other dimension values are or if they are blank.

It is possible to have multiple sets of allocation rules, as long as there are no conflicts. For example, let’s set up allocation rules that will allocate 50% to account “A” and 50% to account “B” when the Department is “OU_1” and the worker is 000131 with any CostCenter and any ExpensePurpose. In addition, allocate 60% to account “A”  and 40% account “B” when the Department is the “OU_1” but the worker is 000123 with any CostCenter and any ExpensePurpose.

 

To see the ledger postings, we’ll post a general journal to this main account. Here is the first journal and voucher with Worker 000131 to illustrate the 50%/50% allocation:

Here’s a similar transaction, except this time using the 000123 Worker dimension to meet the 60%/40% allocation criterion:

As mentioned earlier, there is a potential for conflicts. If I designate a source dimension as “Specific” to Department “OU_1” for the first 50% to account “A”, and then try to set the other 50% to “Unspecific” going to account “B”, an error will occur stating “Allocations with different selection criterion for dimension exist. This may cause wrong allocation.” This is because “Unspecific” for a source dimension includes all values, including the “Specific” “OU_1” dimension. This could cause incorrect allocation, as a transaction with 110180-OU_1- - would meet both source criterion (50% to account “A” and 50% to account “B”), while a transaction with 110180- - - would only meet the “Unspecific” criterion and therefore would only be 50% to account “B” and have a less than 100% allocation. This is incorrect.

Import Customers using Data Import framework | AX 2012 R3

This post illustrate how to import Customers basic information using the data import framework in Dynamics AX 2012 R3


1- Go to Data import export framework --> Setup --> Data import / export framework Parameters.

2- Click browse to select shared working directory then click Validate button then close the form.

3- Go to Data import export framework --> Setup --> Source Data format, Create new source name and make sure to chose File in the Type then Change the Column delimiter if needed.

4- Go to Data import export framework --> Common --> Processing Group, Create New Group then Click the Entities Button.

5- in the entities form select the Customer Entity, CSV in the Sources data format, then Click the generate source file button.

6- in the wizard make sure to select the following fields:
Accountnum (Customer Code)
Name (Customer Name)
CustGroup (Customer Group)
Currency (Customer Currency  ) 
CountryRegionID (Currency Region)
then click Generate sample file.

7-Save the file in the shared folder you specified in step 2.

8-Open the file using MS Excel, fill the data then save the file.

9-Go back to the processing group --> entities---> Select the Sample file path then click the generate source mapping button..

10- if the mapping done successfully Click the Preview source file button to preview the data then close the form.

11- in the processing group select the group name then click the Get staging data button to copy the file data to staging table.

12-Click OK to create job.

13-Click Run in the staging data execution form.

14-Click OK.

15-The records will be inserted in the staging.

16-In the processing group select the group name then click Copy Data to target button.

17-Select the Job ID that we created in step 12 then click OK.

18- Click Run to import the data

19- Click OK.

20-The data will be written to target.

20-Go to the Customers List form, note that customers records was imported successfully.

Payments in advance ( prepayment ) To a Vendor | AX 2012

This Article discuss Payments in advance ( prepayment ) to a Vendor. This article divided into three parts

• Introduction
• Setup (Step by step with screenshots )
• Implementation (Step by step with screenshots )

Introduction:                                                                          

Accounting practices in many countries/regions require that prepayment journal vouchers, or payments in advance, from a customer or to a vendor not be posted to the usual summary accounts for the customer or vendor. Instead, these prepayment journal vouchers must be posted to special ledger accounts for prepayment journal vouchers. When a sales order or purchase order is made, an invoice is issued to the customer or vendor. When the invoice is paid, the prepayment journal voucher and sales tax prepayment journal voucher on the ledger accounts for the prepayment journal vouchers are reversed. The invoice amounts are then automatically posted to the usual summary accounts. 


 Setup:                                                                                     

• Create Prepayment Posting Profile

1- Click Accounts Payable > Setup > Vendor Posting Profiles.

1. 
2. Click New to Create New Posting Profile
   
   
   
   
   
   
3. Write the posting profile name in the Posting Profile Field for example "PRE".
   
   
   
   
   
   
4. Write the posting profile description in the Description Field for example "Prepayments".
   
   
5. Go to the Setup Section.
   
   
   
   
   
   
6. Click the Add Button and select All form the Account Code Filed to apply the setup for All Vendors in the legal entities
   
   
   
   
   
   
7. Select the prepayment summary account from the summary Account filed.
   
   
   
   
   
   
8. (if required) Select the prepayments sales tax Account from Sales Tax prepayments Filed. 
   
   
   
   
   
   
9. Press the Close Button.
   
   
   
   
   
   

• Setup Account Payable parameters to use prepayment posting profile 

1. Click Accounts Payable > Setup > Accounts Payable parameters.
   
   
   
   
   
   
2. Click Ledger and sales tax from the left pane then open the prepayment journal voucher Section .
   
   
   
   
   
   
3. If sales tax is calculated and posted when you post a prepayment journal voucher, select the Sales tax on prepayment journal voucher check box. 
   
   
   
   
   
   
4. In the Posting profile with prepayment journal voucher field, select a posting profile to indicate the accounts to which the prepayment journal voucher and sales tax prepayment journal vouchers are posted. (Select the Posting profile was created in the previous setup)
   
   
5. Press the Close Button
   
   
   
   
   
   

Implementation:                                                                      

• Create a prepayment journal voucher

1. Click Accounts Payable > Journals > Payments > Payment journal.  
   
   
     
2. Create a journal.
   
     
   
   
3. On the Setup tab, select the Amounts include sales tax check box if you want to post the prepayment journal voucher together with sales tax amounts.
   
   
   
4. Click Lines, and then create the payment line.
   
   
   
5. On the Payment tab, select the Prepayment journal voucher check box. The posting profile changes automatically to the posting profile for the prepayment journal voucher.
   
   
   
6. Post the prepayment journal voucher.
   
   
   

• Settle Customer invoice with the prepayment.

1. Click Accounts Payable> Common > Vendor Invoices > Open Vendor Invoices.
   
   
   
   
   
   
2. Select Invoice and Click the Settle Open Invoice Button.
   
   
3. In the Settle open transactions form, settle the invoice with the prepayment journal voucher