Transcript It's month end, now comes the reconciliation crunch

It’s month end, now comes the
reconciliation crunch!
Richard Byrom
Oracle Applications Consultant
http://www.richardbyrom.com
Agenda
•
•
•
•
Presentation assumptions and objectives
Why and how should we reconcile
Setup issues
Close procedures for each module,
reconciliation results & two way drill down
• Additional Tips
• Conclusion
• Questions and Answers
Objectives
• This presentation emphasizes the importance
of on a regular basis so as to ensure
improved control over financial information
• To discuss different setup issues and that
may impact your ability to reconcile.
• To outline the month end close process for
each module and give practical examples of
expected reconciliation results.
Objectives
Payroll &Human
Resources
Manufacturing
subledgers
Oracle
General Ledger
Financial
subledgers
Objectives
ACCOUNTS
RECEIVABLE
Transactions
- Invoices / Memos - Receipts Master Data
- Customers -
FIXED ASSETS
Transactions
- Additions / Disposals - Depreciation Master Data
- Asset Categories -
GENERAL LEDGER
Transactions
- Journals - Interfaces Master Data
- Chart of Accounts! - Sources & Categories - Document Sequencing-
ACCOUNTS PAYABLE
Transactions
- Invoices / Memos - Payments Master Data
- Suppliers -
CASH MANAGEMENT
Transactions
- Reconciled Payments - Reconciled Receipts - Miscellaneous Transactions Master Data
- Bank Accounts -
What should be reconciled?
• Control Accounts
• Inter Modular Movement
• Intercompany Accounts
What happens if we don’t reconcile properly?
• Potential data corruption not
detected
• Sub modules may not be posted
to the GL
• Multiple months may remain open
for a variety of modules
• Repetitive errors may not be
detected
• Posting of non standard journals
to control accounts
• Open item system carries forward
problems to next year.
Why the problem with reconciliations
• Inadequate training/inexperienced users
• Lack of understanding of the system
architecture
• Trying to use old procedures for a new
system
• Bad Management
How should we reconcile?
• Establish your out of system procedures
• Ensure that all the standard functionality provided
by the system is understood and used
• Make use of additional tools is necessary
Setup Issues – General Ledger
• Sequential numbering of documentation
should be taken into consideration.
• Chart of accounts should be
appropriately designed and have an
appropriate groupings and sub grouping
by using parent and child relationships.
• Document types and sub types need to
be looked at
Setup Issues – Sub Ledgers
• Master Data
– Should be separated according to the
different groupings of debtors available per
profile options
– Customers should be given appropriate
hierarchies
– Customer numbering should done in such
a way that facilitates understanding of the
various listings
Setup Issues – Sub Ledgers
• Transactional Data
– Ensure that appropriate transaction types
are created
– Control A/C’s should appropriate filter the
information through to General Ledger
– Autoaccounting
– Need to look at document numbering
Month end close steps
1.
2.
3.
4.
5.
6.
7.
Pre-posting activities
Posting to supporting systems
Run verification Reports
Reconcile against interfacing systems
Close periods
Problem Solution
Two way drill
Receivables Steps – Pre-posting
•
Pre-posting activities
–
–
–
–
–
–
–
Enter Receipts
Enter Invoices
Record NSF checks
Run Autoinvoice Interface
Print New Invoices
Run Incomplete Invoice Reports
Adjustment Approval Report
Receivables Steps – GL Posting
•
Posting to GL
(AR) Interfaces > General Ledger
Receivables Steps – Reports & Period Close
•
Verification Reports
–
–
–
–
–
–
–
–
–
–
•
Sales Journal by GL Account
Receipt Journal Report
Journal Entries Report
Transaction Register
Adjustment Register
Receipt Register Report
Aged Trial Balance
Invoice Exceptions Report
Applied Receipts Register
Unapplied Receipts Register
Close Periods
Receivables Steps – Reconciliation
Results
Beginning AR Trial Balance
+ Transaction Register
- Receipt Register
+/- Adjustment Reqister
- Prepayments Remittance Report*
Ending AR Balance
Jul-02
94,890,824.48
26,197,804.53
-42,318,784.05
0.00
0.00
78,769,844.96
Receivables Steps – Reconciliation
Results
Description
Amount
Current Period End AR Aging Total
39,330,142.00
Prior Period End AR Aging Total
55,454,778.25
Change in AR Balance
-16,124,636.25
Comments
Extracted from AR Aging Report for Jul-02
Extracted from AR Aging Report for Jun-02
x
+ Invoices/Debit Memos
- Credit Memos
- Cash Receipts
- Write Offs
Change in AR Balance
26,409,424.57
-211,620.04
-42,318,784.05
0.00
-16,120,979.52
Extracted from AR Sales Journal Reports
Extracted from AR Sales Journal Reports
Extracted from AR Receipt Journal Reports
Debits Posted
Credits Posted
Change in AR Balance
26,763,035.64 Extracted from Trial Balance
-42,887,671.89 Extracted from Trial Balance
-16,124,636.25 x
x=y=z
-3,656.73
y
Reconciliation Problems
• If AR does not reconcile to the general ledger
– Check that the reports have been run for the
correct date/period
– Run GL Interface
– Make sure all Journals are posted in the General
Ledger
– Re-Run both Trial Balance Reports
– Check for journal entries in the AR Control
accounts in GL, not generated by AR.
– Check for Invoices raised against the AR Control
account
Receivables two way drill - Invoices
Receivables two way drill – Invoice Status
Receivables two way drill – Invoice Accounting
Receivables two way drill – Cash Receipts
Receivables two way drill
Receivables two way drill – Receipt History
Receivables two way drill-Miscellaneous Receipts
Receivables two way drill-Miscellaneous Receipts
Receivables two way drill- GL to Invoice
Receivables two way drill – Drilldown effect
Payables Steps
• Pre-posting activities
– Enter Employee Expense Reports
– Enter Invoices
– Run Invoices on Hold Report
– Run Payables Approval
– Check for Unapproved Invoices
Payables Steps – Transferring to GL
Payables Steps – Create mass additions
Payables Steps – Reports & Period Close
•
Verification Reports
–
–
–
–
–
–
–
•
Accounts Payable Trial Balance
Posted Invoice Register
Posted Payment Register
Expense Distribution Report
Invoice Aging Report
Posting Hold Report
Invoice Register
Close Periods
Payables Steps – Reconciliation Results
Description
(Previous Month) Accounts Payable Trial Balance
+ (Current Month) Posted Invoice Register
- (Current Month) Posted Payment Register
(Current Month) Accounts Payable Trial Balance
Amount
-27,428,898.13
7,778,812.99
-5,779,945.70
-25,430,030.84
• Reconciliation can also be achieved using Tutor
Business Form FOR0672
Reconciliation Problems
• If AP does not reconcile to GL
– Check that the reports have been run for the
correct date/period.
– Run GL Post in Accounts Payable.
– Post all payables generated batches in the
General Ledger
– Re-Run both Trial Balance reports
– Check for Journal Entries in the AP control
account, not generated by Accounts Payable.
– Check for Payments/Invoice Distributions made
against the AP control account.
Payables two way drill – query an invoice
Payables two way drill – view accounting
Fixed Assets Steps
• Pre-posting activities
–
–
–
–
–
–
–
–
Prepare Mass Additions
Post Mass Additions
Delete Mass Additions
Prepare Manual Additions
Retire Assets
Adjust Assets
Capitalize CIP Assets
Run Depreciation
Fixed Assets Steps – GL Posting
• Run Journal Entries
Fixed Assets Steps – Reports & Period Close
• Verification Reports
–
–
–
–
–
–
–
–
–
–
•
Cost Detail Report
CIP Detail Report
Asset Addition Report
Cost Adjustment Report
Asset Retirements Report
Asset Reclassification Reconciliation Report
Asset Transfer Reconciliation Report
CIP Capitalization Report
CIP Assets Report
Unposted Mass Additions Report
Close Periods
Fixed Assets Results
Description
Previous Month’s Ending Balance
+ Asset Additions
+ (-) Adjustments
- Depreciation Charge
- Retirements
+ Capitalizations
+ (-) Reclassifications
+ (-) Transfers
Current Month’s Ending Balance
Cost
120,951,159.11
658,900.00
0.00
NBV
96,421,655.44
658,900.00
-450,506.21
0.00
0.00
0.00
0.00
121,610,059.11
96,630,049.23
• Alternative reconciliation using FOR0674
Tutor Business Form
• Printing of Reconciliation Report
Cash Management Steps
• Pre-posting activities
– Capture/Interface Bank Statements
– Automatically reconcile statements
– Manually reconcile amounts not cleared
– Check all statements are fully reconciled
• Bank Statement Detail Report
• Online checks
Cash Management Steps
• Post to GL
– Ensure payables and receivables posting
has taken place
• Verification Reports
– Cash In Transit Report
– Cleared Transactions Report
– General Ledger Reconciliation
– Transactions available for Reconciliation
Cash Management Results
• Check Receipts against Receivables
Reports, Cash Account per GL and
Cash Management Reports
• Check Payments against Payables
Reports, Cash Account in GL and Cash
Management Reports
Cash Management two way drill – Account Inquiry
Cash Management two way drill – Drilldown effect
General Ledger Steps
• Pre-posting activities
– Reconcile interfaces from feeder systems
– Enter Manual Journal Entries
– Reverse Entries
– Run Journal Import
– Generate Recurring Journal Entries
– Generate Mass allocations
• Post Journal Entries
General Ledger Results
• Verification Reports
–
–
–
–
–
Journal Entry Report
Account analysis Report
Account Analysis – Sub Ledger Detail Report
Trial Balance – Detail Report
Run Financial Statement Generator – FSG’s
• Close Periods
Additional Tips – File Export
• To export the results of an online inquiry or
onscreen view choose File > Export from the
menu
• Save the file generated to your desired
location
• Open the file from Microsoft Excel. Ensure
that from the open dialogue box in Excel you
specify files of type all *.*
• When the text import utility appears choose
finish, you can now sort the file according to
your personal requirements
Additional tips Cont.d – Tools>Copy File
• Use Tools > Copy file from the menu when
you are in a report
• Save the file in web or text format to your
desired location – preferably use text
• Import the file into Excel using the text import
wizard as displayed – use the report column
headers as your fixed width delimiters.
Additional tips cont.d - Tutor
• Use Oracle Tutor Business forms to
give you reconciliation templates.
Conclusion
• Reconciliations are important in
order to identify problem areas
and rectify them on a timely
basis.
• Procedures should be put in
place to facilitate reconciliation
as well as ensure that the
organisation is making use of
all possible tools.
• Setups have a significant
impact on the ease of
reconciliation.
Speaker Information
Name :
e-mail :
Richard Byrom
[email protected]
[email protected]
Company : RPC Data Ltd
Web Site : http://www.rpcdata.com
http://www.richardbyrom.com