Detail Balancing of AR to Ledger Trial Balance

Goal:

Provide an overview of month end processing for ACR
Review of balancing subsidiary to ledger accounts
Detailed Study of how invoices and receipts post to the ledger.
How to Find Documents that are causing balancing issues between the ACR Subsidiary and the ledger.
How when/how you run your balancing reports can make a difference.

Overview of Month End Processing for Ledger:

Data entry completed for SJI Period.
Set your earliest date allowed to the next month.
Everyone out of the ACR package.
Select your SJI period. SJI > Select SJI Period > Select period to be default. All data keyed or edited between these date ranges will be pulled into the SJI and summarized into ledger entries by location code, transaction Code, then source codes.
Run the Create SJI process to create the SJI entries. SJI > Create System Accounts > Accounts Receivable
1. Send reports to the spool.DO NOT DELETE until Fiscal Year is closed.
Select the Ledger Period. LDG > Select Ledger Period > Select Default Period.
Post the System Journal entries in LDG > Journal Entries > System Journal Entries Answer YES to Accounts Receivable (note: AGRIS only allows you to transfer the entries once. So even if you answer yes to a package that has already posted, don't worry. It can only be pulled once. You will not double the ledger balances by just leaving all questions at yes.)
Run the Ledger trial Balance report in LDG > Reports Manager > Journal Entry Reports > Standard reports > Trial Balance Report.
1. This is the report that you use to balance to the subsidiary A/R and Prepayment Accounts.
Run the AR Balance Report with GL Reconciliation for SJI Period Ending Date
1. Everyone MUST be out of the ACR package when this report is run. Run after an aging function has been run.
2. ACR > Reports Manager > AR Balance Reports > Standard > AR Balance Reports > AR Aging Analysis by Location codes > Additional Options Screen > Answer YES to "Calculate Ledger Reconciliation Balances?" > Key in the ending date that matches the ledger period ending date.
3. This report takes the current balances and backs out the invoices/receipts to get back to the balance on the date requested.
The ending balance of the Ledger AR and Prepay accounts should match the AR Balance Report.

What is happening during this process?

The SJI process gathers all the invoices (item and manual) and receipts (payments and prepayments) that were keyed into the ACR for the SJI time frame. It looks at each transaction code (assigned in SJI System Accounts) and assigns the dollars and quantities of these documents to a ledger accounts. So if you know what transaction code should be hitting your ledger account, then you know what document to be looking for when balancing.
Item Invoices: Debit Accounts Receivable for the amount of the invoice (AR-transaction code)Credit Inventory Sales for the amount of the Lines Items (SL-Transaction code)Credit the Sale Tax Payable for the amount of tax (S1-S4 Transaction code)Debit the Inventory Cost of Sales for the cost of the product (CS-Transaction code)Credit the Inventory Asset for the cost of the product (IV-Transaction Code)
Manual Invoices: Debit Accounts receivable for the amount of the invoice (AR-Transaction Code)Credit the GL Account (Finance Charge income, for example)(manual distribution)
Payment (cash or ROA) Debit Bank Code Account (PM-transaction code which is normally replaced by bank code)Credit Accounts Receivable (AR-Transaction Code)
Prepayments: Debit Bank Code Account (Bank Code)Credit Prepayments contra-asset account (PP-Transaction Code)
Application of Prepayment to invoice: Debit Prepayments contra-asset account (PP-transaction code)Credit Accounts Receivable Invoice Number (AR-Transaction code)

When things go wrong

Begin to compare the AR Subsidiary Balance Report to the ledger trial balance and they do not match.

Look for un-posted SJI entries. SJI > Report Manager > SJI Entry Reports > Standard > Un-posted SJI Entries
Look for manual Ledger Posting that has a description of something other than ACCOUNTS RECEIVABLE.LDG > Inquiry options > Account Inquiry > view the detail of the account.
Verify that your subsidiary reports were run after aging and with everyone out.
Focus on what we have learned from the document types.

Document Type Focus

When the subsidiary does not balance to ledger, it has to be a document that is affecting it.

Invoice was posted after SJI was run (example, posting fuel pump sales at import time and tickets importing in prior month)
invoice was voided after SJI was run (like reversing a finance charge)
Payment was reversed after SJI was run (like reversing a prepayment from an invoice)
Payment was keyed after SJI was run (one last ROA payment so customer does not get finance charge)

Balancing Accounts Receivable Ledger Account - 1120

Step One:
Think about how you are balancing a transaction code on a document to the ledger. Example: balancing Accounts Receivable subsidiary, only the AR-Transaction code should be hitting the 1120 ledger account. And if only AR transaction codes should be hitting the AR ledger account, and then only invoices (item or manual) and payment (cash or prepay) should be hitting that ledger account. Because that is the only time the AR-transaction code is used.
Step Two:
Build a current SJI Exec Work file that includes the current month you are trying to balance. It can include more time than the current month.
Step Three:
Run the standard Ledger Account detail report for the AR Ledger Account. Compare the ending Transaction Total with the Trial Balance Change Amount to see if they are the same. Or the change amount from a Ledger Account Inquiry. If they are NOT, then data has been changed AFTER the SJI was created. This could be new data added or reversed, but something has been done to documents with AR transaction codes in the month that you have already gathered the data for the ledger.

Here, the amounts $7,827.00 different. This is stating that if we re-ran the SJI's again for this time period, it would post a larger credit to the ledger. What was invoice or payment entry/edit/reverse was backdated?
Step Four:
If AR is out of balance, then OTHER accounts are out of balance as well. If we can find the other accounts then it will give us a heads up of the type of invoice or payment record that we are looking for.

Run the SJI Execution ID report called MIMIC SYSTEM JOURNAL REPORT using the parameter settings to limit this report to just entries from the AR package. SJI > Report manager > Execution ID reports > Standard Reports > MIMIC SYSTEM JOURNAL REPORT > Key in date range to match ledger period > go to Additional Options > Answer NO to everything but AR. This will give you only the entries that posted with the ACR SJI Creation Process

Compare this report to the standard SJI Entry report MIMIC EXECUTION ID REPORT. SJI > Report manager > Journal Entry Reports > Standard Reports > MIMIC EXECUTION ID REPORT >Select AR to AR for Source parameter and the current period
This gives you all the entries that did post.

Now you can see that it is not just AR Receivable that is out of balance, but also other accounts. Just looking at the AR comparison, we are off by $841.00 and if you look down the list, so is Finance Charges. Because the Finance Charge credit is $841.00 less in the work file than what we did post, I think about the document. It would debit AR and Credit finance charges. So if finance charges and A/R are less, then an invoice was reversed AFTER SJI's were run.
When looking for reversals, keep in mind that you can void which leaves a document behind or you can delete which erases all trace of the document.

Run a listing of voided invoice to look for the finance charge.

If not there, then examine the SJI Creation Invoice reports for the finance charge amount.

This finds the difference for the AR and Finance Charge account. Invoice 999-JZ0034 was reversed and answered YES to re-use document number. So it is no longer in the system.
Step Five:
Make a manual ledger entry to back out the finance charge from the ledger. Debit Finance Charge income $841.00Credit Accounts Receivable asset $841.00

Balancing Customer Prepay Account - 2510

Step One:
Think about how you are balancing a transaction code on a document to the ledger. Example: balancing Prepay subsidiary, only the PP-Prepayment Transaction code should be hitting the 2510 ledger account. And if only PP transaction codes should be hitting the PP ledger account, then only applications to invoices and accepting cash for prepayment should be hitting that ledger account. Because that is the only time the PP-transaction code is used.
Step Two:
Build a current SJI Exec Work file, if one is not already built.
Step Three:
Run the standard Ledger Account detail report for the Prepay Ledger Account.

Compare the ending Transaction Total with the Trial Balance Change Amount to see if they are the same. Or the change amount from a Ledger Account Inquiry.

If they are NOT, then data has been changed AFTER the SJI was created. This could be new data added or reversed, but something has been done to documents with AR transaction codes in the month that you have already gathered the data for the ledger.
Step Four:
If Prepay is out of balance then OTHER accounts are out of balance as well. Run the MIMIC reports for the Prepay ledger account using the steps from balancing AR.
Step Five:
In this case, we can tell just from the LEDGER ACCOUNT DETAIL REPORT of Step Four that the issue is a reversed prepay. The TRIAL BALANCE REPORT only knows about the 379.00 credit, which knowing document would be a prepay entry.But the SJI Work File LEDGER ACCOUNT DETAIL report shows that document was voided.So we need to reverse the prepay entry in the ledger with a manual entry.
Debit Prepay Holding $379.00Credit Bank Account $379.00

Snowball Effect of Balancing AR and Prepay

Step One:
As you make the entries in your research we will still be showing the following accounts different from the original SJI entry. Balancing the subsidiaries will throw the other accounts more out of balance between what did post and what would post. And since these entries came from the Accounts Receivable SJI Entries, there is still cleanup to do.

The Ledger posting is going to go to $80,000 after the reversed prepay is corrected. So now the work file will show $405.53 more in bank than what did post in the original SJI + prepay reversal correction.
The shortages are not amounting to:
Bank – Checking $405.53 Inventory – Feed - Pet $331.25Sales Tax $26.53Feed – Pet Sales $379.00Feed-Pet Food cost of sales $331.25
Step Two:
Going back to how each document has a set of transaction codes that handle the debits and credits of that document.
Look at the shortages above. There is a common link to them. Since we are only looking at the Accounts Receivable package, this has to be an invoice. It is just missing the AR-Transaction code. Instead, there is a bank Transaction code.
So this would be an item invoice that was paid for by cash.
Step Three:
This is an invoice, followed by a payment to the invoice. To find out what invoices, we will run an SJI work file LEDGER ACCOUNT DETAIL REPORT for one of these accounts and see what document number appears.SJI > Report Manager > Exec Work file Reports > Standard > Ledger account detail > set parameter for Sales Tax account 2060 to 2060 > date range for SJI period

There is only one document that makes up the sales tax amount.So now print the standard SJI Work File report called Transaction Analysis for that document number. SJI > Report Manager > Exec Work File Reports > Standard > Transaction Analysis > filter for this location and document number

This proves that there was another document keyed in the month AFTER the SJI's were created.
Step Four:
Create a manual ledger entry to post this document.
Dr 1120 Accounts Receivable $405.53Dr 5340-0N Pet Food Cost of Sales $331.25Cr 1340-0N Pet Food Inventory $331.25Cr 2060-01 sales Tax $ 26.53Cr 4340-0N Pet Food Sales $ 379.00
Step Five: This means that only our bank and A/R are different now but we can see the amount is $405.53 which would be a payment for the above invoice.
To verify, run the SJI Work File LEDGER ACCOUNT DETIAL for that bank code 1021-06. Look for the receipt amount of $405.53.SJI > Report Manager > Exec Work file Reports > Standard > Ledger account detail > set parameter for Sales Tax account 1021 TO 1021 > Detail code 06 to 06 > date range for SJI period

And in this report, you can see the backdated receipt of $405.53 that needs to be keyed into the ledger.
Step Six:
Create a manual ledger entry to post this document.
Dr 1021-06 Cash Bank $405.53Cr – 1120 Accounts Receivable $405.53

Wrap up

Control your earliest and latest dates. This will prevent backdating into a month you have already run SJI and make your life much easier.
Keep your SJI Creation Reports.
Run your balancing reports with all users out of the system.
Run your balancing reports after aging.
Run your balancing reports with General Ledger reconciliation.
Understand documents types and what accounts they are programmed to post to.
Do not rebuild your work file after each manual entry or the manual entry combines with the subsidiary entry to appeared double in the work file.

~Document by Alisa Mueller 10/2013