For a multi-currency instance, is there a way to plan on a modeled sheet and have all levels be the same currency?
The steps below detail using a dimension and lookup table to accomplish this:
- Create a currency dimension and dimension values that represent currencies in the org structure
- From Modeling > Define Dimensions select “New Dimension” and then add values representing each available currency in the instance, such as USD, CAD, MXN, etc.
- Add the currency dimension to the modeled sheet
- Tag each row with the corresponding level currency. You could also add the Level Currency Element to the sheet so your users can easily match the dimension value with the corresponding level currency.
- Create an Associated Monthly Lookup Table on the currency dimension column
- Apply formulas in the lookup table that pulls in the corresponding rate for each currency (using corporate to local rates)
- For example, if we had a corporate currency of USD, we would add the formula
ASSUM.ExchangeRate.USDCADAin the CAD value and
ASSUM.ExchangeRate.USDMXNAin the MXN value
- Create an new calculated account to multiply the original value by the currency lookup table. The formula might look something like:
- Use the result of the calculated account to pull the value converted from the corporate currency to the local currency
- You could also reference this calculated account in a display column if you need to view it on the sheet