One of the challenges when migrating legacy system data is: What do you do with your outstanding checks and deposits? You want them in your new system for your bank reconciliation, but you don’t want to re-create all of the associated invoices and payments, settle them, and reverse the ledger transactions.
The easiest approach is to simply create a list outside of Dynamics AX. As you complete your first few bank reconciliations in Dynamics AX, check off the items from the list as they clear. When everything has been checked off, you’re done. This is a low tech solution, but it works.
Many people don’t want to maintain a list outside of the system especially if they have multiple accounts and lots of outstanding transactions.
Here is an approach for loading outstanding checks and deposits i. Each of the loads mentioned below uses the “Opening balance” entity from the Data Import Export Framework (DIXF).
- Load the bank balance from the legacy system as at the cutover date to a clearing account. This should be done when loading the full trial balance. I generally point all sub-ledger balances (AR, AP, Bank, Inventory, etc.) from the trial balance to one or more clearing accounts and then offset the corresponding clearing account when loading the sub-ledger.
- Load the bank statement balance as at the cutover date to the bank account. The Account type should be Bank and the Offset account should be the clearing account. The remaining balance in the clearing account should equal the total outstanding checks/deposits.
- Load each outstanding check/deposit as a line item. Again the Account type should be Bank and the Offset account should be the clearing account. The balance in the clearing account should now be zero. The bank balance and the associated ledger account balance should match and should equal the legacy system bank balance.
The figure below illustrates an example where the bank balance is $90K, the book balance is $85K, and there are three outstanding checks totaling $5K.
When using this approach, the outstanding checks/deposits are simply recorded as journal lines. They can be used when reconciling the bank, but they won’t appear in your check register.
The figure below is a sample DIXF load file for outstanding checks.