Foreign Exchange (FX) Calculation in TM1

You may find yourself in a situation where you need to do some basic foreign exchange calculations. In our case, we had a client where we needed to do a simple FX translation of New Zealand Dollars to Australian Dollars and then to American Dollars. Source sales data is loaded by transaction with a date, cost centre, product, customer, sales rep, sales value, cost of sales value and quantity. The values are in local currency (AUD and NZD).

FX Conversion Method in TM1

We had a bit of a chat about it internally and decided on the following method for foreign exchange translation in TM1:

1. Create a Sales Analysis cube with dimensions for time, cost centre, product, customer, rep, version (or scenario), currency and measures.
2. Version dimension has Actual, Budget, Forecast etc.
3. Currency dimension has AUD, NZD, USD and Local as elements.
4. Cost Centre dimension has an attribute called Currency and in it is defined what currency transactions for that cost centre are loaded in.
5. Define variables for version with “Actual” in it and another variable for currency defined as “Local”.
6. Load the Sales Analysis cube using the supplied date, cost centre, product code, customer code, rep code, and the defined variables for version and currency.
7. Create a cube for FX Rates, containing dimensions for time, version, currency and measures.
8. Populate the FX Rates cube with FX rates relative to the AUD by month into, in the first instance, the Actual version. Obviously the rate for AUD would be 1.0000!
9. Create rules on the Sales Analysis cube that calculate Actuals in AUD by multiplying the Local values by the FX rate. To do this we are essentially doing a lookup (using DB and ATTRS) of the cost centre. From that then deriving the currency the transaction was loaded in. Then take the currency to the FX Rates cube and for the current month, extract the relevant rate. This converts all values (including those loaded into “Local” in Aussie dollars into AUD.
10. Repeat the calculation from above to convert AUD into NZD and USD, but this time you don’t need to look up the relevant currency for the cost centre as we are converting only from AUD to the others.
11. Finally, you don’t need to do foreign currency translation on the Quantities, so we just put a rule in to copy the values from Local to the three reporting currencies.

Rules for Foreign Exchange Conversion in TM1

So, what was the code we used in the Rules, I hear you ask?  Here it is:

SKIPCHECK;
# Converts all Local values to AUD and then AUD to NZD and USD
['AUD', { 'Sales' , 'Discount' , 'Cost of Sales' } ] = N: ['Local'] * DB ('FX Rates', !Period ,!Version, ATTRS( 'Entity', !Entity, 'Currency') , 'FX Rate') ;
['NZD', { 'Sales' , 'Discount' , 'Cost of Sales' } ] = N: ['AUD']  DB ('FX Rates', !Period ,!Version, 'NZD' , 'FX Rate') ;
['USD', { 'Sales' , 'Discount' , 'Cost of Sales' } ] = N: ['AUD'] * DB ('FX Rates', !Period ,!Version, 'USD' , 'FX Rate') ;

# Copies all Quantities to NZD and USD
['AUD', 'Quantity'] = N: ['Local', 'Quantity'];
['NZD', 'Quantity'] = N: ['Local', 'Quantity'];
['USD', 'Quantity'] = N: ['Local', 'Quantity'];

FEEDERS;
# Feeds all values in Local to all reporting currencies
['Local']=>['AUD'];
['Local']=>['NZD'];
['Local']=>['USD'];