Foreign Currency Translation Using SAP BPC – YTD Model (Blog #7.4)

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 which 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

  1. Financial ModelThis is for Planning and Reporting purpose. 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 ModelThis 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 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
  1. Setup currency conversion with currency translation reserve (FCTR) for reporting currencies EUR and USD.

The Challenge

The following challenges has to be understood clearly.

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

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

  • Average rate and Closing rate.

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.

 

 

 

 

 

 

 

5. Check if the Consolidation Model is linked to the Rates Model in the “General Settings” of consolidation model.

 

 

 

 

 

 

 

6. Check if all the mandatory Properties of the dimensions are used as given below.

7. Maintain the exchange rates in the Rates Model as given in the example below.

 

 

 

 

 

8. Maintain values in the Consolidation Model as given in the example below.

 

 

 

 

 

 

 

 

 

 

 

 

 

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

10. Create “CYRE.LGF” script logic task to invoke the stored procedure and to pass to the program the appropriate parameters.

 

 

 

 

 

 

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

12. Run the data manager package for account transformation

13. Now under EPM tab if we refresh the data, net income is posted to retained earnings account in 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.

 

 

 

16.  Create rule to convert accounts with CLO rate type to use closing rates in the rates model.

17. Create rule to calculate value for Foreign Currency Translation Account (FCTR) account using the formula [AVG]-[CLO] 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.

18.Create “FXTRANS.LGF” script logic task to invoke the stored procedure and to pass to the program the appropriate parameters.

 

 

 

 

 

 

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

20. Run the data manager package for currency translation.

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

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

Recent Posts