How to Reconcile RM to GL when you have multiple RM control accounts

This situation presents us with a unique set of circumstances as you can not restrict the Receivables aged trial balance by account. It can be restricted by payment priority and class so with some forethought and planning you could have debtor classes set up that use a specific control account or something similar along the lines of payment priority. If you have not done this or you think some transactions may have been miss posted then using smartlists would be your next best thing.

In Smartlists you can create a report for the receivables transactions based on the system date range and then create a report for GL (account transactions) for the system date range. This way, you can compare when the information was physically entered in the system (even with a different document date between modules). If one of the modules has the transaction posted and the other is missing, you can determine if the transactions were posted with a different date in GL than in receivables.

Within smartlists, open up the receivables transaction option. Add the column Posted Date. This column stores the date from your computer of when the document was physically entered in receivables. Even if the document date was for the previous year, the posted date stores the date of when you physically entered it. You can compare the posted date to the posting date (holds the document date) or the document date as well. You can narrow your search by entering the posted date since you were last in balance. This will tell you all of the transactions that were entered in receivables since then and you can compare your GL report to that and see if one module is missing the transaction, has a different document date, or a wrong amount as well(possibly the batch was edited in GL before it was posted). To restrict this report to just one of your multiple control accounts you will need to add in the Account receivable Account Number field and then restrict your report by this in the search option. This is where you would put in the value of the Control account you wish to reconcile.

Next, you will want to open the account transaction option within smartlists. Add the columns Originating Posted Date and Originating Document Number. This stores the posted date (system date) from the originating module. Even if you change the batch date, this stores the date of when it was entered. You can compare the originating posted date to the trx date (document date) in account transactions as well to see if they vary from what your receivables transaction report returns. You may also want to add the Originating Document Number and Originating Master ID (debtor id) to this report, that way you can tick transactions off by using the document number instead of looking for amounts. This time to restrict the report to the specific control account you will need to add in the Account Number field to the search criteria and add your Control account number here.

These reports will give you a good guideline on trying to narrow down when something was entered, but there could be other possibilities that could be a cause such as a posting interruption (but this may have updated one module and not the other so you can compare what's missing), receivables history was removed (which only effects receivables and not GL).

The reporting can be automated further by using excel functionality. Both reports can be out put to excel from smartlists. If they are put into the same workbook on different sheets , you can then use the V lookup functionality to compare the Document number on the RM report to the originating document number on the GL report thus highlighting differences and giving you a place to start.

If you are interested in a certain sub set of transactions on the RM such as the unapplied/outstanding transactions then the RM side of the report can be restricted further by adding in the Current Trx Amount field to Columns. If the value of this field is greater than 0.00000 then there is an amount outstanding on the document and so this can be added to the search criteria.


If you have any questions then please contact the helpdesk@touchstone.co.uk