Share

Struggling with the intercompany data even after implementing a financial consolidation application? Have you ever explored the true power of Oracle’s connected EPM? Want to get the maximum out of your Oracle EPM Enterprise Licenses?

This blog describes the solution to streamline the intercompany data and matching process using Oracle EPM’s ARCS application.

Business Needs

Even after implementing any sophisticated financial consolidation application, most of the organisations struggle with matching intercompany data every reporting cycle. While a financial consolidation application helps organisations to match the intercompany data at the balance level, the finance team generally struggles to identify a true mismatch and the root cause for the mismatch.

Most of the organisations don’t have enough tools to investigate intercompany mismatches. Hence, a lot of man-hours are spent on the mismatch investigation process. Incorrect intercompany balances also lead to a risk of understating or overstating financials.

Solution

The view below demonstrates the solution for streamlining the intercompany match process using Connected EPM:

Oracle EPM ARCS 1

The intercompany balance data and the intercompany transaction-level data needs to be loaded into the ARCS application. The profile segments in ARCS need to be created as a unique combination of entity, plug account and intercompany partner.

During the close process, balance level data needs to be loaded into the Reconciliation Compliance module within ARCS to find the balance level mismatch in the reporting currency. For further investigation of the mismatches, the unmatched transactions within the Transaction Matching module need to be analysed further.

Based on the analysis, the correction adjustments (pass back entries for the source systems) can be captured in ARCS. Similarly, any topside adjustment entries for the currency translation difference, etc. (entries to be posted in HFM or FCCS) can also be captured in ARCS. These journal entries can then be exported out of ARCS for further processing into the source systems or HFM/FCCS.

Technical Details

The section explains the technical solution in detail.

ARCS Profiles

ARCS profiles need to be created based on a unique combination of an entity code, plug account code and intercompany partner code. The profile segments can be created as under:

  1. Ledger Entity Code
  2. FCCS Plug Account Code
  3. Ledger Intercompany Entity Code

The asset and expense data needs to be loaded as the Source System data and liability and revenue data needs to be loaded as the Sub-System data.

Mapping Tables

The Reconciliation ID field needs to be mapped within Data Management as under:

  1. Ledger Entity Code: for asset and expense GL accounts, to the first profile segment and for revenue and liability GL accounts, to the third profile segment
  2. GL Account Code: to the second profile segment
  3. Ledger Intercompany Entity Code: for liability and revenue GL accounts, to the first profile segment and for asset and expense GL accounts, to the third profile segments

Additionally, asset and expense GL account codes need to be mapped to Source System and liability and revenue GL account codes need to be mapped to Sub System.

Example (Profile Mappings):

Source Codes

Source Description

Target Value

Account

Entity

15001

100

Intercompany Receivables

100-Plug_ICARAP-200

25001

200

Intercompany Payable

100-Plug_ICARAP-200

15001

200

Intercompany Receivables

200-Plug_ICARAP-300

25001

300

Intercompany Payable

200-Plug_ICARAP-300

Example (Source Mappings):

Account

Target Source

15001

Source System

25001

Sub-system

 

Demonstration

FCCS Plug

Plug report from FCCS:

270073662_475932563964329_6214842150262977673_n

The plug account shows a difference of USD 5075 for a particular combination of an entity and a partner. 

ARCS Plug

 Plug report from ARCS:

Oracle EPM ARCS 3

The plug account shows a difference of USD 5075 for a particular combination of an entity and a partner.

Reconciliation Analysis from ARCS:

Oracle EPM ARCS 4

Differences as per the Plug Account report and as per the Reconciliation Profile are matching (USD 5075). However, the total unexplained difference is zero and hence, by clicking the matching option from the Reconciliation, the root cause of the difference can further be investigated by analysing the unmatched transactions worth USD 6075 for the Source System and USD 1000 for the Sub-system. Transactions worth USD 9000 are matched within ARCS.

Transaction Matching Analysis from ARCS:

Oracle EPM ARCS 5

The total difference is zero however, some transactions are unmatched. The system is suggesting that some difference is due to the currency flux. Based on the volume of the transactions and needs, organisations can set up the confirmed and suggested match criteria in ARCS.

Matched Transactions in ARCS (for information only):

Oracle EPM ARCS 6

Oracle EPM ARCS 7

Oracle EPM ARCS 8

Suggested Transactions in ARCS:

Oracle EPM ARCS 9

Of the total difference of USD 5075, a difference worth USD 75 is due to currency translation difference (from a non-USD currency transaction currency into USD reporting currency). This transaction pair can be marked as a confirmed match by filling in the associated details.

Unmatched Transactions in ARCS:

Oracle EPM ARCS 10

There is an unmatched transaction, which is due to a coding error. A wrong intercompany partner code has been selected. Hence, this transaction can be matched by specifying the correcting entry. This explains the remaining difference of USD 5000. However, the invoice amount is GBP 3731.

Transaction Overview in ARCS (after passing necessary adjustment details):

Oracle EPM ARCS 11

A Currency Flux adjustment needs to be booked into FCCS and a Coding Error adjustment needs to be booked into GL. The trial balances should be imported back into FCCS for eliminating the plug differences in FCCS after the necessary passback journals are posted in the GL.

Currency Flux adjustment details in ARCS:

Oracle EPM ARCS 12

Journal details:

Oracle EPM ARCS 13

Coding Error adjustment details in ARCS:

Oracle EPM ARCS 14

Journal details:

Oracle EPM ARCS 15

Become a member

Newsletter

Receive our newsletter to stay on top of the latest posts.


Dipen Shah

Author

Dipen Shah