A step-by-step audit of discrepancies between the AR Aging reports and the AR GL Register.
This article 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 before this data review. Steps on how to perform the front-end review can be found in this associated article: Reconciling SedonaOffice Reports to the GL*
Data review of AP sub-ledger discrepancies
Journal Entries
Check for any manual journal entries hitting the AP GL Account
Keep a 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 sub-ledger 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 v.Vendor_Code, i.Invoice_Number, invoice_date, g.Register_Number, i.Amount [InvoiceAmount], g.Amount [GLAmount]
from AP_Invoice i
join AP_Vendor v on v.Vendor_Id = i.Vendor_Id
join GL_Register g on i.Register_Id = g.Register_Id and Register_Type_Id = 12
where i.Amount <> g.AmountFix 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 Checks and Voids equals what is posted to the GL.
If they do not match, identify the individual payments causing the discrepancy [See query below].
Select v.Vendor_Code, i.Invoice_Number, g.Register_Number, ci.Payment_Amount [PaymentAmount], c.Check_Date [Date], g.Amount [GLAmount], 'Payment' [Type]
from AP_Invoice i
join AP_Check_Invoices ci on ci.AP_Invoice_Id = i.Invoice_Id
join ap_check c on c.Check_Id = ci.Check_Id
join AP_Vendor v on v.Vendor_Id = i.Vendor_Id
join GL_Register g on ci.Register_Id = g.Register_Id and Register_Type_Id = 9
where ci.Payment_Amount <> g.Amount
union
Select v.Vendor_Code, i.Invoice_Number, g.Register_Number, ci.Void_Amount [VoidedAmount], c.Void_Date [Date], g.Amount [GLAmount], 'Voided' [Type]
from AP_Invoice i
join AP_Check_Invoices ci on ci.AP_Invoice_Id = i.Invoice_Id
join ap_check c on c.Check_Id = ci.Check_Id
join AP_Vendor v on v.Vendor_Id = i.Vendor_Id
join GL_Register g on ci.Void_Register_Id = g.Register_Id and Register_Type_Id = 32
where ci.Void_Amount <> g.Amount
order by Type, Date DescFix 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 vendor credits to ensure that it equals the Total Amount Posted to the GL
If not, you need to identify the individual credit causing the discrepancy [See query below].
Select v.Vendor_Code, i.Credit_Number, g.Register_Number, i.Amount [CreditAmount], i.Credit_Date, g.Amount [GLAmount]
from AP_Credit i
join AP_Vendor v on v.Vendor_Id = i.Vendor_Id
join GL_Register g on i.Register_Id = g.Register_Id Register_Type_Id = 22
where i.Amount <> g.AmountFix 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 posted to the AP account outside the normal AP entries (the normal entry types being Invoices, Checks, Voided Checks, and Credit). 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 one-time thing that you can fix depending on the accounting period limitations, or it may 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 AP retroactive 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.
used the phrase “bring it up to the customer” vaguely quite a few times but this 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.
