Financial data reported by the group companies (subsidiaries, JVs and affiliates) is in the local currencies of these units. The Currency Conversion logic converts the local currencies of these companies into any set of reporting currencies and the group currencies of the scopes.
SAP Analytics Cloud provides a standard currency conversion logic which is based on the accounts using closing and average. However, the requirements for currency conversion can be much more detailed, whereas each Balance sheet account has a movement with a specific conversion rate, such as currency conversion rate for opening, dividend, movement, historical, currency translation adjustment, and closing. To meet such requirements, a separate Rate Model is required, and a comprehensive method for conducting currency conversion is mandatory.
Needless to say, the solution should be scalable and flexible, so thorough consideration to the model’s architecture, advanced formulas and APIs is needed. The currency conversion solution, that is part of the cloud consolidation solution, allows any end users to easily add new currencies and have different rates for the same currency, assign to specific Balance sheet movements and run the currency conversion without any additional line of coding.
The balance sheet accounts’ movements need to be converted from the local currency of the entities, into a set of currencies with varying rates based on the movement. Consider that the rates mentioned below are the conversion rates between EUR and USD, and that the Local Currency of the company is USD and the Reporting currency we want to convert the values to is EUR.
|Opening (Closing PYE)||1.1|
|Account||Flow||Rate||Local Currency||Reporting Currency|
|Transfers from related parties||Average||-20||-26|
|CTA = 100*(1.4-1.1) + 20*(1.4-1.2) -35*(1.4-1.3)||30.5|
The currency translation logic reads all values in local currency (currency = LC), applies the desired exchange rate according to the applicable rate model, and writes the results in the appropriate reporting currency (USD, EUR, and so on). Unlike the default rate conversion of SAP Analytic Cloud that runs on the accounts, this extension allows conversion of each movement within the account in as many rates/currency as required, along with the calculation of the currency translation adjustment.
In the above SAP Analytic Application, the user can input the conversion rates for each currency, whether these rates are specific to an entity or global (will be discussed later in this blog). Adding any new currency is easy, and doesn’t require any programing or technical knowledge. The same applies to adding a new Rate (Incoming, Dividend, Closing, Average). The solution is architected and programmed in a manner that requires ZERO CODE after new currencies, rates, accounts, flows and entities are added.
In the above report, the local currency of the company is EUR, and the reporting currency that we are using is USD. The amounts have been uploaded to the Consolidation Model for the entity in local currency of the company, designated as LC. Each movement of the account is converted from Local currency (EUR) to USD using the global conversion rates.
In the above screenshot, we will take one account, Loans and cash Advances, Non Current Allowance. The account has values on multiple flows/movements, each those flows/movements is assigned a specific rate (shown underneath the flow/movement in yellow). The right side report is linked to the Rate model, which will be used as the driver model for the currency conversion logic. The local currency of the entity is EUR and the Opening rates are last year end closing rates, which would be 1.17.
After triggering the Run currency conversion (Global Rates) Data Action Trigger, the system will convert the amounts from the local currency of the company (EUR) to the multiple reporting currencies that we have selected. For purpose of simplicity, we only showed EUR, LC and USD in the report.
- The first value in local currency for the mentioned account is the opening balance, with a total of 5,230.73 in Local Currency of the company (EUR). The conversion to EUR is the same, since the system understands that the local currency of the company is EUR, so multiplies by 1. but when converting to USD, the value is 6,119.95 (5,230.73 * 1.17)
- The incoming units was converted from 1,000 EUR to 1,115.00 USD (incoming rate is 1.155)
- Dividends were converted from 750.00 EUR to 872.25 USD (Dividend rate is 1.163)
- Average were converted from 5,6.6.23 EUR to 6,531.26 USD (Average rate is 1.165)
- Closing was converted from 12,586.96 EUR to 14,600.87 USD (Closing rate is 1.16)
- Ultimately CTA (Currency translation adjustment) was also generated for the value of -77.59
Historical rates can be used in one of two ways. Either copy mechanism, whereas the historical value is entered in reporting currency and carried forward, or a designated date is entered for the entity, which will be used to query the specific rates as of that date and apply to accounts designated with HIST.
Going beyond the discussed currency conversion, the solution allows for currency conversion based on entity specific rates. In the above example, the rates that were used were global rates, meaning, they apply to all companies. However, we might have instances where the conversion rate from EUR to USD is applicable to all companies except for one or more. The solution will allow us to assign a unique rate for the entity, thereby converting all entities on the global rates, and then for the specific entities with unique rates, those rates are used.
Each entity has a corresponding Rate entity. When a value is entered on the rate entity and the Entity Specific Currency Conversion is triggered, the rate specific entity is used if available, if not, the global rate table is used.