Microsoft Dynamics AX and IFRSS

Native Support for Parallel Accounting for transition to IFRS

Microsoft Dynamics AX have posting layers in the General Ledger and for consolidation that allow it to be one of the unique ERPs that allow for parallel accounting that is needed as part of a transition to IFRS. Microsoft Dynamics AX has 3 layers – one primary and two adjustment layers. The layers are Current, Operations and Tax. For example, the diagram below represents a USA public company listed in the United States that has a public holding company in Europe and needs to report in 3 formats: Tax for the IRS, US GAAP for the SEC and public filings in the United States and IFRS as adopted by the EU for group external reporting. Layer 1 is the ‘Current’ Layer in Microsoft Dynamics AX holds the US GAAP postings. Layer 2 is the Operations Layer and has any IFRS Delta postings. Fixed Asset can be posted in full for all layers so those do not require any deltas as they can automatically post into multiple layers by setting up a dependency through Value models. Effectively this allows the Fixed Asset Sub Ledger to have a full US GAAP, IFRS and Tax set of books in it. Any Tax adjustments or other Tax posting are made in the Tax Layer e.g. Section 179 accelerated depreciation for qualifying assets.

Figure: Microsoft Dynamics AX provides support for multiple posting layers such as current, operations and tax that provide it with unique capability to have native support for parallel accounting. Source of this screen shot is Microsoft Corporation.

Microsoft Dynamics AX has the ability to have different journals post to different layers. For example, I can have a regular GL Journal that posts to the Current (US GAAP layer in our example) and an IFRS General Ledger Journal that posts to the Operations (IFRS Layer in our example)

Figure: Microsoft Dynamics AX support the transition to IFRS with ability to support parallel accounting with different posting layers. In this screenshot, the IFRS GL Journal is posting to the Operations Layer for any IFRS Delta postings

The reporting structure in Microsoft Dynamics AX then allows you to define on the report where you would like the numbers to come from. We can setup a report for US GAAP by choosing the layer as Current and then we can use the same report or a copy of it by setting the source posting layer as Operations plus current and it would show us the IFRS numbers.

Figure: Cash Flow report in Microsoft Dynamics AX showing that the posting layer can be selected on the report definition allowing the same report definition to be used to get numbers in different accounting standards such as US GAAP, IFRS and Tax thus simplifying accounting and reporting greatly.

First Time adoption of IFRS (IAS 1)

IAS 1 requires that businesses adopting IFRS maintain a comparative year and also requires that an ERP system should be able to load or post manual adjustments for the IFRS opening balance in either the last period before the transition date and then roll-forward the IFRS opening balance or in the period of the transition date with a specific identifier in order to track and distinguish the opening balance adjustments for IFRS from regular transactions.

From a Financial system stand-point this requires the following

- Recording of opening balances

- Multiple year over year reporting required

- Adjusting of opening balances under IFRS

- Ability to separately identify normal accounting transactions from those for IFRS adjustments

In Microsoft Dynamics AX 2012, the opening transactions can be posted to a unique period (the opening period) of the fiscal year through import of transactions of by transferring prior year ending balances. Prior year ending balances are transferred to the opening period. The opening period is a special period compared with the normal transaction periods. The opening period is usually the first period of a new fiscal year (for example,it covers only the first day).

Microsoft Dynamics AX 2012 alsohas a closing periodwhere the closing transactions can be posted. A closing sheet can be created to create closing transactions. When creating the closing sheet, manual adjustments can be created and posted. Multiple closing sheets can be created for different purposes, and each can be uniquely identified. E.g. you can have a regular closing sheet and then another one to record the IFRS transition adjustments. For example, the screen shot below shows that the GAAP Closing sheet is posted in the current layer while the IFRS Closing sheet with relevant adjustments is posting to the Operations Layer.

Figure: The Closing Sheet in Microsoft Dynamics AX can post to different layers to record adjusting transactions for the IFRS and be able to identify them separately

In addition IAS1 specifies the need for two statements of comprehensive income, two separate income statements, two statements of cash flows, two statements of changes in equity, related notes, including comparative information. All of this is achieved by having the different layers and running reports either via Management Reporter or the Dynamics AX Financial Statements and selecting the appropriate layer or combination of layers as illustrated in the Cash flow example above.

XBRL Support:

The IFRS has adopted the XBRL Taxonomy as its standard for electronic exchange of financial data prepared in accordance with the IFRSs. TheIFRS Foundation XBRL Team is responsible for developing and maintaining the XBRL representation of the IFRSs, known as the IFRS Taxonomy. Both IFRSs and XBRL are intended to standardize financial reporting in order to promote transparency and to improve the quality and comparability of business information, therefore the two form a perfect partnership. As shown in the screen shot below, Microsoft Dynamics AX has native support for XBRL with the ability to natively to import an XBRL taxonomy and use it very quickly. The IFRS in March 2011 published 12 sample XBRL samples including Statements of Financial Position,Comprehensive Income, andChanges in Equity, Statement of Cash Flows, Employee benefits obligations, Consolidated and separate Statements of Financial Position. Microsoft Dynamics AX 2012 can very quickly allow you to take these examples and start reporting in XBRL.

Figure: Microsoft Dynamics AX provides native support for XBRL

In addition Microsoft Dynamics AX 2012 provides support for drag and drop type editors for the presentation of reports, calculations, labels etc. that make XBRL a fairly straight forward process.

Figure: XBRL Taxonomy details for the Presentation of Financial Statements in Microsoft Dynamics AX 2012

Figure: Formulas in XBRL Taxonomy for Calculation for Financial Statements in Microsoft Dynamics AX 2012

The imported XBRL Taxonomy works with the Microsoft Dynamics AX Reports Structure Designer and the Management Reporter Tool that can be used to generated reports. The screen shot below shows the native structure designer with the XBRL Income Statement.

Figure: Microsoft Dynamics AX Report Row Structure Designer showing the XBRL Taxonomy that was imported from the xbrl.org. The first screen shot shows the row definition and the second one show the simulation with the row labels (notice the simulate check box is checked which forces it to simulate the structure of a published report)

Posting layer AX 2012

There are three posting layers for transactions.

• Current is the default layer. This is used for transactions that are part of the periodic financial reporting.
• Operations is used for other transactions that are part of the periodic financial reporting for internal purposes.
• Tax is used for transactions that are not part of the external or internal reporting, but that are for recording special tax transactions, such as tax depreciation for fixed assets.

You can post only one transaction type in a journal. You can select a posting layer in the Posting layer field on the General tab in the Journal names form to create at least one journal name for each posting layer that you use.

In the Value models form, the posting layer is displayed for each value model that is attached to the fixed asset. You cannot change the posting layer.

You can record fixed asset transactions in journals only for value models that use the same posting layer as defined for the journal name. For example, if the journal name is for theTax posting layer, the value models for the Current or Operations posting layers are not displayed for the journal because they cannot be posted in this journal.

‘Advanced rule structures’ feature explained AX 2012

One of the most underutilized features of AX 2012 is the advanced rule structure. Many end-users neglect to track additional financial dimensions for fear of cluttering the account structure with additional dimensions that will only be used for one or a few accounts or not wanting to create excess structures resulting in cumbersome setup for other processes (ledgers, budgets, etc.) just to accommodate the financial reporting desires. However, the advanced rule structure functionality circumvents these problems with simple setup and targeted impact which doesn’t affect the account structures themselves but basically appends to them.

For example, let’s assume that a company wants to track their various Notes Receivables but without having to make their chart of accounts too detailed, adding a financial dimension to the balance sheet structure which would unnecessarily complicate every posting in that range, or creating an entirely structure just for this desire. A “catch-all” Purpose dimension could be created and the various Notes could be created and assigned a unique number.

Under General Ledger→ Setup → Chart of accounts→ Advanced rule structures, create a new rule structure, give it an appropriate name, and assign it the allowed values just as you would if setting up an account structure.

You can even choose to allow blanks, if desired. Then Activate the structure.

In the Configure account structures form (under General Ledger→ Setup → Chart of accounts), select the structure that you want to add the advanced rule to and click the Advanced rule button in the action pane.

Create New and give the Advanced rule a name. You can then set the criteria for the rule. In this case, the rule will only apply when a user attempts to post to the 1130, Notes Receivable, account. Then click Add and attach the rule structure previously created. The Advanced rule tells the system when to apply the structure and the structure defines the allowed values when the rule is applied.

When a journal entry or anything else attempts to post to the Notes Receivable account, the structure is appended, and the allowed values (and only the allowed values) will appear as options in the drop down.

If another account that doesn’t meet the advanced rule’s structure is chosen, then the regular account structure is applied. This allows users to track a large amount of financial dimensions without complicating unrelated postings with unnecessary dimensions or cluttering the chart of accounts.

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.

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.

AX 2012 Project Budgeting and Enhancements

Before we begin, let us understand few things about project budgeting in general.

Project budgeting is one of the most important parts of the project planning process. It helps companies monitor and control project revenue and expenditures. By comparing budget and actuals, organizations can have more visibility of how effectively their resources are being utilized, how they can further reallocate money and resources to meet their budgetary goals and finally to track project profitability in financial perspective.

Project budgeting is vital for both public and private sector organizations. While public sector industries are more concerned on minimizing their project expenditures by effective resource utilization, on the other hand private industries (being focus on services) have emphasis on maximizing the revenues and maintaining high profitable margins by optimizing their project costs.

AX2012 project budgeting functionality caters to these various industry specific requirements with its simple yet powerful features as below:

1. Unified Project budget form to enter budget details across project hierarchy like project, subproject & activity etc…
2. Maintain budget details per amounts, categories, activities and further extend it for workers anditems in project budget line details.
3. Budget allocations across the project or fiscal period. If allocated over a period, we can choose to carry forward unused amounts to the next fiscal period.
4. Centralized view of budget transactions in Project budget balances form for the complete project hierarchy in one simple go.
5. Powerful workflow management to handle approval requirements within organization and capabilities of change management to keep a history of the budget revisions.
6. Budget details based on amounts and split between costs and revenues.
7. Project budgeting limits the forecast models that are used for budgeting. Fewer forecast models can help increase consistency in projections.
8. Project budgeting provides additional control options for users. Warnings and Overruns may be allowed along with the default option being Disallow of overruns.
9. In project budget control, actual amounts are subtracted from amounts in the remaining budget. This allows for a clearer audit trail.

Example scenario:

Fun times Club a US based customer wants to construct a boundary wall for one of its play ground and approaches Contoso Consulting USA (USSI) for the project contract. The project manager Kevin Cook estimates the total effort required for this construction and negotiates with Fun times club for the fixed contract value of $250,000 to be paid on project’s completion. Kevin further defines the project budget to control the project costs and assigns his key resources to work on the project. Contoso Consulting USA (USSI) has tie up with one of the sub-contractor in the country Industrial Supplies who supplies the construction items. As the project progresses, kevin tracks the project expenditures and project budget balances and takes the necessary actions.

Let us now set up this scenario in AX2012 by following the below steps:

• Navigate to Project management and accounting=> Setup=> Project management and accounting parameters.
• Click on the Cost control link and enable the Use budget control check-box. This is the default setting which will be carried over to new projects created there after and can be over-ridden from project level. Also setup default forecast models for Original and Remaining cost control types.

• In PMA=> Common=> Project contracts form, create a new project contract with customer Fun times club and create a new fixed-price project with milestone value of $250,000.

• Expand Budget and forecast fast tab, observe that budget control is enabled as per default setup in PMA parameters form.
• Now change the Transaction types controlled to Costs only since we want to control only project costs in this example and leave the Budget overrun default to Disallow overruns only.

• On the project details form, switch to Plan tab and click on Project budget button to define the budget details for this project. Create the budget lines with mandatory category and budget amount details. We can also import budget lines from existing forecast models, project transactions, WBS templates etc…

• Submit the original budget and process it further for workflow approval. (I have enabled auto-approval for testing purpose)
• Once the budget is approved budget funds will get committed against the category. To view this click on Project budget balances button. Here we can view the project budget balances grouped by Transaction type, Category, Category group and cost template roll ups.

• Back on project details change the Project status to In progress as the project execution begins.
• Now the resources start working on the project assignment and Kelly (project accountant) books 150 hours spent on the Design of boundary wall on behalf of these workers. The cost rate of these workers are $180/Hour. Kelly further posts this journal to account for the project cost.

• Now view the project budget balances to see the budget consumption details. To do this, simply from project details form click on Project budget balances button on the Control tab.
• We can observe that the cost amount $27000 ($180*150Hours) is consumed from the total budgeted amount $54000 for Design category and Remaining budget is shown in the project budget balances form.

• For the next month, kelly tries to record more 160 hours being spent (Cost rate: $180/Hour) on the project by workers on boundary wall design and as the cost amount exceeds the budgeted amount, an error being thrown restricting kelly to proceed further. Kelly escalates this to proejct manager Kevin to do the needful as budget amount is exceeding by $1800.

• Now kevin reviews this request and increases budget for Design by $1800. To do this, click onNew revision button from project budget balances form. Specify Revision amount= $1800.
• Kevin submits the budget revision to process for further workflow approval. I have configured auto-approval for budget revision in this company for testing purpose. You may configure this to the concerned person responsible for approval in the organization.

• Click on Project budget balances button and observe that Remaining budget amount is increased as per approved revision amount. That is $27,000 Original + $1800 Revised= $28,800.

• Now kevin alerts kelly about design budget amount being revised and asks her to proceed with recording worker hours. Kelly posts the hour journal successfully now.

• Kevin reviews the budget balances and observes that budget fund amount for Design has been totally consumed.

• In the meantime, to start the construction work on the project Kevin; the project manager raises a PR for procuring construction items. He specifies the quantities which are required to start the initial construction.

• Kevin views the project budget status to check the availability for budget funds against this procurement. To do this, click on Financials=> Project budget status.

Note: This is new in AX2012 R3. We can now display the status of a project budget in purchase requisition forms, purchase order forms, and vendor invoice forms. Therefore, we can easily monitor the remaining available budget for projects. This change also supports budget control and the evaluation of proposed purchases during management review or workflow approval.

• Kevin submits the PR to purchase department for review and approval. Alex, the purchase manager increases the quantities on PR to have more inventory stock and sees the effect on Project budget status.

• Alex approves the PR and converts it into purchase order with its sub-contractor Industrial Supplies.
• The project budget status can be viewed from purchase order using Financials=> Project budget status button on purchase order lines.

• Once the items are received, procurement team registers the product reciept and route it for project’s consumption. Alex enters the invoice details into AX2012 sent by the sub-contractor and even from the vendor invoice form he can view the project budget status using Financials=> Project budget status.

• Once the invoice is posted, we can see the effect on project budget balances as below.

• Also for the future purchases raised by procurement department against the project, the budget funds will be committed automatically and budget balances will get effected.