How to Migrate Financial Balances to Dynamics 365/AX: A Step-by-Step Guide

If you are changing to a new financial or ERP system like Microsoft Dynamics 365/AX from a legacy system, migrating financial balances will be mandatory—and complicated. It is a very serious undertaking and the last thing you want is to start off with a system that’s out of balance. At some point, you’ll probably be audited and will need to explain what was done. On top of all these pressures, this is not a typical task for an accountant so we wrote this article to provide you with practical, step-by-step guidance.

First, let me explain that a “legacy system” can be any system you are using—Dynamics NAV, QuickBooks, JDE, SAP, or even a home-grown system. So this article should help anyone needing to migrate opening financial balances from an existing system to Dynamics 365/AX.

For this example, we will use the following scenario:

• Year-end is December
• The new system is going live on April 1

You are tasked with a financial data migration to Dynamics 365/AX that will:

• Bring in the historical trial balance month ends for January to March 31
• Bring in the open Accounts Receivable detail with supporting invoices
• Bring in the open Accounts Payable detail with supporting invoices
• Bring in open Bank transactions that have not be reconciled
• Bring in Fixed Assets with the capital costs and accumulated depreciation detail for all assets
• Bring in open Inventory

Prerequisite:

1. Have the detail information ready and make sure it matches the General Ledger balances as of March 31
   1. Detailed Aged Accounts Payable
   2. Detailed Aged Accounts Receivable
   3. Detailed Fixed Assets Information
   4. Detailed listing of unreconciled bank transactions
   5. Detailed Inventory listing
2. Create one new general ledger account to store the temporary conversion balances. For example:
   1. 99990 Conversion Accounts Payables
   2. 99991 Conversion Accounts Receivable
   3. 99992 Conversion Bank Transactions
   4. 99993 Conversion Fixed Assets Cost
   5. 99994 Conversion Fixed Assets Accumulated Depreciation
   6. 99995 Conversion Inventory
3. Open the months of January to March
4. Go to Journal names and create a journal to be used for Conversion. I suggest setting up a voucher series with the document type CONV (conversion) as shown below. This way, anytime you make an inquiry into the General Ledger and see a document with CONV####, you know it was part of the conversion.

5. For any general ledger main accounts that currently have “Do not allow manual entry” selected, you must temporarily deselect this option for main account 110110 – Bank Account – USD as shown below.

Part 1:  Post all the historical month-end balances into the General Ledger

Step 1: Post the January 1 Trial balance (i.e. you are posting the opening Balance sheet)

• Use the special Conversion journal to post the journal entry for the full trial balance.
• Compare the Dynamics 365/AX January 1 Trial balance to your source and make any corrections if necessary.

Step 2:  Update the Trial Balance to reflect January 31 month end balances (Post the change in the balances for January 1 to January 31)

• For example, if on January 1 the main account #123 had a balance of $500 and at January 31 balance is now $570; then the journal entry would reflect an increase of $70 to this account.
• Use the special Conversion journal to post the net change in the January balances.
• Compare the Dynamics 365/AX January 31 Trial balance to the source and make any corrections; if no errors then set the January period status to “on hold”.

Step 3:  Update the Trial balance to reflect February 28 month end balances (Post the change in the balances for January 31 to February 28)

• Follow the same logic as in Step 2 above and if everything is correct, set the period status of February to “on hold”.

Step 4:  Update the Trial balance to reflect March 31 month end balances (Post the change in the balances for February 28 to March 31)

• Follow the same logic as in Step 2 above to confirm that everything is correct.
• DO NOT PUT MARCH 31 ON HOLD.

Part 2:  Populate the subledger modules to match the March 31 month-end balances in the General Ledger

After completing Part 1 above, you now have the March 31 Trial balance but all the supporting subledgers are empty.The objective of this next part is to populate the subledgers with the details to support the general ledger balances. To accomplish this, we will use a re-class entry to move the balances out of the control accounts and then use the journal import to populate the subledgers.

Using Accounts Receivable for example, but the same methodology will be applied for all the other balances. Note for inventory we will use the movement journal. Let’s say the general ledger account 13000 – Accounts Receivable has a balance of $1,000,000. There are 300 invoices we need to load into the AR subledger and they total the $1,000,000.

You need the invoices listed in the Accounts Receivable subledger because from April 1 when the customers pay, we need to apply the cash to the invoices.

Step 1:  Post a reclassifying entry to set the Accounts Receivable balance to zero

• THE POSTING DATE MUST BE MARCH 31
• Using the Conversion journal, credit General Ledger 13000 Accounts Receivables $1,000,000 and offset it with a debit to 99991 – Conversion Accounts Receivable.

Step 2:  Enter Accounts Receivable detail using a new the Conversion Journal

Below is an example of List tab; the 1^st of the 300 invoice lines to populate the Accounts Receivable subledger

• THE TRANSACTION POSTING DATE MUST BE MARCH 31
• Enter the customer number and open amount for the invoice; the offset will be to General Ledger 99991.

Below is the example of the Invoice tab that relates to the first of the 300 invoice lines:

• Populate the invoice number from your legacy system.
• Populate the document date with the date of the original invoice. This will ensure the Aged Receivables will match the legacy system’s report.
• Make sure that tax is NOT calculated on all lines.

The journal batch will have 300 lines that will total $1,000,000.

Step 3:  Post the Accounts Receivable Conversion Batch

• The Accounts Receivable control balance in account 13000 should now be back to $1,000,000.
• The 99991 Conversion Accounts Receivable account should have a balance of zero.
• The Dynamics 365/AX Aged Accounts Receivable should match the source document; if not, find the error and fix it.

Step 4:  Set the “Do not allow manual entry” to the Accounts Receivable control account

• Once the Accounts Receivable control account is correct, select the “Do not allow manual entry” box in the chart of accounts to prevent manual entries going forward.

Step 5:  Suspend posting to the Conversion Accounts Receivable account

• The balance in the Accounts Receivable conversion account is now zero and you have no further use for this account; therefore, it should be suspended.

Step 6:  Put March period status on hold.

After completing all the above steps in context of our sample scenario, you would have an accurate opening financial balance to migrate into your new system. As mentioned, this can be very tricky, but we hope this helps you feel more confident. If you have any questions or would like assistance, our migration experts are ready to help.

About the Author

Joyce Joseph is Western Computer's Lead Consultant for Microsoft Dynamics AX/365, with over 15 years of experience in implementing ERP financial solutions.

More Content by Joyce Joseph