Being from an accounting background and after years of mannually reconciling AP/ AR in an Excel spreadsheet Microsoft finally developed the “Reconcile to GL” function in Dynamics GP. Introduced in version 10, automatic reconciliation capability has long been an idea of novelty for most accountants. Wouldn’t you agree? Now, it has arrived.
Why Reconcile to GL is necessary? Dynamics GP is an integrated system, meaning it has a core module called General Ledger which stores only the journal entry information. Sub-modules store the data details such as customer name, class, address, payment information, vendor information, invoice and purchasing information etc…depending on what module or series it belongs to. If you click the transaction button on the toolbar the drop down will provide a list and you will see, for example, Sales, Purchasing, Fixed Assets etc… These are what we call Series. Each series, with a few exceptions, in the sub-module post journal entry transactions to the General Ledger module. Consequently, since you are using different modules you will want to reconcile your sub-module to your core or General Ledger module to make sure the ending balances tie out. Also I want to point out the usage of the term “Reconcile” in Dynamics GP 10. There are two types of “Reconcile” in Dynamics GP the “Reconcile to GL” function generates a spreadsheet with no change to the system data. The other as found in the utilities of payables management (Microsoft Dynamics GP>>Tools>>Utilities>>Purchasing>>Reconcile) can actually change data in the system. So just be aware of the differences.
How Does the “Reconcile to GL” Function Work? The reconciliation function currently has the ability to reconcile two sub-modules; Payables Management and Receivables Management including SOP transactions. The range of information is based upon Date and GL Accounts. In the reconciliation process the system queries and matches the General Ledger transactions to the transactions in the sub-module to create an Excel spreadsheet matching the criteria below.
Payables Management/Receivables Management General Ledger
Voucher Number Originating Control Number
TRX Source Originating TRX Source
Posting Date Transaction Date
On Account Amount Debit Amount or Credit Amount
How to Navigate to and Generate the Reconcile to GL function.
1. From the Financials page expand routines and click “Reconcile to GL.”
2. Reconcile to GL window will open.
- (1) Click the dropdown button to select the module to reconcile. Either Payables Management or Receivables Management Module.
- (2) Select the “Date” range.
Tip: start out with a 1 month range. It may be tempting to select the whole year but if you have a large volume of transactions the processing time you may find slow.
- (3) In the “Accounts” section, click the look up button and choose all of the GL accounts to reconcile the sub-module to. For example; here we are reconciling the Receivables Management (RM) module so we have to select all of the AR GL accounts we use in processing from the RM module. This is because reconcile looks at all of the RM transactions it cannot section RM transactions by a specific GL account.
- (4) Click the “Process” button.
Tip: While processing it may appear as though the screen has locked up or is doing nothing. Give the processing a minute or two to do-its-thing. You will see Excel either open or flash on the bottom tool bar. It may still be processing in Excel. If so, you will see the spreadsheet being populated. You will know it is finished when you can find the totals on the bottom left of the spreadsheet.
3. Now that you have your reconciliation spreadsheet you will see that it is divided into several sections. As seen in the next screen shot.
- The left side is the Receivables Module transactions and the right side is the GL transactions.
- The top portion is the Header and Beginning Balances.
- Second section is transactions that cannot be matched in RM to the GL.
- Next section is transactions the system is able to match.
- Bottom is the ending balances.
- Tip: The sum totals are intact. If you click on a total amount you can see what is being totaled or calculated.
Beginning Balances A double check of the beginning balances is always a good practice. From the RM side (Reports>>Sales>>Trial Balance / Historical Aged Trial Balance from prior period). Using the ending balance as your beginning balance. From the General Ledger (Reports>>Financial>>Trial Balance / either Detail or Summary for the period) This will display both the Beginning Balance and Ending Balance.
Unmatched Transactions There can be a number of reasons why the transactions cannot be matched.
- Un-posted batches or transactions. Check to make sure your transactions are posted and posted through to the GL as well if you have “post through to GL” unchecked in the Financial series posting window.
- If manual entry was made directly to the GL then you will see a transaction on the General Ledger side that cannot be matched to a RM transaction.
- Changing the GL Account on the transaction’s distribution window to a GL account other than an AR Account.
- Changing the “Type” on the distributions window to something other than “RECV” in RM or “PAY” in Payables Management. See screenshot below. The “RECV” type must be selected for the AR GL Account.
Potentially Matched Transactions You can print the GL Trial balance the GL account will be on the reconciliation spreadsheet to review for transaction accuracy.
Matched Transactions These are the transactions Dynamics GP successfully matched.
Balances The balances are pretty much self explanatory. The “Difference” amount will be what you will be most concerned with. Usually, this total is made up of the “Unmatched Transactions.” Review #5 above to assist you in locating the source of any discrepancies.
“Reconcile to GL” – Microsoft Development. As with any New function although it works great in most situations there are one or two instances that are currently being revamped.
- Usage with National Accounts can cause you to receive an error. This error is supposed to be corrected in Service Pack 5. In the mean time I did have the MS developer send a script to correct for this error.
- If you find that transactions are not matching due to Multicurrency or Voided Transactions updating to the latest Service Pack should remedy those issues. If not, contact your ISV or Microsoft Support directly and they can send you the fix for any nuances you encounter.
Overall, the usage of the “Reconcile to GL” has been positive. Training clients on the “how to’s” of processing and catching data entry errors before they become a bigger problem is worth the time and effort to learn this functionality. Not to mention the time savings of automation vs. manual reconcile is a wonderful cost savings to any accounting department.
To download a copy of this article click here