GL Cash Account Distribution Report

Normally the GL Trial Balance Reports for Cash Accounts show limited information: Cash in; Cash Out. The offsetting entries are spread all over the GL, in different accounts, "Double-Entry System". Our client wanted a report of these offsetting GL accounts matched to the journal entries in the Cash Trial Balance. In other words, "Where did the cash go?". So we wrote a database query and a small .NET add-on which lets them generate, in Excel, Cash Distribution Reports.

Here is the standard GL Trial Balance Report for early August. While it does give the basic information about where the cash came from and went, it doesn't specify the offsetting, target, GL account, so it is hard to audit.

Here is an example of the Cash GL Distribution Report for the same GP company (Store) as above, for the same period. Notice the repeating journal entries show the cash amount spread over multiple target accounts.

We created an interface using the GP API to add a new menu item to GP Financial Reports.

Here the GP user can set parameters for date range and company. It works for any of the stores. The program is linked to the GP security setup, so only users who have a specified role may access this report.

Pick the company/store to report on.

Here is another output sample for this store, for July.

The heart of this report is to work with the database tables - to run threads back from the GL Journal Entry numbers, to their sub-ledgers, and then to the distribution tables.

The output had to be checked over and over to make sure odd-ball transactions were reported correctly, e.g.: VOIDS, GL Correcting JE's, Inter-Company Transfers, Bank Transfers, etc. The client is happy with the report now.