How to reconcile Accounts Payable to General Ledger

 NOTE: Sage Software Customer Support does not cover walking-through the reconciliation of G/L accounts. But we can provide suggestions on areas to investigate.

The following suggestions assume that the G/L account numbers used in A/P Division Maintenance have not changed and they are the correct accounts. (Postings to different G/L account numbers can be a source of discrepancies. An example would be if the G/L account used to post to Accounts Payable was changed halfway through a period.)

Pre-Reconciliation
Make sure all subsidiary journals or registers that post to Accounts Payable are updated. This includes the A/P Invoice Register, A/P Manual Check Register, and A/P Check Register. (This may also include other registers or journals, such as third-party applications.) Also make sure that the Daily Transaction Register has been updated to the General Ledger.

Determine Timing Of Issue
If the Accounts Payable Trial Balance Report appears to be incorrect and the General Ledger is correct, it may be possible that a subsidiary ledger posting made to Accounts Payable did not update correctly, but the update to General Ledger did. This may have been due to an error during update or due to corruption of data records. A correction may have to be made, by adjusting an invoice, or creating a debit or credit memo, etc. Corrections may also have to be made to the General Ledger, as necessary.

If the Accounts Payable Trial Balance Report appears to be correct and the General Ledger is incorrect, it may be possible that a subsidiary ledger posting made to Accounts Payable did not update to the General Ledger correctly. This may have been due to an error or corruption of data as well. Hopefully the problem has been determined and the proper General Ledger account(s) can be debited or credited in General Ledger, Main, General Journal Entry.

If there is a difference between the two reports, print the two reports as of a month in the past - again using the same date to maintain consistency. If the difference amount remains, then likely the problem may have occurred in the month before and you may have to print the reports as of another month back, etc.

 NOTE: If you have to go back too many months because it has never been balanced, then part of the problem is partly procedural - users should have been balancing at least every month.

If the difference amount between the two reports changes, then it is likely that a discrepancy occurred within the interval between the dates used. Perhaps the reports can be printed as of half that interval, then at a quarter or three fourths interval, etc. to try to pinpoint the date the discrepancy occurred (e.g. If there is a difference of $500 between the two reports if printed as of 12/31/2009, but the two reports show no discrepancy when printed as of 11/30/2009. That would indicate that the discrepancy may have occurred sometime between 11/30/2009 and 12/31/2009, and it may be necessary to print the two reports as of 12/15/2009, and then later as of 12/07/2009 or else as of 12/22/2009, etc.

Note: How far back can you print the Accounts Payable Trial Balance Report? That is determined in Accounts Payable, Setup, Accounts Payable Options, by the "Number of Days to Retain Paid Invoices" setting. Records for open invoices are kept for that number of days after the invoices are paid. They are then eligible to be removed through Full Period End Processing, Full Period End and Year End Processing, or Only Remove Invoices With Zero Balances. (These three options are reached by accessing Accounts Payable / Period End / Period End Processing.) Generally, if the Number of Days to Retain Paid Invoices has been "365" for at least 365 days, then the report can be printed as of any date up to 365 days ago. (If the number was "100" but then changed to "365" only a few days ago, that will not bring back records beyond 100 days.)

Note: How far back can you print the General Ledger Detail Report? That is determined in General Ledger, Setup, General Ledger Options by the "Number of Years to Retain Transaction History" setting. (Again, changing the setting from 2 years to 5 will not bring back information from beyond the two years already set.)

Determine The Source of Issue
Below are SOME suggestions for helping determine the source...

1. General Ledger Problem - The problem could be due to postings made to the G/L account from outside of the A/P application. Try the "General Ledger Detail Report" with the Source Type set to "G/L Journal Postings" to see if anything was posted via G/L to that account.
2. Non-AP, Non-GL Problem - Try the General Ledger Detail Report with Source Type of "Application Registers" and Source Journals such as 'SO', 'WO', 'PO', etc. because the G/L account used may have been wrong. (e.g. a user mistakenly sets I/M Product Line Maintenance with an Accounts Payable account number in place of the Cost of Goods Sold field, or if they set the Accounts Payable account number for an item in S/O Miscellaneous Charge Maintenance.) Correcting entries may have to be made.
3. Discounts Problem - If the 'Allow for Discounts' setting in Accounts Payable Options is set to 'Lost', allowable discounts are recognized at the time invoices are originally recorded (discounts taken). The discount amount lost is recognized if the invoice is not paid on time. In trying to reconcile this back to the General Ledger, the Accounts Payable Trial Balance will not match the General Ledger. Below is an example:
   • If the option to "Allow for Discounts" is set to 'Lost', and a $100 invoice is entered with a 2% discount, below are the GL postings:
     • $100 - Expense
     • $98 - Accounts Payable
     • $2 - Discounts
   • When trying to reconcile back to the Accounts Payable Trial Balance Report, the Invoice Balance figure that you would use to reconcile would show as $100. Reconciling this back to the Accounts Payable Account would give you an out of balance by the $2 discount.
   • In order to reconcile with the "Allow for Discounts Allowed" option set to Lost, deduct the Discount Amount from the Invoice Balance amount on the Trial Balance Report. The difference should then reconcile back to the General Ledger
   • Note: It may be necessary to make a one-sided adjusting entry in G/L. For more information on how to perform a one-sided adjusting entry in General Journal Entry, search the Knowledge Base for "How to create a one-sided journal entry" in the resolution.
     As with any reconciliation, situations may be encountered where the Trial Balance report in A/P does not balance to the corresponding account in the General Ledger. Use the following steps to determine what has caused the balances to be different.
     1. In Accounts Payable, verify that the Invoice Register, Check Register, Manual Check Register and A/P from A/R clearing Register have been updated.
     2. Verify that the Daily Transaction Register has been updated to the General Ledger.
     3. Print the General Ledger Detail Report in General Ledger for the current period and only include Accounts Payable account. Review the entries to ensure all transactions posted to this account did originate from Accounts Payable or Purchase Order. If a transaction was posted to General Ledger that did not come from Accounts Payable or Purchase Order, reverse the entry in the appropriate module and reenter the transaction in the appropriate module, if necessary.
     4. Use original hard copies of the registers, journals, and the Daily Transaction Register to verify entries are not missing from the General Ledger.
   • If a transaction does not appear in the General Ledger, verify the posting was to the correct account.
   • Create a journal entry in the General Ledger for any transactions that do not appear. Make sure you use the correct posting date, amount, accounts, etc.
   • Note: The Accounts Payable Trial Balance Report should be used to reconcile with the General Ledger - not the Accounts Payable Aged Invoice Report. The reason for this is because the A/P Trial Balance uses the transaction (posting) date which matches what the General Ledger uses. The A/P Aged Invoice Report uses the document date ( invoice date) which can differ. Check issues will use the Posting Date.

How to create a one-sided journal entry in General Journal Entry
AP Aged Invoice Report does not match AP Trial Balance Report in Sage 100