Rose Business Solutions Dynamics GP

Is it me or is this the time of year auditors really become annoying (or maybe I should say more annoying than usual.) Here we are several months past end of year and you get requests like “what were your outstanding checks for 12/31 of last year?” If you haven’t kept all your posting journals in binder (I still see many clients running through this silly process of killing enumerable trees) you may curse the day you installed GP as there is no “Outstanding transactions” report available in the bank module as of a certain date. In fact it is only available to print posting journals when you reconcile your bank account.

Well have no fear fellow Dynamics GP user. Here is the solution to your auditors audacious request.

Method 1 Using Smartlist

1. Open Financial>>bank transactions
2. Add “Cleared Date” column
3. Add search criteria on GL posting date to be “Less Than” the transaction date you desire
4. Add Checkbook ID to search criteria if you have more than one checkbook
5. Export to Excel (Here is where it gets a little cumbersome)
6. Sort by GL posting date and Cleared date
7. Delete any rows that cleared date is equal to or less than the desired date. (Please note that the cleared date on some may be blank or “0/0/0000” if they have not been cleared yet. You will need to still include those transactions. Some may have been voided as well so you will have to exclude those transactions as well. You may also have to add the “Voided” and “Void GL Posting Date” if you have to look at those variables with your data.)
8. Sum up transactions if you desire

This should equal your outstanding transactions for that date in time.

Method 2 Using Report Writer

1. Print to screen Reports>>Financial>>Checkbook>>Checkbook Register
2. Choose modify
3. Add Cleared Date to the checkbook register report as shown below
4. Save report and give security access to modified report
5. Open Reports>>Financial>>Checkbook>>Checkbook Register again
6. Insert restrictions on checkbook ID and Date. I left everything from 00/00/0000 to the date required. You could restrict it a bit if you don’t want to show everything before the beginning of time.
7. Send report to a file
8. Open with Excel.
9. Here’s where it gets kind of cumbersome. You will need to sort and delete out all headers, lines, *’s (voids) etc until you only have the rows of data.
10. The data seems to all go to one column. You will need to use text to column function in Excel (Data ribbon). I would try fixed width without doing anything fancy with fixed width. Seems to do the trick.   
11. Then you can sort again by transaction date and cleared date.
12. Delete all rows that the cleared date is before the date you desire

This should equal your outstanding checks for that period of time.

Whew, I did this approach for a client the other day and it tied out to the penny. I’m not going to say I was totally surprised but I will say the client was. Not only that the approached tied out correctly but that a sophisticated system makes you go through such gyrations.

One Response to “Outstanding Bank Transactions”