Have you ever needed to change a default currency of an entity in HFM? Do alarm bells ring about database corruption and data loss? To clear any doubt around it: you can change a default currency of an entity in HFM, but you should proceed with caution. In this blog I’ll explain how you can do it.
For simplicity, I refer to the entity changing currencies as X. In this example, we’re changing currencies from SEK to EUR for the beginning of the year 2019. You can of course change the currencies of several entities at once. In that case, you simply process entities X, Y, Z etc. in their respective currencies as well.
Changing a default currency introduces three issues which I describe here. I’ll explain briefly how to solve them:
All historical data is stored on the old default currency in the Value dimension. To get around this, you need do some one-time operations which are best done by the admin in the candlelight, i.e. while keeping all users logged out from the system.
Prior to loading metadata with the new currency, you need to unpost all journals where Entity X is involved. This is because the journals are stored at the currency-related members <Entity Curr Adjs>, [Parent Adjs] and [Contribution Adjs]. You also need to move Entity X in Process Control into one of the end stations, i.e. Not Started or Published for all phases. Remember to do it in every scenario, year and period where it has been moved.
After this, you are good to load the metadata that changes the default currency. Once the metadata with the changed currency is loaded, you’ll have to treat the data in history. Ideally, you will use a temporary rules file where you add a section to copy all data from the old currency into <Entity Currency> for Entity X. With this rules file in the application, you need to calculate Entity X in all years, periods and scenarios where it has data. Then re-post the journals, load in the permanent rules file with changes described in steps 2 and 3, turn on the lights and let users into the application.
2. Opening balance in the year of the change
This only applies to you if you roll forward the closing balance into the opening of next year. The closing balance of the previous year is in the old currency while the opening balance of the new year should be in the new currency. You can solve this by adding a section to your rules which calculates a factor between the old and the new currencies and applies it to the closing balance while pulling it to the opening balance of the next year. There are ways to make this dynamic. Be careful with how you apply the factor to eventual overrides, CTA balances etc.
Now the data needs to be translated from different currencies depending on the year. Here you can exploit a rather underappreciated functionality of HFM: entity-specific exchange rates. When translating, HFM always first checks for the exchange rates at the entity being translated at <Entity Currency> and uses what it finds.
If nothing is found, it continues the search at the [None] entity where the exchange rates are commonly stored. In rules, you’ll need to add a section to copy exchange rates from entity [None] to entity X. In our example, pull SEK rate up to 2018 and EUR rate starting in 2019.
Limitations and considerations
If you are translating with the PVA method, the currency change works only at year-end. In the middle of the year, the translation loses track of the <Entity Currency> changing.
You should also consider what to use as a currency for the changed ones in the metadata. It’s confusing for history if you simply change the currency to the new one. My advice is to use an artificial currency code like XXX to mark entities with changed currencies. You can then use - for instance - the entity description to tell the user for which currencies the XXX for particular entities stands for and when the change took place. Using an artificial default currency also helps in building the rules.
After you have changed the default currency for an entity, all data load files or FDMEE rules pointing to the old currency code need to be updated. If your users use the variable member <Entity Currency> in the data loads, you are good to go without changing them.
If you need help with changing default currencies in HFM or any other consolidation-related issue with HFM, OneStream or CCH Tagetik, don’t hesitate to contact me or my inlumi colleagues near you.