Receivables & Collections

Get Involved. Join the Conversation.

Topic

    Felix Beaudoin
    AR to GL Reconciliation Report does not Match our AR AgingAnswered
    Topic posted April 5, 2019 by Felix BeaudoinGold Crown: 30,000+ Points, tagged Collections, Financials, General Ledger, Period Close / Reconciliation, Receivables 
    386 Views, 6 Comments
    Title:
    AR to GL Reconciliation Report does not Match our AR Aging
    Summary:
    AR to GL Reconciliation Report does not Match our AR Aging
    Content:

    Hello Gurus!
    Every month-end, the AR Manager completes her reconciliation activities to make sure that the AR subledger and GL balances. There are always a few journal entries going to the wrong accounts which get correct but overall, the AR to GL always balances after these corrections.

    The issue we are facing is that our AR Subledger Balance in that AR to GL Reconciliation report does not match our "Collections Aging by Collector 7 Bucket Report". Therefore, we are wondering what could be causing this discrepancy? This doesn't relate to the GL and technically speaking, shouldn't every AR transaction be part of our Aging?
    I had an SR open and they confirmed that we are running the report correctly but we are always a few thousand dollars off from both reports.
    Has anyone experienced this before and if so, what were your findings?
    Let me know, thanks!
    Regards,
    Felix

    Version:
    18C

    Best Comment

    Alexey Shtrakhov

    Hi Felix,

    Official reconciliation report for AR is "Receivables Aging by General Ledger Account Report". This report is also mentioned on the "Oracle ERP Cloud Period Close Procedures" document...

    The balances in this report can be compared with the General Ledger balances for the same control accounts to reconcile.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    page 28. document attached also.

    10. Review Receivables Aging By General Ledger Account Report

    The Receivables Aging By General Ledger Account report shows the total customer outstanding balance by aging buckets to group invoices by the number of days an item is overdue, such as Current, 1-30 days late, 31-60 days late, 61-90 days late, etc. This information is important because it gives a complete picture of the customer debt and potential ability to collect the outstanding amounts. Typically, the older the invoice – the longer is it outstanding – the less likely it will be collected.


    • The Aging report total should be reconciled to the Receivable End Balance in the Receivables to Ledger Reconciliation Report. Aging and accounting are two different ways to represent the same data, so their totals should agree. If they don’t agree, consider these causes:
    • The Aging report is run by As of Date, while the Reconciliation report is run by accounting period. The As of Date of the Aging report must coincide with the period-end date of the Reconciliation report to compare like data.
    • The Aging report only displays items that have been accounted. Transactions that do not have final accounting may be missing from the Aging Report.
    • The accounting may have posted to accounts not included in the Reconciliation report.
    • There may be activity that has only an accounting impact, which is not represented on the Aging because it does not affect the customer outstanding balance. This could include a manual journal or intercompany accounting.
    • There was a discrepancy in a prior period. The beginning balances on the Reconciliation report should always agree with the prior period Aging report total. If it is not the same, you may have to go back several periods to identify the original out-of-balance condition.
    • The setup can determine if a transaction amount will update the customer balance, and if accounting will be generated for that activity. Some invoices are imported into Receivables for tracking purposes and do not generate accounting.
    • There was a credit/rebill for an internal accounting error. Rather than creating and issuing a credit memo and new invoice to the customer, the credit memo and invoice were created such that they do not update the customer balance, but do generate corrected accounting that posts to the General Ledger.

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Now back to your report.
    I think report mentioned by you cannot be used for recon purpose.
    Anyway what I would do now is:

    1) Run Receivables Aging By General Ledger Account Report and verify that the balance is equal to your ledger balance
    2) Run Receivables Aging By General Ledger Account Report including invoice details and extract it to excel
    3) Run "Collections Aging by Collector 7 Bucket Report" including invoice details and extract it to excel
    4) Do some VLookup magic :) in Excel to find out what is causing the difference.

    As soon as you identified the invoices/transactions which are causing the issue maybe you will be able to verify what kind of scenarious are causing this situation. It can be one of the above mentioned in the document...

    Hopefully this will help you.

    With respect
    Alexey

     

     

     

    Comment

     

    • Alexey Shtrakhov

      Hi Felix,

      Official reconciliation report for AR is "Receivables Aging by General Ledger Account Report". This report is also mentioned on the "Oracle ERP Cloud Period Close Procedures" document...

      The balances in this report can be compared with the General Ledger balances for the same control accounts to reconcile.
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      page 28. document attached also.

      10. Review Receivables Aging By General Ledger Account Report

      The Receivables Aging By General Ledger Account report shows the total customer outstanding balance by aging buckets to group invoices by the number of days an item is overdue, such as Current, 1-30 days late, 31-60 days late, 61-90 days late, etc. This information is important because it gives a complete picture of the customer debt and potential ability to collect the outstanding amounts. Typically, the older the invoice – the longer is it outstanding – the less likely it will be collected.


      • The Aging report total should be reconciled to the Receivable End Balance in the Receivables to Ledger Reconciliation Report. Aging and accounting are two different ways to represent the same data, so their totals should agree. If they don’t agree, consider these causes:
      • The Aging report is run by As of Date, while the Reconciliation report is run by accounting period. The As of Date of the Aging report must coincide with the period-end date of the Reconciliation report to compare like data.
      • The Aging report only displays items that have been accounted. Transactions that do not have final accounting may be missing from the Aging Report.
      • The accounting may have posted to accounts not included in the Reconciliation report.
      • There may be activity that has only an accounting impact, which is not represented on the Aging because it does not affect the customer outstanding balance. This could include a manual journal or intercompany accounting.
      • There was a discrepancy in a prior period. The beginning balances on the Reconciliation report should always agree with the prior period Aging report total. If it is not the same, you may have to go back several periods to identify the original out-of-balance condition.
      • The setup can determine if a transaction amount will update the customer balance, and if accounting will be generated for that activity. Some invoices are imported into Receivables for tracking purposes and do not generate accounting.
      • There was a credit/rebill for an internal accounting error. Rather than creating and issuing a credit memo and new invoice to the customer, the credit memo and invoice were created such that they do not update the customer balance, but do generate corrected accounting that posts to the General Ledger.

      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Now back to your report.
      I think report mentioned by you cannot be used for recon purpose.
      Anyway what I would do now is:

      1) Run Receivables Aging By General Ledger Account Report and verify that the balance is equal to your ledger balance
      2) Run Receivables Aging By General Ledger Account Report including invoice details and extract it to excel
      3) Run "Collections Aging by Collector 7 Bucket Report" including invoice details and extract it to excel
      4) Do some VLookup magic :) in Excel to find out what is causing the difference.

      As soon as you identified the invoices/transactions which are causing the issue maybe you will be able to verify what kind of scenarious are causing this situation. It can be one of the above mentioned in the document...

      Hopefully this will help you.

      With respect
      Alexey

       

       

       

      • Felix Beaudoin

        Thank you Alexey! You are awesome! :D

        We had indeed tried running those steps provided in the Period Close documentation but were always off by a little bit. After giving it another try and removing the "Receipts at Risk" by flagging them as "Do not Show", the balances are 100% aligned. We will proceed with running both reports "Receivables vs Collections" and attempt some VLOOKUP magic as you describe it to close-off that discrepancy. Just very happy that our subledger balances as it is critical for AR to be balanced every month to avoid needing to go back 4 months from go-live.

        As always, we greatly appreciate your help and detailed responses!
        Much respect to you!
        Regards,
        Felix