Friday, July 22, 2011

Reconcile to GL Oddity

I have to admit that I sometimes cringe when I get a call about the Reconcile to GL feature in Dynamics GP (Microsoft Dynamics GP-Tools-Routines-Financial-Reconcile to GL).  I know it is simply because I still haven't learned all the quirks of an otherwise very useful tool. 

For those of you not familiar with it, the Reconcile to GL tool creates assists you with reconciling the payables or receivables subledger by creating a spreadsheet of subledger transactions and the matching (or potentially or unmatched) general ledger transactions.  It replaces the "old" method of exporting the detail from SmartList for payables/receivables and for the general ledger, and then using VLOOKUP in Excel to match up the transactions (oh, my, the fun we have had). 

Reconcile to GL window
Microsoft Dynamics GP-Tools-Routines-Financial-Reconcile to GL

Simply select the module (Receivables Management and Payables Management are the currently available options), enter the date range to examine, and the AP account(s).  Then click Process to generate the spreadsheet. 

The spreadsheet details the transactions that match between GL and Payables Management, those that potentially match, and those that do not match (which could be likely causes of variances between GL and Payables Management).  It then gives you the total of the transactions per GL and per Payables Management.

A funny thing happens, though, when you post a payables transactions with a "check on the fly" (which is a check recorded and printed directly from the Payables Transaction Entry window), and then void both the payment and invoice.  In my experience, this occurs when you have marked the option for Separate Payment Distributions in Company Setup (Tools-Setup-Company-Company-Options button).

Payables Transaction Entry
Transactions-Purchasing-Transaction Entry

First, record the payables transaction and "check on the fly" using the Payables Transaction Entry window.  And then post the transaction, ensuring that the transaction is posted in both Payables Management and the GL.  Then generate the Reconcile to GL spreadsheet:

Reconcile to GL Spreadsheet (after posting of "check on the fly")
Microsoft Dynamics GP-Tools-Routines-Financial-Reconcile to GL

Note that both the invoice and the payment are reflected on the spreadsheet properly, to cancel each other out. But, let's say I then need to void the payment (Transactions-Purchasing-Void Historical) and then void the invoice (Transactions-Purchasing-Void Open).  Then re-generate the Reconcile to GL spreadsheet:

Reconcile to GL Spreadsheet (after voiding of invoice and check from "check on the fly")
Microsoft Dynamics GP-Tools-Routines-Financial-Reconcile to GL

Notice that both of the voids show up in the "Matched Transactions" section, but that the payment no longer shows alongside the original invoice.  So the original invoice has been moved up to "Potentially Matched".  The issue is then that the toal of the payables activity is off since the payment is no longer listed, there is now a net increase of $250 on the total of payables activity.  This is incorrect, and does not match what is actually in GP.

I have walked through this issue with Microsoft, and they are currently researching to determine if it is a quality report.  So please let me know if you have run in to it or not.  Since this tool is used to help find issues when Payables Management and the General Ledger do not match, these little issues can take up valuable time and lead users down the wrong path as to the cause of their discrepancy.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.


Janakhiram said...

I cannot agree with you more than this. Although it is a very handy tool, at times, it behaves strangely.

Unknown said...

It seems that the above scenario produces 2 unmatched transaction.