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
- Have the detail information ready and make sure it matches the General Ledger balances as of March 31
- Detailed Aged Accounts Payable
- Detailed Aged Accounts Receivable
- Detailed Fixed Assets Information
- Detailed listing of unreconciled bank transactions
- Detailed Inventory listing
- Create one new general ledger account to store the temporary conversion balances. For example:
- 99990 Conversion Accounts Payables
- 99991 Conversion Accounts Receivable
- 99992 Conversion Bank Transactions
- 99993 Conversion Fixed Assets Cost
- 99994 Conversion Fixed Assets Accumulated Depreciation
- 99995 Conversion Inventory
- Open the months of January to March
- 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.
- 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 1st 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.
Based on this article, you may be interested in:
Microsoft Dynamics 365 and Office 365 are a powerful combination to elevate customer engagement and boost productivity, especially when most of today’s workers (and customers) are on the go. With the availability of hundreds of third-party apps…Read More
There is something fascinating about seeing machines do the work traditionally done by humans. This past fall at Summit (NAVUG and AXUG), our team setup a robot to demonstrate a pick and stage inventory scenario inside the Western…Read More
There are a variety of costing methods to choose from in Microsoft Dynamics NAV. Depending on the chosen procedure, a costing practice will determine if a budgeted or actual value is used in the cost calculation and it will impact how the cost flow…Read More
Where will you be October 10th-13th 2017? We are a proud Platinum Sponsor of NAVUG Summit Nashville and an Exhibitor at D365UG/AXUG Summit Nashville, two of the most dynamic conferences in the industry.Read More