A step by step audit of discrepancies between the AR Aging reports and the AR GL Register.
This document assumes the report and GL have already been reviewed from the front end to confirm the discrepancy, and search for any obvious causes. The front end review should be done prior to this data review. Steps on how to perform the front end review can be found in this associated article: https://boldgroup.lightning.force.com/lightning/r/Knowledge__kav/ka06g000001m2FgAAI/view
Data review of AR sub ledger discrepancies:
Journal Entries
- Check for any manual journal entries hitting the AR GL Account
- Keep record of the total amount of these entries to see if they sum to 0.
- If the entries net to 0 then they are not the cause of the discrepancy and can be left alone. Any entries that do not sum to 0 will need to be removed or offset. This should be done by the customer.
Invoices
- Check that the total invoice amount in the subledger equals the total invoice amount posted to the GL.
- If they do not match, find the invoice(s) the discrepancy is coming from by comparing each invoice amount to the amount posted to the GL [See the query below].
Select i.Invoice_Number, invoice_date, g.Register_Number, i.Amount [InvoiceAmount], g.Amount [GLAmount]
from AR_Invoice i
join GL_Register g on i.Register_Id = g.Register_Id and Register_Type_Id = 1
where i.Amount <> g.Amount- Fix the issue if you can depending on what it is, or just bring it up to the customer.
- Remember that the customer may have already corrected the issue with a future entry, so it may be no fix is necessary.
- If a fix is necessary, remember that we cannot make changes to data from closed accounting periods without first discussing this with the customer.
Payments
- Check that the total payments coming from Unapplied Cash, and Advance Deposits Checks equals what is posted to the GL.
- If they do not match, identify the individual payments causing the discrepancy [See query below].
Select i.Invoice_Number, g.Register_Number, ud.Amount [PaymentAmount], ud.Date [PaymentDate], g.Amount [GLAmount], 'Cash' [Type]
from AR_Invoice i
join AR_Unapplied_Cash_Detail ud on ud.Invoice_Id = i.Invoice_Id and ud.Invoice_Id > 1
join GL_Register g on ud.Register_Id = g.Register_Id
where ud.Amount <> g.Amount
union
Select i.Invoice_Number, g.Register_Number, ud.Amount [PaymentAmount], c.transaction_Date [PaymentDate], g.Amount [GLAmount], 'EFT' [Type]
from AR_Invoice i
join AR_Deposit_check_Detail ud on ud.Invoice_Id = i.Invoice_Id and ud.Invoice_Id > 1
join AR_Deposit_Check c on c.Deposit_Check_Id = ud.Deposit_Check_Id
join GL_Register g on ud.Register_Id = g.Register_Id
where ud.Amount <> g.Amount
union
Select i.Invoice_Number, g.Register_Number, ud.Amount [PaymentAmount], ud.Date [PaymentDate], g.Amount [GLAmount], 'AdvDeposit' [Type]
from AR_Invoice i
join AR_advance_deposit_Detail ud on ud.Invoice_Id = i.Invoice_Id and ud.Invoice_Id > 1
join AR_Advance_Deposit c on c.Advance_Deposit_Id = ud.Advance_Deposit_Id
join GL_Register g on ud.Register_Id = g.Register_Id
where ud.Amount <> g.Amount
- Fix the issue if you can depending on what it is, or just bring it up to the customer.
- Remember that the customer may have already corrected the issue with a future entry, so it may be no fix is necessary.
- If a fix is necessary, remember that we cannot make changes to data from closed accounting periods without first discussing this with the customer.
Credits
- Check the total amount from all credits applied to invoices, to ensure that it equals the Total Amount Posted to the GL
- If not, you need to identify the individual credit application causing the discrepancy [See query below].
Select i.Invoice_Number, g.Register_Number, ud.Amount [PaymentAmount], ud.Date [PaymentDate], g.Amount [GLAmount], 'Cash' [Type]
from AR_Invoice i
join AR_credit_Detail ud on ud.Invoice_Id = i.Invoice_Id and ud.Invoice_Id > 1
join GL_Register g on ud.Register_Id = g.Register_Id
where ud.Amount <> g.Amount
- Fix the issue if you can depending on what it is, or just bring it up to the customer.
- Remember that the customer may have already corrected the issue with a future entry, so it may be no fix is necessary.
- If a fix is necessary, remember that we cannot make changes to data from closed accounting periods without first discussing this with the customer.
Erroneous Entry Types
Customers sometimes have other transactions posting to the AR account outside the normal AR entries (the normal entry types being invoices, credit applications, and payment applications). Unrelated entries hitting the AR GL account can cause discrepancies.
- Review the AR GL account and look for entries that are not invoices, credit applications, or payment applications.
- Investigate any unrelated entries to determine why they are hitting the AR GL account. Note that third-party applications and user error are common causes.
- Determine if this Is this something that happened sometime in the past and has already been corrected, or if it is an ongoing issue that you may need to bring up to the customer?
- Fix the issue if you can depending on what it is, or just bring it up to the customer.
- Remember that the customer may have already corrected the issue with a future entry, so it may be no fix is necessary.
- If a fix is necessary, remember that we cannot make changes to data from closed accounting periods without first discussing this with the customer.
Report Issues
- If after review no errors are found, but the AR is still out of balance, then the problem might just be a reporting issue. Profiling the report is the next step to figure out where a datalink is possibly broken. And correct any broken links found.
Important Notes:
- Sometimes you can identify the cause of the discrepancy and it might be a onetime thing that you can fix depending on the accounting period limitations, or it may t be something that you need to bring to the attention of the customer to have them fix on their own, but the most important thing is that the numbers must add up. So, let's say there is a $50 discrepancy between the AR aging reports and the register balance, you must make sure that if you are bringing up something for the customer to fix on their own, it should equal the $50 discrepancy that you have.
- I used the phrase “bring it up to the customer” vaguely quite a few times but this really entails not just telling them the cause of the discrepancy, but also demonstrating to them how it has impacted the reports and making recommendations to them on the proper way to fix it. Sometimes it may require collaboration with the customer such as the customer making general journal entries and the analyst tying the journal entries at the back end to the sub ledger entries etc.
