Up until the previous blog, we had progressed as far as setting up a planning model in SAP Analytics Cloud for the material management department of a retail company. The company is now faced with the feat of handling multiple currencies due to its global presence. This blog will provide an overview of the setup of currency conversion.
Based on the location at which the product is procured, the price is specified in the local currency specific to the region. There comes the question of “What if I want to view or enter values in a different currency in the model?”. To counter this, we set up currency conversion in the model as follows ;
- The first step is to ensure that there is a date dimension and a dimension with the currency property enabled in the model.
- We now enabled the currency property in the dimension settings of our currency dimension;
- The data model can contain any text string for the currency dimension, but all the members must be mapped to a valid Currency as follows;
- Although we are directly using the currency dimension directly, we could maintain currencies as part of an organization dimension. In our case, if there is a need, we could have a separate region dimension which has a unique currency to itself as the organization dimension and maintain currency as its attribute.
- All the measures in the model that require a currency are then assigned with the currency dimension.
- The next requirement is to set up and maintain a currency conversion table with the conversion rates in the SAC system. This could be created from scratch in the SAC system or can be imported from the BPC system.
- The exchange rate entries in the currency conversion table will have a validity date range specified for them. In our case since the price of the product is dependent on the order date, we select the order date dimension for the purpose of currency conversion.
- Post creation of currency conversion table, the model is created enabled with ‘currency conversion’ option, available in the model preferences tab.
- Now, in the model preferences, we select the currency conversion table, the currency dimension to be used in the model and the date dimension to be used for currency conversion.
- A limit can be set on a maximum number of currency conversions to be done simultaneously, the default for which is 4. As of 2019.17, the max limit to this option is 20.
- Multiple currencies can be concurrently displayed by using cross calculations in the table.
Follow the below-mentioned steps to create a currency rates table –
- Select Create -> Currency. We can either choose ‘Create new currency conversion table’ or ‘Import from BPC’. For our purpose, we create a new currency table.
- We maintain a primitive conversion table with Source currency, Target currency, Exchange rate and Validity dates. Since exchange rates are prone to changes over time, the change in rates is maintained through validity date.
- Since budget is decided ahead of time, there might be a need to apply a different conversion rate to budget than the actuals, this can also be maintained as part of the Category column in the Currency conversion table. When Category is set to specific, a rate version can be created. This rate version can be directly chosen when creating a cross calculation for specific scenarios. Choosing this will ignore other rates specified for the same Source, Target Currency pair. Rate type can be optionally specified to indicate if the rate entries are ‘average’ or ‘closing’.
- Our table has conversion rates between CAD and USD and vice versa as follows –
Now that the currency conversion has been successfully set up, we consume it in a story to see it in action. Since the default currency is USD, the price data is converted to USD and displayed in the table.
- If the data is to be displayed in the local currency or any other currency in the conversion table, the ‘Cross Calculation’ dimension is added to the table as follows –
- The data can be displayed in local currency by changing the filter on the cross calculation to include ‘Currency’ as follows
- To show other currencies that are present in the currency conversion table, create a new currency conversion cross calculation as follows –
If any special rates need to be used, we set the category as required instead of the dynamic option.
- Now, the new currency conversion calculation gets added to the existing cross calculations.
- The same measure price is now available in three different currencies – Default currency in USD, local (in both USD and CAD, as posted), Currency calculation in ‘CAD’. The limit to the number of such currencies that can be displayed at a time has already been set to 4 in the model itself and hence more than four currencies cannot be filtered in the table.
Now, the user had the capacity to edit in any of the given currencies and all the other currency columns get updated.
In the blogs that follow we will dwell deeper into the nuances of implementing planning for the folks down at procurement.