Welcome

Welcome to HandsonERP.com

This site provides video based training of Oracle Financials.
Learn concepts and functionality in a step by step manner.

Login

HandsonERP - Oracle E-Business Suite Training
Welcome, Guest
Please Login or Register.    Lost Password?
Re:Bank Reconciliation Reports and Best Practices (1 viewing) (1) Guest
Please post inquiries related to lessons content here. All questions and inquiries related to the website should be submitted directly using Contact form in the top menu.
Go to bottom Post Reply Favoured: 0
TOPIC: Re:Bank Reconciliation Reports and Best Practices
#2289
gavrobere (User)
Gold Boarder
Posts: 316
graphgraph
User Offline Click here to see the profile of this user
Bank Reconciliation Reports and Best Practices 12 Years, 5 Months ago  
Hi Hasan,

Please, could you help me with the Cash Management Reconciliation variance issues. There are some unexplained variances when reconciling cash Management with general Ledger relevant accounts.
The client main worries is to be unable to explain the variances appearing in the bank reconciliation, no matter what is happening is causing it, then to review, evaluate and the system setup and the business processes.

1)The client has not implemented neither Cash Clearing account nor Bank Charges account (meaning that when payment is issued or receipts are applied, the GL Cash account is instantly hit). Could this be the problem liked to the unexplained variances?
2) The client runs automatic bank reconciliation and occasionally or rarely does manual reconciliation.
3) They have never run Bank Reconciliation Cashflow program and they do not even know what it means. What problem this can cause?
4) When running General Ledger Bank Reconciliation report, they provide the value zero as athe opening bank statement parameter, whereas the value is actually not zero. Would this distort the report output?
5) Would the timing differences (say, a day or hours differences) of the various bank reconciliation reports production also be a possible source of the variances?
6) The client goes back more than 3 or 6 months as their report date parameter. Could this also explain the unexplain variances.

Please, could you provide the best, clear and simple approach to doing bank reconciliation at the end of each day, or at the end of the month, depending on the organization need whilst addressing the following points:

a) The bank reconciliation reports needed to run the item of figures to appear in the reconciliation process.
Should the bank statement opening balance figure be assumed to be zero even when it is something like $3,000, when providing parameters for “General Ledger Reconciliation Detail report”?
Is it a problem to be going back to a remote period, or future period when providing the report date parameter?

I have attached a Workbook comprising of two worksheets, one on how the client has been doing bank reconciliation process, and the other worksheet, on various bank statement reports figures which you may want to explain in relation to bank reconciliation compilation.

Thank you very much.
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#2290
gavrobere (User)
Gold Boarder
Posts: 316
graphgraph
User Offline Click here to see the profile of this user
Re:Bank Reconciliation Reports and Best Practices 12 Years, 5 Months ago  
Reconciliation for the period: Oct-11 as at 07/10/2011
Bank Statement no. - Date: XXXXX - 04/10/11

Bank statement
Opening Bal 30.00
Closing Bal 51,135,132.49
Net Movement 51,135,102.49

LESS
GL Cash Account Balance 50,278,925.05

Variance between Bank and GL 856,177.44

Representing:

Items in Bank not yet in GL
Unreconciled Satement Lines 2,044,399.12
(Transactions available)
Fees 0.00
Part Reconciled Lines 0.00
Timing issues between the Bank and GL 0.00
Sub Total Item in Bank not in GL 2,044,399.12

LESS:

Items in GL not on Bank Statement
Unreconciled Receipts 300.00
Unreconciled Payment 0.00
Unreconciled Journal Lines 1,184,493.40
Incomplete Reversed Receipts 0.00
Reversal Receipts Reconciled against a Bank Statement -1,000.00

Net Explained Variance between Bank and GL 1,183,793.40

LESS
Unexplained Account Variance 327,615.96

Total Variance between Bank and GL 856,177.44
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#2291
gavrobere (User)
Gold Boarder
Posts: 316
graphgraph
User Offline Click here to see the profile of this user
Re:Bank Reconciliation Reports and Best Practices 12 Years, 5 Months ago  
Sorry, I couldn't attached the two files.

1/ GENERAL LEDGER RECONCILIATION DETAIL REPORT As of 30-OCT-11

Report Date: 12-OCT-11 09:29
Bank Account Number: XXXXX

General Ledger Position:

General Ledger Cash Account: $49,258,287.07

Bank Position:

Bank Statement Closing Balance $0.00
Total Unreconciled Receipts: $300833.09
Total Unreconciled Payments: $0.00
Total Unreconciled Cashflow $0.00
Total Unreconciled Journal Entries $1,246,947.28
Total Lines marked as Errors $0.00
Adjustment Bank Statement Balance: $1,547,780.37
DIFFERENCE (GL Cash Account Balance - Adjusted Bank Statement) $47,710,506.70


2/ BANK STATEMENT SUMMARY REPORT

Statement Date: 01-Apr-11 to 31-Mar-12
Bank Account Number: XXXXX

Opening Balance (03-OCT-11): $56,234,131.10
Closing Balance (03-OCT-11): $49586817.66
Net Movement (03-OCT-11): $-6647314.44
Number of Lines: 379

Reversals 201
Opening Balance (03-OCT-11): $500000.00
Closing Balance (03-OCT-11): $900000.00
Net Movement (03-OCT-11): $400000.00
Number of Lines: 627

Zero Transact
Opening Balance (03-OCT-11): $0.00
Closing Balance (03-OCT-11): $0.00
Net Movement (03-OCT-11): $0.00
Number of Lines: 282


3/ TRANSACTIONS AVAILABLE FOR RECONCILIATION REPORT

Report Date: 12-OCT-11 09:36

Total Available Receipts: $764832.92
Total Reversed Receipts: $1,150.00
Net Journal Entry Lines: $1246947.28
Total Unreconcilied Statement Lines: $1,876,376.96
Net Available $3,887,007.16
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#2295
handsonerp (Admin)
Admin
Posts: 1989
graph
User Offline Click here to see the profile of this user
Re:Bank Reconciliation Reports and Best Practices 12 Years, 5 Months ago  
Question for you.

You said:

"There are some unexplained variances when reconciling cash Management with general Ledger relevant accounts. "

I know you have posted lots of details. I may have missed something but to me it is not clear which two numbers are not agreeing?

The balance in GL Cash (Bank) account and the ending balance on your bank statement?

If the above is true then I would do the following:
1) Go back to the month when the two were agreeing.
2) From that point onwards find out if there is any journal in GL that has directly touched GL Cash account and is not coming from AP or AR.

When cash account is touched directly by other modules than Cash Management then it becomes a nightmare to do the reconciliation. That is the whole purpose of Cash Clearing account, so that Cash is touched only by Cash Management.

Reconciliation has to be done every period (be it daily or month). One cannot do reconiliation once in a while. Otherwise you have to trace back and see when the problem started.
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#2304
gavrobere (User)
Gold Boarder
Posts: 316
graphgraph
User Offline Click here to see the profile of this user
Re:Bank Reconciliation Reports and Best Practices 12 Years, 5 Months ago  
Thank you, Hasan, for your intervention. In a nutshell, the following is what I need:
--

I need to know the various relevant reports to produce for bank reconciliation processes, the figures on them to use in the process, how to explain the source of possible discrepancy between GL Cash account balance and the Bank Statement closing balance, taken into account things like "Available Transactions", "Unreconciled Bank Statement Lines", "Bank Error Lines", etc.

In other words, a general template with various items on it for bank reconciliation process for businesses using Oracle Payables, Receivables and/or Payroll and cash Management modules.

Any useful document on this would be very well appreciated!

Thank you in advance for your inputs.

Regards,

Gavro
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#2307
handsonerp (Admin)
Admin
Posts: 1989
graph
User Offline Click here to see the profile of this user
Re:Bank Reconciliation Reports and Best Practices 12 Years, 5 Months ago  
The idea is to make sure the ending balance in GL Cash account is the same as ending balance in the bank statement. This activity should not be made too complicated. I know users in companies keep their own Excel version to help them in reconciliation tasks. However, the number of these worksheets should be kept to minimal and more focus should be give to reconciliation report.

The GL reocnciliation report in Cash Management is sufficient enough. If there are complex requirement then perhaps a custom report could easily be produced.

The seeded report lists a balance and an adjusted balance for the bank statement. It also lists a separate adjustment amount for un-reconciled receipts, payments, and journal entries, as well as bank errors. Before running the report Create Accounting, Transfer to GL, Import and Post must be run in GL. Also run account analysis to make sure there direct manual entries through GL enter journal screen or Spreadsheet wizard are not touching the cash account.
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
Go to top Post Reply
Powered by FireBoardget the latest posts directly to your desktop