Detail Balancing of Bank to Ledger Trial Balance
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-ACP-BNK-GRN-PAY packages.Bank is unique in that the Bank Ledger Accounts will be updated by 5-different packages.
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, and then source code.
!worddavbf37602f48876f8dcb0d65f12a11389c.png|height=247,width=267!Each package pulls it's transactions into ledger and posts to the Bank ledger accounts. Each package interfaces to the BANK code assignment in Bank by keying in an account code of BANK.
Run the Create SJI process to create the SJI entries.SJI > Create System Entries > Select Payroll Sent reports to the spool.SJI > Create System Entries > Select BankSend reports to the spool.SJI > Create System Entries > Select Accounts PayableSend reports to the spool.SJI > Create System Entries > Select Accounts ReceivableSend reports to the spool.SJI > Create System Entries > Select Commodity AccountingSend 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.LDG > Journal Entries > System Journal EntriesAnswer YES to All Packages. (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 Bank Accounts.
Run the Bank Register with Running Balance for SJI Period Ending DateEveryone MUST be out of the BNK package when this report is run.Run after a "verify account balance" function has been run.BNK > Bank Account > Bank Maintenance > Select Bank > 6) Bank Balance Inquiry > Answer YES to "Re-Calculate Bank Balance"then run report BNK > Reports Manager > Bank Account Reports > 1) Print Standard Reports > Bank Register Trx Date Beginning and ending should be last day of the SJI period > Print
This report takes the Purge Balance and adds up all the transactions up to the first transaction you are printing for the filters. Then adds the rest of the transaction up to the end of the filter range.
The ending balance of the Bank Register should match the Trial Balance Report.
What is happening during this SJI Creation Process?
The SJI process gathers all the deposits and withdrawals that were keyed into package. 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. All of the following packages affect the bank ledger.
PAY Paycheck: Debit Payroll Expense (RP-Regular Pay or OP-Overtime Pay or OI-Other Income transaction code)Credit Bank Account (NT – Check Amount/Bank Code)
BNK Manual Withdrawal: Debit General Ledger Code keyed at time of entryCredit Bank Code Account (##-Bank Code)
BNK Manual Deposit: Debit Bank Code Account (##-Bank Code)Credit General Ledger Code keyed at time of entry
BNK Transfers: Debit Transfer Clearing Account (BT-Bank Transfer Code)Credit Bank Code Account (##-Bank Code)Debit Bank Code Account (##-Bank Code)Credit Transfer Clearing Account (BT-Bank Transfer Code)
ACP Disbursements: Debit Accounts Payable (AP-Transaction code)Credit Bank Code Account (PM-transaction code/bank code)
ACP Prepayments: Debit Prepayment contra-liability account or asset account (PP-Transaction code/bank code)Credit Bank Code Account (Bank Code)
ACR Payment (cash or ROA) Debit Bank Code Account (PM-transaction code/bank code)Credit Accounts Receivable (AR-Transaction Code)
ACR Prepayments: Debit Bank Code Account (PP-Transaction code/bank code)Credit Prepayments contra-asset account (PP-Transaction Code)
GRN Purchase Settlement: Debit Commodity Code Cost of Sales (GR-Grain Purchase/Sale Code)Credit Bank Code Account (GC-Grain Check/Bank Code)
GRN Sales Settlement: Debit Bank code Account (CR-Cash Receipt/Bank Code)Credit Commodity Code Sales (GR-Grain Purchase/Sale Code)
Bank Package is a Subsidiary of all other packages.
Each document type is creating an entry that will post to the SJI and Ledger
Each document is also creating a withdrawal or deposit in the bank register.
The bank register must balance to the ledger
When things go wrong
Begin to compare the Bank Register 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 Bank Account Names. LDG > Inquiry options > Account Inquiry > view the detail of the account.
Verify that your subsidiary reports were run after verifying balances and with everyone out.
Since it is Bank, verify that all packages that affect bank have the SJI's run.SJI > Create SJI Entries > Are any packages still available for selection.
Focus on what we have learned from the document types.
Focus on what we have learned about how the bank register is updated from other packages.
Document Type Focus
When the subsidiary does not balance to ledger, it has to be a document that is affecting it.
A/P Payment on Account was posted after ACP SJI was run (example, keying a prepayment into the prior month.)
A/P Disbursements was posted after ACP SJI was run (like issuing a check to pay this month's credit card bill)
Entering a bank transfers after BNK SJI was run (like moving the daily cash receipts to the live bank for the deposit)
Voiding a grain purchase settlement after GRN SJI was run (attempting to correct a lost grain check)
Balancing Bank 1021-06 ledger to Bank Register Difference $285.00
Step One:
Think about how you are balancing a transaction code on a document to the ledger. Example: balancing Bank 06 subsidiary, only certain document types should be hitting this account
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 1020-06 Ledger Account.Compare the ending Transaction Amount 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 are $285.00 different. This is stating that if we re-ran the SJI's again for this time period, it would post a larger debit to the ledger. Something as debited the bank register yet was not there when the SJI was originally created. There was a A/R Receipt or voided AP Disbursement or manual bank entry or Grain Settlement or voided Payroll check 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 Bank is out of balance then OTHER accounts are out of balance as well. Because bank is a package that is affected by all other packages, it is usually in your favor to balance the other subsidiaries first. We can use the Mimic System Journal Reports to find the differences.But in this case, a quick scan of the data in the LEDGER ACCOUNT DETAIL report shows the $285.00.
The Bank Document 999-Z00015 stands out for $285.00 on the 8/31 (last day).
So think about the documents that hit the bank. This would be a bank transfer document. It would debit one bank and Credit another bank account. So if the bank should be higher, then a transfer was issued AFTER the SJI's were run.
Step Five:
To verify the backdating, look to the SJI Reports that were saved to the spool during the SJI creation process.Search for document Z000015. It is not found. So this document hit the bank but never the ledger. But we need to know the offset for this.Use the SJI to print a Transaction Analysis of this document.
Step Five:
Make a manual ledger entry to post the transfer to the ledger.Debit 1021-06 for $285.00Credit 1050-FP for $285.00 This entry also corrects the $285.00 difference in 1050-FP.
Balancing Bank Account 1050-01 to the Bank RegisterDifference of $9,980.00
Step One:
Think about how you are balancing a transaction code on a document to the ledger. The Bank Register is $9,980.00 Higher than the ledger. So what makes a bank have more money? A deposit or a voided withdrawal. If the Bank should have $9,980.00 more, then AR o AP or Grain purchase/sales or payroll should also be off. This bank deals primarily with ACR, so we know to focus on AR.
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 1050-01 Bank asset account.
Compare the ending Transaction Total of the ledger account detail 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:
Again, it might be more helpful to focus on balancing the AR subsidiary because if bank is off, so is another account.Or in case, we will do a search for the $10,000 amount that is different from the transaction change amount and the amount of change in the ledger.
Step Five:
In this case, we can tell just from the LEDGER ACCOUNT DETAIL REPORT of Step Three that the issue is a prepayment entry for $10,000. The TRIAL BALANCE REPORT shows no change amount.But the SJI Work File LEDGER ACCOUNT DETAIL report shows that document was entered.We can verify if this receipt 999-RA0113 was included in the SJI Created reports saved to the spool.
Step Six: Now we post the entry to the ledger.
Debit 1050-01 for $10,000Credit Prepayment 2501 for $10,000
Snowball Effect of Balancing Each Account
Step One:
As you make the entries in your research we see that the difference between the ledger and the 1050-01 account has now moved to a $20 shortage now. This is where we make one correction and the amount we are searching for begins to change. So now we are looking for $20 withdrawal that affected the register but not the ledger.
Step Two:
At this point we have solved all the back dating issues. We know this because the transaction change amounts from the SJI LEDGER ACCOUNT DETAIL now match the AMOUNT CHANGE of the Trial Balance.
So now we have to start looking at the fact that the bank register is a subsidiary of other packages.
Step Three:
Each transaction in the bank register has source code of the back that it came from.We must now begin running each packages payment reports to compare back to the bank account limited to that package to see if they compare.
What we are looking for is a transaction that is in a package (and therefore would have gone to the ledger) that did not hit the bank register. Or vice versa, one that is in the bank register that is not showing in the package.
Knowing that we are focusing on bank C1 that is our daily cash receipts bank and that it therefore usually only works with the ACR package, it makes sense to start where the majority of transactions exist.
Run an AR Payment Report for bank code C1 for the entry date of the month of August.
Run the same AR Payment report for bank code C1 but for the reverse date of the month of August.
Add these two reports together to find out the dollar amount that should have posted to the bank register.
Now run a bank register for the C1 bank, for the month of August, with a source code of R-Receivables.
Compare this amount (withdrawals and deposits) to the total you calculated from the AR Payment reports.
If they do not match, then something is not in synch between ACR and BANK.Either something posted to ACR but never hit the bank thus driving to the ledger but not updating the running balance in the bank.ORSomething was keyed into the bank saying it was from R-Receivable and therefore never went to the ledger.
So we can see that there is a difference of $20 between what the AR Payment Reports print and what the Bank Register for type R transactions prints. Which is correct? The AR Payment Reports as they are reporting the detail that flowed to the ledger.
Step Four:
Looking at the detail in the bank register, we find document 999-Z00014 for a withdrawal of $20 that was keyed as a type R source code.
This means that it lowered the bank register by $20 but because it is marked as R-Receivable, AGRIS would only drive this $20 to the ledger if it actually existed in the AR accounts. This document does not exist in the AR accounts.
So now you begin to track down why this made. In this case, we can see by the description that it was for cash short. After speaking to our clerks, we realize that we were $20 short on this day and they keyed it to R-Receivables because it was "caused by Receivable's" (this would be human logic)
But in the AGRIS database rule structure, once a document is keyed into the bank with a source code other than Manual or Transfer, it will ONLY affect the bank register. There is no structure in the program to post this to the ledger.
Step Five:
To correct this we need to make a manual entry crediting the bank 1050-C1 and debiting Cash Short expense.
Now we are in balance.
Bank Register is updated by Other Packages – Review
Each document post to the ledger AND to the Bank Register.
You can create documents that due to an error, may not post to the bank register.These documents will still update the ledger bank.
You can create transactions in the bank only that will never hit the ledger if you use a source code other than Manual. These documents will change the running balance only
Because of this feature, if you have exhausted your other options for balancing, it is time to balance each package to the Bank.
ACP Disbursements – Bank Source Code A:
Run an AP Disbursement report for each bank code, for the entry date of the month.
Run an AP Disbursement report for each bank code, for the reversal date of the month.
Add the amounts to find the net that should have posted to the bank.
Run a Bank Register for each bank code, for the ledger date of the month, for the source code of A-Payable.
This amount should be equal
GRN Settlements – Bank Source Code G:
Run a GRN Settlement Report each bank code, for the entry date of the month.
Run a GRN Settlement Report for each bank code, for the reversal date of the month.
Add the amounts to find the net that should have posted to the bank.
Run a Bank Register for each bank code, for the ledger date of the month, for the source code of G = Grain.
This amount should be equal
PAY Paychecks – Bank Source Code P:
Run a Payroll Check Report for each bank code, for the entry date of the month.
There is no need to run a void report in payroll because payroll package does not store voids. Anything reversed is deleted as if it never existed.
Run a Bank Register for each bank code, for the ledger date of the month, for the source code of P = Payroll.
This amount should be equal
ACR Payments – Bank Source Code R:
Run an AR Payment report for each bank code, for the entry date of the month.
Run an AR Payment report for each bank code, for the reversal date of the month.
Add the amounts to find the net that should have posted to the bank.
Run a Bank Register for each bank code, for the ledger date of the month, for the source code of R-Receivable.
This amount should be equal
GRN Settlements – Bank Source Code G Example:
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 verifying balances.
It is easier to balance grain if the other subsidiaries, like ACR or ACP are in balance.
Be aware that there are documents hit the ledger AND the bank register.
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 02/2014
