Thursday, April 28, 2016

HFM translations against the grain

When HFM is set up, the application settings have a section for specifying where balance accounts should be translated with PVA or VAL and same happens for flow (income statement) accounts. Normally this works fine. Typically we do VAL for balance and for flow we do PVA if the exchange rate is a monthly average and VAL if the exchange rate is a year-to-date average.

What happens, though, if some of the data works with these settings and some doesn't?

My current client has this situation. For actuals, they use year-to-date exchange rates. But for budget and forecast, which are coming over from Hyperion Planning, they do monthly translations. If the rate doesn't change through the year, it doesn't matter. But if the rate changes, then there are problems.

Fortunately, there's an easy solution. In the translate subroutine, the function DefaultTranslate allows you to override the application settings with the translation methods and exchange rates for balance and flow. So, I did an if/then to apply this function with the appropriate settings for budget and forecast scenarios and we're good to go.

The code looks like this. Note you can't point to the exchange rate accounts - you have to supply the rate. So I used GetRate for balance and flow and included them in the DefaultTranslate function with variables.

ElseIf pov_scenario = "Forecast" or pov_scenario = "Budget" Then

   AvgRt = .GetRate("A#AvgRate")
   EOMRt = .GetRate("A#EOMRate")
   .DefaultTranslate EOMRt, AvgRt, "True", "False"