What can you do before you call us for help? Read on to find out:
While we are very happy to help you isolate an out of balance, there are a number of self-help things you can do first.
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.
To prevent this from happening again, consider turning off the ability to post financial transactions to your control account. Make sure the below indicated box is unchecked on the Account Maintenance window:
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 (although official materials from Microsoft would say to - but then they also say to back up before you post a batch), 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
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 partner 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 have had ones like this).
- 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.
Heather Roggeveen is a MS Dynamics GP Consultant with Olympic Software. After 15 years of working with the end user all the way from designing the solution to user training, she has become a Dynamics GP expert. Heather regularly shares her knowledge, including tips and tricks for end users in her blog articles. Follow her on Twitter @HRoggeveen to be notified of her latest articles. You can also like Olympic Software on Facebook or follow us on LinkedIn or on Twitter @OlympicSoftware. For more information about Dynamics GP and how it could benefit your business, view the Dynamics GP page on our website or give us a call, 09-357 0022.
