InterCompany Reconciliation

Here is another informative document on InterCompany Reconciliation from the 'BI VPC 6.0'.

********************************************

Details on IC Reconciliation

This document is indented to provide instructions on setting up, executing and validating intercompany reconciliation.

IC Reconciliation from 10,000 feet

The purpose of IC reconciliation is to ensure that errors in recording intercompany transactions to not create erroneous debt or revenue in an intercompany entity. For example, if Americas sells a product to International, there will be an entry in America’s receivables account and an entry in International’s payable account for the transaction. However, if America records the transaction as $100 but International records the transaction at $120, we’ve got a problem. Intercompany reconciliation resolves this problem by adding -$20 to a special account that belongs to the seller. So, when everything gets rolled up during consolidation, America and International’s intercompany balances will be even.

Prerequisites for IC Reconciliation

1. You’ll need Modeler permissions in Business Modeler and direct access to the SQL app databases for your application.
2. You’ll need a financial model or financial model with shares. This is critical; IC reconciliation rules won’t run on any other type of model.
3. You’ll need an entity dimension. This is important for obvious reasons, specifically because you need to have entities associated with your transactions (intercompany or not!).
4. You’ll need an intercompany dimension. It’s usually just like the entity dimension except the intercompany dimension is a flat hierarchy. When you write your intercompany reconciliation rules, you’ll need to reference members in the intercompany dimension.
5. You’ll need a time dimension.
6. You’ll need a flow dimension if your financial model has shares. If not, the flow dimension is optional.
7. If you have entities that use different currencies, you’ll need an exchange rate model. You’ll also need to populate your exchange rate data for the time periods you care about.
8. You’ll need at least 3 accounts for a decent IC reconciliation. First you’ll need 2 intercompany accounts to hold the transaction from the buyer side and seller side. The third account will be the balancing account. I’ll cover this in more detail later.
9. You’ll need intercompany transaction data.
10. You’ll need intercompany reconciliation rules.

Getting started

Configuring model properties

First we’ll need to set the following model properties. Open Business Modeler and connect to the model site you’re working with (this is where all of our Business Modeler work will take place). Check out your financial model of interest and go to the Model Properties tab. Scroll down to the Reconciliation Balancing Account. Your screen should look something like this.

Set your Reconciliation Offset and Reconciliation Balancing accounts to their proper values.

Loading Intercompany Transactions

Intercompany transaction data is stored in the default measure group table in the. After you have loaded your data, you can run the following query to see your data more easily:

select s.[name] as Scenario, time_month, b.label as [Account], b.[name] as Account, at.[name] as [Account Type], ac.[name] as [Account Classification], dc.[name] as [Debit/Credit], c.[name] as BusinessProcess, currency.[name] as Currency, d.[name] as Entity, g.[name] as IntercompanyMember, a.[value], t.Label as [Time Data View], r.rulesetorrulename as [Rule], RuleID, ContextID, AssignmentID, JournalID, a.createdatetime, l.comments, a.changedatetime
from mg_managementreporting_measuregroup_default_partition a
left outer  join [d_scenario] s on a.[scenario_memberid] = s.memberid
left outer  join [d_account] b on a.[account_memberid] = b.memberid
left outer  join [ag_accounttype] at on b.[accounttypememberid] = at.memberid
left outer  join [ag_debitcredit] dc on at.[debitcreditmemberid] = dc.memberid
left outer  join [ag_accountclassification] ac on at.[accountclassificationmemberid] = ac.memberid
left outer  join [d_businessprocess] c on a.[businessProcess_MemberId] = c.memberid
left outer  join [d_currency] currency on a.[currency_memberId] = currency.memberid
left outer  join [d_entity] d on a.[entity_memberid] = d.memberid
left outer  join [d_entity] g on a.[intercompany_memberid] = g.memberid
left outer  join [rulesetsorrules] r on a.ruleid = r.rulesetorruleid
left outer  join [d_timedataview] t on a.TimeDataView_MemberId = t.memberid
left outer join LoadingControlId l on l.LoadingControlID = a.LoadingControlID
--where
--a.time_month = 200301
--and g.memberid != -1

Creating Business Rules

1. Now that we have some IC reconciliation data, we need to create a business rules to actually run our reconciliation. Open Business Modeler, check out the Consolidation model and click on the “Business Rules” tab. You should see a root ruleset of type “InterCompanyReconciliation” called “Intercompany Reconciliation Rules”.
2. Inside this ruleset, create a new rule. As an example, let’s look at a reconciliation rule for Tahoe and Vermont (from AdventureWorks data). This rule will reconcile transactions between Tahoe and Vermont for the accounts we specify. In the rule expression pane, type the following:

Reconcile(
[Intercompany].[All Members].[Tahoe],
[Intercompany].[All Members].[Vermont],
[Account].[Consolidation].[Cons 1013000],
[Account].[Consolidation].[Cons 2015500],
[Flow].[All Members].[ADD]
);

This rule states that we will reconcile balances treating Tahoe as the seller and Vermont as the buyer for transactions where Tahoe used account 1013000 and Vermont used the account 2015500. We will only reconcile transactions in the Addition flow. As you can see, the entities and accounts here match the inputs we created in the measure group table. That’s good. Your screen should look something like this:

1. Validate, save and deploy your rules. Now we’re ready to run!

Running the Job

1. Open an Excel client with the Performance Point add-in installed. Connect to the PerformancePoint server. We’ll run the IC reconciliation job by choosing PerformancePoint > Jobs and Assignments > Launch Job. Here are the wizard steps to follow:
   1. Choose Create New Job and click Next.
   2. From the Job Type dropdown, select “ICReconciliation Job” and click Next.
   3. Specify your job parameters.
   4. Click Next and Finish to launch the job.

1. The job shouldn’t take much time to run; you can monitor its status with the Job Status tool (found under PerformancePoint > Jobs and Assignments > Job Status).

Note that you can also run the job directly from Business Modeler by clicking the Process Management link and selecting “Jobs” from the View dropdown. Then click “Schedule Job…” and follow the wizard. Save the model site then right-click on the job you just created to launch it.

Verifying the Results

1. Once the job has finished successfully (you can check on its success in the Job Status tool; we’ll need to go back to the measure group table to verify that the results were written out correctly. The easiest way to do this is to re-run the IC Reconciliation MG query that we used earlier. This time, you’ll notice that more rows exist in the measure group table. These rows were created by the IC Reconciliation job (look at the Rule column), which will automatically launch the currency translation job as needed.

The Tao of Reconciliation

You may be asking yourself, “Self, what is an Offset Account? What is a Balancing Account for that matter?” Well, when an IC reconciliation job runs it looks at the difference between the values recorded by the buyer and the seller. In this case, the seller recorded the transaction at $50.00 higher than the buyer. IC reconciliation always assumes the buyer’s value is correct and so we must reduce the seller’s value by 50 bucks. The offset account (that we set in the model properties) is considered a “seller’s account” so adding -$50.00 will bring the whole shebang into balance. But, our ancient Greek accounting rules tell us that we need to record a double entry for this transaction. Enter: the balancing account. The balancing account will always get a double-entry for the same amount and opposite sign as the offset account.

We said earlier that the offset account must be of type intercompany and the balancing account must not. When we perform a consolidation on this set of entities and accounts, all of the transactions in the offset account (specifically the ones created by intercompany reconciliation) will be eliminated automatically because they are intercompany transactions. However, the transactions we created in the balancing account will not be eliminated and their value will roll up into the balance sheet when consolidation happens. This is how we restore balance in the universe when there are general ledger discrepancies.

The diagram above illustrates the relationship between the offset and balancing accounts and is especially helpful for people who like buckets.

********************************************