Wednesday, December 4, 2013

First impressions with the Calculation Manager

Having read that Calculation Manager is now a mature product, I left aside some time for evaluating this new development environment for HFM. First, I consulted the material found on the net, and got immediately started. I wrote and tested all the typical calculation rules found in most of my client implementations.

Here is some must-read material that I found very useful during this endeavor:
It appears that there are benefits from moving to calculation manager, though I'm much concerned with productivity of rules writing. There is too much clicking around on the visual replacements of both logic and functions.

On the contrary, using a good text editor, which:
1. supports customization to highlight the HFM VBScipt syntax,
2. provides with custom function and subroutine quick lists,
3. bookmarks, and
4. intellisense
is way more productive to write old fashioned classic rules than using the calculation manager. Though it may be worth keeping with the currents, as is always the case with technology.

One thing I was curious about, was whether the process of deploying rules with the calculation manager is irreversible, like the metadata case. However, it seems that both classic and calculation manager rules can overwrite each other at any time. In addition as soon as there are rules implemented with the calculation manager, you can extract them as you would do with classic rules.

Thursday, November 28, 2013

HFM Dimensions and Metatada

Overview
In this article, we shall first clear up a confusion observed frequently between HFM Dimensions and Metadata and explore their relationships. We shall close by examining a peculiarity of the Value dimension.

Note that all presented information refers to Oracle Hyperion Financial Management version 11.1.2.2 or earlier.

HFM Dimensions and Metadata
When training people in designing or maintaining HFM applications, all too often there is confusion between metadata items and dimensions. It is probably because for the most part of the application design, we populate members in Entities, Accounts and Custom dimensions through using our metadata design tools, which seem to be in one-to-one relationship with the dimensions used and selected in the application.

It may be helpful to separate between the two by visualizing metadata as an abstract concept related to application design and dimensions as the incarnation of the design in a working application. One thing is for sure: there is no one-to-one correspondence between a metadata item and a dimension.

In addition, not all dimensions behave the same. The following table summarizes the similarities and the differences in nature and behavior of both metadata items and dimensions pertaining to an HFM application.


A common HFM application uses 12 dimensions. More custom dimensions may be added in newest versions of the software; still the 12 dimensions are the core. Our first observation then, is that the number of metadata items does not equal to the number of core dimensions (refer to the first column).

Second, there are no dimensions for Currency, ConsolidationMethod, SecurityClass and Alias. We could have also added the AppSettings item found in the classic metadata designer, still this is so obvious that this item does not relate to any dimension whatsoever.

Our third observation is that there are also differences in the degree that we can customize each metadata item. For example, while we can freely add members almost to any metadata item, though ICP, Value and View metadata items (and their corresponding dimensions) limit what we can do with them. We can add members to the ICP dimension only indirectly, by adding Entities and marking them with the IsICP flag.

In addition, there is no way to add members in the Value dimension directly. Though for each new member we add to the Currency metadata item, three new members appear in the Value dimension -e.g. by adding GBP in the currencies list, we get GBP, GBP Adj and GBP Total in the Value dimension.

Finally, the View metadata item can be customized only to provide with the strictly defined members YTD, HYTD, QTD, MTD etc. Here we are restricted to the keywords that we can use as well as to the number of members which must equal to the number of levels set in the Period dimension.

Next, we observe that not all dimensions support hierarchical structure and those that do, may not support hierarchical aggregation. Even though we can structure Scenarios in hierarchies, the members of those hierarchies will not roll-up to their parent. Even though Entities can form hierarchies which aggregate, the aggregation calculations are performed only when consolidation is executed and they are dictated by the consolidation rules logic.

ICP and Value dimensions form fixed structures that cannot be customized. In addition the Value dimension has a complex hierarchical aggregation, whereby adjustment members are dynamically added with their corresponding members, while other members like <Parent Currency> and [Proportion]/[Elimination] are populated by executing translation or consolidation logic.

Value - Is it Really a Dimension?
Finally, I should mention another important peculiarity with HFM dimensionality. Value is not a “real” dimension. It behaves like a normal dimension as regards a single entity. However, when we roll-up to parent entities, it stops to behave like any other dimension. For example, consider a custom dimension member; let's call it Road bikes being part of the Total Products hierarchy in Custom1.


When you input a number in the Road bikes member in the Entity NY, you expect that this number will be part of the total found in the Road bikes, of the parent entity US.

Now consider the Value dimension, part of it is shown below:


When you post an adjustment to the <Entity Curr Adjs> member in the Entity NY, it will not be part of the <Entity Curr Adjs> member in the parent entity US. Instead, the adjustment number will be part of the total in the <Entity Currency> member of the parent entity. What you should keep in mind from the above discussion is that the Value dimension limits its behavior as a dimension within a single entity.

You can find more detail on how the value dimension works in a fine article here:

http://thanos-onetruth.blogspot.gr/2013/11/value-dimension.html

A Final Word

As mentioned above the present article refers to versions of HFM, earlier than 11.1.2.3. In 11.1.2.3 we have a few changes related to our discussion worth mentioning:
1. A new metadata item has been added corresponding to the cell text
2. The core dimensions have been decreased to 10, leaving Custom3 and Custom4 out of the core. It would not make sense to also leave Custom1 and Custom2 out as well, since they hold the currency members which hold the exchange rates.

Acknowledgements
Special thanks to Thanos Antzoulatos for reviewing and commenting on the above content.

Thursday, October 31, 2013

Best Practices in Account Details Breakdown

Introduction
This topic first appeared as my answer to a question posted in Oracle's OTN Financial Consolidation forums. Since then I have included several improvements to clear-up the presented information. The description of the design issue goes like this:

Suppose we have a Sales account which needs to be detailed by TotalProducts hierarchy in a custom dimension. Therefore TotalProducts is the sum of its children Product1, Product2, Product3 etc. Usually we get the total sales figure as part of the company's General Ledger trial balance. However, in order to have the sales figure both participate on the company's income statement and provide with the desired product analysis, we need to wait until the individual product sales figures become available. This prevents us from previewing calculated and consolidated early drafts of our financial statements.

The concept of the Data Stream
The pattern described above is all too common in consolidation applications. Different pieces of data, arrive at different points in time each providing different information or different aspects of the same information. This is roughly what we call Data Streams. We may identify two or more streams of data in a more formal fashion by analyzing the following characteristics:
  1. We get them from different sources (systems, people, departments etc)
  2. They become available at different points in time
  3. They are of different levels of detail or use different aspects of analysis
  4. They come in different frequencies (monthly, quarterly, annually)
  5. They may address to different audiences (internal management, external authorities, auditors etc)
It is imperative that we hold data from the different streams at different locations. We should never overwrite or delete information, even if it is redundant as in our example Sales account. All information used must be present and auditable at any time. And more often than not, redundant information among different streams must reconcile.

Let's return to our setting. We receive a sales figure from the trial balance stream and several sales figures broken down by product in a subsequent stream. Keeping in mind the above imperative, we can choose from the following solutions, all equally applied in typical HFM applications:

1st solution: use supplementary accounts
With this method we don't keep a single account for both sales figures. We use account Sales as a target of the trial balance and account SupplSales as a target for the product stream. The account SupplSales attribute Custom1TopMember is set to TotalProducts to provide with product analysis. Here is how the product hierarchy in Custom1 dimension looks like (indented members are children):


In addition to the two accounts Sales and SupplSales, we shall also need a validation account for instance VSales to hold the difference between Sales and SupplSales. To calculate VSales we have to write a validation rule like this:


While VSales equals Sales when the trial balance stream is submitted, as soon as the product stream is also submitted VSales will zero out. We can also connect VSales to the application validation accounts, to control the process flow.

2nd solution: submit the product stream as a journal entry
In this case we need a single sales account and use a slightly modified product hierarchy in Custom1:


When loading the trial balance, the total sales figure will be targeted to the TB_AllProducts member. From there the sales figure will roll-up to TotalProducts and the total sales figure will enable us preview the financial statements before all submissions of different streams are made available.

At a later time when the products stream comes in, we shall load it as a balanced journal entry. This journal entry will credit product sales figures to Product1, Product2 and Product3 and debit the total to TB_AllProducts, effectively countering the previously loaded amount, without overwriting it. The result will look like this:


Note that when we select <Entity Curr Total>, we get both the trial balance figure at the summary level, and the individual product sales at the detail level. Also note that if the individual product sales submitted, do not meet the summary loaded with the trial balance, a balancing figure will appear in the cell identified by the combination A#Sales.C1#TB_AllProducts.V#<Entity Curr Total>, instead of zero. This data cell can be considered as the validation, though we have neither provided with a validation account, nor a validation calculation rule. Once more, we can connect the above cell to the application validation accounts, to control the process flow.

3rd solution: use an automatically calculated balancing entry
This is a more advanced technique, whereby you keep a single Sales account and support with product analysis in a custom dimension as before, with the following changes:


Note that in this case we must also set the attribute AggrWeight of TB_AllProducts to zero, so that we ensure that there is no double counting when both the trial balance and the product streams are loaded. In order for this to work we also need to write the following calculation rule:


As soon as we load the trial balance stream and execute the calculations, the picture will be like this:


Note that the whole trial balance amount is captured from the calculation and rolls all the way up to the total, while the product sales cells are still blank.

Subsequently, the product stream comes in, we calculate again and the above picture changes as follows:


Note that in the above example the balancing entry is zero, while the TotalProducts value has not changed since the initial load of the trial balance.

Now, interesting things happen when the product values do not meet the trial balance total. In that case, the balancing figure will fill the gap between the two totals. We can view the balancing member as the validation between the two streams. Like the previous solutions, we can connect the balancing member to the application's validation accounts to control the process.

Conclusion
We presented three techniques that we can use for addressing the agreement of redundant information coming in different streams. There is no single answer to the question which is best. One has to examine each individual account and the related data streams that correspond to the account and decide what fits best. I have cases where all three patterns have been applied to the same application for different accounts and/or streams. Possibly, you could come up with a hybrid solution, it is all fine.

Sunday, January 8, 2012

Hierarchical versus Flat Financial Consolidation

or The implications of structure over financial consolidation calculations

Forward to the blog edition


I first considered the concepts and issues presented in this article, during early consolidation engagements back in 2004. There was a general assumption among financial reporting practitioners that financial consolidation calculations would arrive at the same numbers no matter what the consolidation structure of an indirect investment would be. However, this assumption turned out to be false. The differences became apparent during the elaboration of the mathematical formulation of financial consolidation I was working on at that time. Four years later, in August 2008 this material was first organized and published in print as a whitepaper, originally released only to my clients.

As you'll see, despite the fact that it is a product of a mathematical model, the article is written in finance terms, because my intention is to increase awareness of finance executives about the effect of choosing between different group configurations on their final consolidated numbers. Having decided to release this article to the wider audience, I hope it will be as useful as it has been to my clients.

As a final word, the reader may recognize that the calculations of the non-controlling interests (or NCI, formerly called the minority interest) in the article, are according to IFRS 3 (2004). While the measurement of NCI based on the share of the identifiable net assets of the acquiree is still valid, IFRS 3 (2008) provides with an alternative measurement based on the fair value of the NCI. I may be able to write something about this change in a later post.


Overview
The purpose of this working paper is to compare the results of the two commonly used methods for consolidating indirectly owned fully controlled subsidiaries in forming the Group Balance Sheet. In order to demonstrate the arising differences in calculated values, we use a numeric example. Finally we attempt to explain the differences and suggest ways to re-conciliate the two methods.

Setting the Stage

Our subject is a subsidiary company which belongs to a multi-level group of companies. This means that the company in question is directly owned by another company which is itself owned by a group’s holding company.  It can be shown that the same issues arise if we place our subsidiary company at an even lower hierarchical level, but for simplicity we limit our discussion to a two level hierarchy. The following figure exhibits the hierarchy in question.
Company H has acquired 60% of S and has the full control of the company, therefore H consolidates S using the full consolidation method. In the same fashion company G owns 80% of H and also uses the full consolidation method to calculate H’s contribution to the consolidated reports.

At the time of the first acquisition of S by H, H spent 110.000 to acquire S’s net assets consisting of 100.000 Share Capital and 50.000 Reserves. To simplify matters, ever since the first acquisition there was no change in percentage owned, or the invested amount. Furthermore, the percentage of G in H has never changed. Finally, at the time the consolidated numbers are calculated, S reports 115.000 Reserves.

Flat and Hierarchical Methods

As mentioned in the introduction of this article, we have observed that group financial reporting practitioners are using two distinct methods for consolidating S in G’s consolidated Balance Sheet. Apparently both of the methods are acceptable to the auditing authorities.
The Flat method ignores the fact that it is indirectly owned and consolidates S as if it were a direct subsidiary using the effective percentage which calculates as the product of the two percentages – i.e. 80% x 60% = 48%. It also calculates an effective minority percentage – i.e. 100% - 48% = 52%.

The Hierarchical method performs the calculation in two steps; in the first step S is consolidated into H’s consolidated Balance Sheet using the direct ownership of H in S (60%) and then the results are further consolidated into G’s group Balance Sheet using the percentage of G in H (80%).

The two methods calculate the same contribution of S’s reserves to the consolidated reserves, but calculate different numbers for Goodwill and Minority Interest. In the following sections we demonstrate the schedules calculating the contributions of S, using the two methods, illustrating the differences in produced results.

The Flat Method Schedules

As mentioned above in the following schedules we are using the effective percentages for ownership and minority interests – i.e. 48% and 52% respectively.

Schedule 1: Goodwill calculation


Investment (80% x 110.000)

88.000
Less:


Share Capital
100.000

Reserves at acquisition
50.000


150.000

Indirect percentage (48%)

(72.000)
Goodwill

16.000



Schedule 2: Reserves calculation


Reserves
115.000

Reserves at acquisition
(50.000)

Effective percentage (48%)
65.000
31.200
Consolidated Reserves

31.200



Schedule 3: Minority Interest calculation


Share Capital
100.000

Reserves
115.000


215.000

Effective Minority Percentage (52%)

111.800
Investment
110.000

Group's minority percentage (100% - 80%)

(22.000)
Minority Interest

89.800

In Schedule 1 the consideration that there is no exclusive right of G in the investment of 110.000, is taken into account in the Goodwill calculation. Since we exclude the part of investment which is attributed to the G’s minority from the Goodwill calculation, we have to exclude the same amount from Minority Interests, therefore the adjustment at the bottom of Schedule 3.


Clarification
An alternative view of the above calculations would have been to calculate Goodwill using the full investment and Minority Interest without the adjustment. This calculation would yield Goodwill of 38.000 and Minority Interest of 111.800. Then we should have posted a journal entry to eliminate the part of the investment attributed to G’s minority as shown on the side.




Dr
Cr
Minority Interest
22.000

Goodwill

22.000

22.000
22.000


Below is the contribution of S to the Group’s consolidated Balance Sheet as a single journal entry.


Dr
Cr
Elimination of Investment

110.000
Goodwill
16.000

Elimination of Share Capital + Reserves
215.000

Consolidated Reserves

31.200
Minority Interest

89.800

231.000
231.000

The Hierarchical Method Schedules

Here we are going to follow two steps of calculations; first for consolidating S in H and second for consolidating H in G. This results in six schedules instead of three.
Step One
Schedule 1: Goodwill calculation


Investment

110.000
Less:


Share Capital
100.000

Reserves at acquisition
50.000

Direct percentage (60%)
150.000
(90.000)
Goodwill

20.000



Schedule 2: Reserves calculation


Reserves
115.000

Reserves at acquisition
(50.000)

Direct percentage (60%)
65.000
39.000
Consolidated Reserves

39.000



Schedule 3: Minority Interest calculation


Share Capital
100.000

Reserves
115.000

Minority Interest percentage (100% - 60%)
215.000
86.000
Minority Interest

86.000

In the first step there is nothing special. It is a consolidation schedule of a direct ownership.
Step Two
Schedule 4: Goodwill calculation


Goodwill from previous step

20.000
Goodwill

20.000



Schedule 5: Reserves calculation


Consolidated Reserves from previous step
39.000

Percentage owned in subgroup (80%)

31.200
Consolidated Reserves

31.200



Schedule 6: Minority Interest calculation


Minority Interest from previous step

86.000
Consolidated Reserves from previous step
39.000

Minority Interest in subgroup (100% - 80%)

7.800
Minority Interest

93.800

In the second step we adjust the values calculated in the first by spreading the consolidated reserves of the first step to the group and the minority. It could be viewed as a reclassification of the amount 7.800 from the Consolidated Reserves account to the Minority Interest account. One observation at this point is that the Goodwill calculated in the first step is preserved in the second.

The contribution of S to the Group Balance Sheet as a single journal entry is:


Dr
Cr
Elimination of Investment

110.000
Goodwill
20.000

Elimination of Share Capital + Reserves
215.000

Consolidated Reserves

31.200
Minority Interest

93.800

235.000
235.000

Comparing Results

The following table summarises the results of the two methods:


Hierarchical
Flat
Difference
Goodwill
20.000
16.000
4.000
Consolidated Reserves
31.200
31.200
0
Minority Interest
93.800
89.800
4.000

It is evident that the Hierarchical method overstates Goodwill and Minority Interest by the same amount. As a result the Total Assets of the resulting Balance Sheet are also overstated by that amount. It can be shown, although it is not in the scope of this article, that the difference of the two methods can be calculated by multiplying the value of Goodwill calculated in the first step of the Hierarchical method by the Group’s minority percentage. In our case at hand this value is 20.000 x 20% = 4.000.

It is as though the Hierarchical method fails to take into account that there can be a claim in the Goodwill of the subgroup by the group minority, something that we took into account in the Flat method when we attributed part of the Investment to the group’s minority and subsequently excluded that part from calculations. Having all this in mind, we can attempt to reconciliate between the two methods. Goodwill and Minority Interest accounts resulting from the Hierarchical method can be adjusted to match the values resulting from the Flat method using the following journal entry:

Reconciliation Journal Entry



Dr
Cr
Minority Interest
4.000

Goodwill

4.000

4.000
4.000

The amount posted can be calculated as shown earlier. One view of the issue could be that the above adjustment should be part of the schedules of the Hierarchical method in the first place, but as we mentioned before the method demonstrated herein is based on common practices that have been observed during real-world group reporting engagements.

Of course one can claim that the correct numbers are those calculated by the Hierarchical method. It is not the intention of the author to make such an argument, therefore if there is such a case, then a journal entry with inverted Debit and Credit can be posted to the Flat method results to reconcile to the Hierarchical method numbers.

A final observation is that the issue arises when the group’s percentage in the subgroup is not 100%. In the case of a fully owned subgroup the exhibited difference in calculation would zero out. It follows that the less the percentage owned the greater the calculated difference (and the greater the reconciliation amount).

There are a number of reasons why the Hierarchical method would be preferred over the Flat. First, the whole procedure of preparing consolidated financial statements with the most popular tool, the spreadsheet, needs a painful effort from finance staff therefore receiving ready consolidated statements from subgroups greatly minimizes the required effort. Second, an organization which invests in acquiring an existing group, will find out that the newly acquired form will already have reporting mechanisms in place, which is less costly to re-use than substitute.

However, the above reasons are no longer valid, if the organization uses special software to prepare its consolidated financial statements, rather than the commonly used spreadsheets.

Conclusion

In this article we have demonstrated the two methods commonly used by companies to consolidate indirectly owned subsidiaries and compared the results. Having isolated the issue and being aware of the arising differences companies can select either method while at the same time apply due diligence and build confidence in their published results.