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.
- SAP Managed Tags:
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 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 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: 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 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: REGION
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: 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 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 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”.
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 calculation after execution
The DataAction with the includes Advanaced Formular Step are shown in the Pictures below.
DataAction with Advanaced Formular Step Visual
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.
4 Comments
| User | Count |
|---|---|
| 12 | |
| 12 | |
| 11 | |
| 10 | |
| 10 | |
| 9 | |
| 8 | |
| 7 | |
| 7 | |
| 7 |
