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:
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
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.
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 filipponaggi
Stay tuned for interesting SAP Analytics Cloud out-of-the-box deep dive blogs.
Filippo
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
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.
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 filipponaggi
Stay tuned for interesting SAP Analytics Cloud out-of-the-box deep dive blogs.
Filippo
- SAP Managed Tags:
5 Comments
