Article ID: 866570 - View products that this article applies to.
This article discusses why the Accounts Payable account balance or the Accounts Receivables account balance in General Ledger differs from the total amount that is due on the Historical Aged Trial Balance report in Microsoft Dynamics GP. There are commonly asked questions at the end of this article.
The Reconcile to GL routine was new to Microsoft Dynamics GP 10.0 (SP2). This routine generates a Microsoft Office Exel spreadsheet. You can use this spreadsheet to match transactions in Payables Management or Receivables Management that were posted to General Ledger. This process does not generate correcting transactions. However, this process can help you determine the transaction differences that are listed in this section. To open the "Reconcile to GL" window, point to Tools on the Microsoft Dynamics GP menu, point to Routines, point to Financial, and then click Reconcile to GL.
Below is a running list of issues that we have seen that may cause differences:
Commonly Asked Questions:
Q1: Is the Reconcile to GL spreadsheet a true reconciliation?
A1: The reconcile to GL feature is a 'troubleshooting tool' to help users identify unmatched distributions between RM/PM and GL. It was not necessarily meant to tie out to the HATB and that was not the intended purpose, although we know clients are doing it. The balances on the Reconcile to GL spreadsheet are best estimates using a simple addition/subtraction on the distributions in the table. Whereas the balances on the HATB take nearly every table into consideration and are much more complex and accurate balances and so the two often don't tie out.
The true reconcile should be between the RM or PM Historical Aged Trial Balance (HATB) and the GL Trial Balance reports. If these match, then you would not necessarily need to run the Reconcile to GL tool for that month. The GL tables are made up of debits and credits, and the tables that the HATB pulls from are transaction header and apply record tables. So customers asked for a way to reconcile the distributions in GL to the distribution tables in RM or PM to help find differences at that level. So this is the reason why the Reconcile to GL routine was created. It was intended to be a troubleshooting tool to compare distributions to distributions between the modules to help identify missing distributions, that may lead you back to a missing transaction from the HATB. So use the Reconcile to GL tool as an 'aid' only to help you reconcile the HATB to GL Trial Balance. If the HATB and GL TB balance, then there isn't really a need to run the Reconcile to GL tool for that month.
Q2: Should the totals on the Reconcile to GL spreadsheet match the totals on the HATB?
A2: No. The totals on the Reconcile to GL spreadsheet are just a simple addition/subtraction of the distribution records in that table and doesn't take any other tables into consideration. Whereas the HATB looks at entirely different tables to calculate a balance using the transaction and apply records tables and is a much more complex calculation. Because of the different calculation methods/tables used to obtain the balances, the Reconcile to GL spreadsheet is not expected to outright tie to the aging balances on the HATB reports and would make reconciling them difficult. It is not necessary to tie the balances on the reconcile to GL spreadsheet to the HATB report.
We suggest to ignore the totals on the reconcile to GL spreadsheet, and just use the Unmatched and Potentially Matched sections to help you find differences to research to see if that may also explain a difference between the GL TB and HATB. The reconcile to GL spreadsheet is not a true reconciliation, and was only intended to be an 'aid' to help you identify distribution differences to research to see if this is also a difference at the transaction level. In fact, if the HATB matches the GL TB, there really wouldn't be a need to run the reconcile to GL utility for that month at all, since there are no differences to identify.
If you still wish to tie the balance on the reconcile to GL spreadsheet to the balance on the HATB, this is not supported in a regular support case. The reasons we have identified are listed at the top of this KB and there could be more reasons not yet identified. But since this reconcile is not necessary between the simple total balance listed on the reconcile to GL spreadsheet and the more complex calculated balance on the HATB report, and not the intended purpose of this reconcile utility, it would be considered a consulting expense to dig into your data to assist you to reconcile these to each other.
Q3: What should I do if distributions are missing on the GL side?
A3: If you find distributions on the RM or PM side that are not on the GL side, investigate the GL side for timing differences. Check to make sure that all GL batches are posted. If they are truly missing on the GL side, you will need to key an adjusting journal entry directly into GL for the entry to create the GL distributions.
Q4: What should I do if distributions are missing on the RM or PM side?
A4: If the GL distribution is listed, but is missing on the RM or PM side, first investigate for timing differences. Also research to see if the transaction itself is listed on the HATB report and is already accounted for. It is possible that the transaction exists, but the distributions are just missing. So the question becomes 'how do I get the distributions in RM or PM then, if the transaction does exist?' First, keep in mind that the distribution tables in RM or PM are not used for any other purpose or reports in GP, other than this reconcile spreadsheet. So is it really necessary to get them added back into RM or PM? Evaluate if it's worth your time to populate a table that is not used anywhere else.
But if you so choose to fix the PM distribution table, you would have to void the document, so the applied records move back to open. Then use the Remove Transaction History Utility to remove the voided document. Make sure to set the posting to 'post to' GL and do not 'post through to GL'. Delete the GL batch created by the void. Then rekey the document back into Payables, so the transactions and distributions are recreated. Be sure to void the GL batch for this. Then reapply the new document to the open documents and they should move to history again.
To fix it on the RM distribution table, you'd have to remove both sides of the invoice and payment and rekey them both back in, and delete the batch in GL.
Q5: The transactions in the Potentially Matched section look like they match. Why aren't they in the Matched section?
A5: There are several fields that are matched for each distribution record. All of the fields must match to move it to the Matched section. If some but not all of the fields match, then it will put it in the Potentially Matched Section. For example, here are the fields that are matched for PM to GL:
Payables Management -- GL
Voucher Number -- Originating Control Number
TRX Source -- Originating TRX Source
Posting Date -- Transaction Date
On Account Amount -- Debit Amount or Credit Amount
Q6: If I key the missing distributions in GL or RM/PM and rerun the Reconcile to GL spreadsheet, will the unmatched or potentially matched items move to the Matched section?
A6: No. If the transactions are keyed separately, the will have different voucher numbers and Trx Source codes. At best the Posting Date and Amounts may match, which could put them in the Potentially Matched section of the spreadsheet.
Q7:Why does the Ending Balance on a monthly or quarterly spreadsheet not match the Beginning Balance on the next monthly or quarterly spreadsheet?
A7: If the Ending Balance of one period does not match the Beginning balance of the next period, it is often due to orphaned distribution records that have no header record in the sub-ledger table. The Ending Balance is calculated by Excel right in the spreadsheet. It just takes the beginning balance for that period at the top of the spreadsheet and adds/subtracts the distribution records that appear on the spreadsheet to arrive at the ending balance. On the other hand, in the next period, the Beginning balance is calculated by taking a simple debit/credit calculation of the distribution records in the SQL table and the stored procedure does join the header table, so it will not include any distributions that are missing a header record. The end result could be that some distributions were calculated into the ending balance on the prior spreadsheet, and omitted from the beginning balance on the next period.
Q8: The distributions on the RM/PM side do exist, but are not pulling into the spreadsheet.
A8: Review these troubleshooting tips:
Article ID: 866570 - Last Review: February 9, 2013 - Revision: 11.0