Skip to Content
Technical Articles
Author's profile photo Filippo Naggi

Standard Currency Conversion in SAP Analytics Cloud: Portfolio Value Simulation

This Blog Post is written by Filippo Naggi, I’m an SAP Solution Architect working with both BPC and SAC.

With this article, I would like to deep dive into the SAC Currency Conversion standard framework.

We will simulate how the Currency Rate can affect a Stock portfolio. The solution is entirely no code, all the simulations can be done with the SAC Currency out-of-the-box functionalities.

We will build the Conversion Table and the Model to store and calculate the financials.

Then with an input Story we will execute the no-code currency conversion simulation.

 

Conversion Table

To begin with, let’s build the Currency Conversion Table to be used.

From the Modeler, we can create a Currency Conversion table

and we can specify the rates:

 

Direct Rates: Source and Target
The rows in this table represent the direct rates: they are the conversions rates used  to multiply the Source Currency to get the Target Currency (eg. for line 1, the Conversion Rate from USD to BRL is 5.35, so 10 USD = 10 * 5.35 BRL)

Valid From:
This is the start validity date for the Conversion Rate. The Conversion Rate with the most recent Valid From date will be used.

Category:
Every data Version is classified into a Category.
This attribute will enable the Conversion Rate matching from Version to Category.
The available categories are Actual, Budget, Planning, Forecast, Rolling Forecast

Rate Version:
It is possible to override the Category Conversion Rate using the Category Specific selection.
Only in this case, we can specify the Rate Version attribute to derive the Currency Rate.

For example, we can define a Simulation Rate:

and we can use it for the Planning version:

Rate Type:
This attribute is used to match the RateType Property with the Account Type Dimension.
Available values are Average (to be used for INC and EXP Account) and Closing (to be used for AST and LEQ Account).

Exchange Rate:
This is the number to be multiplied by the Source Currency to derive the Target Currency.

Bonus Exceptions
SAC currency conversion manages these exceptions:

  • if the Category is not found, Currency Conversion will look for Category not assigned records
  • if a direct rate is not found, the Currency Conversion will look for the Reciprocal rate and perform the math to reciprocate

 

Model

The Model will manage the Account with an Account-Type dimension (RateType is the attribute to be matched with the Currency Conversion Table)

 

The Organization dimension will be used to associate a Currency to each Stock.
This could be useful in case we want to activate a Workflow and assign different managers to each Stock.
Note that we have created two dummies members, EUR and BRL, to show the Currency Rates in reports

 

Now we can activate the Currency Conversion framework on top of our model:

Default Currency
This is the Target Currency available in every Story. There will be no need to specify the destination currency in the Stories, this is will be available everywhere.

Dimension with the Currency Property Enabled
This specifies which dimension will be used to derive the source currency to be converted.
In our case we are going to use Organization, with this configuration:

Currency Rate Table
This is the table to be used to derive the Currency Rates.
Note that after saving the Model, this setup becomes not changeable.

These are the stock currencies setup up:

TESLA ->USD
FERRARI ->EUR
PETROBRAS ->BRL

 

other mandatory parameters are:

Preconverted Actual
Only for Actuals, this parameter enables the pre-converted load.
Currency Conversion will not be applied to Actual Values and the Converted number will come from the source.

Maximum number of Currency Conversions
For performance reasons, it could be useful to restrict the maximum number of Currency Conversions available in a Story. The default value is 4 and can be extended to 20.
Currently, after saving this setup, is not possible to downsize it.

 

 

Story
We are all set to build a Story and proceed with the simulation.

We will have two versions: Actual and Planning, and we would like to see both also with Actual Rates:

 

 

Let’s see the Story details:

Currency is the Source Currency, it is derived from the Currency property of each stock. For example, you can see that the Ferrari currency is EUR, while the TESLA currency is USD.

Default Currency(USD) is the Model Default Currency, every story will have this available.
I have used the member EUR and BRL to show the Currency Rates that we are using for the simulation.

For Actual (November), 1 EUR = 1.02 USD and 1BRL=0.19 USD
For Planning (December), 1EUR = 1.11 USD and 1BRL = 0.25 USD

 

In this story, we have two additional Cross Calculations:

The first one, @ActualRates, shows the Planning converted using Actuals Rates

 

The second one, RateEffect%, calculates the weight of the ActualRate on top of the Current Category Rate:

 

Finally, let’s do testing.

Currently, with 210 EUR invested in FERRARI and 26.06 BRL invested in PETROBRAS, the combined RateEffect% is 4.81%, while the EUR is 8.16% and the BRL is 25.23 %.

 

In our test case, we will increase the equity in FERRARI, expecting the Portfolio Rate Effect % will bend to the EUR one.

 

Let’s proceed and increase the FERRARI equity from 210 EUR to 20,000 EUR:

 

 

As you can see the Story is recalculating in real-time neither a line of code written, and the Rate Effect % is moving from 4.81% to 8.10%, bending to 8.16%.

This proves that if we overexpose our portfolio to EUR, the EUR rate Effect will drive it.

 

Thank you so much for reading this blog, please ask any questions in the comment if you need further detail on the SAC currency conversion framework, and feel free to share feedback.

If you liked this blog, follow me at https://people.sap.com/filipponaggi

 

Stay tuned for interesting SAP Analytics Cloud out-of-the-box deep dive blogs.

Filippo

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Maximilian Paul Gander
      Maximilian Paul Gander

      Hi https://people.sap.com/filipponaggi

      Thanks for your blog post! I suggest that you also consider the capabilities of the New Model in SAP Analytics Cloud including:

      • Conversion Measures
      • Currency conversion steps in Data Actions
      • Currency variables

      Some further info here:

      Get Started with the New Model Type

      Plan with Currency Conversion

      Convert Currencies in a Data Action

      Author's profile photo Filippo Naggi
      Filippo Naggi
      Blog Post Author

      Thank you for your comment Maximilian, I will look into these capabilities.

      Filippo

      Author's profile photo Nikhil Anand
      Nikhil Anand

      https://people.sap.com/filipponaggi Precise and Crisp. Thanks for writing this up.

       

      Regards

      Nikhil

      Author's profile photo Filippo Naggi
      Filippo Naggi
      Blog Post Author

      Thank you, Nikhil, appreciate your comment.