- SAP Managed Tags:
In this article, I would like to describe the business challenge of IC Charging between Cost Centers with different currencies and how it can be handled within SAP Analytics Cloud.
This blog is referring to the already existing blog of @Thomas_Häusel, see Intercompany Elimination with SAP Analytics Cloud, which has a similar approach and adding currency conversion logic to it.
Problem definition:
The business case here consists in IT Cost Centers in different countries who can charge to each other during the budgeting process. Every Cost Center will enter planned charges to other Cost Centers in their own local currency. The receiving Cost Center of course needs this information in their own local currency as well as at the end translated to common group currency or other reporting currencies.
The diagram is showing examples of potential IC charges, could be just amongst Cost Center with the same currency, between different Cost Centers in different countries and therefore different currency and also IC charging to each other, meaning each of the Cost Centers could be sending Cost Center as well as receiving Cost Center.
Technical Solution:
This example was built on a SAP Analytics Cloud tenant not yet on HANA Cloud, therefore it was necessary to not only work with conversion measures but also create base measures with fixed currency to store the respective values. As soon as we will have SAP Analytics Cloud under HANA Cloud we will probably be able to directly use the dynamically calculated conversion measures.
An elimination per Region wasn`t needed here, so I skipped the usage of the ELIMMEMBER function and only applied the “mirror posting” approach from Cost Center sender to Cost Center Receiver. At the end all IC Charges in and out just need to be 0 in total.
So let`s have a look into the solution details.
The model structure contains the following dimensions: Cost Center, Partner Cost Center, Account, Version and Date and of course Currency conversion is enabled.
The ACCOUNT dimension includes just “IC_CHARGES” as final check for 0 across all Cost Centers and Partner Cost Centers. “IC_CHARGES_OUT” is tagged as “INC” indicating the charges from sender to receiver. Of course it could also be handled as EXPENSE, then cross-charging formulas are simply multiplied by “-1”.
The following screenshots are showing the Cost Center Structure with currency information and the related hierarchy setup.
To be able to cross-charge we need the same cost center as partner dimension. The property of currency is also needed here to be filtered on during the IC Charging data action.
Based on the “New Model” approach and the need of currency conversion we need to setup measures. The starting base measure is always the Value in local currency “VALUE_LC”.
As I couldn`t directly relate to dynamically converted measures I had to setup the same as base measures with fixed currency.
To use the standard currency translation here you see the setup of the respective conversion measures fixed by specific currency.
Rates for every combination of source and target currency. As we just have expenses, only average rates are needed.
The table below shows the charging inputs for account “IC Charges out” of a specific cost center to other partner cost centers. All values are also displayed in other currencies just for checking purpose. The table displays the conversion measures.
After data input the data action “IC Charging” will be launched to post the IC charges out to the account “IC Charges in” of the respective partner cost centers.
Changing to the second tab to see the view of the receiving cost centers, the starting point is of course with empty values
The screenhot below shows the result of the data action. Base measure “Value in local currency” is displayed as well as all the other conversion measures to check, if IC charging is working correctly.
Here in this case swiss Cost Center CH01 was charging 1.000 CHF to ES02 and UK02 and 2.000 CHF to MX02.
As you can see, the values have been transferred correctly to the local currency of each receiving cost center. As cost center CH01 was charging the amounts in CHF, column “Value in CHF” is showing the check, converted from local currency to the other ones. Display of measures here is base measure “Value_LC” and conversion measures.
The final table is just displaying the results if IC Charging with every combination of cost center and partner cost center. IC Charges as parent of IC charges out and IC charges in must be 0 at the top node, in this case in CHF as common group currency.
Let`s dive into the data action and see what`s behind it.
We need 3 steps, first we convert the IC Charges out into all currencies, second step is the IC charging itself and the last step is the conversion of IC charges in. With that we don`t to think about what kind of measure to choose, as both base measures with currencies as well as dynamically displayed conversion measures will have the same amounts.
Purely graphical setup of conversion step into each of the currencies. The third step is the same, just filtering on “IC Charges in”.
Despite the fact, that the advanced formula step can also be displayed in visual mode, the script mode is more convenient to understand the logic.
The advanced formula script is as follows
CONFIG.GENERATE_UNBOOKED_DATA = OFF
MEMBERSET [d/Account]="IC_CHARGES_OUT"
DELETE([d/Account]="IC_CHARGES_IN")
IF [d/CostCenter].[p/currency] = "CHF" AND [d/P_CostCenter].[p/Curr] = "CHF" OR [d/CostCenter].[p/currency] = "EUR" AND [d/P_CostCenter].[p/Curr] = "EUR" OR [d/CostCenter].[p/currency] = "GBP" AND [d/P_CostCenter].[p/Curr] = "GBP" OR [d/CostCenter].[p/currency] = "MXN" AND [d/P_CostCenter].[p/Curr] = "MXN" THEN
DATA([d/Account] = "IC_CHARGES_IN", [d/CostCenter] = [d/P_CostCenter].[p/ID], [d/P_CostCenter] = [d/CostCenter].[p/ID]) = RESULTLOOKUP()
ENDIF
IF [d/CostCenter].[p/currency] = "CHF" AND [d/P_CostCenter].[p/Curr] = "EUR" OR [d/CostCenter].[p/currency] = "GBP" AND [d/P_CostCenter].[p/Curr] = "EUR" OR [d/CostCenter].[p/currency] = "MXN" AND [d/P_CostCenter].[p/Curr] = "EUR" THEN
DATA([d/Account] = "IC_CHARGES_IN", [d/CostCenter] = [d/P_CostCenter].[p/ID], [d/P_CostCenter] = [d/CostCenter].[p/ID], [d/Measures] = "VALUE_LC") = RESULTLOOKUP([d/Measures] = "VALUE_EUR")
ENDIF
IF [d/CostCenter].[p/currency] = "CHF" AND [d/P_CostCenter].[p/Curr] = "GBP" OR [d/CostCenter].[p/currency] = "EUR" AND [d/P_CostCenter].[p/Curr] = "GBP" OR [d/CostCenter].[p/currency] = "MXN" AND [d/P_CostCenter].[p/Curr] = "GBP" THEN
DATA([d/Account] = "IC_CHARGES_IN", [d/CostCenter] = [d/P_CostCenter].[p/ID], [d/P_CostCenter] = [d/CostCenter].[p/ID], [d/Measures] = "VALUE_LC") = RESULTLOOKUP([d/Measures] = "VALUE_GBP")
ENDIF
IF [d/CostCenter].[p/currency] = "CHF" AND [d/P_CostCenter].[p/Curr] = "MXN" OR [d/CostCenter].[p/currency] = "EUR" AND [d/P_CostCenter].[p/Curr] = "MXN" OR [d/CostCenter].[p/currency] = "GBP" AND [d/P_CostCenter].[p/Curr] = "MXN" THEN
DATA([d/Account] = "IC_CHARGES_IN", [d/CostCenter] = [d/P_CostCenter].[p/ID], [d/P_CostCenter] = [d/CostCenter].[p/ID], [d/Measures] = "VALUE_LC") = RESULTLOOKUP([d/Measures] = "VALUE_MXN")
ENDIF
The MEMBERSET function relates to the account from where we take the data, in this case “IC Charges out”.
Next line is to delete any data for “IC Charges in”, which might be already stored from previous actions.
The first IF Statement is checking, if the cost center and partner cost center have the same currency.
It then can use all measures (Local Currency and stored measures in all currencies) in the same way and copy it to IC Charges in.
For example if a swiss Cost Center is charging to another swiss cost center, in our use case CH01 to CH02 all values can stay as they are.
The next section of IF Statement is checking, if the partner cost center is one with EUR as local currency (remember the importance to also put currency as a property to the partner cost center dimension).
In that case we will use the values in EUR to paste them to the local currency value.
Same concept for the next sections checking, if partner cost center currency is GBP or MXN and then paste the respective currency values to value in local currency.
In a "real world" application you would probably also add an audit dimension to track the changes.
This could be setup as “Total” with two children “Before IC charging” and “IC charging”.
Kudos to my colleague @PatrickFavre , who was supporting me for this use case.
Hope, you find it useful.
