Advanced bank reconciliation in AX 2012 is a new feature to help automate and accelerate the bank reconciliation. It helps users to match transactions with the bank statement which is automatically imported in AX. Rules can be created based on user defined parameters to mass match transactions and reduce the amount of manual work and errors.
However, before using this great new feature, you might need to consider few details and test it:
1. No going back - The first obvious and most important detail to consider is that once the advanced bank reconciliation feature is turned on, then you CANNOT turn it off!!
Make sure you test it first for few months before being sure to use it in Production, there is no going back.
I strongly advise to test full months and not just few days of the month to ensure the ending balance is correct and match your bank statement.
An option is to pick a bank account with limited activity and let the user manually create the bank statement in AX so you can still use the matching rules to practice. This limit the number of examples and potential issues you might have though.
2. Bank Integration - The standard format to exchange bank statements between AX and the bank is called BAI2. BAI file format is a file format for performing electronic cash management balance reporting and exchange data. Each bank will have its own version of this format and you will need to customize AX to ensure you can read and import the statement in AX. AIF services are provided in standard AX and can be modified to match your bank requirements.
Also, if you have several companies in AX that are using advanced bank reconciliation function, you will need to most likely split your BAI2 file to import in each company. Certain bank are sending one file with all the bank accounts that are turned on and AX doesn't make the distinction between bank accounts so it will import the entire file in one entity. By splitting the file you will be able to import each statement in the correct entity.
Finally, consider to create an automatic import of the statements in AX as certain bank will send daily statements. This will prevent your users to have to import 30 files a months especially if the file needs to be re-formatted before the import.
3. Auto Matching - When it's time to create the matching rules keep in mind to create rules with at least a few defined matching attributes. Try to refrain from creating rules that are matching only on the amount for example.
Make sure to create different rules for different type of transactions: receipts, outgoing checks, outgoing wires, GL entries... Each scenario can be treated with different attributes.
Also, the order of the execution of the rules is important as you want to match the most specific transactions first and then for the few remaining you can be more confident that matching on the date and amount for example will not clear the wrong transactions.
Rules are not set in stone and can be updated and improved as you learn about the process.
4. Reporting - The standard bank statement report provided in the advanced bank reconciliation doesn't have the list of outstanding transactions for the reconciled period. It is most likely that you will be asked to add this to the report as this will be used to match your bank balance with your GL balance.
The report do show cleared transactions and doesn't have an option to not show them which for accounts that have a lot of activity can end up being too much. Be prepared to be asked to have an option to hide those on the report as it's not necessary useful to have this :)
5. User adoption varies! The excitement at first, being able to use matching rules, having the bank statement imported in AX, this is all very exciting for a user. But once they start to use it, obviously they have to learn and learning curve can take some time. Make sure they understand that the rules can be modified and improved. Emphasize the communication with your users to understand what takes time for them, what are the type of transactions that are hard to match.
I hope this will help you and let me know if you want more information, I would be more than happy to share details :)
Monday, August 4, 2014
Thursday, July 31, 2014
Prepayments in AX 2012
Today we are going to talk about prepayments in AX 2012.
Prepayment function is used to record a receipt from a client into A/R that doesn't have an invoice yet. This prepayment function is used to offset the cash on a prepaid A/R account instead of the regular Accounts Receivable account.
Once the invoice is created and settled against the prepayment, the system will automatically post a customer settlement transaction to offset the A/R prepaid account with regular A/R and ensure the balances are closed. Below are the steps and the result of this process:
Before creating your payment journal, you need to define a new posting profile for prepayment. This will ensure the payment is recorded in prepaid A/R instead of regular A/R. The new posting profile is then selected in AR > Setup > Parameters > Ledger and sales tax > Prepayment journal voucher fields group.
Select the new posting profile in the "Posting profile with prepayment journal voucher" field. Also, check the "Sales tax on prepayment journal voucher" if you want sales tax to be deducted from the payment received. Sales tax will be adjusted when the invoice is created and settled against the prepayment only if the amount o the invoice is more than the prepayment itself.
Go to Accounts Receivable > Journals > Payments > Payment journal. Create a new payment journal like you would usually do when receiving a receipt however since you don't have an invoice yet, do not select the invoice, the invoice field will remain blank.
Prepayment function is used to record a receipt from a client into A/R that doesn't have an invoice yet. This prepayment function is used to offset the cash on a prepaid A/R account instead of the regular Accounts Receivable account.
Once the invoice is created and settled against the prepayment, the system will automatically post a customer settlement transaction to offset the A/R prepaid account with regular A/R and ensure the balances are closed. Below are the steps and the result of this process:
Before creating your payment journal, you need to define a new posting profile for prepayment. This will ensure the payment is recorded in prepaid A/R instead of regular A/R. The new posting profile is then selected in AR > Setup > Parameters > Ledger and sales tax > Prepayment journal voucher fields group.
Select the new posting profile in the "Posting profile with prepayment journal voucher" field. Also, check the "Sales tax on prepayment journal voucher" if you want sales tax to be deducted from the payment received. Sales tax will be adjusted when the invoice is created and settled against the prepayment only if the amount o the invoice is more than the prepayment itself.
Before posting the journal make sure to flag the payment as prepayment. For that, on the journal go to the "Payment" tab and mark the check box "Prepayment journal voucher". By marking this check box, the posting profile will automatically be changed to the prepayment posting profile previously selected in the parameters.
Once all the information related to the receipt (amount, receipt number, date...) are entered, post the journal and verify the voucher. The result should be DB to cash and CR to A/R prepayment account selected on the new posting profile.
Once you are ready to invoice the customer, create a new invoice as a free text or sales order invoice and post. Validate that the voucher is DB A/R and CR revenue.
At that point A/R and A/R prepayment do have open balances and the invoice and the payment transactions are open.
To close both transactions and zero out A/R and A/R prepayment, go to AR > All customers > search for your customer and click on Collect > Settled open transactions
You will see the invoice and the prepayment transactions, mark them both and click on Update button.
The result of the settlement is a new posted voucher with DB A/R prepayment and CR A/R. Both A/R are zeroed out and invoice and payment transactions are settled and closed.
Wednesday, April 30, 2014
Consolidation in Management Reporter with MR CU8
Can't help myself but talk about consolidation again! It's a hot subject in our company so I wanted to share my experience.
Before upgrading Management Reported to their latest version CU8, I was doing our consolidation and eliminations in AX. I always preferred to have a set of consolidation balances in a trial balance that I can look at in AX. Also, Management Reporter was not mature to offer a better process.
Recently we upgraded to Management Reporter CU8 which is the latest version at the moment and I just started to build our consolidated trial balance as a report. Here are few ideas on how it works:
1) The first element to setup is your reporting tree. The reporting tree will have the list of all the companies you need to consolidate. To create a new reporting tree in MR, click on File > New > Reporting Tree Definition. Then manually select the companies you want to add in your tree by clicking in the Company column and selecting in the drop down. Manually enter the Unit Name and Unit Description of what you will want to see in your reports.
You want to leave the first line that is automatically created when you create a new reporting tree definition. This row will allow you to run your report for all companies, useful if you don't create a side by side report (view side by side the companies, see below).
2) The second element to setup is the row definition. The row definition will have the list of all the accounts you want to display on your report. To create a new row definition in MR, click on File > New > Row Definition. Below is a simple example of 2 accounts and a total row for the sum of the 2 accounts. The purpose of this post is not to go through all the options on the row definition.
Do not forget to setup the category account on your retained earnings account in AX in order for MR to calculate the balance correctly.
In your row definition you will need to setup a row for the currency translation adjustment if you are dealing with entities in different currencies. I will write a new post soon to explain how to do that.
3) Then you need to decide if you wand to view you consolidated companies side by side or a single company at a time. This will define the format of your column definition. I will talk about the side by side format. However, the single company at a time view is simpler and only requires to use the reporting tree.
In your column definition, add one column per company you want to see on your report. The screen shot below shows an example with 4 companies side by side, the total pre-elimination, the elimination entries and the consolidation total.
For each company column, select the reporting unit from your reporting tree to specify which company to pull the number from.
The elimination entries are coming from the elimination company in AX. This is not mandatory to book the elimination entries in AX, you can eliminate on the report directly. However, in our case, we have to eliminate specific amounts and we wanted to keep track of those entries in AX for audit purposes.
When it's time to execute your report, make sure to select the correct row and column definitions. Then select the reporting tree you used in your column definition under Tree Type and Tree.
I hope this will help you create your consolidation reports in Management Reporter without having to run the consolidation in AX. Let me know if you have any questions and enjoy!
Before upgrading Management Reported to their latest version CU8, I was doing our consolidation and eliminations in AX. I always preferred to have a set of consolidation balances in a trial balance that I can look at in AX. Also, Management Reporter was not mature to offer a better process.
Recently we upgraded to Management Reporter CU8 which is the latest version at the moment and I just started to build our consolidated trial balance as a report. Here are few ideas on how it works:
1) The first element to setup is your reporting tree. The reporting tree will have the list of all the companies you need to consolidate. To create a new reporting tree in MR, click on File > New > Reporting Tree Definition. Then manually select the companies you want to add in your tree by clicking in the Company column and selecting in the drop down. Manually enter the Unit Name and Unit Description of what you will want to see in your reports.
You want to leave the first line that is automatically created when you create a new reporting tree definition. This row will allow you to run your report for all companies, useful if you don't create a side by side report (view side by side the companies, see below).
2) The second element to setup is the row definition. The row definition will have the list of all the accounts you want to display on your report. To create a new row definition in MR, click on File > New > Row Definition. Below is a simple example of 2 accounts and a total row for the sum of the 2 accounts. The purpose of this post is not to go through all the options on the row definition.
In your row definition you will need to setup a row for the currency translation adjustment if you are dealing with entities in different currencies. I will write a new post soon to explain how to do that.
3) Then you need to decide if you wand to view you consolidated companies side by side or a single company at a time. This will define the format of your column definition. I will talk about the side by side format. However, the single company at a time view is simpler and only requires to use the reporting tree.
In your column definition, add one column per company you want to see on your report. The screen shot below shows an example with 4 companies side by side, the total pre-elimination, the elimination entries and the consolidation total.
For each company column, select the reporting unit from your reporting tree to specify which company to pull the number from.
The elimination entries are coming from the elimination company in AX. This is not mandatory to book the elimination entries in AX, you can eliminate on the report directly. However, in our case, we have to eliminate specific amounts and we wanted to keep track of those entries in AX for audit purposes.
When it's time to execute your report, make sure to select the correct row and column definitions. Then select the reporting tree you used in your column definition under Tree Type and Tree.
I hope this will help you create your consolidation reports in Management Reporter without having to run the consolidation in AX. Let me know if you have any questions and enjoy!
Tuesday, March 11, 2014
Management Reporter CU8 currency conversion
I feel that my blog should be renamed "everything you need to know about currency translation" as I have been only writing about this topic since I started. Well it's probably because I am dealing with this on a daily basis and I am still discovering new features everyday!
So today I wanted to share with you how Management Reporter (MR) handles currency translation.
MR R2 CU6 and CU7/CU8 (the latest) are handling currency conversion much differently.
The CU6 version does currency conversion in AX using the reporting currency as opposed to CU7 or CU8 which does the currency conversion in MR directly, I am going to explain both setup:
Management Reporter MR CU6 and prior:
In order to generate reports in a different currency than the one setup as accounting currency in the company in AX, you have to setup a reporting currency or create a consolidation company in the currency you want to report on.
- The reporting currency has to be defined when the company is created and cannot be changed once transactions are posted. To change it you will need to use the periodic function located under GL > Periodic > Currency conversions > Ledger reporting currency conversion.
Once transactions are posted, you can create reports in Management Reporter and force the currency you want to display using the currency display drop down on the column definition of your report. You will have the option to pick the accounting currency or the reporting currency.
Management Reporter MR CU7 and after:
In the newest versions of Management Reporter, the currency translation happens "on the fly" when the report gets generated. It is now possible to display a report in any currency as long as a rate is setup in AX.
The first step to have this work is to setup each main account with the currency conversion method MR will use. Go to General Ledger > Main Account and under the Management Reporter fields group.
The exchange rate type will point to the correct set of rates that must be used. Remember that you can save several sets of rates (daily rates, historical rates, spot rates...). Refer to previous post on this blog for this. Then the currency translation type will specify how to use this rate
This setup has to be made on each account of your COA. Main account templates could be used in order to mass update your COA (General Ledger > Setup > Chart of Accounts > Main Account Templates)
Also, retained earnings account should be setup in order for the balance to be calculated correctly. The main account category of the retained earnings account must be set as "RETEARN". If manual entries are posted to the retained earnings account, Management Reported will not calculate the balances correctly. A separate account must be created to handle manual adjustments to the retained earnings balance.
Upon execution of the report in Management Reporter, the balances will be displayed in the currency of the company by default. Then you can switch to any currency you want by using the web viewer (change your options in MR to make sure you use the web viewer, the "Use Management Reporter Report Viewer as default viewer" should be unchecked).
In the web viewer click the Currency button and select the currency to show. It might take few minutes to display the data as MR has to translate those numbers based on the setup.
I hope this post will help you and as usual if you need more information or have any questions, I will be more than happy to help :) Enjoy!
So today I wanted to share with you how Management Reporter (MR) handles currency translation.
MR R2 CU6 and CU7/CU8 (the latest) are handling currency conversion much differently.
The CU6 version does currency conversion in AX using the reporting currency as opposed to CU7 or CU8 which does the currency conversion in MR directly, I am going to explain both setup:
Management Reporter MR CU6 and prior:
In order to generate reports in a different currency than the one setup as accounting currency in the company in AX, you have to setup a reporting currency or create a consolidation company in the currency you want to report on.
- The reporting currency has to be defined when the company is created and cannot be changed once transactions are posted. To change it you will need to use the periodic function located under GL > Periodic > Currency conversions > Ledger reporting currency conversion.
Once transactions are posted, you can create reports in Management Reporter and force the currency you want to display using the currency display drop down on the column definition of your report. You will have the option to pick the accounting currency or the reporting currency.
- The second option is to create a consolidation company in AX in the currency you want to report on. Consolidate all your transactions by specifying which exchange rates to use (refer to previous post on this blog on how to execute consolidation). Then in Management Reporter you can create a report and run it for the consolidation company (no need to specify the currency to display as it will take the accounting currency of the company by default)
Management Reporter MR CU7 and after:
In the newest versions of Management Reporter, the currency translation happens "on the fly" when the report gets generated. It is now possible to display a report in any currency as long as a rate is setup in AX.
The first step to have this work is to setup each main account with the currency conversion method MR will use. Go to General Ledger > Main Account and under the Management Reporter fields group.
The exchange rate type will point to the correct set of rates that must be used. Remember that you can save several sets of rates (daily rates, historical rates, spot rates...). Refer to previous post on this blog for this. Then the currency translation type will specify how to use this rate
The currency transaction types are:
- Average: This calculates an average rate based on all the rates in effect for the period. The formula used is (Total of exchange rates/Number of exchange rates)
- Weighted average: This also calculates an average rate based on all the rates in effect for the period and in addition takes into account the number of days each rate was effective. The formula is as follows (Exchange rate * Days in effect)/Days in period
- Current: This will use the rate that is effective on or before the date of the report or column in Management Reporter
- Transaction date: This method is used for fixed assets accounts. The exchange rate used is the rate on the day the asset was acquired. If a rate is not entered for that date, the rate used is the previously entered rate closest to the asset acquisition date.
This setup has to be made on each account of your COA. Main account templates could be used in order to mass update your COA (General Ledger > Setup > Chart of Accounts > Main Account Templates)
Also, retained earnings account should be setup in order for the balance to be calculated correctly. The main account category of the retained earnings account must be set as "RETEARN". If manual entries are posted to the retained earnings account, Management Reported will not calculate the balances correctly. A separate account must be created to handle manual adjustments to the retained earnings balance.
Upon execution of the report in Management Reporter, the balances will be displayed in the currency of the company by default. Then you can switch to any currency you want by using the web viewer (change your options in MR to make sure you use the web viewer, the "Use Management Reporter Report Viewer as default viewer" should be unchecked).
In the web viewer click the Currency button and select the currency to show. It might take few minutes to display the data as MR has to translate those numbers based on the setup.
I hope this post will help you and as usual if you need more information or have any questions, I will be more than happy to help :) Enjoy!
Thursday, February 20, 2014
Foreign Currency Revaluation
The foreign currency revaluation periodic function is used to recalculate currency amounts based on a new exchange rate. At the time of the transaction, the system uses the spot rate setup in the currency exchange rate table. At month end, the currency amounts should be reevaluated based on the month end rate (for BS accounts) or the monthly average rate (for P&L accounts).
Also, if your company is setup with a reporting currency, this function must be used to recalculate correctly the reporting currency amounts. One example is to reevaluate the open balances with the correct exchange rate (see previous post on this blog to understand why the open balances are not properly translated).
Before the function can be executed, the exchange gain/loss accounts must be setup. Ensure those accounts are setup properly before executing the function otherwise the adjustments will be posted to the same account as the original transaction. This will not adjust the balance of your trial balance and also the adjustments will not be updated if the setup is changed after the fact, you would have to do a manual reclass. There are two areas to setup those accounts:
- General Ledger > Setup > Ledger: this setup is company wide and not specific to a currency. Specify the realized gain and loss accounts for transactions that are fully settled and setup the unrealized gain and loss accounts for transactions that are open (invoices not paid for example).
- General Ledger > Setup > Currency > Currency revaluation accounts. First select the currency you want to setup the accounts for, this will override the setup on the ledger form (see above).
The next step is to setup the exchange rate that the function will use to reevaluate. By default, the rate used is the one setup in the table selected on the ledger form. For BS accounts, it will use the last rate setup for the period so nothing should be changed, however for P&L we need to calculate the average of the period.
The last step is to setup the number sequence that the system will use to post the adjustments entries. This is important for audit purpose since those transactions are system generated.
Go to General Ledger > Setup > General ledger parameters > Number Sequences and select the Foreign currency revaluation number sequence. I used "GL-FCR-" as a prefix so I can easily identify those transactions on the account.
Once the setup is completed you can then execute the revaluation. The function is located under General Ledger > Periodic > Foreign currency revaluation:
- The function can be executed either for a specific account or for the entire chart of account. You will want to select a single account when you reevaluate bank accounts for example, this will prevent foreign transactions posted to other accounts to be reevaluated.
However, if you want to reevaluate the reporting currency, you can select the entire chart of account (select the first account of your COA in the from main account and the last account of your COA in the To main account field).
- The from and to dates are used to only reevaluate transactions that have been posted in that period.
- The currency code will be the currency you want to reevaluate. For bank account it would be the currency of the original transaction posted as opposed to reporting currency would be the reporting currency setup on the company and not the currency of the transaction.
- Select which account type you want to reevaluate BS or P&L. This is an extra filter if you enter a range of accounts in the from and to main account but only want to reevaluate the P&L accounts for example. If you want to reevaluate the entire COA then you will check both check boxes.
Before you click Ok make sure the correct exchange rate type is selected on the ledger form so the correct exchange rate will be used. Also, keep in mind that if you modify the default exchange rate type on the ledger form, it will impact the rest of your business if they post transactions, so you might need to run this reevaluation outside of business hours to prevent errors.
Once the function has been executed, you can verify the posted transactions in the account being reevaluated and in the account setup for exchange gain/loss. The voucher of those transactions will be based on the setup of your number sequence previously done.
I hope this will help you and if you have any questions please do not hesitate :)
Also, if your company is setup with a reporting currency, this function must be used to recalculate correctly the reporting currency amounts. One example is to reevaluate the open balances with the correct exchange rate (see previous post on this blog to understand why the open balances are not properly translated).
Before the function can be executed, the exchange gain/loss accounts must be setup. Ensure those accounts are setup properly before executing the function otherwise the adjustments will be posted to the same account as the original transaction. This will not adjust the balance of your trial balance and also the adjustments will not be updated if the setup is changed after the fact, you would have to do a manual reclass. There are two areas to setup those accounts:
- General Ledger > Setup > Ledger: this setup is company wide and not specific to a currency. Specify the realized gain and loss accounts for transactions that are fully settled and setup the unrealized gain and loss accounts for transactions that are open (invoices not paid for example).
- General Ledger > Setup > Currency > Currency revaluation accounts. First select the currency you want to setup the accounts for, this will override the setup on the ledger form (see above).
The next step is to setup the exchange rate that the function will use to reevaluate. By default, the rate used is the one setup in the table selected on the ledger form. For BS accounts, it will use the last rate setup for the period so nothing should be changed, however for P&L we need to calculate the average of the period.
You will need to calculate your average off system and manually add it to the currency exchange rates type table found under General Ledger > Setup > Currency > Currency exchange rates.
Create a new exchange rate type to make sure the spot rates are not overridden and manually add the rate for the period and the currency in that new exchange rate type.
The last step is to setup the number sequence that the system will use to post the adjustments entries. This is important for audit purpose since those transactions are system generated.
Go to General Ledger > Setup > General ledger parameters > Number Sequences and select the Foreign currency revaluation number sequence. I used "GL-FCR-" as a prefix so I can easily identify those transactions on the account.
Once the setup is completed you can then execute the revaluation. The function is located under General Ledger > Periodic > Foreign currency revaluation:
- The function can be executed either for a specific account or for the entire chart of account. You will want to select a single account when you reevaluate bank accounts for example, this will prevent foreign transactions posted to other accounts to be reevaluated.
However, if you want to reevaluate the reporting currency, you can select the entire chart of account (select the first account of your COA in the from main account and the last account of your COA in the To main account field).
- The from and to dates are used to only reevaluate transactions that have been posted in that period.
- The currency code will be the currency you want to reevaluate. For bank account it would be the currency of the original transaction posted as opposed to reporting currency would be the reporting currency setup on the company and not the currency of the transaction.
- Select which account type you want to reevaluate BS or P&L. This is an extra filter if you enter a range of accounts in the from and to main account but only want to reevaluate the P&L accounts for example. If you want to reevaluate the entire COA then you will check both check boxes.
Before you click Ok make sure the correct exchange rate type is selected on the ledger form so the correct exchange rate will be used. Also, keep in mind that if you modify the default exchange rate type on the ledger form, it will impact the rest of your business if they post transactions, so you might need to run this reevaluation outside of business hours to prevent errors.
Once the function has been executed, you can verify the posted transactions in the account being reevaluated and in the account setup for exchange gain/loss. The voucher of those transactions will be based on the setup of your number sequence previously done.
I hope this will help you and if you have any questions please do not hesitate :)
Tuesday, January 21, 2014
Consolidation in AX 2012 - Currency Translation
Let's review today how to setup currency translation on the consolidate [Online] process in AX 2012. I would like to share this with you because it took me some time to understand how the calculation was done especially for the reporting currency to come up correctly. I have several foreign entities setup with foreign currency and I am using USD as my reporting currency in order to pull reports from AX and from Management Reporter.
The first set of setup that needs to be done is the different rates that will be used during the consolidation. The rate can be different depending on the type of the account, balance sheet accounts usually use an average rate compare to profit and loss accounts that usually use historical rates.
The consolidate online form in AX 2012 offers the capability to either manually enter the rate to use during consolidation or to use the exchange rate type and exchange rate tables. I found very useful to actually use the tables so the users can always refer back to what rates was used for what and when. If you do manually enter the rates on the consolidation form, it will not save the rate for future reference.
The exchange rate type is setup under GL > Setup > Currency < Exchange rate types.
Then for each exchange rate type, you will need to setup the rate for the period it is valid for under GL > Setup > Currency > Currency Exchange Rates. Remember that the Start date is the effective date so if you are to calculate an end of month rate, the start date should be set as the first of the month in order to reevaluate all the transactions of that month with the end of month rate. On the picture below you can see that the rates start on 10/1/12 and 10/1/13.
Note: Up to CU6, AX 2012 doesn't offer any functionality to calculate an average rate. You will have to manually calculate it and enter it in an exchange rate type you call average in order to use it during the consolidation in AX. In CU7, AX will auto-calculate the average rate for the period but the consolidation has to happen in Management Report and not in AX, I will create a post on this new process soon.
Once all the rates are setup in the exchange rate tables, you will need to specify the accounts and tell which exchange rate type it should use in the consolidation form. The From and To account can be a range of accounts or a single account (you would specify the same account in the two fields).
Once you select the exchange rate type, the rate that the consolidation will use is displayed. This rate is selected based on the consolidation period selected on the Criteria tab of the consolidation form.
Once all the accounts are setup, you can click Ok to execute the consolidation. Refer to the previous post for the rest of the setup.
The consolidation process will first convert the amount in the consolidation currency and then sum the transactions and generate a consolidation entry based on the grouping of financial dimensions that should be brought over in the consolidation company. If no dimension are brought over then only one entry will be created per account.
The fact that the amount gets translated on every single transaction before it gets summed is creating currency evaluation errors. The sum of converted amounts doesn't equal to the translation of the sum of those amounts. I know this is hard to picture so let's review an example:
In the table above you can see that each line is using an exchange rate that is in range of a CAN/USD exchange rate but the last line which is the sum of all the transactions (and the consolidation entry that AX will create) generates a USD value that is in fact wrong.
The translation of the currency should happen on the total and not on single transaction when the consolidation happens. In order to work around this, the foreign currency revaluation function can be executed. This will reevaluate the consolidation entries and apply the correct rate on the total.
The foreign currency revaluation function is located under GL > Periodic > Foreign currency revaluation. In my example, I want to reevaluate the USD value only since the transaction in the local foreign currency is created correctly (just the sum of the original transactions in the foreign currency). Only the USD value is wrong due to the conversion happening on each transactions instead of the sum of all those transactions.
Once executed, you can see how AX created a new entry in USD only to adjust the difference created during the consolidation.
This process can also be executed for the reporting currency to be corrected evaluated in foreign entities, we will talk about this in another post if you want more information :)
I hope you enjoyed this post, please leave comments or ask any questions if you want more details! See you soon!
The first set of setup that needs to be done is the different rates that will be used during the consolidation. The rate can be different depending on the type of the account, balance sheet accounts usually use an average rate compare to profit and loss accounts that usually use historical rates.
The consolidate online form in AX 2012 offers the capability to either manually enter the rate to use during consolidation or to use the exchange rate type and exchange rate tables. I found very useful to actually use the tables so the users can always refer back to what rates was used for what and when. If you do manually enter the rates on the consolidation form, it will not save the rate for future reference.
The exchange rate type is setup under GL > Setup > Currency < Exchange rate types.
Then for each exchange rate type, you will need to setup the rate for the period it is valid for under GL > Setup > Currency > Currency Exchange Rates. Remember that the Start date is the effective date so if you are to calculate an end of month rate, the start date should be set as the first of the month in order to reevaluate all the transactions of that month with the end of month rate. On the picture below you can see that the rates start on 10/1/12 and 10/1/13.
Note: Up to CU6, AX 2012 doesn't offer any functionality to calculate an average rate. You will have to manually calculate it and enter it in an exchange rate type you call average in order to use it during the consolidation in AX. In CU7, AX will auto-calculate the average rate for the period but the consolidation has to happen in Management Report and not in AX, I will create a post on this new process soon.
Once all the rates are setup in the exchange rate tables, you will need to specify the accounts and tell which exchange rate type it should use in the consolidation form. The From and To account can be a range of accounts or a single account (you would specify the same account in the two fields).
Once you select the exchange rate type, the rate that the consolidation will use is displayed. This rate is selected based on the consolidation period selected on the Criteria tab of the consolidation form.
Once all the accounts are setup, you can click Ok to execute the consolidation. Refer to the previous post for the rest of the setup.
The consolidation process will first convert the amount in the consolidation currency and then sum the transactions and generate a consolidation entry based on the grouping of financial dimensions that should be brought over in the consolidation company. If no dimension are brought over then only one entry will be created per account.
The fact that the amount gets translated on every single transaction before it gets summed is creating currency evaluation errors. The sum of converted amounts doesn't equal to the translation of the sum of those amounts. I know this is hard to picture so let's review an example:
In the table above you can see that each line is using an exchange rate that is in range of a CAN/USD exchange rate but the last line which is the sum of all the transactions (and the consolidation entry that AX will create) generates a USD value that is in fact wrong.
The translation of the currency should happen on the total and not on single transaction when the consolidation happens. In order to work around this, the foreign currency revaluation function can be executed. This will reevaluate the consolidation entries and apply the correct rate on the total.
The foreign currency revaluation function is located under GL > Periodic > Foreign currency revaluation. In my example, I want to reevaluate the USD value only since the transaction in the local foreign currency is created correctly (just the sum of the original transactions in the foreign currency). Only the USD value is wrong due to the conversion happening on each transactions instead of the sum of all those transactions.
Once executed, you can see how AX created a new entry in USD only to adjust the difference created during the consolidation.
This process can also be executed for the reporting currency to be corrected evaluated in foreign entities, we will talk about this in another post if you want more information :)
I hope you enjoyed this post, please leave comments or ask any questions if you want more details! See you soon!
Monday, November 18, 2013
Consolidation in AX 2012 - Introduction and Pre-Requisites
I have been debating between doing consolidation in Dynamics AX or in Management Reporter. I decided to do it in AX since the setup is very basic. Below are some ideas and suggestions on where to start.
Once the entity is created, you will need to flag the company as consolidation entity or elimination entity or both. For that go to Organization administration > Setup > Organization > Legal Entities. Under the General tab you will find two flags "Use for financial consolidation process" and "Use for financial elimination process".
- Consolidation vs Elimination companies. You need to remember that you CANNOT post any manual entries in a consolidation company. So if you do intent to use your consolidation company as your elimination company as well, keep in mind that you won't be able to manually post eliminations. Also, a company cannot be deleted from AX 2012 (although it can in previous versions of AX), make sure to think about your consolidation structure before creating those entities in AX.
Once the entity is created, you will need to flag the company as consolidation entity or elimination entity or both. For that go to Organization administration > Setup > Organization > Legal Entities. Under the General tab you will find two flags "Use for financial consolidation process" and "Use for financial elimination process".
- Consolidation and open balances. If you have been using AX for several years but you just start to execute consolidation in AX, you will need to bring all your activity over in the consolidation company in order to get your open balances. The Online consolidation doesn't have an option to bring opening balances. Opening balances will be created once you execute the year-end close process. So if you do have data like me since 1994, you will need to close almost 20 years before getting your open balances for the consolidation numbers to be correct
- Rebuild balances during consolidation process. I suggest to un-check this option as AX will schedule a job to rebuild all dimension sets you have setup. However, if you don't consolidate at the dimension level, you don't need to rebuild all dimension sets. Also, you might have to run the consolidation several times before verifying the numbers and it's faster to execute the rebuild manually in that case
- Elimination during consolidation process. If you created a different entity for the elimination then this option will not be valid for you since the proposal should be created in the elimination entity and not in the consolidation entity. Also, it's always better to verify the elimination proposal before posting it. Finally, you might need to calculate fixed amount and modify your elimination rules based on the consolidation result before being able to execute the elimination rules.
This is some food for thoughts, I will soon post more details about the consolidation process and the currency conversion. Looking forward for feedback and reviews. Enjoy!
Subscribe to:
Posts (Atom)