For reconciling the AP subledger to the GL, the AP G/L Transactions report is one of the best reports to use to compare all the transactions that have gone through AP with all the transactions that have gone through the GL. This report is found under AP Transaction Reports.
For example purposes, let's say that the AP balance in June is 26,302.18 and the GL balance is 26,619.43 giving us an out of balance amount of 317.25. Select all the Batch Types (invoice, payment, adjustment) and identify which posting sequences relate to the period that you are reconciling. It is best to have some overlap – for example, if you are reconciling June, select some posting sequences from the end of May through the beginning of July to ensure that all the June transactions will be included. Ensure the Report Format is Detail, and Sort by Account No.
Once the report has generated, export it to Microsoft Excel 2007 (XLSX) Data-only. This format is not pretty, but it makes it easier to manipulate the data without alot of formatting issues getting in the way.
I usually make a copy of the worksheet, so I can go back to the original and start again in case I make a mistake. On the copy, locate the transaction list for the AP Control account. Delete all rows above this account, and delete all rows below this account, so the only transactions on the spreadsheet are the ones going into the AP Control Account.
Now do a data sort (select all the data, go to the Data tab and click on Sort) on Column A (year/period) to put them in ascending order. Delete any lines that are not in the period you are reconciling. Now you have a complete list of all the transactions that have gone through the AP module into the AP Control account in the GL, for one period. Do a sum of all the debits and credits and a total of each of these to find the net change in AP.
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 those totals before doing anything else will be important. If they are the same, then there is no imbalance amount for that period. If there is a difference, that difference will be the amount that you are out for that particular period. By checking and verifying this total, you can also confirm that you have included all the transactions for the select period in the posting sequences selected for the original report. For our example above, here is the listing of the two reports:
The difference between the totals on the AP report and the Net Change for the period in the GL Transaction report is 317.25, which is the amount out of balance for the sub-ledger.
The next step is to compare the transactions listed on the two reports to locate the differences. Once the source of the problem has been found, it can then be fixed. Note that, if these two values were equal, then the out of balance amount would not be related to the transactions within this period, and we would need to look in another fiscal period for the problem.