There is a very powerful tool in Microsoft Dynamics GP that can greatly reduce the time it takes you to find errors between your Trial Balances in your subledgers (Purchasing, Receivables, Inventory, Bank Reconciliation) and your General Ledger (GL). This tool is called the Reconcile to GL routine and can be found by going to Financial > Routines > Financial > Reconcile to GL.
It should be noted that this is a troubleshooting tool only. It will not make corrections, and it is not meant to tie out to the Historical Aged Trial Balance (HATB). If your HATB matches your GL balance, there is no need to use the Reconcile to GL tool.
Example
I’m going to set up the Reconcile to GL for my Payables. In this example, I have set up the Reconciliation as follows:
The Reconciliation Date is 04/12/2017.
- The Date Range I am reconciling is 03/01/2017 – 03/31/2017.
- The Module is Payables Management.
- I only have one A/P GL, and it is 000-2100-00. I could add more GLs to the list if I had more than one A/P GL.
- I have chosen an Output File to download the information to. The file will open in Excel format.
- When choosing Bank Reconciliation as the Module, another field will appear to choose the Checkbook.
This window is completed, and I can click the Process button to run. The Reconciliation number will increment on its own, no need to enter anything in that field. When I click Process, two things will occur: A file will be saved to the location I chose for Output File, and the file will open in Excel format.
File Output
The file is divided into three sections. Let’s take a look.
Matched Transactions:
Matched transactions are those that Microsoft Dynamics GP has verified all the following:
- 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
If all of these items for a transaction can be matched, it will show up under Matched Transactions.
Potentially Matched Transactions:
Potentially Matched Transactions may contain those transactions that have most data points verified, but may be missing verification for other data points. The most common reason are discounts. Items will show up under Potentially Matched Transactions when the discount amount differs. To avoid this, make sure you use the Discount GL Account as well as the regular A/P GL Account.
Unmatched Transactions:
Unmatched Transactions are those that do not match any of the criteria, meaning there is a transaction in Purchasing, but not on the GL side; Or there is an entry in the GL that does not match up to any transactions in Purchasing. Unmatched Transactions are likely where your error is if your reconciliation is unbalanced, and the recommendation is to concentrate your efforts here to find reconciliation issues.
Things to check:
In the Unmatched Transactions example above, there is a GL entry with no corresponding Payables Transaction. This GL entry should be examined for veracity and may need to be reversed and entered through the Purchasing module.
Other things to check are:
- Verify that you have included all of your accounts payable or accounts receivable GLs are included, if you use more than one.
- Payables or Receivables GL Batches are not posted, or Payables or Receivables batches were changed before being posted to General Ledger.
- Payables or Receivables adjustments were entered directly into the GL, which updates the General Ledger, but not the Historical Aged Trial Balance Report.
- The Aged Trial Balance Report is printed with restrictions. Attempt to print the report with only a date restriction.
- Transactions are posted to Payables or Receivables, but not to the GL. If the Post to General ledger check box is not selected in the Posting Setup window for Purchasing or Sales, there will be no GL posting created. The transactions would still show up in Payables or Receivables.
- You track discounts. If the Track Discounts Available in GL check box is selected in the Payables or Receivables Management Setup window, the net amount of the invoice will be posted to GL. The remaining amount is posted to a discounts available account. Only the net amount appears on the Detailed Trial Balance Report in GL. The invoice displays the gross invoice total on the Historical Aged Trial Balance Report.
- Documents were voided in a different period than they were originally posted.
- Posting interruptions may have prevented a batch from reaching the General Ledger.
- There are outstanding GL batches to be posted.
- When you printed the Historical Aged Trial Balance, Unposted Applied Credit Documents, Zero Balance, and Activity boxes were not checked to be excluded.
- You use Multicurrency Management when you revalue and you chose to post to the Purchasing/Sales offset account.
If you have questions or need some assistance, visit our support page for more help.
