Reconcile Accounts Payable to General Ledger

 NOTE: Sage Software Customer Support doesn’t cover walking-through the reconciliation of accounts. We can provide suggestions on areas to investigate.

The following suggestions assume that the G/L account numbers used in A/P Division Maintenance haven’t changed and they’re the correct accounts. (Postings to different G/L account numbers can be a source of discrepancies. For example, if you changed the G/L account used to post to Accounts Payable halfway through a period.)

Pre-Reconciliation

• Update all subsidiary journals or registers that post to Accounts Payable
  • A/P Invoice Register
  • A/P Manual Check Register
  • A/P Check Register
  • Any other relevant registers (including third-party applications)
• Update the Daily Transaction Register to the General Ledger

Determine Timing Of Issue

If the A/P Trial Balance is incorrect, but the General Ledger is correct

• A posting to Accounts Payable didn't update correctly
• The update to the General Ledger likely succeeded
• This issue could result from an update error or data corruption
• Correct it by
  • Adjusting an invoice
  • Creating a debit or credit memo
  • Making necessary General Ledger adjustments

If the A/P Trial Balance is correct, but the General Ledger is incorrect

• A posting failed to update the General Ledger
• This could also be due to an error or data corruption
• Once you identify the issue, debit or credit to the correct General Ledger account using
  • General Ledger, Main, General Journal Entry

If there’s a difference between the two reports

• Print both reports using the same date from a previous month
• If the difference remains, the issue likely occurred earlier
• Continue printing reports from earlier months until you isolate the discrepancy
   NOTE: If you have never balanced the account, the issue is likely procedural—reconcile monthly 
• If the report difference changes, the discrepancy likely occurred within the date range you're reviewing
• To narrow it down
  • Print the reports at the midpoint of the date range
  • If the discrepancy still exists, print at the quarter or three-quarter mark
  • Keep adjusting the date range to zero in on when the discrepancy first appears
  • For Example
    • If reports differ on 12/31 but match on 11/30, the issue likely occurred in December
    • Try printing the reports as of 12/15
    • Then either 12/07 or 12/22, depending on the results
    • Continue this process until you find the date of the difference
• How far back can you print the Accounts Payable Trial Balance Report
  • Find the Number of Days to Retain Paid Invoices setting in Accounts Payable, Setup, Accounts Payable Options. It determines how long Sage 100 keeps records of paid invoices before they’re eligible for removal
  • Sage retains open invoice records for the number of days specified after payment
  • After that period, the system can remove them during
    • Full Period End Processing
    • Full Period and Year-End Processing
    • Only Remove Invoices with Zero Balances
      (All found under Accounts Payable, Period End, Period End Processing)
  • For example
    • If you set the setting to 365 days for at least 365 days. You can print the report for any date up to 365 days in the past
    • If you change the setting from 100 to 365, it won’t show records older than 100 days
• How far back can you print the General Ledger Detail Report
  • The Number of Years to Retain Transaction History setting in General Ledger, Setup, General Ledger Options controls this
  • Increasing the retention period doesn't restore older data that was already removed

Determine The Source of the Issue

General Ledger Problem

• Run the General Ledger Detail Report
  • Set Source Type to G/L Journal Postings
  • Check if any transactions posted directly to the G/L Account

Non-A/P, Non-G/L Problem

1. Run the General Ledger Detail Report.
   • Set Source Type to Application Registers
   • Use Source Journals like SO, WO, PO
2. Look for incorrect G/L account use.
   • Example: I/M Product Line Maintenance uses an A/P account instead of Cost of Goods Sold
   • Or S/O Miscellaneous Charge Maintenance uses the wrong A/P account
3. Make correcting entries if needed.

Discounts Problem

1. Check Allow for Discounts in Accounts Payable, Setup, Accounts Payable Options.
   • If set to Lost, Sage posts discounts when invoices are recorded
2. Understand how Sage posts the entries.
   • $100 invoice with 2% discount posts
     • $100 to Expense
     • $98 to Accounts Payable
     • $2 to Discounts
3. Know why the A/P Trial Balance won’t match G/L.
   • Trial Balance shows $100
   • G/L shows $98
   • $2 difference causes imbalance
4. To reconcile, subtract the discount from the invoice balance on the Trial Balance Report.
5. It's possible you need a one-sided journal entry.

    

Steps to Find the Source of the Issue

1. Update the following items in Accounts Payable before proceeding.
   
   • Invoice Register
   • Check Register
   • Manual Check Register
   • A/P from A/R Clearing Register
2. Make sure the Daily Transaction Register updated to the General Ledger.
3. Print the General Ledger Detail Report in General Ledger.
   • Filter for the current period and A/P account only
   • Check if all entries came from Accounts Payable or Purchase Order
4. If an entry didn’t come from A/P or P/O.
   • Reverse it in the correct module
   • Reenter it in the correct module if needed
5. Use original hard copies of registers and journals.
   • Confirm entries aren’t missing from the General Ledger
6. If a transaction is missing in G/L.
   • Check if it posted to the correct account
7. Create a journal entry in G/L for any missing transactions.
   • Use the correct date, amount, and accounts

Other information

• Use the Accounts Payable Trial Balance Report to reconcile with the General Ledger
• Don’t use the A/P Aged Invoice Report
• The Trial Balance uses the posting date, which matches the G/L
• The Aged Invoice Report uses the document date, which can differ
• Check issues use the posting date

AP Aged Invoice Report doesn’t match the AP Trial Balance Report in Sage 100