Foreign Currency Translation Using SAP BPC – YTD Model (Blog #7.4)
This document guides you to set up a currency translation for a YTD Model (Consolidation).
- 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 EUR and USD.
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.
- Create / copy an existing consolidation model
- Create / copy an existing rate model
- Ensure you have all the mandatory dimensions for currency conversion in the consolidation 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 Consolidation Model is linked to the Rates Model in the “General Settings” of the consolidation model.
- Check if all the mandatory Properties of the dimensions are used as given below.
- Maintain the exchange rates in the Rates Model as given in the example below.
- Maintain values in the Consolidation 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
Destination Account: BS213 – retained earnings cumulative
Apply to YTD: No need to check this as consolidation model is already YTD model
- Create “CYRE.LGF” script logic task to invoke the stored procedure and to pass to the program the appropriate parameters.
- Run the data manager package for account transformation.
- 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.
- Create rule to convert accounts with CLO rate type to use closing rates in the rates model.
- 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.
- 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 “account transformation”> 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.
- 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.