AR out of balance in Dynamics GP - some self-help tips
I have dealt with a few AR reconciliations over the last couple of weeks so thought it might be timely to address some of the areas that you, as a Dynamics GP user, can do yourself to troubleshoot. Of course we are more than happy to help our clients out with such issues, but often you end up spending money on our time to do tasks that you can easily do yourself. Often it is quicker too. So here are the areas to check out to isolate the issue.
Historical Aged Trial Balance vs Aged Trial Balance – these reports have two different functions. The Historical Aged Trial Balance gives you a point in time report allowing you to continue processing in a new month while still finalising the old. Run the Historical Aged Trial Balance based on GL posting date to check balances. Sometimes an issue arises in the Historical Aged Trial Balance itself. Because it is looking at all historical transactions, sometimes an old transaction can cause a corruption in the report. To check whether the report is the issue, run the Historical Aged Trial Balance as at now and the Aged Trial Balance as at now. If they show the same result, then it is definitely an out of balance.
Posting in the wrong period – the Historical Aged Trial balance will also help to see whether this is the problem. There are two options on the report – GL Posting Date or Document Date. GL Posting Date should always balance to the GL so this is the one you should run as part of your month end process. If you run it using Document Date and something has posted to the wrong period e.g. document dated March, posted into April, then the report won’t balance to the GL.
GL transaction posted to the AR Control Account – do a Smartlist search on your AR Control account for any transaction that did not originate from the Sales ledger (search type does not equal Sales). You can also run the Reconcile to GL routine which may help identify. If you have a large number of transactions, this can take a while to run. It puts a file to Excel that may help identify an issue.
To run, go to Microsoft Dynamics GP à Tools à Routines à Financial à Reconcile to GL. Make sure all your control accounts are included in the accounts field.
Reconcile Utility – so now you have determined that there is definitely something out of balance. The first process to run is the Reconcile Utility. This utility will (9 times out of 10) sort out an allocation issue. It is not uncommon for an allocated payment or credit to throw an error. E.g an invoice for $100 paid off by a receipt for $100. Both show an apply record, but one still shows an amount remaining of $100. If you leave unallocated, the amount remaining will go to $200. Reconcile will usually correct this.
You don’t need to do a backup before running this utility, and it is worth including it as part of your month end processes. I would do it as one of the first things before aging and statements.
To run, go to Microsoft Dynamics GP à Tools à Utilities à Sales à Reconcile. Run the “Outstanding Document Amounts” option first. Then go back and run “Current Debtor Information”. Even if the reports come back blank, still rerun your trial balances to see if the out of balance has disappeared.
Check Links – so the reconcile didn’t fix the problem? It could be worth running a check links. Check links is an inbuilt maintenance process within Dynamics GP designed to delete / update orphan records in the database. It literally “checks the links” between the tables. Now this one you must run a backup before doing. Because of the nature of the process, there is potential for it to go horribly wrong. Ensure that you print the report to file – don’t print to printer as it can be very long.
Check links can be run on all modules within the system. Unless you have been given good reason not to run it (i.e. it caused a data issue in the past), there is no reason not to try this as part of the analysis process. It is recommended that this be run as part of your year-end processes, but can be run more often. It can take a while to run if you have lots of data records. Users need to be out of the system.
To run, go to Microsoft Dynamics GP à Maintenance à Check Links. Change the series to Sales, insert all and click OK. It will ask you at this point for where you want to send the report. Send to screen and file.
Once the report has generated, check it before letting users back in. The sorts of things you should see are: the tax detail for record xyz could not be found and has been deleted. If you are seeing pages and pages of stuff and you don’t know whether it is an issue, make sure you give your partner a call to check.
Now what? Once you have done all this, if it is still out of balance, it is time to give your support desk a call. There is going to be a database level issue that we will need to resolve. Ideally, if you can manage to pin it down to a specific period, customer or document, that will be a great help. Some things you can do to try and isolate:
- Run the Historical Aged Trial Balance for the end of the prior period to see if the difference is the same. Keep moving back period by period to establish where the out of balance sits. While it may have become apparent during say March, if it is a database issue, the problem may stem back to January (I had one like this recently).
- Compare your printed trial balance from that period to the Historical Trial Balance generated now – can you find the difference by comparing customers?
- If you didn’t print your trial balance (which you should – at least in summary), can you get a copy of the backup from that period restored to your test company and do the comparison?
- Search the receivables tables for a document that matches the amount.
In both instances that I have been involved with recently that went to this stage, it was pure luck that we were able to identify the specific transaction without too much difficulty.
The chances are that it will relate to an apply record somehow. The way we would find it via the tables would be to make comparisons between the apply tables and the open tables to try and isolate. This could potentially take hours or days to do. The more you can isolate the issue, the better.
Unfortunately, these issues do crop up and need to be fixed. You can’t just leave them because they will result in your customers getting wrong information or an on-going “reconciliation” in your database.