Thursday, September 9, 2010

How to Upload Opening Balance for Bank in Microsoft Dynamics AX 2009

This article provides you the steps to upload bank opening balance in Dynamics AX 2009.

Bank opening balance scenario:
------------------------------------

Normally, a bank has un-reconciled transactions at end of the any period. Hence, when a company needs to upload bank opening balance, all reconciled transactions balance is updated as one transaction and all un-reconciled transactions as individual entries.

For example, a company receives their bank statement dated 31st December and shows an ending credit balance of $100,950, of which $950 is the sum of un-reconciled transactions while $100,000 has been reconciled.

Section 1: Setup Journal, Bank and Ledger accounts for bank.
-----------

(NOTE: Follow this section only if Journal, bank, and ledger accounts are not created otherwise go to section 2)

  1. Go to General ledger  Setup  Journals  Journal names
  2. Setup a new journal.
  3. Go to General Ledger  Chart of accounts  setup new account code 111110 for ‘USDBANK’
  4. Go to General Ledger  Chart of accounts  setup new temporary account code 111111 for ‘USDBANK’
  5. Go to Bank  Bank accounts details and setup the bank ‘USDBANK’ with required details and link ledger account 111110 to bank.

Section 2: Enter reconciled balance for the bank.
-----------

Follow the steps below to update the reconciled balance.

  1. Go to General Ledger  Journals  General Journal  Create a new journal  Click the lines button.
  2. Enter values in following fields.
    2.1. Date = date for opening balance
    2.2. Account type = Bank
    2.3. Account no. = ‘USDBANK’
    2.4. Transaction text = appropriate text (‘Reconciled opening balance for the year 2009)
    2.5. Credit = 100,000
    2.6. Offset A/c type = ‘Ledger’
    2.7. Account no. = 111110 (Ledger a/c code for bank)

    Note: Step 2.7, ledger account for bank is selected to ensure that balance gets nullified in chart of accounts. So, when user enters the trial balance, user doesn’t need to worry about bank ledger accounts.

  3. Post the journal and check the bank balance via Bank  Bank details  Balance. It shows user balance as Credit 100,000.

Section 3: Enter un-reconciled transactions for the bank.
-----------

Follow the steps below to enter all un-reconciled transactions.

  1. Go to General Ledger  Journals  create a journal  click lines.
  2. Create a new line for each un-reconciled transaction with following fields.

2.1. Date = date for Opening balance (as an example 01-Jan-2009)
2.2. Account type = Bank
2.3. Account no. = ‘USDBANK’
2.4. Transaction text = ‘un-reconciled bank transactions as at 01-Jan-2009’ (as an example)
2.5. Debit amount = transaction amount.
2.6. Go to the payment tab, and enter values in following fields

2.6.1. Payment reference = document details
2.6.2. Bank transaction type = Cheque or deposit etc. from available types.

Use below table to simulate and get an opening balance as highlight above.

Account type

Account

Transaction text

Debit

Credit

Bank transaction type

Payment reference

Bank

USDBANK

un-reconciled bank transactions as at 01-Jan-2009

350

01

Doc 1

Bank

USDBANK

un-reconciled bank transactions as at 01-Jan-2009

1200

01

Doc 2

Bank

USDBANK

un-reconciled bank transactions as at 01-Jan-2009

150

01

Doc 3

Bank

USDBANK

un-reconciled bank transactions as at 01-Jan-2009

700

01

Doc 4

Bank

USDBANK

un-reconciled bank transactions as at 01-Jan-2009

1000

05

CHQ 1

Bank

USDBANK

un-reconciled bank transactions as at 01-Jan-2009

450

05

CHQ 2

Ledger

111111

Opening transactions balance

950

Section 4: Reconciled opening balance ‘reconciled’ transaction.
-----------

We have $100,000 entered as bank reconciled opening balance. Hence, we need to reconcile it. Follow the steps below to complete the process.

  1. Go to Bank  Bank Account details  Select USDBANK record Function  Account Reconciliation.
  2. Press CTRL + N to enter new bank statement.
  3. Enter ‘dummy’ statement as ‘OP Balance’, statement date as 01-Jan-2009 and ending balance as $100,000.
  4. Click ‘Transactions’ button.
  5. Mark the ‘Cleared’ check box for Opening balance’ transaction of $100,000.
  6. Click the button ‘Reconcile account’.

Upon completion, only those unreconciled open transaction will appear as un-reconciled transactions.

Section 5: Settle and close the temporary bank account.
-----------

  1. Go to General Ledger  Journals  create a journal  click lines.
  2. Create a line with following fields.
    • Date = date for Opening balance (as an example 01-Jan-2009)
    • Account type = Ledger
    • Account no. = ‘111110’
    • Transaction text = ‘Settle and close temporary bank account’
    • Debit amount = transaction amount. (In this case it is $950)
    • Offset a/c type= Ledger
    • Account no. = ‘111111’
  3. Post the Journal.
  4. Go to Chart of Account details screen and verify that 111110 and 111111 have zero balance.
  5. Select the account 111111 and go to ‘General’ tab and tick ‘Locked in Journal’. This is to avoid any further journal entries to this account.

Section 6: Verify all balances and bank transactions on posting of trial balance.
-----------

NOTE: It is assumed that trial balance has been entered.

  1. Go to General ledger  Chart of Account details and refer the ‘balance column or verify balance via ‘Balance’ button.
  2. Go to Bank  Bank Account details  Balance button and verify that balance is correct.
  3. Go to Bank  Reports  Transactions  Bank account statement and print the report and verify transactions.
  4. Go to Bank  Reports  Reconciliation  Un-reconciled bank transactions report and verify it is correct.
(Source Micorsoft Dynamics AX 2009)

No comments:

Post a Comment