Generalised currency conversion

I'm building a model which holds revenue data. The revenue that the company receives is in a mix of US Dollars, UK Sterling and Euros, so one of my lists represents currency. There is a requirement as well to aggregate that revenue into a single "Converted Sterling" currency, which would be calculated as (Revenue in UKP) + (Revenue in USD * USD Exchange Rate) + (Revenue in EUR * EUR Exchange Rate).

I've also got a fair number of Line Items that I want to apply such a currency conversion to, some of which have their own formulae.

I really only want to write the currency conversion formula once; I don't want it included in each of the Line Items that it applies to.

How do I do that in Anaplan?

Thanks in advance.

Accepted answers

PaulRitner

Hi,

There are lots of ways to do this, I wouldn't suggest replicating every line item.

Also, whatever the solution, the design should be built so that new currencies can be added w/o the need to open the hood. In this sense, we would not build a line item that represents a single currency (say, USD)...

A simple example involving many currencies & many line items ollows:

Client has 30 operating units in 30 parts of the world. Each operating unit forecasts its P&L in its (single) local GL currency. Some of the currencies are the same, but, overall, there are about 15 currencies in use across the 30 operating units. The client's business is expanding into new countries, so the model needs to accomodate this. In this case, we might do the following:
1. Build the "P&L Local Currency Module" with the following dimensions: Entity, Months, Versions, and line items representing the Local Currency P&L (this can be 10 line items, but could be 50 or even 100 line items)
2. Create a line item subset based on the P&L Local Currency Module, and include all of the numeric P&L rows from the source module. Let's call this the "P&L LIS"
3. Create a P&L Reporting Currency Module with the following Lists: P&L LIS, Entity, Months, Versions.
  1. Add the following line items:
    1. "Rptg Amt" (number; formula something like: Collect() * FX Rates.Rate[Lookup: FXCurrency]
    2. Item Entity (format to entity list, only include the Entity list in Applies To, omit Time & Versions to contain model size)
    3. FXCurrency (Format as Currencies list; limit dimensions the same as Item Entity, above. Formula something like: Entity.FXCurrency[Lookup: Item Entity
4. The P&L Reporting Currency Module assumes the following:
  1. The Entity list contains a property named FXCurrency
    1. The FXCurrency Property is formatted to an FX Currencies List, and populated to represent the Local Currency applicable to each entity.
  2. Creation of an FX Rates module. this will include the FX Currencies List, Versions, and Months. This is where you load your FX rates.
5. The above solution will scale well as new entities are added (that have new currencies).

The above is typical for currency conversion. where things can get a little squirrelly is if Transaction Currency (beyond a single local currency per area) is needed. Not conceptually difficult, but needs to be built in a way that will ensure the model won't get too large in the long term.

The above is an example only: facts on the ground often drive a slightly different approach at each client.

Paul

All comments

PaulRitner

Hello,

There are two contexts in which currency conversion applies:

(Simplest) Data (for example, revenue) is loaded in the local entity currency. For example, the US entity's transactions are loaded 100% in USD, even though, technically, some of the US entity's transactions may be in other currencies, perhaps Canadian Dollars (****). In this case, data can be loaded to either line items or a dimension representing "Local Currency", and based on the Entity's Currency, converted into the Reporting currency (in this case,GBP/Sterling). Therefore, in this example, there are two elements: Local Currency & Reporting Currency.
(More complex) Data is loaded in the transaction currency. Building on the previous example, the US entity's data may be loaded as a mix of USD & **** data, which would be converted into USD first (in Anaplan), and then converted into GBP (Sterling). Therefore, in this scenario, there are three elements: Transaction Currency, Local Currency, and Reporting Currency. Companies who have entities that conduct business in multiple currencies often need to manage at the transactional currency level so that they can conduct currency-sensitivity analysis. In companies where the transaction currency is highly aligned with the Local currency, then this extra level of detail isn't needed (because the output is substantially the same).

So, it sounds like your challenge is the simpler one. Here are my thoughts:

Load data in "Local Currency"... at the level of dimensional detail required. This might include a mix of actuals and future forecast. Note that the aggregation of all Local Currency Data would probably not be useful, since its a mix of currencies. However, at the input level, people know what the currency is. In this context, a Currency dimension is not needed, which helps contain model size. If you require aggregations by currency, do that by adding a separate module at a higher/summary level.
Add a 2nd line item directly in the load module that converts the local currency data to Reporting currency. Since Reporting currency is all one currency, this can be aggregated to the top of the hierarchies for Total-Company views.
An implication here is that there is a table that contains your FX rates. I usually build this using an FX dimension & by month/version.
Some companies load this data at the Entity Level (for example, the dimensions might be Entity, Product, Months, and Versions)
Some companies load this data at the Country Level (for example, Country, Product, Months, Versions)... and then map each Country into an Entity. This allows the "Local" currency to be 1:1 with the Country. I've seen some companies us a single EURO Zone country for all EURO activity, becauase its the currency that important, and no necessarily the Country. Note that Actuals need to include country (or currency) to do this.

Let me know if this helps!

Paul

steve_*11057

Hi Paul

Thanks for the quick response.

What I'm keen to do is minimise the amount of duplicated code. Perhaps I'm misunderstanding what you're suggesting, but I should probably emphasise that I have quite a few Line Items to which this currency conversion needs to apply. I don't want to have to include currency conversion calculation in the formula for each of them, because (a) it makes the formula for each Line Item more verbose and (b) the currency conversion would then be replicated in many different places, making it difficult to change if the organisation changes the way it converts currency or adds new currencies in the future.

Does this mean I need a module where the currencies are represented by Line Items, and I need a further dimension that's made up of (something that looks like) the Line Items where I want to perform currency conversion? And if so, how do I set up the data feeds into that module so that the dimensions match up with the source module?

Thanks in advance

Steve

PaulRitner

Hi,

There are lots of ways to do this, I wouldn't suggest replicating every line item.

A simple example involving many currencies & many line items ollows:

Client has 30 operating units in 30 parts of the world. Each operating unit forecasts its P&L in its (single) local GL currency. Some of the currencies are the same, but, overall, there are about 15 currencies in use across the 30 operating units. The client's business is expanding into new countries, so the model needs to accomodate this. In this case, we might do the following:
1. Build the "P&L Local Currency Module" with the following dimensions: Entity, Months, Versions, and line items representing the Local Currency P&L (this can be 10 line items, but could be 50 or even 100 line items)
2. Create a line item subset based on the P&L Local Currency Module, and include all of the numeric P&L rows from the source module. Let's call this the "P&L LIS"
3. Create a P&L Reporting Currency Module with the following Lists: P&L LIS, Entity, Months, Versions.
  1. Add the following line items:
    1. "Rptg Amt" (number; formula something like: Collect() * FX Rates.Rate[Lookup: FXCurrency]
    2. Item Entity (format to entity list, only include the Entity list in Applies To, omit Time & Versions to contain model size)
    3. FXCurrency (Format as Currencies list; limit dimensions the same as Item Entity, above. Formula something like: Entity.FXCurrency[Lookup: Item Entity
4. The P&L Reporting Currency Module assumes the following:
  1. The Entity list contains a property named FXCurrency
    1. The FXCurrency Property is formatted to an FX Currencies List, and populated to represent the Local Currency applicable to each entity.
  2. Creation of an FX Rates module. this will include the FX Currencies List, Versions, and Months. This is where you load your FX rates.
5. The above solution will scale well as new entities are added (that have new currencies).

The above is an example only: facts on the ground often drive a slightly different approach at each client.

Paul

steve_*11057

Hi Paul

Thanks again for the response.

OK, I see what you're doing here; the complication that I have is that each entity does not have a single local currency. Each entity can write business in any or all of the currencies in the model. So entity and currency have to be two different dimensions, and I think I need to add a Currency list to your step 3 and then I should be on my way, I hope. I'll try it and see how squirelly it gets 🙂

Thanks

Steve

PaulRitner

Hi Steve,

If the data you are working with was at the transaction level then currency can be an attribute of the (data). This typically isn't the case; even at customer level, data can often be in multiple currencies.

I've worked with revenue data (in particular) that is broken out by country/region in such a way that currency can still be an attribute (having revenue as an attribute really helps).

Currency can be a dimension. For some clients, this works fine, but the challenge is that the model tends to grow substantially when a new currency is added. (model size may or may not be a concern for you.) Another way to work with this is by using a Composite Hierarchy, where currency is at or near the bottom. For user experience, I prefer composite hierarchies. From a maintenance perspective, having currency as a separate dimension is easier.

Good luck!

Paul

Quick Links

Can you help?

Unanswered questions

Sorting General List
Hi, I wonder if there's a way I can sort my general list alphabetically. The list is used as a drop down menu in a line item module and I want the drop down menu to be easy to navigate. I've already tried the Order List action. I already have a module with a text-formatted line item based on that list. But when I try to…
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…