Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

A Way to Dynamically get a Levels Exchange Rate to Base

There are times when you may need to be able to dynamically get the exchange rate between the current level and the model’s base currency.  This example walks through setting up two custom accounts that make this all dynamic, versus using a switch statement or nested if statements to work out the level's exchange rate.

A use case for this is situations where you may have data in a level that is not in the level's currency, a common example of that is an employee paid in a different currency.

The first account we create is a simple account with a master formula set to 1.  We will be leveraging a built-in feature of Adaptive Insights that auto translates data between levels by referencing this account in formulas.
 

Create a custom account with the following settings:

Code:  Avg_Rate

Name:  Avg Rate

Display As:  Currency

Master Formula:  

Exchange Rate:  Avg: Monthly Average

Data Privacy:  Value of account is public at all levels

Note:  Create an account like the one above for any other exchange rates you need.
 

Create a second custom account with the following settings:

Code:  AvgRate_Corp_to_here

Name:  Avg Rate Corp to here

Display As:  Currency

Master Formula:  ACCT.Avg_Rate[level=Total Company(-)]

Decimal Places:  Set to as much of the exchange rate as you want to see

Exchange Rate:  Does not matter, should match the first account

Note:  The name of your top level may be different than this example, common examples are:

  • Top Level(-)

  • Corporate Plan(-)
     

To see the account working, create a new standard sheet with the Avg Rate Corp to here account on it.  Assign it to all levels and then navigate to the sheet. You will see the value of the account is the exchange rate of the level you are on to the model's base currency.

This account can now be used in formulas, or you can bypass the account all together by just using the master formula from the account directly in your formulas.
 

Known oddities:

  • This account will look weird at roll ups (eg:  time, levels…).

  • If you use sub version for Actuals, this will also display weird at the roll up Actual versions.


  • Was this article helpful?