AR / AP opening balances
Open transactions are defined as the unpaid and partly-settled invoices, both from customers and from vendors, that exist in legacy systems prior to migration to Microsoft Dynamics AX
Consider the following when you work with open transactions:
- Since handling open transaction must be done manually, try to minimize the number of open transactions you must work on before migrating to Microsoft Dynamics AX. To do this, settle as many accounts receivable and accounts payable as possible before transferring transactions.
- Partly-settled invoices should indicate only:
· The original Invoice amount, should be captured in a transaction text.
· The current open balance.
· Create journals for customers and vendors : Dynamics AX General Ledger journals with account type of journal is set to “Customer” for AR and “Vendor” for AP will be used to accept open transactions into AX. The journal screen can be opened from General Ledger > Journals > General journal
· Import the data in the journal lines. The table involved is LedgerJournalTrans.Enter open transactions in the journal lines using one line for each unsettled invoice by:
· Pointing directly at the Accounts Receivable/Accounts Payable (AR/AP) accounts.
· Specifying the AR/AP ledger control account respectively as the offset account. The summary accounts are set to “Locked in journals’ as there should be no manual entry in this chart of account. For the purpose of uploading the opening balances we would set the “Locked in journal” = No for the summary account and set the same back to Yes after posting the opening balances.
This allows each journal line to be balanced and the net AR/AP ledger control account posting to be zero. This method requires the General Ledger opening balance to include the control account totals.
· Post the journal.
Example : When a journal line for an customer’s open transaction is imported , following points should be considered.
a. ‘Date’ is the posting date of the invoice originally. The period must be open for that date.
b. The ‘Debit’ or ‘Credit’ are the amounts for the open invoice balance and inclusive of sales tax.
c. ‘Transaction Text’ may be used for capturing the original invoice amount.
d. ‘Currency’ is the currency of the invoice.
e. Item Sales tax group should be BLANK as we would not calculate and post the sales tax for the voucher line.
f. Financial dimensions must be populated correctly.
g. We have taken offset account 130100 based on the following reason :
i. The customer 1103 belongs to the Customer group 10.
ii. The voucher has posting profile selected as ‘GEN’. If we check the setup for summary update account in GEN posting profile then we find that the summary account is 130100.
iii Posting profile of customer transaction can be found at: Account Receivable > Setup > Posting profiles
iv. Posting profile of vendor transaction can be found at: Account Payable > Setup > Posting profiles
h. The invoice number must be provided.
i. The Terms of payments comes as default from customer account. The “Due date” gets calculated based on posting date and the terms of payment or can be provided for the invoice.
j. If there is a Settlement discount applicable then Settlement discount code and Settlement discount date should be provided.
Similar points should be considered for importing the vendor’s open transactions.
Inventory opening balance
Inventory openings are defined as the beginning stock quantity and the value for the stock items.
A movement journal is used to enter the beginning inventory for each item.
· Create a Movement journal.
· Import data in the lines of the journal. The table involved is InventJournalTrans.
· Check the journal lines.
· Make sure the right quantity and cost values and correct inventory dimensions are used.
· Specify the Inventory ledger control account respectively as the offset account. The net Inventory ledger control account posting will be zero. This method requires the General Ledger opening balance to include the control account totals.
· Post the journal.
Example : When opening stock is imported into a inventory movement journal line, following points should be considered.
a. The items are not having “Standard cost” costing method so that the manually calculated cost can be provided for the open quantity.
b. Correct “Item dimensions” (size, color, site, warehouse location etc.) and financial dimensions should be provided.
c. Correct Quantity and the Cost price should be provided.
d. Date of posting the journal line should belong to an open period.
a. Offset account: The item in the journal lines belongs to the Item Group “Parts” which has a account 140920 as the “Receipt” account for the “Inventory module” postings. Therefore the offset account for the journal lines should be 140920. The control account’s Locked in Journal should be set to = No for the purpose of posting the opening stock /inventory and then again changed to Yes so that manual entries are not done which may create problems in Inventory Vs GL reconciliation.
General Ledger Opening balance
Create a General Journal with the following consideration:
Create a template for the Journal Lines for importing data. The table involved is LedgerJournalTrans.
Start with a completed book year and import the ending balance (including Profit & Loss accounts) into AX. The balanced trial balance would be uploaded. The import should be a single voucher which will have the sum of the chart of account balance equal to zero and hence would be a balanced voucher in AX.
Post journal with opening balances in to opening periods i.e if post the voucher in 31st Dec 2009 if the system is being implemented w.e.f. 01st Jan 2010.
Run year end closing procedure to create the closed year’s closing balance as the new year’s opening balance. For example if you want to load opening balances of 1-1-2009 enter a journal dated 31-12-2008 (closing period). Then run year end closing process. These balances become opening balances of 1-1-2009.
Now for each new month we would need to post the movements only. Consider the following:
Create separate voucher numbers for each month you are importing, like JVH01_2009 for January 2009 historic date, JVH02_2009 for February 2009 etc. This way you will get a voucher per month.
No Sales tax should be posted for any line. (Item Sales tax should be blank).
Correct financial dimensions should be provided for each line if the balances are per financial dimensions (which would create multiple records per chart of account.)The general practise is that the opening balance is uploaded without the dimensions.