Foreign Currency Translation Using SAP BPC (Blog # 7.2)
Refer to our previous blog in this series to get the business context of the currency conversion Blog 7.1.
- The following Models are required to perform currency conversion
- Rate Model
- Financial / Consolidation Model
- Rate Model stores the currency rates used for the translation into the reporting / group currency.
- Linking the Financial / Consolidation Model to Rate Model
- Currency dimension: Two distinct currency type dimensions are used for implementing currency conversion in SAP BPC. The reporting currency (RPTCURRENCY) in Consolidation model and input currency (INPUTCURRENCY) in the Rate model
- CURRENCY attribute in Entity dimension: defines the local reporting currency of the input / submitted values for the entity member
- RATETYPE attribute in Account dimension: determines the rate and the logic to be used in translation of the given account (average, closing or historical)
- Business Rules for currency conversion are defined with the rates and logic to be applied to each Account Rate Type is set in the business rules table.
- The Script Logic to invoke the stored procedure and to pass to the program the appropriate parameters.
- Data Manager Package to execute the task set.
A Rate Model is a support/driver model for financial and consolidation models. It is used to store the exchange rates that support the currency translation in financial applications. The Time and Category/Version dimension of Rate model must be identical to the Time and Category/Version dimension used by the Financial / Consolidation models.
Financial / Consolidation model must be linked to the Rate Model in the General Settings of the model as given in the picture below:
Financial Model is used for Planning and Reporting purposes and it is a periodic model.
Consolidation Model is used to consolidate the financial results and it is a YTD model which consolidates results. Note: The master data (dimension) can be shared by models within an environment.
Rate Model must include Currency (INPUTCURRENCY) dimension detailing the exchange rates by each input currency.The Currency Conversion process makes use of the Rate Model, where the appropriate exchange rates will be searched for each relevant currency. But for Rate Model to fulfil certain requirements R_ACCOUNT (Account dimension for Rate Model) and R_ENTITY (Entity dimension for Rate Model) are specific and utilised only by Rate Model.
This dimension is used to store the different types of rates like Average Rate (AVG), Closing Rate (CLO), Historical Rate (HIST) etc.
R_ENTITY is used as the entity dimension for the Rate model. In this example, we will be using the default member, typically named Global.
This dimension is used to store each applicable local currencies. This dimension will have the exhaustive list of currencies used by the entities in the Group.
This dimension defines the groups in which we store the values of our Model. Like Plan, Actual and Forecast.
This dimension defines the units of time for our Model and how these units aggregate. CATEGORY and TIME can be shared by all models involved in Financial Planning / Consolidation.
This dimension is used to define the reporting currencies in which we need to perform currency translation. This dimension will have only those currencies which are needed for reporting and the local currency (LC). Currency Type attribute with “R” denotes Reporting Currency, “L” denotes Local Currency and “G” denotes Group Currency.
Difference between Reporting Currency Vs Input Currency
Input currency (INPUTCURRENCY) can be any currency used across the Group for the local reporting and planning purposes. Report Currency (RPTCURRENCY) are those currencies which we used to report, consolidate and plan for an organisation which is present in multiple geographies. For example: A company based in United States having operations in India and Australia will convert the local currency values to US Dollar.
In this example Input Currency is INR for India and AUD for Australia but Reporting Currency is USD,
Currency Type dimension in RPT Currency should be same as Input Currency
Required Dimension Attributes
Currency Translation can run on any type of reporting application. Before running it, there are some required dimension attributes that should be checked to ensure that the following conditions, which are mandatory for currency translation are met.
Account Dimension Attribute – RATETYPE
The System uses this attribute to determine value. This attribute must be a valid member of RATE Account Dimension, such as AVG conversion rate for Average, CLO for Closing period rate for Closing. This value is not optional.
Entity Dimension Attribute – CURRENCY
The attribute is used to denote the local currency for current entity for example for ENTITY US, the currency is USD for ENTITY AUSTRALIA, Currency is AUD. Value of this attribute should be valid member of INPUTCURRENCY Dimension.
Currency Dimension Attribute – CURRENCY_TYPE
The currency dimension must include the attribute REPORTING, whose values are Y or blank, and CURRENCY_TYPE, whose values can be G for group currency, R for reporting currency, T for transaction currency, or L for local currency.
Note – For Group Currency (this value is mainly used for Consolidation, so for other type of reporting application, it is can be omitted.)
Time Dimension Attribute – YEAR
This attribute contains the year information of id. The YEAR attribute also allows you to filter, sort, and report based on the year. For example, if ID is 2017.JUL, YEAR is 2017
Time Dimension Attribute – PERIOD
This attribute denotes which period current time belongs to. The PERIOD attribute allows you to filter, sort, and report based on the period.
For example, if ID is 2017.JUL, period is JUL.
Time Dimension Attribute – TIMEID
This is one of the most important properties that are required for the currency translation to work. The system uses this attribute to lookup rates from the rate application. This attribute is a numerical value for current time.
For example, 2017.JUL, TIMEID is 20170700.
Time Dimension Attribute – MONTHNUM
The system uses this attribute to determine the “Last Period” of prior fiscal year. This attribute basically, helps to determine the opening period rate in the rate formula. This attribute takes the numerical value of month.
For example, 2017.JUL, MONTHNUM is 7.
Input Currency Dimension Attribute – MD
This attribute is used to denote the relationship between current currency and the standard currency. It can take two values:
D: Divide. If attribute “MD” does not exist, “D” is default relationship.
M: Multiply. If the rate of a currency to the standard one is a small number, for example AUD to USD, we can store the amount in AUD equal to 1 USD and mark it as “M”. This attribute can be used to improve accuracy.
SAP has made available multiple options for Currency conversion now. On the million dollar question of which tool you should select for Currency conversion depends on various factors. Please contact us for your Currency conversion specific requirements, we will be able to guide you through the process and arrive at best choice suited to your organisational needs.