One of the most common questions in support is actually a statement. My AP (AR) doesn’t balance to the GL. We are going to look at some tips for how to prevent this from happening. And, in addition to prevention, we are also going to look at some tips for quickly locating the source of the imbalance and finally, if none of the usual shortcuts help us, and a full reconciliation is required, we’ll show you how you can do that with as little pain as possible.
As we go through these tips, I am going to use the AP sub-ledger as an example. You can insert AR into everywhere you hear me say AP, as the process and tips will be exactly the same.
(Note: these tips are helpful for the 'theme' of reconciling sub-ledgers, but they are also helpful for many more functions than just this one!)
Preventing Sub-Ledgers from becoming out of balance to the GL
Tip # 1:
Setup the AP Trade account as a true control account, eliminating the possibility of having entries posted to that account from anywhere besides AP. Click on this link for detailed instructions on setting that up:
Tip # 2
Turn on GL Security and restrict user access to the AP Control Account. Click this link for detailed instructions:
Restricting access to the AP Control Account will still allow the user to post any invoices, payments or adjustments in the AP module (even though the unseen portion of these entries will be the control account), but it will not allow that user to see any of these entries in the GL. Nor will that user be able to create any entries by selecting or entering the AP Control account number into a transaction.
This is an extreme measure, for sure, and you need to take into consideration that this will completely eliminate the AP user from being able to reconcile this account. So, you need to consider division of labour and who will be responsible for reconciling the AP before implementing this restriction on all users.
Note though – that this feature has lots of other uses as well, not least of which is hiding payroll GL accounts for purposes of confidentiality.
Preparing for Reconciliation when (if!) it becomes necessary
Tip # 3
GL integration settings need to be configured to provide the simplest and easiest way to compare documents on a GL report with documents on an AP report. In AP Setup / GL Integration, on the Transactions tab, you can set what information from an AP Invoice will get sent with the transaction and show up on the GL Transactions Listing Report. For detailed instructions on configuring GL Integration in Accounts Payables, click this link:
Note that when these settings are changed, they will not affect any transactions that have been already posted. They only affect transactions going forward. This is why these things need to be considered well before any problem surfaces – so we have the best setup going to be able to find the problem.
Tip # 4
Set the prepayment account in AP Account sets to be a different account than the payables control account.
It is not “wrong” to set this to be the same account – but it can make reconciliation very confusing. Specifically when the prepayment is applied to a document, the debits and credits of that transaction will go in and out of the AP Control account several times. When matching transactions, it becomes more difficult to follow. The following illustration shows the same transactions processed using the Prepayment account as the same and then as different from the Control Account. Which one do you think would be easier to track?
Note: If you make the prepayment account different from the AP Control account, ensure that you are running the AP Aging report excluding the Transaction Type 'Prepayment' when you are comparing the AP Aging balance to the balance of the control account in the GL.
The ‘Short’ Reconciliation – Hoping for The Quick Fix
Tip # 5
Things to check for in hopes of finding the out of balance amount quickly and easily:
- Ensure all batches are posted in the GL – including error batches.
- Check for any batches from AP that may have been deleted in the GL
- Check for any entries that have been posted to the AP Control Account which do not come from AP. Print the GL Transactions Listing report and look for any source code that does not start in AP.
It is also possible to create a source journal profile that includes all modules except AP, and use that source journal to print the Transactions Listing Source Journals report. Go to GL Setup / Source Journal Profiles to create the profile, then go to GL Reports and print the Source Journals report for the AP Control Account. Only transactions which did not come from AP will be displayed. While it takes a bit of time to create the source journal profile initially, it will more than make up for time in searching through a report, and this resource can be used over and over again.
For more detailed steps, click here: Customize GL Transactions Listing Report Using Source Journals
- Compare the AP to the GL at the end of year (or some point in the future) to see if the accounts are out of balance in an earlier period due to date issues. One of the most common date problems is having the invoice or payment date in AP post to a different fiscal period in the GL, due to locked periods. This is often reflected in the original AP transaction with different document dates and posting dates.
For a clear description of the differences between Document Dates and Posting Dates, and how to use them to your advantage, click on this link: Document Date VS Posting Date in AP. Print the AP Aging report to compare to the GL using the Posting Date, or year/period, when matching to the GL.
When All Else Fails and a Full Blown Reconciliation Needs to be Done!
Tip # 6
Find the point in time where the AP and the GL were last in balance. You do this by printing the AP Aging report for each period going backwards in time, and comparing with the GL balance for that period, until you find one that matches. This will be the starting point for your reconciliation. You must have a balanced starting point. For example, let’s say it is year end and we are checking to see if our sub-ledger is balanced to the GL, and find that it is not. We go back through the previous periods until we find the point where it was in balance, and then start our reconciliation in the subsequent period.
It may be that once we reconcile the first period, all of the others become balanced also because we are not carrying forward the imbalance from the prior period. If we were to reconcile a period (in this example) which did not have a balanced starting point, we may not find any problems at all, because this period is ony out of balance because the prior period is out of balance and that amount is being carried forward. So – this step is extremely important so that you are not wasting time reconciling a period that is not truly out of balance. You can confirm this is the case, if all the periods are out of balance by the same amount. If the out of balance amount fluctuates, then there is likely something out of balance in more than one period.
Tip # 7
What if we cannot find a point where they were ever in balance? Then we need to make one. Normally year end is a good time to force the sub-ledger into balance before starting the New Year. The Accounts Payable module is generally taken to be the correct value, since that is where the source documents are entered. A GL journal entry can be posted to adjust the GL balance to the AP outstanding amount and then we have a starting point. One side of the entry will be the AP control account, and you will need to confer with your accountant to decide where to post the other side of the entry. But, if we have our control account set to not allow transactions from any module besides AP (see Tip #1), how do we make a GL entry? We need to add the GL module to the subledger tab in the GL Account screen, post the entry, and then remove the GL subledger again. Because this is such a simple process, we need to ensure adequate security is setup so that only select users are able to perform this function. This is going to be done by setting up the appropriate security groups in Administrative Services / Security Groups.
The access which controls this function is called Account Maintenance. Once the groups are setup to grant or deny access to this function – assign the group to the user under Administrative Services / User Authorizations.
Tip # 8
Which reports to print in GL and AP and how to compare them?
- The GL report is the GL Transaction Listing report found under GL Reports.
- On the AP side, print the AP G/L Transactions report found under AP Transaction Reports
- Note that the sum total of the transactions from the AP G/L Transaction report should equal the Net Change for that period on the GL Transaction Listing report – so checking the totals firstly might be a shortcut. (The difference will be the amount that you are out).
- for a detailed explanation of the AP G/L Transactions report, how to print it and export it to Excel for best comparison to the GL report, review these articles:
- Reconciling Accounts Payables
- How to Reconcile AP using the AP G/L Subledger Report
- Compare the AP and GL reports. You will see that the GL Integration setup (see Tip #3) is now going to help us. Once the comparison has been completed, there should be entries in AP that are not in GL, and/or entries in GL that are not in AP.
- When these transactions have been identified and researched, they can be fixed, and procedures put in place to avoid them in the future.
Your Subledger is now balanced to the GL - Congratulations!
There is a product called Toolpac, by Systronics, that integrates with Sage and helps with reconciliations. Toolpac will help with Tip #6, finding the last point where the AP & GL were in balance by quickly comparing the net changes in each period.
Toolpac will also help with Tip # 8 - when you have to compare the reports in AP with the reports in GL to find what is missing from each module. Toolpac does the comparison for you and provides you with a list of what is in AP that is not in GL, and what is in GL that is not in AP!
You still need to do the research to find out why these items are out, and make the necessary adjustments to correct them.
Toolpac has other reconciliation functions for other modules as well - AR, IC, OE, and PO. You can find more information on this product here: Toolpac and then give us a call with any questions, or if you would like to add this product to your system.