How to manage the currency conversion

Center of Excellence

1. Abstract

Nowadays, most companies work on a global level and accordingly also have the need to be able to display and edit their KPIs in different currencies while keeping the ability of a consolidated view.

In this article, we will first show how to implement a currency conversion where different local currencies and one group currency have to be taken into account.

2. Context

The principle of a currency conversion is always the same, one value is multiplied by a factor to determine the other currency value.

This factor is the conversion or exchange rate. If we assume that a company has branches in different countries, the local sales, for example, are usually stored in local currency.

For an overall reporting, however, these local currency values must be converted into the group currency’s value.

Top-down planning, on the other hand, requires that the plan values recorded in group currency must be converted into the respective local currencies.

3. Content

In this example we will assume a globally acting company with 4 legal entities having different local currencies.

One of those legal entities (“Alpha”) is also the group entity, thus both locally and globally working with the same currency (“USD”).

3.1 Preliminary considerations

As a best practice recommendation, we will introduce an Entity “Currency” which will be related to the different legal entities of the company, defining the local currency.

Have a look at this example from a consolidation application:

Figure 1: Relationship between legal entity and local currency

Figure 2: Each Legal Entity is related to a Local Currency

Of course, one could also discuss alternative models in which the local currency has no direct relationship to the legal entity but is assigned via mappings; or a "free" entity "Currency" that has an attribute "Local/Group".

However, both would mean more administrative effort than we need here in our simple example.

In the end, a certain form of modelling is always the result of the requirements analysis and can therefore be evaluated differently from case to case and this also applies, of course, as far as best practices are concerned.

In all those cases, we’ll need a mapping to define one of the currencies to be the group currency. Thus, we’ll implement an additional currency entity holding the same members as the original one, and a cube dimensioned by this entity to flag the group currency. It is not mandatory to make this assignment dynamic but, based on the experience, it is more usable in reporting, as it wouldn't need fixed selections.

Figure 3: Settings to define the group currency

Figure 4: Group Currency is set for the Company

3.2 The conversion table

In order to translate the local currency values into group currency values and vice versa, we will need a conversion table.

Which time dimension to be used here depends on the requirements. In this example we assume that the conversion will take place at a yearly rate.

Figure 5: Dimensions of the conversion table

The data to populate this cube can be derived from freely available sources (e.g. The European Central Bank’s web service) or it can be populated manually.

Figure 6: Configuring the exchange rates regarding Group Currency USD for 2020

For future years this cube needs to be populated manually, of course.

3.3 Converting the currency values

Since in this example the local currency is directly related to the legal entities you will have different currency values for e.g. sales figures. Consider the following structure of the sales cube

Figure 7: Dimensions of Sales Cube

That will then lead to a representation like this

Figure 8: Sales figures per legal entity in local currency

These figures can’t be compared to each other, and a row total would not make sense, either, because they are stored with different currencies.

To apply the conversion to group currency, we will have to multiply those local values with the conversion table to achieve comparable figures.

The cube for storing the group currency values “ACT Net Sales in GC” has the same structure as the source cube containing the local currency values.

The conversion can then be done by a simple dataflow

Figure 9: Dataflow calculating the group currency values

This works although the rates are stored at a more aggregated level than the target requires - making use of Board’s dataflow rule “all sources are aligned to the target” - resulting in yearly rates being copied to the monthly level before the multiplication.

The according steps for applying this calculation would look like this

Figure 10: Procedure steps to calculate the currency conversion

Figure 11: Net Sales Actuals after conversion (right) to group currency

You will notice that Alpha’s values remain the same since Alpha’s local currency corresponds to the group currency.

Additionally, a row total truly makes sense here.

4. Conclusion

Introducing currency conversion to your application can be quite simple if you follow the best practices recommendations.

Of course, one should always keep in mind that requirements drive the solution and there might be future requirements which will need the solution to be adapted.

In this case that could mean more than one group currency, conversion rates at a more detailed level or with additional attributes, such as rates used for reporting or for planning purposes.

It might even be that transaction and local currency differ.

Still, the approach will always be carrying currency information for both source and target and implementing one or more conversion tables to be used for calculation.

Center of Excellence

Thanks @Helmut Heimann for the insights on this article!