HFM User:

I recently updated data and consolidated, but I’m not seeing the correct numbers—Please HELP!

Chris Barbieri:

Frequently, this problem stems for the Value dimension member that is in your Point of View.  Your issue may be that your POV is set to USD Total rather than <Entity Curr Total> or <Parent Curr Total>.  Here’s why this is important:

Data is not automatically translated into USD within HFM.  During consolidation, HFM will only translate from each entity’s <Entity Curr Total> into the currency of its immediate parent entity.  So, if a non-USD child does not have an immediate parent whose default currency is USD, HFM will not generate USD balances or re-translate data into USD.  Accordingly, I discourage clients from using USD Total as the default Value dimension member for grids, reports, and data extracts unless they are 100% certain that every foreign currency entity translates into USD.

There are a few common approaches that we use if clients need to report every foreign currency entity in USD:

The first is to ensure that all foreign currency entities have an immediate parent entity in USD.  This is typically designed using natural entity hierarchies based on reporting needs or by using an alternate hierarchy created specifically for translation.  That way, we can rely on HFM’s native functionality to translate each foreign currency into USD.  When evaluating this option, it is important to consider that any alternate hierarchies would need to be consolidated to accurately translate data into USD.

Another approach is to manually translate entities into USD by clicking “Translate” in a data grid that is set to V#USD.  The problem with this approach is that it is manual and requires users to return to this grid and re-translate after every data change.  I call this “babysitting the data” and I do not recommend it because it is unreliable – the users must remember to perform this step, which is no way to guarantee data quality.

Another approach is to utilize a Reporting Currency hierarchy in a custom dimension.  This is similar to how Essbase translates data.  It can work well, but it requires careful consideration and a thorough design.  This approach works very well if the users want to see each entity in both local and USD currency without maintaining a separate translation entity hierarchy.

I hope that you found this explanation of various ways to translate data for all entities into USD valuable.  Please email insights@finit.com with any questions or feedback on this topic.

Do you have an issue or topic that you’d like Chris to discuss in his next blog post?  Please e-mail us at insights@finit.com and we will “Ask our ACE”!

Posted By: jbulman