Foreign Exchange (FX) Translation in TM1

We have a client where we needed to do some basic foreign exchange translation. It was 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).

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 and from that 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.

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

# 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'];

# Feeds all values in Local to all reporting currencies
If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.
This entry was posted in Rule Examples and tagged currencies, foreign exchange, fx. Bookmark the permalink.