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
- Run the HATB report, in summary mode, set for the reconciliation date
- Obtain the balance on the GL control account(s) as at the reconciliation date
- Compare the balance on the HATB report with the balance on the GL control accounts
- If there isn’t a difference the job is done.
- 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
- Click the new button, the window below will appear
- Add a unique name in the option field
- Uncheck the detail checkbox, at this stage a summary view is all that is required and is quicker to produce
- Set the print/age date to the cut-off date for the reconciliation
- Select the GL Posting date option
- 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
- 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
- Click save
- The new report will appear in the Option list
- There are two ways to run the report
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- Against the control account for the reconciliation period
- Against the control account without any date filters
- 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
- 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
- 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
- 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
- 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
| Glossary |
|
| Term | Description |
| Debtor | Customer |
| Customer | Debtor |
| Creditor | Vendor, supplier |
| Vendor | Creditor, supplier |
| Supplier | Creditor, vendor |
| AP | Accounts payable, Purchase ledger, Payables Management |
| Purchase Ledger | AP, Accounts payable, Payables Management |
| Accounts payable | AP, Purchase ledger, Payables Management |
| Payables Management | AP, Accounts payable, Purchase ledger |
| AR | Accounts receivable, Sales ledger, Receivables management |
| Accounts Receivable | AR, sales ledger, Receivables Management |
| Sales Ledger | AR, Accounts receivable, Receivables Management |
| Receivables Management | AR, Accounts receivables, sales Ledger |
| Historical Aged Trial Balance | Report used to determine the outstanding balances on debtors or creditors |
| HATB | See Historical Aged Trial Balance |
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.
