E business Key Account Reconcilations

Key Account Reconciliation is definitely a thankless job in most of the organization unless it comes on the radar of Auditors.
So as long as all your accounts are reconciled correctly there are less questions auditors will ask.

I am an Oracle ebusiness consultant in Oracle Financials for more than 10 years now. It took a while to decide on what will be my blog title and i ended up deciding with this topic.

KEY ACCOUNT RECONCILIATION IN ORACLE eBUSINESS

Obviously any reconciliation is across all applications in Finance domain and it will never be limited to ebusiness suite, but focus of this blog will be more dedicated to ebusiness.

To Start with, each organization or accountant must decide following 

• What are the Key Accounts that must be reconcile
• What is the cut off time for Key Account Reconciliation
  (Monthly , Quarterly and Yearly, Cash Account can be on weekly basis)
• What will be the treatment for unreonciled  balance in each account
  (adjustment Entry in GL which will be reversed in next month when correction flows to GL from Subledger)

The answers to above are common in most organizations

Account Name	Reports used to Reconcile
AP Accrual Account	PO Accrual Report
Trade Creditors or AP Liability	Accounts Payable Trial Balance Report
Inventory Clearing Account	Material Distribution and Material Transaction Report
Stock / Inventory Account	On hand Quantity Report , Material Distribution Report
Receivables Account	Receivables Aging Report

Credit Card Clearing Account                   Credit Card outstanding transactions listing


From an ERP or application perspective each consultant or business account must understand the accounting engine behind account event. 

• In short -
  how the application is configured
  What accounting events will occur in each transaction
  what will be account entry for each accounting event
  when these accounting entry will reflect in balance sheet or trial balance or in general ledger

Although many of us will already be knowing how to reconcile but most of us may not know how to identify unreconciled transaction and blog is intended to detailed the step by step reconciliation process for each Key Account.

AP/PO Accrual Account

The outstanding balance in AP/PO Accrual account will show uninvoiced or over invoiced receipts.
Balance in this account after Purchasing , Inventory and AP closure must reconcile with Accrual Reconciliation Report

Simple formula to derive the balance against each Receipt is

Receipt Amount - (Invoice Amount + Write Off Amount) = Accrual Amount

Event	Debit	Credit
-------------------------	-------------------------------------------	-----------------------------------------------
Receiving	Inventory Clearing Account
Receiving		AP Accrual Account
-------------------------	-------------------------------------------	-----------------------------------------------
Delivery	Inventory / Stock /Expense Account
Delivery		Inventory Clearing Account
-------------------------	-------------------------------------------	-----------------------------------------------
Invoice Booking	AP Accrual Account
Invoice Booking		AP Liability / Trade Creditors Account
-------------------------	-------------------------------------------	-----------------------------------------------
Write Off	AP Accrual Account
Write Off		Inventory / Stock /Expense Account

------------------------------------------------------------------------------

Common Errors leading to unreconcile balance

1) Manual Journal Entry in General Ledger or unposted entries in GL

2) Journal Entry received from other sub ledger except Purchasing / Cost Management and Payables
Like Misc Receipt being booked in AR.

3) Incorrect Accrue at receipt Flag on purchase order
    At Po Distribution  Accrue at receipt flag is set which plays the key role
    If PO is set to 3 way then Accrue at Receipt must be set to Yes
    If PO is set to 2 way then Accrue at Receipt must be set to No
In most reconciliation cases it is incorrect Accrue at Receipt flag leads to unreconcile balance in accrual Account between GL and Accrual Report
4) Check the Suspense Account Entries for the month. This depends on EBS configuration for posting i.e. if posting program is enabled for suspense account then check what has gone to posting instead of Accrual account.


Identifying unreconcile Receipt / Invoice or Purchase order depends on availability of the Report.

• Accrual Reconciliation Report is available at subledger level (Inventory, Purchasing and Payable)
• In General Ledger, Account Analysis Report is available or many organisations use the custom GL Transaction listing report which will give Purchase Order and Receipt reference in the Report output.
• Simple V-lookup can help to identify the purchase order that needs to be investigated (compare outstanding Accrual Amount 
• Alternative to identify affected orders will be below SQL
  (more to come.........................)