Hi Rita,
Here is a process you can use in GP without buying a utility or using anything fancier than Excel:
You need two Smartlist reports, one for GL and one for AP transactions. Both reports should have a date range filter on them for your monthly reconciliation process. For the GL, use the standard Account Transactions report, and make sure you have the columns included along with your debits and credits: Originating Master Id, Originating Master Name, and Originating Document Number.
Export this to Excel and create a balance column =(debits - Credits)
Next, open Smartlist report Purchasing > Payables Transactions, and add a filter for "Voided is equal to No" and "Posting Date is between (your date range)." Make sure you have the columns included: "Document Number, Document Type, and Document Amount."
Export this report to Excel.
Reconciling:
Take both exports and put them into a single Excel workbook, and in the AP transactions export, you need to switch the signs for the document amount column for all rows that have a document type of Invoice. This is because we are going to compare them to the GL and AP invoices should have a normal credit balance.
To do this quickly in Excel, in an empty cell, type -1 and copy it. While copied, select the range of document amounts that need to be switched, and right click, paste special > multiply. This will change all the numbers to negatives.
Next we need to go to the GL tab and in the first row with data, choose an empty column and use the COUNTIF() function to find any GL entries not originating in the AP sub-ledger. To do this, we need to use the "Originating Document Number" column and compare that to the "Document Number" column in the AP worksheet. We also want to not include any GL journal entries, so use this formula.
Let's assume:
the "GL" worksheet has the Originating Document Number in column J
The "AP" worksheet has the Document Number in column B
This is my formula:
=IF(GL!J2<>"", COUNTIF(AP!B:B, GL!J2), 0)
What this does is tells me that Excel has found AP entries made to the GL (X) number of times. If the result is 0, that means the entry is a void, GL entry, or multi-currency application entry, or an AP transaction that was posted in a different period than in the sub-ledger.
All the entries that have 0s are entries in the GL that do not exist in the sub-ledger for that period. If the sum of all these are not the variance between your GL and AP, then you need to do the same exact COUNTIF process using the AP > GL. Basically, it is the same process we just used except it is now looking for all AP transactions not in the GL.
You would get these if you posted to the sub-ledger, but the entries did not post to the GL. This is most commonly caused by not having AP post through the sub-ledger, and instead the batch is stopped at the financial module and deleted before the entries can be posted.
Once you do this process, the actual time it takes to reconcile can be done in minutes, and the majority of the time is spent waiting for the reports to export from Smart List :) You can also use this same exact process for reconciling AR > GL as well. Just remember to reverse your signs for anything that is a normal credit in the GL.
Hope this helps you,
Joshua Pelkola
