Opening Balance Migration in Microsoft Dynamics AX

Posted: February 9, 2014 in Opening Balance
Tags: , , ,

Friends,

This is my first ever blog or article, writing on Microsoft Dynamics AX. As while implementation of the AX or any ERP system, migration of the opening balances are the most crucial part. On the various AX forums I read a lot of questions related to the migration of the opening balances, so here I decided to share my experience with you all.

When company migrates to Microsoft Dynamics AX from the Legacy System, the most important process is to migrate the opening balances correctly, for that it is very important to take a systematic approach to the process of planning, designing, and executing the migration of data for trial balances and sub-ledgers.

There are some factors which we need to consider before we start importing and posting data. Following are steps which are normally followed:

1              Take the backup the database.

2              Cutoff date of the data.

3              Define the separate journal name for the opening balances so that can be recognize easily in future.

4              Collection of the Trail balance from the legacy system.

5              Define one error or data migration account in Chart of Account (Normally the last account of the Chart of Account i.e. 9999999). After importing the all Opening balances the balance of this account will be zero. And once the Balances got transfer block this account for further transaction.

Once these factors are defined, then we divide the trail balance in five sections as the whole trail balance consists of these elements:

1              Fixed Assets

2              Inventory

3              Vendors

4              Customers

5              Sub- Ledgers and Bank

6              Projects

Out of the five, except Sub- Ledger all we enter from the module, so that the Module and the General Ledger reports will tally.

Fixed Assets

To post the Opening balances for the Fixed Assets, you will have to pass the three journal, first for acquisition of the net book value, second for the acquisition adjustment (to update the cost) and last with the Depreciation adjustment (to update the Accumulated depreciation). All the journals will be passed through the module and the posting will be posted as per the posting Profile.

For example the cost of the Fixed Asset is 1000 and its accumulated depreciation is 200, then its net book value is 800.

First entry, For Acquisition for NBV selects the transaction type “Acquisition” and debits (800) the FA and offset it with the error account. Second entry will be, select the “Acquisition Adjustment” type and debit (200) the Fixed Asset and credit the Error account, third and the final entry will, select the transaction type “Depreciation Adjustment” and credit (200) the fixed asset and debit the error account.

So the cost of the assets 1000 (NBV + Acquisition Adjustment) and Accumulated Depreciation will be 200. For putting the opening balances opening we should do in this manner otherwise the reports (Like Fixed Assets Balance report) will not come correct and the Module GL will not tally.

The sum of the Journal should be equal to the sum of the Debtors account in the trail balance of legacy system.

 

Inventory Opening Balances

For the migration of the Inventory, we will use Movement Journal. In the movement journal we will define the items with their cost, quantity and its physical dimensions (Site, warehouse, size etc.) and the date of posting. Offset account will be the error account, when we post the journal, Items (Inventory) get debited and the offset account gets credited. Based on the Item group attached to the item, ledger account gets affected (Debited) in the GL. By doing so the Inventory increase by its Quantity and the value, and directly affect the value in the GL.

The sum of the Journal should be equal to the sum of the inventory account in the trail balance of legacy system.

Vendor/Creditors (Accounts Payable)

Vendors Opening Balances will be enter through the Invoice Journal or through the GL, vendor wise. While entering the Opening balances through the GL, should be careful as the account type will selected as the vendor. While entering the Balances, in the journal we have to select the Vendor Account and put the amount in the Credit field and offset the Error account, which is debit. The balances transfer in the GL as per the Vendor Grouping and the Posting profile.

The sum of the Journal should be equal to the sum of the Creditors account in the trail balance of legacy system.

Customer/Debtors (Accounts Receivables)

Customer Opening Balances will be enter through the Free Text Invoice or through the GL, Customer wise. While entering the Opening balances through the GL, should be careful as the account type will selected as the Customer. While entering the Balances, in the journal we have to select the Customer Account and put the amount in the Debit field and offset the Error account, which is Credit. The balances transfer in the GL as per the Vendor Grouping and the Posting profile.

The sum of the Journal should be equal to the sum of the Debtors account in the trail balance of legacy system.

 

Sub Ledger and Bank

For the Sub- Ledger, trail balance of the legacy system will be enter as it is, which is debit make it debit and credit as credit, this entry will be pass through the Ledger General journal.

Bank Opening Balance will be enter through the GL, just select the account type as the Bank and put the value Debit/Credit.

Once these entries passed, there will be the difference value (voucher imbalance) while passing the Journal for the Sub- Ledger, and that difference will equal to the balance of the Error Account. So, that value will be entered in the suspense account and the balance of the suspense account will be zero.

Projects

To enter the Opening Balances for the Projects, we can directly use the “Beginning Balance ”  journal. In the Journal just select the transaction type (Hour/Item/Expenses/Fee) ,Project Category Cost and Price quantity and post it.. As by posting of the journal there is no impact on the General Ledger only in the module.

 

 

After Importing the balances generate the trail balance from the AX that should tally the trail balance of the Legacy system.

So, in this way I used to migrate the Opening Balances at my clients.

Please share your views and comments.

Advertisements
Comments
  1. Paul says:

    What about project opening balance?

    Like

    • Hi Paul

      Opening balance for the project we enter from the Project module by creating the projects first then from the setup section Beginning balance and enter the opening balance by transaction type (Hour,Expense,Fee, item and On Account) and post it.

      Like

  2. Mohammed Aleem Akhtar says:

    Hello Imran,

    Good effort. But want to bring to your notice some of the key points.

    1. For Fixed Asset opening balance, need not pass acquisition adjustment. You could just acquire it with the acquisition price of 1000 in your example. Pass the depreciation adjustment of 200 so the net book value becomes 800. Remaining depreciation run with the service life remaining. no need to pass the acquisition adjustment. The steps included is just a work around.

    2. Always pass the opening balance from the General journal voucher. Though you get the correct accounts through Invoice or free text invoice journals, its a standard practice to pass the OB through the General Journal.

    Cheers,
    Aleem A

    Like

    • Thanks for giving your time in reading the blog and for your feedback.

      Regarding your first point, i want to know when we pass the depreciation adjustment what account we will offset, as with my work around in adjustments i am using the suspense will get null after posting both journals . And for your second point, i am 100% agree because anyhow it will give the same impact.

      Like

  3. Rehan says:

    Dear Imran,

    You did a very good job. Can you please share the way you import the balances. I mean by notepad, excel addin etc.

    Kind Regards
    Rehan Habib

    Like

  4. Hemant says:

    Hi Imran,
    Your notes is very valuable, thanks for direction

    Thanks
    Hemant

    Like

  5. Ajay Jha says:

    Hi Imran,

    Good post.

    Can you highlight some information on open customer/vendor invoices. Looking for more options.

    Thanks,
    Ajay Jha

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s