Debtor or Creditor Reconciliations in Dynamics GP - Identifying a Difference 

The process of reconciling the debtor and creditor balances with the control accounts held in the General Ledger occasionally reveals a difference.  Sometimes identifying how the difference came about can be difficult.
These notes will set out the process of creating the reconciliation reports for debtors and creditors and then work through the steps for identifying any difference

Note: The steps for identifying a difference are ordered by the easiest first ending at the most time consuming and tedious

Although the Dynamics GP accounting system is designed, like every accounting system, to maintain transactional integrity, it is possible for errors to creep in.

The Reconciliation
The reconciliation of the debtor and creditor balances is usually a part of the month end routine and ranks alongside the bank reconciliation in importance.

There are various assumptions made in these notes, the main one being the number of periods in the financial year will be 12 and that the period end dates fall at the end of a calendar month
If the number of financial period’s setup in GP is different, substitute the actual period end dates when generating the debtor balances
The financial period setup can be found in
Microsoft Dynamics GP > Tools > Setup > Company > Financial Periods
The reconciliation process takes data from two different sources from within GP and compares them.

The reconciliation formula is:

The balance of the debt due to or the debt owed by the company, at a given date, taken from the receivables or payables sub ledger
Subtracted from
The balance of the debt due or owed, according to the General Ledger control accounts, at the same date
The result should be zero

The formula is the same whether reconciling sales or payables, the data just comes from different areas of GP

Sales or Payables                                                                                                                             £

                Total Debt per HATB report         as at [dd/mm/yy]                                                         0.00       
                Less Total Debt per GL control accounts as at [dd/mm/yy]                                            0.00
                Total                                                                                                                                0.00

               
The Reconciliation Process

The process for reconciling debtor or creditor balances to the General Ledger is:
Complete the  sales and creditor  transaction entry and postings

Note:  Do not worry about posting transactions into GP dated after the reconciliation date, GP is date based so it can filter the transactions for the reconciliation

2. Run  the HATB report, in summary mode, set for  the reconciliation date
3. Obtain the balance on  the GL control account(s) as at  the reconciliation date
4. Compare  the  balance on  the HATB report with  the balance on the GL control accounts
5. If   there isn’t a difference the job is done.
6. If there is a difference start working through  the steps below

The primary report for determining the total of the debtor or creditor balances from the Receivables Management or Payables Management modules is the Historical Aged Trial Balance, also known as the HATB report.
There are several flavours of debtor and creditor trial balance reports, the best report for reconciliation purposes is the Historical Aged Trial Balance

Preparing for the Reconciliations

The following notes describe how to access, create or modify the HATB reports and gather the data required for the reconciliation calculations

Receivables Reconciliation Report and Balance

• Create or modify the Receivables HATB report
• The Historical Aged Trial Balance report, (HATB), is available from 
• Reports > Sales > Trial Balance > Historical Aged Trial Balance
• The Receivables Trial Balance Report Window is shown below

There may be an existing report that can be modified or a new report may be needed

Note: GP expects users to build up a library of reusable reports. These reports will be available to all GP users

Note: Be careful modifying an existing report that isn’t one of ‘yours’, reconfiguring a report may cause problems for colleagues

To create a new report

After selecting the Historical Aged Trial Balance option

1. Click  the new button, the window below will appear
2. Add a unique name in  the option field
3. Uncheck  the detail checkbox, at  this stage  a summary  view is all  that is required and is quicker to produce
4. Set  the print/age  date to  the cut-off date for  the reconciliation
5. Select  the GL Posting date option
6. Uncheck  the no activity  check box in  the exclude section, just because an account has no activity doesn’t mean it doesn’t have a balance
7. Click  the destination button, set  the output to  screen

Important: Whatever GP report is being run, always check the destination, some reports can contain hundreds of pages, why waste paper

8. Click save
9. The new report will appear in  the Option list

10. There are two ways to run the report

1. Highlight  the report and click insert, the report ID will appear in  the right hand pane, click Print in the menu bar to start producing the report
2. Highlight  the report and click modify,  the report will open in  the Trial Balance Report Options  window, check  and change any parameters, the click print from  the menu bar to produce the report

To modify  an existing report go to step 10.2 above

11. Remember to click save to retain any changes

Sample report from a test GP install with just two postings

Control Account Balance

It would be unusual but not unknown for an organisation to have more than one debtor or creditor control account. These notes will work on the assumption there is only one of each.

To obtain the balance from the debtors control account go to
Enquiry > Financial > Summary > Enter the GL account number for the debtors control account

The example below is from a test GP install with just two sales transactions posted to the same period

Payables Reconciliation Report and Balance

The process for generating the Payables balances is just the same as that described above for the sales balances, except

The payables HATB report is found under
Reports >Purchasing > Trial Balance > Historical Aged Trial Balance

The notes above for sales will work for creating or modifying the payables HATB report
To obtain the balance of the Payables control account use the same summary enquiry window as above, just substitute the GL account code for to the payables control account
 
Finding a difference

The following steps will help identify a difference between the balances held on the subsidiary ledgers and the related GL control accounts
Re-run the reports and re-calculate  the reconciliation  if any changes are made

1. Check the dates used on the HATB  reports, especially if  the  difference is large, also check  that  the GL posting date option has been selected
2. Check for unposted batches. Not just in  the subsidiary ledgers, but especially unposted GL batches

Check for transactions with a GL post date on or before the date used in the HATB report setup. Have the batches posted

3. Check for balances brought forward into the GL account. If the yearend rollover has not been run the GL control account balance may not be correct. It will be necessary to add the closing balance from the previous financial year, (assuming that is in fact an historical year) to  the balance in the current year

The HATB report balances are totals of all the transactions posted into the subsidiary ledgers and ignore the GL financial year structure

4. Run the reconciliation for the previous period. This reveals if  the difference  was caused by some retrospective postings and identifies where the errant posting might be.

Repeat the re-runs until a zero balance is achieved on the reconciliation

Note:  Having once spent many hours checking all the transactions in one period against the GL account, only to find someone had opened an early posting period and entered some transactions, this step is strongly recommended

5. Create a Financial Account Transaction  Smartlist report with the following fields

• Journal Entry
• Series
• TRX Date
• Account Number
• Debit Amount
• Credit Amount
• Originating Master ID
• Originating Document number

This report can be used for both the sales and payables reconciliation, just by changing the account number in the filter

6. Run the Smartlist report for the period being reconciled, filtering on  the TRX date field

Order the report by series, there should not be any postings from any other series than sales or payables, depending on which reconciliation is being done.  Investigate any posting that came from another series, especially any general ledger postings

Note: it can be possible to post GL journals directly into the control accounts, these postings will probably not have a corresponding entry in sales or payables

7. Use the built in reconciliation utility. This can be found in
   Microsoft Dynamics GP > Tools > Routines > Financial > Reconcile to GL

Configure the reconciliation by adding: 

• A from and to date range, the start and end date of the period being reconciled 
• The control account number
• The appropriate module
• An output path and file name

This process will create an Excel spreadsheet, it must be run from a machine that has Excel installed.

Click Process to start the reconciliation.

This routine has some built in matching criteria, these do not always work and can result in a lot of false positive results. However it is a useful tool and can help isolate the transactions that make up the difference.

The example below shows the very simple reconciliation from a test GP install
The key section will be unmatched transactions, this may contain the false positives, but these can usually be quickly weeded out to reveal transactions which are the potential problem postings

8. Use the Smartlist report from 5. Above to search for  the amount of  the difference

Amend the filter on the report to look for the amount of the difference in the debit amount column

Run this three times

1. Against the control account for  the  reconciliation period
2. Against the control account without any date filters
3. Against  he entire GL by removing all  filters apart from  the amount filter

Repeat for the credit amount column

Note: Frankly this is a long shot and if the difference is a common amount, i.e. 100.00, then the number of results is going to be outrageous. But it only takes 10 to 15 minutes to run and can actually, sometimes, be helpful
 
Hopefully the steps above have identified the transactions that make up the difference.

If not the following steps are time consuming and tedious and fall back to the old school ‘tick and bash’ manual checking  

9. Matching transactions

Create a new Smartlist report
Microsoft Dynamics GP > Smartlist > Sales> Receivables Transactions
That includes these columns:

• Customer Number
• Document Number
• Document Type
• Document date
• Sales amount
• GL Posting Date

Filter on the GL posting date field, setting the from and to dates to match the reconciliation period dates
Export the results to Excel
Sort by customer number and GL post date
Run the GL smartlist from step 5.
Export to Excel
Sort by Originating Master ID and TRX date
Copy and paste into a single worksheet
Match the document numbers and customer numbers\ID’s
Use Excel functions to compare the entries.
The =EXACT function is very useful
Total and net the GL debit and credit columns
Sort the RM\PM transactions by document type, create a new column for the ‘credit’ transaction types, i.e.  receipts, payments,  credit notes and returns
Total and net the ‘credit’ and ‘debit’ type columns
Compare the two new totals, any difference should match the difference on the reconciliation

The transaction comparison should reveal the problem transactions
Create a similar Smartlist report for purchasing transactions

10. Run the HATB report for smaller segments of  the reconcile period

Amend the GL postdate in the HATB report setup for half way through the reconciliation period

Use the Smartlist report from 5. create a running to date total in a new column, you will need to bring in the balance as at the end of the previous reconciliation period

A useful formula:-



Calculate the reconciliation formula
If it agrees to zero, re-run the process at 3 quarters the way through the period
If it shows the same difference as the original reconciliation calculation, try again at a quarter the way through the reconciliation period

The aim is to reduce the number of transactions that need to be checked 

11. Compare debtor or creditor balances

This one is really tedious and time consuming and may not be worth doing, especially if there are a lot of transactions

                Run the HATB report in summary for the previous reconcile period
                Open a blank spreadsheet\worksheet
                Add the debtor\creditor ID and the account balance
                Run the HATB report to the end of the current reconciliation period
                Add the account balances to the spreadsheet
                Calculate the difference for each account, which is  the change in  the period

There may well be account balances on the later HATB report that are not on the earlier report. This is OK, the account may have had a receipt or a payment may have been made

Run the Smartlist from 5.above to Excel
Sort by customer or vendor ID and transaction type
Separate the transaction types by ‘credit’ and ‘debit’
Run the Excel Sub-total routine on a change to the customer or vendor ID
Calculate the balance for each account
Compare these balances with those calculated from the two HATB reports

Note: this is an extremely tedious operation, only undertake it if you enjoy doing jigsaw puzzles face down

12. If none of these work, log a call with the support desk

Common Causes of a Reconciliation Difference

The most common causes of a difference in a reconciliation are:-

                Unposted batches, either in the subsidiary ledger, or more likely in the GL
                Transactions posted to the wrong date in the GL
                GL batches deleted
                The wrong GL control account used when entering transactions in the subsidiary ledger
                GL journal postings made directly into the control account

 There are other causes but the list above are the most common.

Appendix

Advantage will not accept any liability for error or omissions

The information contained in this artilce is for general guidance purposes only. It should not be taken for, nor is it intended as, financial or legal advice. Please make sure you conduct your own investigation on the issue addressed by this document and where appropriate seek out the advice of a financial/legal professional.  Please ensure that all processes or system changes are carried out  and fully tested, in a test environment, before implementation in your live environment.   
Advantage will not accept any liability for error or omissions in these documents.