Wednesday, December 15, 2010

Opening Balances Upload

There are 4 types of opening balances that needs to be transferred, in the Dynamics AX from the legacy system. They are:
•General Ledger opening balance
•AR/AP opening balance
•Bank Balances
•Inventory opening balance

We will discuss about each of them in detail.

General Ledger opening balance

After the COA (Chart of Accounts) are imported into the Dynamics AX from the legacy system, the balances in each and every ledger account has to be created.
For this purpose the user should create one Data migration control account, which will be used as an offset while creating opening balances in Dynamics AX. After all the transactions for each and every ledger account has been transferred, make sure that the Data Migration Control Account has ZERO balance. This will ensure that the opening balances in the New Dynamics AX system are all ok.
To bring the opening balances in the Dynamics system, the user can make use of General Journal.
Example 1:
Let the Data migration Control account = 989898
Before creating the opening balances for ANY account, make sure whether the current balance of that account in the legacy system is Debit Balance or Credit Balance.
For e.g. Trade Debtors (Accounts Receivable) will generally have a DEBIT balance. Keeping this in mind, try to make use of the Data Migration Control Account accordingly.
One more thing to keep in mind while transferring the Opening Balances for AR / AP / Bank, these all accounts are control accounts and hence they will be locked. But just for the purpose of creating the opening balance, remove the “Locked In Journal” checkbox from the COA.
1.General Journal entry to be passed while creating the opening balances for Account Receivable will be:
Account Receivable A/c (700700) Dr 92000
To Data migration Control A/c (989898) 92000
2.General Journal entry to be passed while creating the opening balances for Account Payable will be:
Data migration Control A/c (989898) Dr 75000
To Accounts Payable A/c (600600) 75000
3.General Journal entry to be passed while creating the opening balances for Fixed Assets will be:
Fixed Assets A/c (500500) Dr 50000
To Data migration Control A/c (989898) 50000
4.General Journal entry to be passed while creating the opening balances for Ledger Accounts for Bank will be:
Bank Account A/c (400400) Dr 150000
To Data migration Control A/c (989898) 150000

At the end of the day, after all the balances for all the Ledger Accounts are in place in Dynamics AX, the balance in Data Migration Control Account should be 0.
To determine what sort of balance an account should posses; refer to the trial balance from legacy system. Ask the personnel from the company to provide you with the hard copy of the trial balance from the legacy system. This will give you a fair idea about the nature (Debit or Credit) of balances that each ledger account should have.
One more way to determine whether the account should have a Debit or Credit Balance is to try and match that account with the Balance Sheet. If the Account falls on the Debit side of the Balance Sheet, then that account will generally have a DEBIT balance. For e.g. Accounts Receivables, Fixed Assets, Cash, Bank etc.

AR/AP opening balance

Now all the opening balances for the COA are in place. However if you go to the individual Customer in AR (Sales Ledger) and check for the balances, then you will not find any balances. This means that though the Accounts Receivable Control Account (700700) has a balance of 92000 in it, however that amount is still not segregated as per the customer. To do this, the user will again have to pass the General Journal. Remember open transactions are UNPAID, UNSETTLED or PARTLY PAID invoices (for Customers and Vendors)
Some things to keep in mind before creating the opening balance for Customers (AR) and Vendors (AP)
•Try to settle as many open AR and AP transactions before you migrate data into dynamics AX.
•For partly settled invoices, the original invoice amount should be captured in the transaction text.
To transfer the opening balance, the user will have to pass the General Journal with the account type “CUSTOMER” for AR (and account type “VENDOR” for AP).
The offset account in this case will be the Accounts Receivable control account, which in this case will be Account Receivable A/c (700700).
The transaction will look like this:
Customer A A/c (CUST0001) Dr 12000
To Accounts Receivable A/c (700700) 12000

If you think, the net effect of this account will post the opening balance in Customer A’s account AND will net off the amount in Account Receivable A/c. This is because Customer A account is in effect Accounts receivable A/c.
There is one more thing that I want to bring to the notice is: Opening balances for the Customer can be transferred in two manners .i.e.
•Summary Amount can be transferred, but then in the new system the user will lose track of the invoices that account for the final figure. (For e.g. : Outstanding Amount for Customer A is 12000 INR, however if that is transferred, by passing just one single line in the General Journal, then though the Balance for the customer will be correct, but the breakup of the invoices comprising the of amount 12000 will not be there.)
•Detail Invoice break up can be transferred, by passing those many lines in the General Journal and mentioning the Invoice Number in the Transaction Text field.
So taking into consideration, the above example, suppose INR 12000 comprises of 4 invoices, with the following amounts:
•INR 2000
•INR 4500
•INR 5000
•INR 500
Customer A A/c (CUST0001) Invoice Q Dr 2000
Customer A A/c (CUST0001) Invoice W Dr 4500
Customer A A/c (CUST0001) Invoice E Dr 5000
Customer A A/c (CUST0001) Invoice R Dr 500
To Accounts Receivable A/c (700700) 12000

Similar procedure should be followed while transferring the AP (Vendors) balances.

Bank Balances

General Journal entry to be passed while creating the opening balances for Ledger Accounts for Bank will be:
Bank Account A/c (400400) Dr 150000
To Data migration Control A/c (989898) 150000

Similar is the situation with the Bank Accounts. Bank balances are transferred in ledger account 400400. However if you go to the particular bank account in the Bank module and check the balances, then balance will be ZERO.
To bring the balances in the Bank Account, pass a General Journal, with Account type Bank.
General Journal entry to be passed while creating the opening balances for Bank Accounts after the Bank Balance is transferred in the related ledger account:
HSBC Bank A/c Dr 150000
To Bank Account A/c (400400) 150000

This will bring balances in the Bank Accounts created in the Bank Module.
There is one more side to this Bank upload.
There may be a situation when more than one bank is assigned the same ledger account. In that case the transaction will be:
HSBC Bank A/c Dr 50000
Santander Bank A/c Dr 100000
To Bank Account A/c (400400) 150000

Inventory opening balance

For creating the opening balances for the items (stock), a Movement Journal is used. But in this case as well, the offset account will be Inventory Ledger Control Account.
The whole idea here is distributing the value to the individual items that has been brought in the system after the Ledger Opening Balances were transferred. When the opening balances were brought in the system using the Data Migration Control Account, at that time the opening balances for the Inventory were also brought. By doing this exercise, we are just segregating the values to the individual items.
For e.g.:
Inventory Control Account: 300300 has a opening balance of 3000
Item A cost price: 300
Movement Journal for Item A will be:
Item A Cost Price = 30, Offset Account = 300300.
This transaction will negate the entry and the overall impact on account 300300 will be zero and this is what we wanted. This step can be repeated for all the items in the inventory.

2 comments:

  1. how do you zero out the data migration control account?

    ReplyDelete
  2. Data Migration control would be zero out automatically if you have posted the opening balances correctly. If the Data Migration control account is not zeroed out, that means you have missed some transactions.

    ReplyDelete