Home » AX 2012 Financials » Opening Balance Migration in Dynamics AX

Opening Balance Migration in Dynamics AX


Hi Friends,

I have read lot of queries and questions related to opening balance migration. The general questions are:

1. On which date should we post opening balance?

2. Do we need to run the closing process after opening balance is imported?

3. How opening inventory is posted and tallied to balance sheet?

The above mentioned questions are only few. There are many others which i have seen in sites like dynamics community and dynamicsuser.net.

I will try to answer the questions written above and also some important tips which should be helpful.

Data migration and specially opening balance migration is one of the most important process and should be planned carefully. The plan should be data analysis, cut off date of data, journal names and other things depending on the industry and volume of data.

I will be mainly focusing on the opening balance migration.

Opening Balance Migration

We can go-live only when opening balance is migrated, posted and validated successfully. There are some factors which we need to consider before we start importing and posting data. Following are steps which are normally followed:

Thumb Rule: Remember to take the DB backup at every stage.

1. On what date are we migrating the balance: This is a customer based decision and project plan. Let’s take an example where the Go-Live date is 1st Jan. In this case we have two options again. One is either we post on 31 December of the last year on we can post on 1 Jan. Again here also we need to decide as we can post on either of date.

Avoid middle of the month as it’s really difficult to get the balances and otherwise you will be required to post backdated transactions also.

2. Accounts whose balance needs to be posted: Every company is different from another and hence requirement changes. We need to understand the client’s business, look at their financial transactions and then we can decide the financial data migration. For example, inventory is very common in trading and manufacturing industries but not in service industries. Hence we need to finalize the financial data first.

3. Financial data to be posted: After data analysis, we need to figure out the balances which we need to post. For example, we first post inventory and then ledger and sub-ledger balances. Inventory is posted warehouse wise separately. Similarly we create separate journal for AP, AR, GL. Here also, try to create different journals again. If project is being used, it is suggested to post on the project transactions directly. Remember all sub-ledgers are different from other.

4. Inventory data to be posted: Movement journal is used for this. Create separate movement journal for all the warehouses.  

5. Should we run closing after Opening upload: This totally depends on the opening balance migration date and the approach which is suitable for client. Closing can only be run if we are starting AX on the first day of financial year. Assume that the fiscal of client’s starts from 1 Jan and on the same date they are going live. In this case we can migrate the opening balance on 31 Dec, run the inventory closing (if applicable) and then fiscal closing.

If the start day is in between of financial year then we can’t run the fiscal close. We can post the opening balances and start the operation.

6. Opening balance journal name: The best process is to create a dedicated opening balance journal with a separate voucher series. Some company wants it. You can propose this to client.

7. Validation of data posted: Lastly, when the data is migrated, post the data one by one and get it validated. Take the backup frequently. Remember that opening balance is successfully migrated when trial balance matches of AX to legacy system. 

The 999999 Account

I guess account 999999 is most commonly used account for opening balance migration. Let me explain you why we use this account.

When we get the opening balance, the balances are in  Debit side and Credit side. The sum of Debit and Credit will always be 0. This is as per double entry book keeping. You will never get balance where debit balance is different from that of credit.

Another point, when you get the balance from client, look closely at the account type and balance. Asset accounts has Debit balance and Liability has Credit balance. So when we migrate Asset balance, like FA, Bank, AR, Inventory.. we offset it to 999999 account.

Now when we migrate Liability balances like AP, Loans, Accumulated Depr, Equity.. we again offset it to 999999.

This way, when our migration is complete then the net balance in 999999 will be 0. 999999 having 0 balances gives message that the debit and credit balances are equal, however it doesn’t guarantee that.

Anyway, once the balances is migrated, suspend this account for any future transactions.

we actually use 999999 to balance the debits and credits.

Apart from the discussion above, i am also attaching certain links of prominent AX knowledge base sites and these are worth looking at.

http://dynamicsuser.net/forums/p/54636/287593.aspx

http://technet.microsoft.com/en-us/library/aa834497(v=ax.50).aspx

Further if you have any doubts/issues or scenario, please reply here. Let’s discuss it and come to a solution.

Opening balance migration is an endless topic. Please let me know if this post needs any editing or update.

Your comments and suggestions are most welcomed.

Very soon i will come up with fiscal year close in AX.

Pranav…

Let’s share knowledge

Advertisements

23 Comments

  1. Austin says:

    what about this question ?
    Do we need to run the closing process after opening balance is imported ?

    • Pranav's says:

      Thanks Austin for pointing out. I have updated the post to include that point.
      Running close process totally depends on the opening balance migration date and the approach which is suitable for client. Closing can only be run if we are starting AX on the first day of financial year. Assume that the fiscal of client’s starts from 1 Jan and on the same date they are going live. In this case we can migrate the opening balance on 31 Dec, run the inventory closing (if applicable) and then fiscal closing.

      If the start day is in between of financial year then we can’t run the fiscal close. We can post the opening balances and start the operation.

      Thanks,
      Pranav

  2. Sanjay says:

    Hi Pranav,

    What is the ideal date for opening balance migration? Can we do this on any given date of a month?

    Thanks
    Sanjay

    • Pranav's says:

      Hi Sanjay, I will recommend to post the balance on the first day of the month, however, you can post it on any given date.

      The most important thing here is that the opening balance migration date should be consistent, you can’t post bank balances on 10th of month and Fixed assets balance on 11th.

      Choosing first date of the month has other benefits also wrt to reporting also as you will be running most of the reports for a given full month and not from any other date.

      Let me know if you need any more information.

      Pranav

  3. lally says:

    Thank you pranav for the post.

    a) You mentioned that need to maintain the seperate journal and sepearate voucher series for the opening balances . After completing the opening balnaces how to control / block that journal to select by the ussers ?

    b) After posting the balances what are all the reports need to be consider for the reconciliation purpose ?

    • Pranav's says:

      Hi Lally,

      a) Separate journal for balance migration is not necessary. The best is to use separate voucher series. Now you can use the voucher series of journal itself. During opening balance migration, change the number sequence at journal level and after you are done with opening balance, put the default voucher sequence one.

      b) After opening balance migration, you need to reconcile the balance at individual module level, better to ask specific user to validate also. You can use following reports:
      GL: Trial balance
      AP: Vendor invoice transaction report
      AR: Customer invoice transaction report
      FA: Fixed asset balances report

      Thanks,
      Pranav

  4. Myo says:

    Thanks Pranav for sharing. I have one client who want to go live on 1st April. They only use Financial module. My migration process would be as below:

    1. GL opening balance for BS as of 31/Dec/13, can i use posting date as 1/4/14 so that i don’t need year end closing.
    2. GL opening balance for TB as of 31/Jan/14 so i use posting date as 31/Jan/14
    3. Similarly, GL OB of TB for Feb and March.
    4. OB for AR/AP/FA/Bank as of 31/Mar/14

    So, is this correct and complete process? Is the posting date correct? Client want to separate 3 different batch because they want to generate FS with comparison between this year and next year.

    Thanks.
    Myo

    • Pranav's says:

      Hi Myo,

      The Go Live date is 1 April and the client is providing you with the opening balance starting 1 Jan. I will suggest you following steps:
      1. Post the ledger balances as of 12/31/2013 and then perform the closing. I will recommend this because when you perform closing, system creates transactions with transaction type as opening balance and that will be very good for FS. Some countries localization reports require opening balances on FS.

      2. Post the monthly trial balance for Jan, Feb and March. At this point, the trial balance will match from legacy system.

      3. Now post the subledger balances on 1 April. Since balance is already there is ledger account, i will suggest you to use the same account for debit and credit. This way there will be no ledger impact and balances will be migrated to sub ledgers. For example, there will be AP control account. Now on 1 April, post the vendor balances such that the account is posting profile is same as AP invoice journal offset account. Same thing needs to followed for AR, FA and Bank.

      4. Before posting transactions, make sure to take the backup of production for safer side and after posting validate the subledger balances.

      Pranav…

      • Lenin says:

        Hello Pranav, when you say that the account should be the same. Are you referring to the clearing account (999999)?

      • Pranav's says:

        Hi Lenin,

        Basically, the idea is to have 0 balance on 999999. As we know we have debit and credit balances same, when we import the balances, if 999999 is offsetting all the transactions, then the balance in opening balance account will be 0.

        Thanks,
        Pranav

      • myosandar says:

        Thanks Pranav, for the reply…. Understood the suggestion.

  5. lally says:

    Hi Pranav,

    This question is related to Financial year closing .

    As we all know that will use the dimensions for the P&L accounts to minimize the accounts and during the posting the transactions to P&L accounts will make the mandatory dimensions.

    Assume that I have two accounts called _ Sales revenue and Expense account posted the following transactions to this account with the dimensions.

    Sales Revenue – Dim1 – $1000

    Sales Revenue – Dim2 – $ 2000

    Sales Revenue – Dim 3 – $1500

    Expense – Dim 1 – $800

    Now when it comes to financial year closing system will check the Reveune and expenses then post the profit or loss to my retained earnings account .

    a) how to take the Dimension wise closing the financial year ?

    b) Take the above example and explain if i close the dimension wise how system post the entries to P&L accounts to make the balance Zero and How the entry will be created in retained earning account in AX 2012 ?

    Thanks in advance.

    • Pranav's says:

      Hi Lally,

      Sorry, i oversaw this query. I will reply it now.
      a. When you perform year end close, on the financial dimension tab, system provides option for transferring financial dimensions with balances.
      b. Practically, there will be many dimensions transactions that get posted in P&L. However, for the retained earning account, system will check the dimensions associated to it. If the dimension is made as applicable for retained earning account, system will update the dimension values also and trial balance can be seen with dimensions.

      Thanks,
      Pranav

  6. Ritu says:

    Hi Pranav, love reading your blog and how you explain.
    I have a pressing question about GL opening balance and AP and AR opening transactions upload. We are going live on Nov 1. However, the financial data will not be available until
    Nov 12. Is it ok to start using the new system without bringing in the opening balance and open transactions.
    The opening inventory will be brought into AX 2012 on Nov 1

    Thanks for your help

    Regards,
    Ritu

    • Pranav's says:

      Thanks Ritu. You can start using system with inventory upload but not a very good or recommended approach. The key risk is ledger balance reconciliation on Nov 12. The AP balance you upload should match with GL account as of that date. Same applies to other sub ledgers. Also, you have to take care of bank a/c reconciliation since checks will not cut from new system as AP balances will not be there between Nov 1 to Nov 12 and same when you receive payments from customers, where to apply those payments. If the transaction volume is heavy, this may be a risk.

      I would suggest 2 things:
      1. If possible post the opening balance as of Nov 1 with financial data. If something wrong is posted, adjustment can be posted anytime. You can discuss with your client about the cutoff activity such that balances are available on Nov 1.

      2. If somehow the balances will be available on Nov 12, then you should test the data migration activities thoroughly. I would suggest, try to run the opening balance migration for the month of October also, so that issues(if any) will be identified sooner.

      I understand your client might be willing to give you data after their month end closing activity is done, but if we convince and explain them issues, the adjustments etc, they can give you balances as of Nov1.

      Thanks,
      Pranav

  7. Ram says:

    Hi Pranav, Nice post & explanation
    Can you please reply to lally as I have the same query
    — Thanks Ram

  8. Maha says:

    Hi Pranav,

    I do have a question around migrating the opening balance. I completely understand the use of the migration control account (99999) for transferring the data. However this will be useful for the balance sheet accounts. What about the p&l accounts like sales and cost account? Will those accounts be migrated in a similar fashion? If they are migrated in the similar fashion then how do you segregate them based on what the cost / sale was for in the new system?

    Apologies too many questions.

    Post is excellent!

    Kind Regards
    SK

    • Pranav's says:

      Hi SK,

      Depending on the case, i will rather prefer to migrate the complete trial balance which will include both BS and PL items and later adjust sub-ledgers.
      Since the PL balances will be carried over balance, it needs to have some identification in description to know the nature of expense. Once again, i will be careful with dimensions, generally clients uses new dimension which is not in legacy system, if dimension information is available, it will be helpful.

  9. Ankur Patel says:

    Hi Pranav,

    i want to know if we are using continued data posting in ax.. like F.Y. 2014-15 to 2015-16 then how can we rectify or correct the opening and closing balance ?

    please share the method.

    • Pranav's says:

      Hi Ankur;

      As long as you have not closed (putting period status as closed) the period, you can make changes. You can post the correction entry in previous fiscal year and run the opening balance job to update the retained earning and account balances.

      Thanks,
      Pranav

  10. Hazeel Zafar says:

    How can we upload opening balances dimension wise and we are using advance rule structure??

    • Pranav's says:

      Hi Hazeel,

      You have to be careful when you are populating the data on your opening balance template. Depending on how you are migrating balance (like uploading through a csv template), advance rule structure will not create issue if data is as per rule.

      Another point, when you are migrating balance, the data maynot be as per advance rule setup in AX, but it may conflict as data was like that in legacy system. In this case, you may have to disable advance rule structure, since client may want to compare AX trial balance with their legacy system at the end of data migration. Discuss this point with client before taking decision.

      Let me know if this answers your query.

      Pranav

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

%d bloggers like this: