Dynamics AX – Handle open balances

 

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.

About these ads
This entry was posted in Dynamics AX. Bookmark the permalink.

10 Responses to Dynamics AX – Handle open balances

  1. Unknown says:

    A very nice insight, great content!!!Thanks Shekhar for the wonderful knowledge you share and your command on Dynamics AX.Anil Kumar Gupta

  2. tphillips says:

    Excellent detail. Can you describe how to actually do the import? For example: from where do you actually get the LedgerJournalTrans layout? How do you populate it? How do you import it? Into what do you import it and then what do you do? I want to know if others do it the way I do ….

  3. tphillips says:

    Importing Inventory Items (not quantities) … I have found that it requires at least three tables to import the Inventory Items … InventTable, InventTableModule and InventItemLocation. Do you suggest any other tables (assuming that all the Setup details have been completed) ?

  4. Shekhar says:

    Hi Phillips,In addition to the the three tables mentioned in your comment i think it will be good to add default order settings by including the tables InventItemSalesSetup,InventItemPurchSetup,InventItemInventSetup Cheers

  5. Unknown says:

    Hi Shekhar, great contents are there in ur space, These are really helpful for the Ax implementors, you hv a nice command on Dynamics…Thanks….Cheers…

  6. Beth says:

    Hi Shekhar-Do you have any experience importing ledger transaction with project details? I\’mattempting to import vendor invoices using the LedgerJournalTrans table in an excel definition group and the project category, cost & sales price fields do not populate. I\’ve tried importing directly to AP->Invoice Journal and also GL->General Journal and when I look at the Project tab, the fields are blank. Thanks for any help you can provide!Beth

  7. AS says:

    Hi Shekhar, I am BI guy with not much knowdege on AX and no access to screens or code. But I am supposed to write query to report AR aging buckets and unallocated cash aging buckets. I am aware of tables such as custtrans, custopentrans, ledgertrans and PK/FK to join them but not sure how to get AR and unallocated cash. Can you please help

  8. Anh Mai says:

    Hi Shekhar,

    As your article, Would you please confirm me about the Date on journal line for each module:

    I assumed that my new system will be implement on 21 Dec
    1. AR/AP Opening Balance : the Date on Journal Line is the original invoice posting date
    2. GL : the date is the ending date of previous period (mean 20 Dec)
    3. IM : you wrote that “Date of posting the journal line should belong to an open period” this mean is the beginning date of new period (21 Dec)

    Thank your for your reading. I’m looking forward for your reply.

    Thanks!

  9. Sarah says:

    Hi Shekhar, how to I enter the outstanding checks and deposits so my first bank reconciliation can be done accurately, I would like to use excel add in or DMF to load to the BankAccountTrans table. But I get an error when attempting to open it in Excel addin.

  10. Sarah says:

    Hi Shekhar, how to I enter the outstanding checks and deposits so my first bank reconciliation can be done accurately, I would like to use excel add in or DMF to load to the BankAccountTrans table. But I get an error when attempting to open it in Excel addin.

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