Detail Balancing of AP to Ledger Trial Balance

Goal:

Provide an overview of month end processing for ACP
Review of balancing subsidiary to ledger accounts
Detailed Study of how vouchers and disbursements post to the ledger.
How to Find Documents that are causing balancing issues between the ACP 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 ACP 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 Payable Sent reports to the spool. DO NOT DELETE until Fiscal Year is closed.
Select the Ledger Period in LDG > Select Ledger Period > Select Default Period.
Post the System Journal entries. LDG > Journal Entries > System Journal Entries
1. Answer YES to Accounts Payable (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. LDG > Reports Manager > Journal Entry Reports > Standard reports > Trial Balance Report. This is the report that you use to balance to the subsidiary A/P and Prepayment Accounts.
Run the AP Balance Report with GL Reconciliation for SJI Period Ending Date
1. Everyone MUST be out of the ACP package when this report is run. Run after an aging function has been run.
2. ACP > Reports Manager > AP Balance Reports > Standard > AP Balance Reports > AP 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. This report takes the current balances and backs out the vouchers/disbursements to get back to the balance on the date requested.
The ending balance of the Ledger AP and Prepay accounts should match the AP Balance Report.

What is happening during this process?

The SJI process gathers all the vouchers (item and manual) and disbursements (payments and prepayments) that were keyed into the ACP 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 Vouchers: Debit Inventory Asset for the amount of the Line Items (IV-transaction code)Debit Inventory Asset for the amount of the service items allocated to them (IV-transaction code)Credit Accounts Payable for the amount of the voucher (AP-transaction code)
Manual Invoices: Debit GL Account for the amount of the GL Line Distribution (manual distribution) Credit Accounts Payable for the amount of the voucher (AP-transaction code)
Disbursement Debit Accounts Payable (AP-Transaction code)Credit Bank Code Account (PM-transaction code which is normally replaced by bank code) Prepayments: Debit Prepayment contra-liability account or asset account (PP-Transaction code)Credit Bank Code Account (Bank Code)
Application of Prepayment to voucher: Debit Accounts Payable Voucher Number (AP-transaction code)Credit Prepayments contra-liability or asset account (PP-transaction code)

When things go wrong

Begin to compare the AP 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 PAYABLE.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.

Voucher was posted after SJI was run (example, posting a stock addition in the month AFTER SJI's to get the physical count correct)
Voucher was voided after SJI was run (like reversing a manual voucher to get expense in right month)
Disbursement was reversed after SJI was run (like reversing a disbursement from a stock addition because you need to edit the stock addition)
Disbursement was keyed after SJI was run (trying to get the prepays into the accounting month the manager wants)

Balancing Accounts Payable Ledger Payable Ledger Account – 2020Difference of $24,543

Step One:
Think about how you are balancing a transaction code on a document to the ledger. Example: balancing Accounts Payable subsidiary, only the AP-Transaction code should be hitting the 2020 ledger account. And if only AP transaction codes should be hitting the AP ledger account, then only vouchers (item or manual) and disbursements (regular or prepay) should be hitting that ledger account. Because that is the only time the AP-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 AP 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 AP transaction codes in the month that you have already gathered the data for the ledger.

Here, the amounts $24,383.00 different. This is stating that if we re-ran the SJI's again for this time period, it would post a smaller credit to the ledger. There was a voucher or disbursement that was entered/edited/reversed using a back date, or date into a month that the SJI had already been run. What were these documents?
Step Four:
If AP 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 voucher or disbursement 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 AP to AP 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 AP Payables that is out of balance, but also other accounts. Just looking at the AP comparison, we are off by $24,383.00 and if you look down the list, The amounts for the Bank-1021-06, the Inventory-chem-1340-4k, and the Electric Expense-6305 is also off in amounts that total the $24,383.00. Starting with the Bank, account 1021-06, it is off by $23,580. If we were to rerun the SJI's today it would post $23,580 more than it did the first time the SJI's were run. So think about the documents that hit the bank. That would be a disbursement document. It would debit AP and Credit the bank account. So if the bank should be higher, then a check was issued AFTER the SJI's were run.

Run a listing of disbursements to look for that amount.

This finds the difference for the Bank Account. Check #99-000188 was keyed after the SJI was created.
Step Five:
Make a manual ledger entry to post the disbursement into the ledger. Debit Accounts Payable liability for $23,580.00Credit Bank Account Asset for $23,580.00

Balancing Accounts Payable Ledger Payable Ledger Account – 2020 Now Difference of $963.00

Step One:
Think about how you are balancing a transaction code on a document to the ledger. Chemical Inventory (account 1340-4k) is out of balance by $150. The Inventory Asset is affected when a stock addition is keyed or edited. If the Inventory Asset should have $150 more, then a stock addition had to be keyed or edited.
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 Inventory asset 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 that affect inventory asset in the month that you have already gathered the data for the ledger.
Step Four:
Run the MIMIC reports for the Prepay ledger account using the steps from balancing AP.
Step Five:
In this case, we can tell just from the LEDGER ACCOUNT DETAIL REPORT of Step Three that the issue is a corrected/edited stock addition. The TRIAL BALANCE REPORT only knows about the 1000.00 debit, which knowing how documents post would be a stock addition voucher. But the SJI Work File LEDGER ACCOUNT DETAIL report shows that document was corrected. So we need to post this edit to the inventory asset in the ledger.
Debit Inventory Chemical for $150.00Credit Accounts Payable for $150.00

Snowball Effect of Balancing Each Account

Step One:
As you make the entries in your research we will see that the difference in the Accounts Payable is changing with each entry. Now we are only off by $953.00. We are using the other accounts affected by the Accounts Payable SJI to find the overall issue with Accounts Payable.

Step Two:
Look at the MIMIC reports that we have been comparing.

Going back to how each document has a set of transaction codes that handle the debits and credits of that document.
The only other amount that we are off is the $953 for Electric Expense that was in the original SJI's but is not in the Execution work file.
Since there is not Transaction Code (Tr Co) for this line, that is telling us it is a manual voucher distribution. It is the GL Coding to offset the credit of a voucher. So this would be a manual invoice that has been deleted AFTER the SJI creation was run but backdated.
Step Three:
This is manual voucher. To find out what the voucher was, we have to go back to the SJI Creation reports and search for the $953 amount.
When you ran the SJI creation, there were report created for all the vouchers and disbursements that were summarized. You should have saved them to the spool for safe keeping and ease of research. Print out the A/P TRANSACTION REPORT BY VOUCHER to the screen and search for that dollar amount.

There is only one document that makes up that amount and affects that expense account.
Step Four:
Create a manual ledger entry to post this reversal to the ledger.
Dr 2020 Accounts Payable for $953.00 Cr 6305 Electric Expense for $953.00
Step Five: Run your trial balance one more time to compare to the aging.

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.
Note that once we got a balancing report, we assumed it was correct and worked to make the ledger balance to the AP Balance Report
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 Created by: Alisa Mueller 12/2013