The purpose of this article is to describe how to add a Local/Group Currency system to a new TM1 cube; this can be core requirement for International Companies and is relatively common.
To explain the Group/Local split,
- Group – the company’s home country currency – used primarily for Reporting
- Local – the branch/profit-centre’s currency – how data is entered.
The article below describes how to add this system to a new cube (where we have the ability to add a currency dimension) as opposed to how it would be built into an existing cube (which would be quite different in large implementations).
As with anything in TM1 there are many ways to do build a solution to a specific requirement/problem. Adding a complete multicurrency system would also be built a different way to the solution to this problem (but could be built by expanding on this system).
For this example we are going to assume that your general ledger / financials (GL) cube has a values dimension and that it conforms to best practices policies (an assumption that may be unrealistic).
Assuming the prior GL cube sits with the following dimensions:
- Base_Year
- Base_Month
- Base_Scenario
- Plan_Profitcentre
- Plan_Account
- Values_GL
Step 1 – Financial Cube Setup
The initial change would be to add a Plan_Currency Dimension which contains a ‘Local’ and a ‘Group’ element; this could be expanded into a full multi-currency reporting system in the future by adding currency codes (and a few rules).
So the GL cube would look as follows
- Base_Year
- Base_Month
- Base_Scenario
- Plan_Profitcentre
- Plan_Account
- Plan_Currency
- Values_GL
Now all planning / data entry should happen against the ‘Local’ Currency in the currency of that particular profit centre doing the input.
Step 2 – Currency Cube Setup
Next we need to add the currency cube; this should hold all currencies that profit centre’s trade in and their conversion rate to the group currency.
As we would also like to run scenarios as for what would happen if certain currency rates changed in certain ways we might add the Base_Scenario dimension to this cube as well as the Base_Month, Base_Year and a new ‘Plan_Currency’ dimension.
So the cube ends up looking like this:
- Base_Year
- Base_Month
- Base_Scenario
- Plan_Currency (three letter currency codes of local currencies)
- Values_Currency
The Values dimension could just have a single element labeled ‘Rate’ being the rate at which the Plan_Currency element converts to the group currency at a Base_Month for a Base_Year.
Optionally it’s not uncommon to have a ‘Plan_Currency_From’ and a ‘Plan_Currency_To’ to easily review reverse rates etc.
Initially this assumes that you will put in a rate per every month, year and per every currency you will be converting to Group currency, this is slightly inefficient and what I would recommend is using a rolling currency rate within the Values Currency Dimension to pre-populate the currency with last month’s unless there is a currency entered. I will cover how to make a value roll from month to month within another tutorial.
Start Typing… fill in the currency rates
Step 3 – Setting the Profit centre’s local currency
For this step we will create a text attribute on the Profit-centre dimension being ‘Currency’ and populate this with three letter currency codes that we used as elements in the Plan_Currency dimension.
Start typing… fill in the currency codes per profit centre
Step 4 – Rules in the GL Cube
Within the General Ledger cube well add a rule that applies to the ‘Group’ currency element only (which means it will apply to every other element in every other dimension).
This rule will firstly check the profit centre that’s being tested/calculated for its local currency if its currency is not populate it will assume that the currency is already group and will retrieve the amount that is against the local element. If there is a Currency populated it will pull the rate from the Currency cube and multiply that by the Local amount.
It will go something like this:
[‘Group’] =N:
IF(
ATTRS(‘Plan_Profitcentre’,!Plan_Profitcentre,’Currency’) @= ‘’
,
[‘Local’]
,
[‘Local’] *
DB(‘Currency’,!Base_Year,!Base_Month,!Base_Scenario,
ATTRS(‘Plan_Profitcentre’,!Plan_Profitcentre,’Currency’)
, ‘Rate’ )
);
Step 5 – Feeders in the GL Cube
Yes, you heard me… in the GL cube…
The feeder triggers the calculation to happen. So the logic is – What is the value least populated within the system that will always be populated when rule ‘X’ needs to be calculated. In this case it is local dollars, because we will not always have an amount entered into every combination of profit-centre, month, year, scenario but where ever we do have a local dollar amount we will want the group rule to calculate.
The feeder would be written as follows:
[‘Local’] =>
[‘Group’];
Side Note: What do you think would happen if we fed the currency rate?
We would over feed the whole cube, so the ‘Group’ rule would calculate even where there is no ‘Local’ amount, epic fail.
So there it is, a Local – Group currency system, now by simply slicing and dicing you can view the international corporate Profit and Loss in the Group Currency while still letting your international counterparts plan in their local currency.
