Background

Refer to our previous blog in this series to get the business context of the currency conversion Blog 7.1, Blog 7.2, and Blog 7.3.

Currency translation is the process of converting one currency value in the denomination of another currency based on the exchange rates defined. Companies that operate in different countries have the need to translate their financial reports from different local currencies to one common reporting currency. In SAP BPC currency translation is used in two places.

Among the other things, Fibonacci introduced to the Western world a sequence of numbers discovered by 6th-century Indian mathematicians. In that sequence each number is the sum of the previous two numbers – and it would later be named the Fibonacci Sequence. Using the numbers geometrically will create a logarithmic spiral. In case we didn’t lose you yet, we can actually show you that spiral – because it’s prominently placed right on the hood of the A9.

Maybe just really lonely. Maybe I just want to be alone. Maybe loneliness is the only way to make that happen. Like having two pairs of eyes and just seeing the same thing. Seems like such a late hour. Nothing seems, or feels new anymore. I’d live again, to feel that way once more. Among the other things Fibonacci introduced to the Western world was a sequence of numbers discovered by 6th century Indian mathematicians.

Financial Model – This is for Planning and Reporting purposes. This model is generally a periodic model. In a periodic model, the P&L accounts (Account types – EXP/INC) are stored Periodic and Balance Sheet Accounts (Account types – LEQ/AST) are stored as YTD.

Consolidation Model –This is to consolidate the financial results from different local currencies to one common Group Currency. This model is generally a YTD model. In a YTD model, the P&L accounts (Account types – EXP/INC) and Balance Sheet Accounts (Account types – LEQ/AST) are stored as YTD.

This document guides you to set up a currency translation for a YTD Model (Consolidation).

The Business Case

XYZ company’s P&L and Balance Sheet is given below reported in Australian Dollars. This reported number needs to be converted to reporting currencies USD & EUR at the given exchange rate. The model in use is YTD Model.

  1. Setup the current year retained earnings calculations in the system – Business Rules, script, and DM package.
  2. Setup currency conversion with currency translation reserve (FCTR) for reporting currencies EUR and USD.

The Challenge

The following challenges has to be understood clearly.

This imbalance is called Foreign Currency Translation Reserve (FCTR). We will be creating a new Account in Account Dimension called Foreign Currency Translation Reserve (FCTR) and create formula under Currency Conversion Business Rule to automatically calculate FCTR.

Step by Step Process for Consolidation Model (YTD Model)

  1. Create / copy an existing consolidation model
  1. Create / copy an existing rate model
  1. Ensure you have all the mandatory dimensions for currency conversion in the consolidation model like account, category, time, entity and reporting currency.
  1. Ensure you have all the mandatory dimensions for currency conversion in the rate model like R_Account, category, time, R_Entity and input currency.
  1. Check if the Consolidation Model is linked to the Rates Model in the “General Settings” of the consolidation model.
  1. Check if all the mandatory Properties of the dimensions are used as given below.
  1. Maintain the exchange rates in the Rates Model as given in the example below.
  1. Maintain values in the Consolidation Model as given in the example below.
  2. Create a business rule “CYRE” for account transformation to transfer the net income to retained earnings.

Source Account: PL600 – net income

Destination Account: BS213 – retained earnings cumulative

Apply to YTD: No need to check this as consolidation model is already YTD model

  1. Create “CYRE.LGF” script logic task to invoke the stored procedure and to pass to the program the appropriate parameters.
  1. Run the data manager package for account transformation.
  2. Now under the EPM tab if we refresh the data, net income is posted to retained earnings account in the balance sheet and we have a tied-out Balance Sheet.

14. Now to get these values converted into in Euro and US dollar go to business rule and add currency translation business rule.

15. Create rule to convert accounts with AVG rate type to use average rates in the rates model.

  1. Create rule to convert accounts with CLO rate type to use closing rates in the rates model.
  2. Create rule to calculate value for Foreign Currency Translation Account (FCTR) account using the formula which means the difference in exchange rate value between average and closing Rate arising out of retained earnings account will be posted to this account to tie out the balance sheet.
  1. Create “FXTRANS.LGF” script logic task to invoke the stored procedure and to pass to the program the appropriate parameters.
  1. In the data manager > organise package list >financial process> select “account transformation”> right click “modify package” >modify script> select “advanced” and edit the script to include the “FXTRANS.LGF” in the script.
  2. Run the data manager package for currency translation.
  3. Now under the EPM tab if we refresh the data we will get the currency translated for US dollar and Euro as given in the rates model for closing rates and average rates.
  1. FCTR is calculated automatically as a difference between average rate and closing rate and posted in the FCTR account to have a tied-out balance sheet.

Conclusion

SAP has made available multiple options for Currency conversion now. 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 the best choice suited to your organizational needs.

0

Introduction

Comments section is the important attribute for Planning and Variance analysis reporting. It helps the users to provide explanation for the variance and helps the users to explain why the value is being entered, it communicates more than the mere number and adds value to the planning and reporting process. This article helps you to understand some basic concepts around how to enable the comments field in BPC embedded and the steps involved in getting it done.

Comments BPC Classic Vs Embedded

Enabling comments in BPC Classic is a very easy exercise which can be done through EPM Functions save/retrieve comment and by enabling the comments in sheet options to view them. In embedded it requires a creation of a separate ADSO (Advanced Data Store Object – Direct Input). Then this must be connected to the Multiprovider along with other cubes for Actual and Planning and aggregation on top of it. The performance of this comments is better than EPM, as the ADSO is part of the OLAP engine whereas in EPM, the comments can be enabled only by having EPM comment function in each cell where the comment is required.

Steps involved for enabling comments in BPC embedded

Step 1: Create an Infoobject

An Info object ZCOMMENT2 is created as CHAR data type and of length 250 (maximum allowed)

Step 2: Create an ADSO

Create an ADSO (ZCOMM in the example below) use “Planning in Direct Update” template and apply it. This will make the ADSO as the direct update ADSO.

Add infoobjects for G/L Account, Cost Center and ZCOMMENT2 to the ADSO created and click on the ZCOMMENT infoobject and enable the check box “Use Characteristic as Key Figure”.

Additionally note that you should make all the non-key figure info objects participating in direct-update-ADSO to be Key. This can be done by using the “manage keys” option in the screen above.

Step 3: Add the Comment ADSO to the Composite Provider

Add the comment ADSO to the Composite Provider along with the other cubes/ADSOs.

Map the source info objects to the target info objects under the scenario tab and activate the Composite Provider.

Step 4: Create an Aggregation Level on top of the CompositeProvider

Create an aggregation level on top of the Composite Provider “ZACTPL_CP”

Add InfoProvider fields to the “Output” tab of the Aggregation Level.

Step 5: Create a Query on top of Aggregation Level

Check the check box “Start Query in Input Mode” under General tab.

Map the infocube to the infoobject under selection details under sheet definition tab for “General” Properties.

Select Input-Ready under Planning properties under sheet definition tab for Plan, Amount and Comment Infoobjects.

Select Master Data for G/L Account under Extended category under sheet definition tab. 

Step 6: Analysis for Office

Now you should be able to open the query in analysis for Office and save comments from it.

Conclusion

BPC Embedded provides similar functionality as the BPC classic when it comes to this aspect of SAP BPC (Business planning and consolidation). Please contact us for your implementation requirements, support or consulting requirements.

0

Blog

Background

Refer to our previous blog in this series to get the business context of the currency translation Blog 7.1 and Blog 7.2

Currency translation is the process of converting one currency value in the denomination of another currency based on the exchange rates defined. Companies which operate in different countries need to translate their financial reports from different local currencies to one common reporting/group currency.

In SAP BPC Currency Translation is used in two places

  1. Financial Model – This is for Planning and Reporting purposes. This model is generally a periodic model.  In a periodic model,  the P&L accounts (Account types – EXP/INC) are stored Periodic and Balance Sheet Accounts (Account types – LEQ/AST) are stored as YTD.
  2. Consolidation Model – This is to consolidate the financial results from different local currencies to one common Group Currency.

This document guides you to set up a currency translation for a Periodic Model.

The Business Case

XYZ company’s P&L and Balance Sheet given below is reported in Australian dollars. This reported number needs to be converted to reporting currencies USD & EUR at the given exchange rate. The model in use is a Periodic Model. The following high level activities must be performed for achieve this.

  1. Setup the current year retained earnings calculations in the system – business rules, script and DM package
  2. Setup currency conversion with currency translation reserve (FCTR) for reporting currencies Euro and USD.

The Challenge

The following challenges should be understood clearly:

  1. In a periodic model,  the P&L accounts (Account types – EXP/INC) are stored Periodic and Balance Sheet Accounts (Account types – LEQ/AST) are stored as YTD.
  2. It should also be noted that the P&L is converted in average exchange(AVG) rate and the balance sheet is converted in closing (CLO) exchange rate.
  3. Retained earnings is a calculation based on P&L (periodic) which is transferred to the balance sheet on a YTD basis.

These factors will cause an imbalance in the Balance Sheet to an extent of

  1. Average rate and Closing rate and
  2. Retained earnings at a blended rate and Closing rate

This imbalance is called Foreign Currency Translation Reserve. To accommodate this, we will be creating a new Account in Account Dimension called Foreign Currency Translation Reserve (FCTR) and create two new accounts for Retained Earnings to calculate at Average Rate (BS214) and Closing rates (BS215) outside Balance Sheet.

The source data for this calculation looks as below.

Step by Step Process for Planning Model (Periodic Model)

  1. Create / copy an existing planning model
  1. Create/copy an existing rate model
  1. Ensure you have all the mandatory dimensions for currency conversion in the planning model like account, category, time, entity, and reporting currency.
  1. Ensure you have all the mandatory dimensions for currency conversion in the rate model like R_Account, category, Time, R_Entity and input currency.
  1. Check if the planning model is linked to the rates model in the “general settings” of planning model.
  1. Check if all the mandatory attributes of the dimensions are configured as given below.
  1. Maintain the exchange rates in the Rates Model as given in the example below.
  1. Maintain values in the Planning Model as given in the example below.
  1. Create a business rule “CYRE” for account transformation to transfer the net income to retained earnings.

    Source Account: PL600 – Net Income

    Destination Account:
    BS213 – Retained Earnings Cumulative
    BS214 – Retained Earnings Closing
    BS215 – Retained Earnings Average

    Apply to YTD: Checking this option will enable to calculate YTD of the Retained Earning. This is essential as our P&L accounts are Periodic and Balance Sheet accounts are YTD in nature.
  1. Create “CYRE.LGF” script logic task to invoke the stored procedure and to pass to the program the appropriate parameters.
  1. In the Data Manager > Organise Package List >Financial Process> Select “Account Transformation”> Right Click “Modify Package” >Modify Script> Select “Advanced” and edit the script to include the “CYRE.LGF” in the script.
  2. Run the data manager package for account transformation.
  3. Now under EPM tab if we refresh the data, net income is posted to retained earnings account in the balance sheet and we have a tied-out Balance Sheet.
  1. Now to get these values converted into in Euro and US Dollar go to Business Rule and add Currency Translation Business Rule.
  2. Create rule to convert accounts with AVG rate type to use average rates in the rates model.
  1. Create rule to convert accounts with CLO rate type to use closing rates in the rates model.
  1. Create “FXTRANS.LGF” script logic task to invoke the stored procedure and to pass to the program the appropriate parameters.
  1. In the data manager > organise package list >financial process> select “FX Restatement”> right click “modify package” >modify script> select “advanced” and edit the script to include the “FXTRANS.LGF” in the script.
  2. Run the data manager package for currency translation.
  3. Now under the EPM tab if we refresh the data we will get the currency translated for US dollar and Euro as given in the rates model for closing rates and average rates. But the retained earnings doesn’t match with the P&L retained earnings as the rate applied in balance sheet is on YTD values on each month, based on each month average rate, whereas we need the cumulative values of translated values.
  1. We will create one more account transformation for CYRE1 to transform the translated cumulative value to retained earnings in balance sheet and we will run this only for BS213 and repeat the steps from 9 to 12.
  2. Now under EPM tab if we refresh the data, net income of translated values is posted to retained earnings account in balance sheet and retained earnings of balance sheet matching to YTD retained earnings of P&L. Now we have the difference in balance sheet due to difference in BS213 (average rate) and BS214 (closing rate).
  1. Create one more business rule for account transformation for FCTR using BS213 and BS214 by checking reverse sign check box, so that is will calculate the difference between BS213 and BS214 and post the value to FCTR account to clear the balance sheet difference.
  1. Once you repeat the steps from 9 to 12 for “FCRT.LGF” and refresh the data. Now you will get FCTR values posted to FCTR account and a tied-out balance sheet.

Conclusion

SAP has made available multiple options for Currency conversion now. As you can see that we have used a work around of implementing currency conversion in periodic model. On the million dollar question of which option you should select for Currency conversion depends on various factors like the model type, conversion requirements, need for historical rates etc.

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.

0

Blog

Business Case

Refer to our previous blog in this series to get the business context of the currency conversion Blog 7.1.

Introduction

  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.
  2. Linking the Financial / Consolidation Model to Rate Model
  3. 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
  4. CURRENCY attribute in Entity dimension: defines the local reporting currency of the input / submitted values for the entity member
  5. RATETYPE attribute in Account dimension: determines the rate and the logic to be used in translation of the given account (average, closing or historical)
  6. 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.
  7. The Script Logic to invoke the stored procedure and to pass to the program the appropriate parameters.
  8. Data Manager Package to execute the task set.

Required Models

Rate Model

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:
currency Translation

Financial Model

Financial Model is used for Planning and Reporting purposes and it is a periodic model.

Consolidation 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.

Required Dimensions

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.

currency Translation

R_ACCOUNT

This dimension is used to store the different types of rates like Average Rate (AVG), Closing Rate (CLO), Historical Rate (HIST) etc.
currency Translation

 ENTITY

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.

currency Translation

INPUT CURRENCY

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.

currency Translation

CATEGORY/VERSION

This dimension defines the groups in which we store the values of our Model. Like Plan, Actual and Forecast.

currency Translation

 TIME

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.

RPTCURRENCY

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
currency Translation

 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.

currency Translation

 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 Translation

 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.)
currency Translation

 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.

currency Translation

Conclusion

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.

0