In PerformancePoint Planning Server there are several pre-defined model types that satisfy different requirements. One of these model types is Financial Model with Shares Calculations and its primary use is for the financial consolidation of a group of companies where the ownership of the companies within the group is not 100%.
Take the following international group of companies:
Adatis (UK) is the ultimate parent (and in this case the holding company too) and own a 90% shareholding of Adatis (Italy) and Adatis (US). Adatis (Canada) is only 60% owned, although notice that Adatis (Italy) owns the remaining 40% of Adatis (Canada). Adatis (France) and Adatis (Switzerland) are both 50% owned by Adatis (Italy) who also owns 45% of Adatis (Germany).
In order to work out the consolidated accounts of the Adatis (UK) Group, one of the first steps is to determine the effective ownership of each of the companies within the group. This needs to be done from the perspective of the holding company.
What do I mean by effective ownership? I mentioned earlier that Adatis (Canada) was 60% owned directly by Adatis (UK) and 40% owned by Adatis (Italy). So you would be forgiven by thinking that Adatis (Canada) is 100% owned within the group albeit split between two of the companies.
However, notice t hat Adatis (Italy) is only 90% owned. This means that Adatis (UK) only effectively owns 90% of the 40% of Adatis (Canada) plus the 60% it owns directly. Therefore, the effective ownership is 90% of 40% = 36% + 60% = 96%.
Below outlines the effective ownership from the perspective of Adatis (UK) for the entire group.
PerformancePoint Server ‘With Shares’ Financial Model contains a built in Shares Calculation job that should, in theory, determine the effective ownership for a group of companies.
In order to test the PerformancePoint Shares Calculations you need to load data into two of the three additional measure groups that are automatically created in a ‘With Shares’ Financial Model.
Shares Outstanding
Each company needs a set of shares issued and the number of those shares that have voting rights. To keep everything simple I’ve issued all companies with 10000 shares with all shares having voting rights.
Shares Owned
For each of the parent companies the share ownership needs specifying. We have two parent companies, Adatis (UK) and Adatis (Italy). The shares owned have been set according to our company breakdown illustrated above.
Adatis (UK) owned companies
Adatis (Italy) owned companies
Control and Ownership
With the data loaded the shares calculation job can be executed. The results are posted to the third of the additional measure groups, Control and Ownership.
It’s the POWN columns, highlighted, that contain the effective ownership and encouragingly this matches our expectation. So it looks like, under these simple conditions the Shares Calculation job does what it should.
Incidentally, there are a few additional columns that are of interest.
- The PCON columns are the percentage that will be used in the consolidation.
- The PCTRL (Ultimate Percent Control) columns specify the controlling percentage. This is essentially controlled by the number of voting shares compared to shares owned and effective ownership.
- The ‘Overridden’ equivalents can be used to override the results of the shares calculation job and are the actual values used in the consolidation process. The non-overridden columns exist to show the outcome of the calculation. These can be altered but will have no effect as they are not used in the consolidation process itself.
- The final additional column represents the Consolidation Method. There is a simple formula that PPS uses to determine the Consolidation Method:
o If the ultimate percent control (PCTRL) is >= 0.0 and < 0.2, the consolidation method is NONE.
o If the ultimate percent control (PCTRL) is >= 0.2 and < 0.5, the consolidation method is Equity.
o If the ultimate percent control (PCTRL) is >= 0.5 and <= 1.0, the consolidation method is Full.
o If the entity is the holding company for the model, the consolidation method is Holding.
It’s important to note that there is no automatic way of setting a proportionally consolidated company. The consolidation method can be changed and will need to be changed if, for example, you wanted to proportionally consolidate Switzerland, and/or France as opposed to the Full consolidation by default.
Just a quick tip to save some mouse clicks.
When you are setting parameters for period start/end type variables you only have to set the end period if it differs from the start period. I used to think all parameters were mandatory.
It's particularly handy for a Consolidation Job as there are 2 sets of periods to set. It annoys me slightly that there is no Dynamic time selection available for Job parameters so you have to scroll through the time dimension to find a specific member whereas now I only have to do this twice, not 4 times !
PerformancePoint provides a staging database to load data into. Data loaded can be validated within the staging database before synchronising to the main application.
Below is a quick description of the validation steps performed - it could help feed into an ETL specification - if any of the 'rules' are broken validation will fail and the main application model cannot be synchronised.
Foreign Key Checks
All foreign keys must reference dimension members that exist.
Leaf Level Checks
Can only load leaf level facts unless the Business Process member is set to MANADJ or FXADJ.
Header Accounts
Facts cannot be loaded against accounts of type 'Header'
Business Process Members
The following Business Process members are calculated and cannot be loaded:
- CONSOLIDATED
- PREALLOC
- POSTALLOC
- PREELIM
Time Data View
Facts can only be loaded against the 'Periodic' dimension member.
Currency Checks
Input Currency must match Entity currency unless you are loading against the FXAdj Business Process in which case you can specify a different currency.
Flow Checks
The following Flow members are calculated and cannot be loaded:
- FX
- FXO
- FXF
- OPE
- MVT
- INT
Account Type/Flow Checks
No Flow is allowed on Income Statement/Profit and Loss and Non Financial accounts.
Flow is required for Balance Sheet accounts.
Intercompany Checks
Entity, Intercompany and Account dimension members must all have appropriate Entity and Account types, that set the 'Intercompany' member property to true for all Intercompany facts
A slightly off-topic post this one but it’s too good not to share. I say it’s too good, I only found out about this yesterday, maybe everyone knows about it already!
In Excel 2007 - apparently the functionality was introduced in Excel 2003! How out of date am I?! - there is a ‘Formula Auditing’ chunk on the Formulas Ribbon tab.
There are all sorts of goodies in here.
Trace Precedents allows you to find out the cells that make up the current cells data.
If you select a cell that is sourced from a formula and then click ‘Trace Precedents’ arrows appear indicating which cells the value is sourced from. Click ‘Trace Precedents’ again more arrows appear indicating where those source values are sourced from and so on and so forth. Below I’ve clicked ‘Trace Precedents’ four times to go back 4 levels from the selected cell J8.
You can go the other way too; Trace Dependents details all cells that that depend on that value. Selecting cell B3 and clicking ‘Trace Dependents’ four times traces all related dependencies.
It’s worth having a play with the other commands in the chunk too. Show Formulas, Error Checking, Evaluate formula and the Watch Window, together with the tracing functionality could really help debugging or reverse engineering complex spreadsheets. I just wish I knew about it all before now !