By TopDown Team
April 27th, 2016
Over the past few years, I’ve run into more clients who have asked for a solution in their Hyperion consolidation application to permanently store data. The common factor with these clients has been that their consolidation process involves using the HFM application to make topside adjustments in the form of journals. It has always been a leading practice in my mind that you should make a commitment to have data be in a traceable chain from the source systems into the HFM application and so when you were using HFM journals, you were breaking that chain because the journal adjustments were new data that did not exist in the source systems. Well, what happened was another leading practice was introduced into the HFM application which was the use of the “Data Type” custom dimension to mitigate the loss of traceability of source system data. See below for an example of the Data Type custom dimension.
With the Data Type dimension, you could tag data in the HFM application to identify where it came from and so it made it easier to see data with or without HFM journal adjustments. You could then pick a specific member or roll-up point in the Data Type dimension to trace back to the source system, for example member DATALOAD or TOTAL_LOCAL.
On the flip side, there were data type members for tagging HFM journals to categorize them, for example ADJ_LOCAL or ADJ_USGAAP, and so you could have some order to all the possible journal adjustments data in the HFM application.
Now going back to my initial point: once the end-users became comfortable with using their HFM application and managing data via the Data Type dimension, they wanted to take the next step which was to dispense with having to post the same HFM journal adjustments every period. For various reasons, the consolidation typically involves creating and posting HFM journal adjustments that are basically permanent data. It could be discontinued operations and maintaining a static balance sheet. It could be a consolidating adjustment that cannot find a home on a specific G/L. The HFM journals module has a nice feature that is the recurring journal template that applies to these situations. However, I’ve encountered client users who just do not want to post another “x” number of HFM journals every period. So they did not want to use recurring journal templates. The users want to reduce the mechanical steps that they have to complete during the close process so they have more time to analyze data. And they have approval from their management and an acceptable plan for their auditors to have controls in place. If the above conditions exist, this is a case of the consulting truism that the client is always right. We can enhance the HFM application to store permanent data.
First, we enhance the Data Type dimension. We will add specific members to tag the permanent data and facilitate the roll-forward of this data. At minimum, you need 2 new members. (It is possible that you create more members for better detail but it depends on the situation.) Create 1 new member to store the permanent data input via HFM journal – ADJ_RECUR and create another new member to store the roll-forward of the permanent data – ADJ_CALCREC. After updating the data Type dimension, this is what our example looks like below:
Second, we have to add a ruleset to HFM rules to calculate the roll-forward. The trick to rolling forward the permanent data is closing out the Income Statement to Opening Retained Earnings when you close the fiscal year and star a new year. So you need to treat the accounts in the Income Statement differently from the accounts on the Balance Sheet and additionally, you need to treat the account for Opening Retained Earnings uniquely. We’ll show the example in classic VB Script below. You’ll see that there is no rolling forward calculation for Income Statement accounts in the first period of the year. Additionally, you’ll see that there is a special calculation for opening retained earnings.
Third, a bonus tip for modifying the HFM rules is to also add an ImpactStatus calculation. Whenever you have any type of rules that automatically roll forward data, you need to ensure that the HFM application triggers those rules at the beginning of a new fiscal year. Add an ImpactStatus rule so that the calculation trigger is activated in the first period of the next fiscal year whenever there is calculation activity on the last period of the year.
After the Data Type custom dimension and HFM rules have been modified, the end-users can create and post HFM journals to ADJ_RECUR and have their data carry forward permanently. The key here is to use HFM journals for the initial data so that there is a distinct audit record of it in the Hyperion application and it can be easily modified or reversed out for any reason.