Foreign Currency Translation Using SAP BPC – Periodic Model (Blog # 7.3)
In SAP BPC Currency Translation is used in two places
- 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 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.
- Setup the current year retained earnings calculations in the system – business rules, script and DM package
- Setup currency conversion with currency translation reserve (FCTR) for reporting currencies Euro and USD.
The following challenges should be understood clearly:
- 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.
- 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.
- 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
- Average rate and Closing rate and
- 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)
- Create / copy an existing planning model
- Create/copy an existing rate model
- Ensure you have all the mandatory dimensions for currency conversion in the planning model like account, category, time, entity, and reporting currency.
- Ensure you have all the mandatory dimensions for currency conversion in the rate model like R_Account, category, Time, R_Entity and input currency.
- Check if the planning model is linked to the rates model in the “general settings” of planning model.
- Check if all the mandatory attributes of the dimensions are configured as given below.
- Maintain the exchange rates in the Rates Model as given in the example below.
- Maintain values in the Planning Model as given in the example below.
- Create a business rule “CYRE” for account transformation to transfer the net income to retained earnings.
Source Account: PL600 – Net Income
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.
- Create “CYRE.LGF” script logic task to invoke the stored procedure and to pass to the program the appropriate parameters.
- 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.
- Run the data manager package for account transformation.
- 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.
- Now to get these values converted into in Euro and US Dollar go to Business Rule and add Currency Translation Business Rule.
- Create rule to convert accounts with AVG rate type to use average rates in the rates model.
- Create rule to convert accounts with CLO rate type to use closing rates in the rates model.
- Create “FXTRANS.LGF” script logic task to invoke the stored procedure and to pass to the program the appropriate parameters.
- 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.
- Run the data manager package for currency translation.
- 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.
- 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.
- 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).
- 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.
- 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.
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.