In this brief blog, I will describe the different methods of conducting detailed currency conversion using Cloud Consolidation Extension for SAP Analytics Cloud. This will be a preliminary step to the “integration” logic of the Consolidation process, which will be discussed in subsequent blogs.
When the Financial data that is reported by the group companies (subsidiaries, JVs and affiliates), it is usually in the local currencies of these units. Prior to conducting any calculations, matching, eliminations or any logic, these values in the local currencies of the entities should be converted to a reporting or group currency. The Currency Model and the Currency Conversion logic will automatically convert the financial data of all the companies from their local currencies into any set of reporting currencies and/or the group currencies of the scopes. (note: much like SAP BPC, we designate which AUDIT_TRAIL members are subject to currency conversion and which are subject to consolidation. Audit Trail members with “IS_CONVERTED = N” will not be included in the currency conversion process)
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 set of movements/flows 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.
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 these rates to specific Flow members and run the currency conversion without any additional line of coding.
Basic example:
The balance sheet accounts’ movements/flows need to be converted from the local currency of the entities to a set of currencies with varying rates based on each movement/flow. 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 |
| Incoming | 1.2 |
| Average | 1.3 |
| Closing | 1.4 |
| Account | Flow | Rate | Local Currency | Reporting Currency |
|---|---|---|---|---|
| Inventory | Opening | Opening | 100 | 110 |
| Additions | Incoming | 20 | 24 | |
| Disposal | Average | -15 | -19.5 | |
| Transfers from related parties | Average | -20 | -26 | |
| Closing | Closing | 85 | 119 | |
| 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, SAR, AED, QAR, 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/currencies as required, along with the calculation of the currency translation adjustment (CTA), for each account.
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.
In the above screenshot, the Statement of Financial Position for Entity E0 (local currency is EUR) was converted into USD using the Global Currency Rates. Each Flow (in the columns) was converted according to the specific movement rates shown on the top right corner. The values on F01 and F20, which both have a specific rate, were converted at the Incoming rate of 1.155 for the EUR. F00 (opening flow) was converted at LYE closing rate. F06 (which is a flow for dividends) was converted at 1.163, F99 (closing) was converted at the closing rate of 1.16 and the remaining flows with AVG were converted at 1.165.
The F80, which is the currency translation adjustment (CTA) is automatically calculated, as mentioned in prior part of this blog.
Going beyond the discussed currency conversion, the solution allows for currency conversion based on entity specific rates. In the prior 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.
Each entity has a corresponding “Rate entity” in the Rates model. If currency rates are entered on the “rate entities” and the Entity Specific Currency Conversion is triggered, the trial balances of these entities will be converted based on their entity specific rates. For any entity without any specific “Rate Entity”, the global rates will be used.
In the above screenshot, since the Entity Specific Rates were triggered, and since E0 has its Rate Entity (R_E0) populated with values, the system will convert its financial statements based on its specific rates. Each Flow (in the columns) was converted according to the entity specific movement rates shown on the bottom right corner. The values on F01 and F20, which both have a specific rate, were converted at the Incoming rate of 1.167850 for the EUR. F00 (opening flow) was converted at LYE closing rate. F06 (which is a flow for dividends) was converted at 1.173250, F99 (closing) was converted at the closing rate of 1.17 and the remaining flows with AVG were converted at 1.172.
In the next blog, we will discuss the automated eliminations and adjustments done by InsightCubes Cloud Consolidation extension for SAP Analytics Cloud
