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
The master data is prepared and uploaded into Microsoft Dynamics AX (chart of accounts, banks, fixed assets, financial dimensions, vendors, customers, and inventory items )
Maintain high levels of coordination between the controllership and application financial consultants in the design phase, including finalization of mapping between the old chart of accounts (legacy system) and the new chart of accounts
Application financial consultants should ensure the setup of the needed fields in the data collection template he/she will use to upload the opening balance.
The accountant who will fill in the opening balance data collection sheet must understand the fields and how he/she will fill it in
Create a separate journal name under the general ledger journal and voucher number sequence for easier tracking
Create separate journal names under inventory management (movement journal) and voucher number sequence for easier tracking
If adjustments are needed for the opening balance, use the same journal name and voucher
The opening balances of general ledger and sub ledger are uploaded together. Avoid separating their uploads as much as you can
The sub ledger (vendors, and customers) posting profiles should be assigned to opening balance
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
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)
If there are fixed assets that are acquired in a foreign currency, modify the acquisition entry with the currency the exchange rate
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
Identify the GL account balances which are not affected by sub ledger posting profiles
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
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)
Replace the fixed assets accounts by an error account in order to close the amount in the error account which resulted from acquisition transactions
Replace depreciation account by error account in order to close the amount in the error account which resulted from depreciation transactions
If there are balances in a foreign currency, upload the opening balance entry with the currency the exchange rate
Validation
Perform closing voucher to transfer all profit and loss balances to Retained Earnings account
Print trial balance report with closing balance criteria
User Group: Dynamics Arabia
Vote for me in Microsoft Dynamics Most InfluentialPeople: Click Here
