Design Patterns for Historical Translations in Oracle HFM Part 1

There is always very much confusion surrounding the topic of currency translation in Oracle Hyperion Financial Management (HFM). I will focus here on one of its more challenging aspects – translation of equity/capital accounts, aka historical translation. I will be arguing that:

• “Override accounts”, the traditional design for historical translations, is not the best fit for applications where consolidation structures call for multi-layer translations, or where journals or custom detail are in active use; the design also scales poorly
• The primary reason is that override accounts work around, but do not address directly the problem of periodic translation for capital accounts as required by GAAP
• A better alternative is to leverage the HFM built-in TransPeriodic function to:
  • Streamline periodic translation for capital accounts with the end-of-month rate
  • Book the resulting exchange differences to Cumulative Translation Adjustment accounts
  • Build a manual adjustments interface for users to fine-tune the streamlined result

Traditional design and why it’s bad

From my experience, in the HFM world equity translation is most commonly handled through a set of so-called “override” accounts. This override set, which comprises duplicates for all accounts that require historical translation, is a rollup placed somewhere outside the balance sheet and used for tracking foreign currency balances and maybe also for entering pre-translated amounts. This tracking and entering occurs at <Entity Currency> level; once HFM gets to process a foreign currency, it pulls those balances from <Entity Currency> to replace (i.e. override) the results of the HFM default translation – hence the name “override accounts”.

Consider the following example:

1. Opening balances are pulled from the prior year
2. There might be variations in the process here:
   • Sometimes calculation rules would pick up the local currency number and apply an exchange rate to it: ending, average or some blended rate entered by the user;
   • And sometimes it is the users’ responsibility to enter manually translated amounts directly into override accounts.
3. The ending balance rolls forward, that is, it gets calculated as the opening balance plus activity.
4. The numbers calculated above are pulled from <Entity Currency> to <Parent Currency>.

I note in passing a certain degree of pain in the HFM administrator’s neck implicit in this design: he/she has to keep duplicate override accounts for each reporting currency, maintain a less readable code with lots of nested ifs and counterintuitive status impacts (impact from <Parent Currency> in the last period to the next year’s <Entity Currency>, for example), and deal with accommodating periodic input for override numbers (because most likely users won’t appreciate entering them in a year-to-date (YTD) manner). But this is all tolerable compared to the flaws that might arguably be perceived as deal breakers when considering translation setup:

• First, there is this apparent problem that pre-staging (at <Entity Currency>) takes place before processing journals (found at <Entity Curr Adjs>). It’s relevant to compare this to how native translation happens in HFM: it first reads local numbers from <Entity Curr Total>, and then translates and writes the result to <Parent Currency>. Of course, the pre-staging calc will retrieve local numbers for translation from <Entity Curr Total> too – in attempt to fetch journal adjustments posted to equity accounts or the income statement. But the problem is that at this point any calculation logic assigned to the <Entity Curr Adjs> layer has not yet happened, <Entity Curr Adjs> is in CH (data has CHanged) state, and so is <Entity Curr Total>. Ultimately, it results in:
  • Reading numbers from CH cells, i.e. from an unreliable source.
  • Missing out on HFM built-in calculation optimization (coming with the Consolidate option as opposed to Consolidate All With Data or Consolidate All) as users always need to start calculations from the bottom layer (<Entity Currency>) even though it might be in OK status. For example, consider the situation where users update their journals, but the data loaded to <Entity Currency> was calculated long ago.
• Second, this design poorly scales up for multi-staged translations, which are the situations where data is translated more than once as it travels up the consolidation hierarchy. The issue here is with pushing the overriding numbers up. Because these numbers are ultimately destined to replace data, the pre-staging logic must now tap into entities originally not intended for use in overriding at all (think for example of USD entities found at the bottom of different hierarchies, all of which ultimately end up in USD) or intended only partially, and incorporate all consolidation adjustments encountered along the way in several currencies. Most likely the resulting solution will be tailored to specific translation paths and require metadata and calculation updates as new currencies are added to the application. As a side note, it also proves to be surprisingly hard to seed opening balances for new currencies or new parent entities.
• Third, this pre-staging, rendered normally through a set of HS.Exp statements, almost invariably results in some loss of custom detail upon translation – particularly intercompany and roll-forward detail, which are extremely important for elimination of intercompany equity. This happens either because HS.Exp syntax calls for refining the POV resolution, or, if users in addition enter some override data manually, because it’s not feasible to make users override every existing intersection. And because of this it also poorly scales up for new custom detail added to the application.

My personal experience with struggling through scalability issues during HFM updates suggests that all these flaws are but the effect of forcing the periodic translation logic (normally applied to the income statement accounts) into the resisting year-to-date perspective. Once the right tool for manipulating periodic data has been adopted, the logic will fit in, and the above concerns will no longer exist.

Periodic translation in GAAP requirements

To make my point about periodic translation I start by taking a closer look at how capital accounts are supposed to be translated according to the rules of accounting. Both US GAAP and IFRS imply translation of any change to equity balances (i.e. equity activity comprising capital transactions) at the rate on the date of the corresponding transaction (the transaction rate). I stress the word “imply” because the issue is not addressed anywhere in the foreign currency topics directly (ASC 830, IAS 21). The idea implied here is to be able to reconcile capital transactions and resulting balances within the group as required explicitly by the consolidation topics (ASC 810, IAS 27). That is, investment accounts on the parent books and equity on subsidiary’s must match in reporting currency to properly eliminate, and rolling ending balances forward through transaction rates ensures just that. The resulting exchange differences are booked to the Cumulative Translation Adjustment (CTA) account in US GAAP, or Translation Reserve under Other Comprehensive Income in IFRS.

From the HFM standpoint two aspects appear challenging here: 1) use of transaction rates (as HFM applications normally don’t track daily activity), and 2) the way the ending balance is not translated at the ending rate (as balance sheet accounts are out of the box), but rather accumulates each change to the balance, duly translated at the appropriate rate.

The former challenge normally draws attention away from the second one. Instead of coming up with creative solutions in this regard though, I suggest employing the approximation trick I learned from clients doing their consolidations in Excel workbooks: to speed things up during month-close processes, they would routinely use the end-of-month rate as a convenient stand-in for all transactions happened in the period. The approximation usually works fine for quick month-end reporting and can be fine-tuned in audited reports. In HFM this would mean to have a special tool to do that, and I will get back to fine-tuning translation results through foreign currency adjustments in the next blogpost.

So much for transaction rates then. We can now re-consider the second challenge and put it this way: equity/capital balances accumulate each change to the balance translated at the end-of-month rate.

Now that this formulation is in place, I would like to highlight the fact that HFM is well aware of this translation method. The PVAforBalance checkbox under application settings, once checked, forces all accounts of asset and liability type to translate exactly in this manner. Now in HFM this translation is called PVA (Periodic Value Added) translation. The translation method being in place when the box is unchecked – i.e. when numbers are translated in the YTD view instead – is referred to in the admin guide as VAL (VALue at Exchange Rate) translation. We can say that in the typical HFM setup the balance sheet accounts are VAL translated, and the income statement and cash flow accounts are PVA translated (in accordance with what GAAP refers to as the current rate method).

Perhaps we can better appreciate this PVA-VAL opposition if we put it into the broader context of the periodic-YTD opposition, found at the very core of HFM. This opposition is best pictured in terms of data flowing up through consolidation layers and becoming subject to the corresponding HFM settings:

There is much to say about this diagram and about periodic-YTD interaction in general, but for now I simply point out that:

• Starting with data entry at the bottom, at each level HFM makes the administrator pick up the master view – that is, between Periodic and YTD. The other view will be derived, i.e. calculated by HFM based on the master view. For example, at the bottom layer the master view is the view in which HFM will expect users to load data.
• While it might appear obvious, it is important to add here that at any given layer the master view is also the view in which HFM will expect calculation rules to calculate data.
• At the translation level, the <parent currency> level, the PVA settings give the administrator some control over what to consider periodic and what to consider YTD. For PVA translation Periodic is the master view. That is, translated data is considered periodic, and YTD is then derived by HFM. And for VAL translation YTD is the master view and Periodic is derived.

HS.TransPeriodic

With this quick introduction in place we can again consider the PVAforBalance setting for equity translation purposes. It feels like the right place to look, but unfortunately, it seems to be an all-or-nothing thing – once turned on, it will become the default translation mode for all balance sheet accounts. It helps to know though that the HFM calculation engine comes equipped with a related set of functions – namely, HS.DefaultTranslate and HS.Trans/TransPeriodic.

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

The former lets you override translation settings – that is, translation rates and translation methods (PVA or VAL), – for a specific calculation unit: say, some particular scenario or scenario/entity combo. But it is the latter function, which gives you the same control over a particular POV within the calculation unit, that looks really promising. Indeed, we’d like to restrict this behavior to certain accounts – that of equity type only.

The screenshot above is from HFM Admin Guide (https://docs.oracle.com/cd/E57185_01/OHFMA/transperiodic.htm). TransPeriodic is the function that calculates the exchange difference between Rate1 and Rate2 on the periodic portion of the source POV and writes the result into the periodic portion of the target POV[1]. The source POV may differ from the target, but if the source is omitted in the function call, the same POV is used.

Apparently, if Rate2 is omitted, the result is simply PVA translation of the source POV at Rate1. For example, the below line tells HFM to PVA-translate the Share Capital account at the end-of-month (EOM) rate (aka closing rate aka ending rate):

There is no need for specific mention of custom detail or ICP for the account; the new translation method is simply applied to all valid intersections.

Streamlined translation of equity roll-forward

Below is a sample vbscript code that leverages HS.TransPeriodic to switch equity accounts into the PVA mode. In this example equity accounts come with roll-forward detail attached:

Line 199: The account list looped through here (“PVAatEOM”) is UD-based – it’s the list of accounts that have “PVAatEOM” typed into their UD1 property. This is the list historical translation will be restricted to.

Line 200: The preferred design for the roll-forward dimension (referred to throughout the code by its short name “RF”) is with the “true” ending balance sitting at the top, always representing as such the sum of the opening balance and all activity members. In the grid below this relationship is indicated with blue lines.
For activity members SwitchTypeForFlow=Y.

Lines 201-202: Here we assume that elsewhere in the rules opening balances get pulled from the prior year. The only piece left thus is to PVA-translate activity members at the EOM rate found at A#EOMRate. This is what happens at line 202: TransPeriodic PVA-translates all combinations of the accounts captured in the PVAatEOM member list and base roll-forward members that represent equity activity (that is, those members where SwitchTypeForFlow is true – line 201).
The ending balance then comes together as the top member aggregating the opening balance and all PVA-translated activity members. The red lines below illustrate this process – activity is translated in the periodic view by TransPeriodic and then the YTD number is derived (shown by red dashed lines).

This design also comes with low maintenance cost: no additional action on the admin side needed to attach a new currency, roll-forward member or equity account. Furthermore, all custom detail including the vital ICP detail is subject to the same translation approach and is preserved during translation and subsequent contribution to the parent.

Streamlined translation of equity balances

The ending balance set up as the parent of properly translated pieces of roll-forward is the preferred design, but it is not at all uncommon to see the ending balance as loaded from the general ledger to roll up instead. Compare it to the roll-forward hierarchy discussed above:

Two differences worth highlighting: first, this time the cell we wish to translate is not a flow, but a balance (because SwitchTypeForFlow property reads No). In HFM, flow cells support the Periodic-YTD concept, and balance cells do not. As we are about to see though, TransPeriodic does the same great job translating balance type cells as it does flow type. Second, even though TransPeriodic can “see” the periodic portion of activity, in the first period this activity is lumped together with the opening balance.

We can further generalize this case as the situation where there is no roll-forward attached at all, i.e. where equity accounts are simply balances:

Lines 149-151: Accounts in the PVAatEOM list get PVA-translated at the ending rate. No additional action is needed – TransPeriodic works the same way with balance type cells.

Lines 153-172: In the first period during the above PVA translation we can distinct two portions within the ending balance: Ending Balance @ EOM rate = Activity @ EOM rate + Opening Balance @ EOM rate. What’s in fact expected in the first period is Ending Balance = Activity @ EOM rate + Opening Balance @ Historical Rate (the blended rate as of the end of the prior year). The difference between the two is Opening Balance @ Historical Rate – Opening Balance @ EOM Rate = Opening Balance @ (Historical Rate – Opening Rate). A little awkwardly, this is what is accomplished at line 169. The historical blended rate is calculated individually for each ending balance intersection existing in the prior year – the list of intersections is retrieved by the OpenDataUnit call at line 154.

So these above are two very basic designs (with and without roll-forward) to build a historical translation solution upon. In the next post I will show how TransPeriodic also comes in handy when developing complementary solutions for the historical translation impact on CTA and translation adjustment

[1] That is, “write” from the calculation engine perspective. From the HFM database perspective data is always stored YTD, and periodic data is calculated on the fly, so of course any “writes” to the database will be in YTD.