When a company migrates to Microsoft Dynamics AX from a legacy ERP solution, one of the important data migration tasks is the creating opening balances in the new Dynamics AX system based on the closing balances of the previous closed period (often the fiscal year) from the legacy system. In order to ensure accuracy in your Dynamics AX opening balances, it is important to take a systematic approach to the process of planning, designing, and executing the migration of data for trial balances and sub-ledgers, as well as validation and reconciliation of these elements along with general ledger, sub ledger, and financial dimensions. In this article I will lay out the process and the elements involved in creating new opening balances in MS Dynamics AX coming from a legacy system. The opening balances elements are:

• Trial balance The trial balances (General ledger accounts) consist of the balance accounts that move their balances from one year to another, and profit and loss accounts which represent the income statement results and do not move to another year.

• Sub ledger The sub ledgers are fixed assets, banks, vendors, customers, and items. The sub ledgers are linked to the chart of accounts through the posting profile setup.

• Validation and reconciliation The balance of sub ledgers (fixed assets, banks, vendors, customers, and items) must represent the balance of general ledger accounts (trial balance), with respect to the financial dimensions (business units, department, and purpose) balance in case it is used through sub modules. The controllership and financial consultants should finalize and validate the design and deployment of financial dimensions or the dimension hierarchy set.

Data integrity between the General ledger, sub ledger, and financial dimension is one of the main objectives of Enterprise Resource Planning (ERP), and it must be considered from day one of the opening balances since the opening balance transactions may affect the daily transactions after going live.

Opening Balances Best Practices

After various attempts to upload opening balances to Microsoft Dynamics AX, I have followed an approach where I have separated the upload of ledger accounts (trial balance) and sub ledgers (vendors, Customers, Bank, Inventory, and Fixed Assets) by using an Error Account in sub ledgers entries as debit and credit sides (Dr. Error account, Cr. Error account) only to balance the sub ledger.

This approach took much time and effort in order to ensure that the GL account and sub ledgers are reconciled, as well as to ensure the balance of Error account is zero. I have worked with my team, and controllership to develop a series of best practices that successfully guided us through the process:

Planning and design

Execution The opening balance will be executed in three waves (Fixed assets acquisition and depreciation, items, and trial balance with sub ledger). Here are the methods we utilized to ensure correct execution. Wave I Fixed assets

1. Fixed assets acquisition - The fixed assets acquisition will be executed through a fixed assets acquisition proposal. The posting profile setup will generate the following entry (Dr. Fixed assets accounts, Cr. Error account)
2. If there are fixed assets that are acquired in a foreign currency, modify the acquisition entry with the currency the exchange rate
3. Fixed assets depreciation - The fixed assets depreciation will be executed through a fixed assets depreciation proposal. The posting profile setup will generate the following entry (Dr. Error account, Cr. Accumulated depreciation)

Wave II inventory items Inventory opening balance will be uploaded from movement journal (inventory sub ledger) the posting profile setup will generate the following entry (Dr. Inventory, Cr. Error account) Wave III trial balance and sub ledger

1. Identify the GL account balances which are not affected by sub ledger posting profiles
2. GL accounts that are affected by sub ledgers will be broken down by their relevant sub ledger (Banks, vendors, and customers) and the accounts will be affected directly by the sub ledger posting profile
3. Make sure that the assigned posting profile is the proper posting profile for each customer/vendor for two reasons. First, make sure the customer opening balance hits the right account. Second, ensure that the entries occurred during the settlement process (during year operations)
4. Replace the fixed assets accounts by an error account in order to close the amount in the error account which resulted from acquisition transactions
5. Replace depreciation account by error account in order to close the amount in the error account which resulted from depreciation transactions
6. If there are balances in a foreign currency, upload the opening balance entry with the currency the exchange rate

Validation

1. Perform closing voucher to transfer all profit and loss balances to Retained Earnings account
2. Print trial balance report with closing balance criteria