Skip to Content
Technical Articles
Author's profile photo Thomas Haeusel

Currency Simulation with SAP Analytics Cloud

In this article I would like to describe a typical business challenge in the context of integrated financial and cost planning and how it can be solved with SAP Analytics Cloud.

 

Problem definition:

In the area of integrated financial planning for an internationally operating company, it is necessary to be able to carry out planning in different currencies across several countries. A typical use case is the collection of financial data, such as sales, costs or other key figures from the locally operating country subsidiaries of a global company in local currency. The second step is the calculation of this input data with currency translation into the group currency. In addition to the necessary currency translation, it is necessary to have the possibility to simulate the corresponding currency rates in different scenarios flexibly over the time axis.

Technical Solution:

SAP Analytics Cloud already contains extensive standard functionalities for all currency translation tasks. This includes the use of currency tables with conversion rates for closing rates, average rates, etc. for all scenarios used. In particular, the use of the new planning model offers even more options for performing currency calculations at runtime.SAP%20Analytics%20Cloud%20Currency%20Table

SAP Analytics Cloud Currency Table

If there is a further need to simulate currency rates, it is possible to do this with an additional exchange rate model. This can be used to store currency rates over the timeline in all conversation rates and scenarios related to the used planning model. In this case there are no limits to the conversion of local currencies into group currencies because all planning core functionalities, like the “Planning Panel” can be used for the simulation of currency rates. In the following I would like to describe the used SAC planning model based on the SAC New Model technology.

For this use case two planning models are used. On model named C_CURRENCY to store all the currency rates and a second model named C_SALES which use the currency rates from the currency rates model to calculate key figure like Revenue from local currency for every country in which the revenue planning is done into the group currency for the whole organization.

The following is a description of the C_CURRENCY exchange rate planning model for storing all currency rates in different scenarios and versions.Planning%20Model%3A%20C_CURRENCY

Planning Model: C_CURRENCY

The used currency simulation model includes the dimensions: VERSION (Scenarios like Actual, Plan, Budget etc.), Currency (Swiss Franc, Euro, British Pound, Norwegian Krone, Swedish Krona, US Dollar etc.), DATE (Month, Quarter, Year) and CURRENCY_TYPE (Current, Average, End of period, Closing) and an additional MEASURE (Value) to store all values. This planning model will be used to store the currency rates for all currencies in all versions and over the timeline.Dimension%3A%20Currency_Type

Dimension: Currency_Type

The Dimension includes currency type elements like “Current”, “Average”, “End of period” and “Closing” that makes it possible to have currency rates for all kind of calculation periods.

The C_SALES planning model is used to record key figures such as company-wide sales in local currency, e.g., by sales or account managers throughout the company.Planning%20Model%3A%20C_SALES

Planning Model: C_SALES

The used sales planning model C_SALES includes the dimensions: VERSION (Scenarios like Actual, Plan, Budget etc.), ACCOUNT (Revenue), REGION with the organizational structure and the property “Currency” to store the regional local currency, the dimension CURRENCY_VALUETYPE (Local Currency, Group Currency) and an additional MEASURE (Value) to store all values.Dimension%3A%20REGION

Dimension: REGION

Dimension%3A%20CURRNCY_VALUETYPE

Dimension: CURRENCY_VALUETYPE

In the Currency Simulation Story two tables are included. One table with the view to the C_SALES planning model with actual and budget values for different organization countries and regional organization countries with revenue figures in local and group currencies.Story%20Currency%20Simulation

Story: Currency Simulation

And a second table with a view to the C_CURRENY planning model with all the currency rates for every currency with actual and budget values on a monthly based timeline. Now it is possible, after the collection of Revenue figures from all region, to simulate currency changes in the C_CURRENCY table. In this case the “Planning Panel” is used to change a series of currency rates for “British Pound”.SAP%20Analytics%20Cloud%20Planning%20Panel

SAP Analytics Cloud Planning Panel

In this case, a change of currency rates for British Pounds are done and are highlighted in the yellow cells.Change%20of%20currency%20rates%20for%20British%20Pound

Change of currency rates for British Pound

After the change of the currency rates, the simulation can be started with the execution of the planning trigger “Currency Simulation”.Planning%20Trigger%20execution

When executing the planning trigger, the calculation results are displayed in the C_SALES view with the sales figures for “Great Britain” in group currency and additionally the aggregated results on regional group level for “Northern Europe”. In this way, any type of currency simulation can be performed.Currency%20calculation%20after%20execution

Currency calculation after execution

The DataAction with the includes Advanaced Formular Step are shown in the Pictures below.DataAction%20with%20Advanaced%20Formular%20Step%20Visual

DataAction with Advanaced Formular Step Visual

DataAction%20with%20Advanaced%20Formular%20Step%20as%20Script

DataAction with Advanaced Formular Step as Script

######################################################################
MEMBERSET[d/Date]=”202201″ TO “202212”

FOREACH[d/Date]

IF[d/REGION].[p/currency]=”EUR” THEN
Data([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”GC”)=
RESULTLOOKUP([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”LC”)
ENDIF

IF[d/REGION].[p/currency]=”USD” THEN
Data([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”GC”)=
(RESULTLOOKUP([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”LC”)*
Link([C_CURRENCY], [d/CURRENCY_TYPE]=”C1000″, [d/CURRENCY]=”USD”, [d/Version]=”public.Budget”))
ENDIF

IF[d/REGION].[p/currency]=”GBP” THEN
Data([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”GC”)=
(RESULTLOOKUP([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”LC”)*
Link([C_CURRENCY], [d/CURRENCY_TYPE]=”C1000″, [d/CURRENCY]=”GBP”, [d/Version]=”public.Budget”))
ENDIF

IF[d/REGION].[p/currency]=”CHF” THEN
Data([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”GC”)=
(RESULTLOOKUP([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”LC”)*
Link([C_CURRENCY], [d/CURRENCY_TYPE]=”C1000″, [d/CURRENCY]=”CHF”, [d/Version]=”public.Budget”))
ENDIF

IF[d/REGION].[p/currency]=”NOK” THEN
Data([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”GC”)=
(RESULTLOOKUP([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”LC”)*
Link([C_CURRENCY], [d/CURRENCY_TYPE]=”C1000″, [d/CURRENCY]=”NOK”, [d/Version]=”public.Budget”))
ENDIF

IF[d/REGION].[p/currency]=”SEK” THEN
Data([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”GC”)=
(RESULTLOOKUP([d/ACCOUNT]=”M1110″, [d/CURRENCY_VALURTYPE]=”LC”)*
Link([C_CURRENCY], [d/CURRENCY_TYPE]=”C1000″, [d/CURRENCY]=”SEK”, [d/Version]=”public.Budget”))
ENDIF

ENDFOR
######################################################################

Outlook:

This currency simulation example should be used as a possible way to simulated currency rates. For example, in the Advanced Formular Script, a single script part is used for every currency which can be results in a huge script. This can be optimized in a real project depending on the business requirements.

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jef Baeyens
      Jef Baeyens

      Hi Thomas,

      Great write-up on this topic!

      Will SAP still invest in expanding the standard currency table features (to allow planning & simulation) because users like the detection of missing currency rates and the on-the-fly / real-time calculation with calculated fx measures.

      Author's profile photo iwam Nainggolan
      iwam Nainggolan

      ThankYou Mr

      Author's profile photo Hariesh G
      Hariesh G

      Hi Thomas,

      Can you tell about the rate type (average & closing) ? How it impacts the currency conversion table ? And what is the use of mentioning the rate type