Dynamics GP Reconciliation Tips and Tricks for Accounts Payables, Account Receivables, Banking, and General Ledger

Have you ever had a scenario where your accounts payable, accounts receivable, or banking subledger do not tie out to the period balance of your control accounts in the general ledger (GL)?

The key to analyzing the issue and identifying the problem is understanding the methodology of what you are trying to prove with account reconciliations and matching the key date parameters of a transaction from subledger to general ledger.

Monthly account reconciliations of subledgers to the general ledger activity is a process which verifies that all transactions created during the period are recorded correctly and accurately into your general ledger – and ultimately, reported on your financials. The three main tasks to this process are:

1. Ensure the accuracy of the subledger activity
2. Validate all the transactions are recorded in the GL at the same time or period as the subledger
3. Tie out the subledger reporting to the General Ledger reporting

Most people assume that steps one and two are accurate and move straight to step three. It is only when they find issues that they back track to the other two steps. A review of all the steps should be understood when attempting to perform account reconciliations.

For the first task, make sure that the recorded dates and GL distributions in the subledger (AR, AP or Banking) are accurate for the source document at the time of data entry. Pay specific attention to the following document details:

• Transaction date – this is the document date (AP = invoice date, AR = billing date, Banking = transfer date, misc. charged date, deposit date)
• Posting date (GL posting date) – should be within the same period that the transaction date is entered. There are exceptions to this best practice, but they should at least be in the most current reporting period open when you received the source document.
  • Posting dates can be assigned by batch – for AR and AP
  • Posting date = Transaction date for banking activity

• Voiding transactions – be especially cautious of the dates and posting set-ups on these transactions.
  • When voiding transactions, Dynamics GP suggests a default of the transaction date you are voiding. Manually change the void date and posting date to be the same date and within the most current posting period.
  • Voiding transaction can also create batches to be posted to the GL, requiring a second posting before the GL is updated. If this is not done in a timely fashion, or posted in the GL, then your ledgers will be different.

• Distributions – the system set-up for GP should include the default account for the control accounts for each of these subledgers.
  • Distribution type should match account type, RECV = accounts receivable account, CASH = cash account, and so on.
  • The offset account SHOULD NOT be the control account – a simple way to prevent users from selecting the control accounts is to uncheck the allow accounts entry field on the control account set-up. (Financial > Cards > Account – uncheck the allow account entry checkbox)

If all of these precautions and data entry fields are used correctly, the subledger balances should be accurate and timely in reporting the customer, vendor, and banking balances in your subledgers.

To prove the same subledger transactions are accurately recorded in the GL, the key is to analyze the GL post dates from the subledger and the GL posting date in the GL.

The best tool for this job is the Reconcile to GL functionality found in Microsoft Dynamics GP version 10 or later (Financial > Routines > Reconcile to GL). This tool basically proves net change activity on the two subledgers given the parameters specified and then exports the analysis to Excel.

To use this tool properly, ensure the following tasks are met when using this tool:

• Provide a specific date range and understand this to be the GL posting date
• Verify all control accounts are selected when analyzing subledger activity – such as multiple GL accounts for AP or AR

* In GP 2013, Dynamics GP stores this information for reference within the system to review at a later time.

The key to using this tool is to identify the unmatched or potentially unmatched sections of this report. In this area of the report are potential issues that might cause your subledger reports and GL period balances not to match.

Using the Reconcile to GL tool only validates net change or activity that has happened during a period. The overall GL period balance for your control accounts need to be validated by the subledger reports, which extend beyond analyzing just the current period's activity. Use the following reports to validate the period balance for your control accounts:

• Accounts Receivable or Accounts Payable – Historical Aged Trial Balance
  • Select GL posting date as the date parameter
  • Exclude (check the box) – un-posted applied credit documents, zero balance activity, fully paid documents

• Banking – Bank Inquiry on Checkbook Balance (Financials > Inquiry > Checkbook balance)
  • Select a checkbook ID that has the set-up relating to the GL account
  • Select a the date you are trying to reconcile as the to date

If the reports do not match the General ledger trail balance (Financials > reports > trial balance – select the date range and the account number in the report restrictions), then you need to comb through the detail of the transactional data to determine why the GL and subledgers do not match.

Here are some common issues these two reports can be different:

• Errors are date related. The subledger GL posting date was different than the GL posting date.
  • If you have Post to GL parameter turned on, a batch could be dated differently or deleted before the GL has recorded the subledger transaction. Voiding can also cause a GL batch to be posted to fully record the subledger transactions

• General journal entries were posted to control accounts. Any adjustments made in the GL will not be reflected in the subledger.
  • General journal entry to control accounts for adjustments
  • Choosing the control account on the subledger transactions for both a debit and credit value for the same amount.

For more information on this topic, please refer to the following Microsoft published articles:

Information about differences when you reconcile GL to AR and AP

In an ideal world, monthly account reconciliation should be efficient, performed in a reasonable amount of time, and give you assurance that the system is capturing all business transactions within a reporting period. While things are not always ideal at the end of every period in business, I hope this article has helped your understanding of how to perform a reconciliation and some guidance to navigate the Microsoft Dynamics GP product for this process.