BPC - How to manage Historical currency rates?

[Former Member]

I got a query on currency translation using historical rate.

Scenario is as given below

For capital accounts, we need to calculate the number with history rate in each transaction time point. Ex: if we issue stock for three times, then we should

Capital $60,000

2006/1/1 20,000 -> 20,000 * 34.2

2007/8/3 20,000 -> 20,000 * 33.4

2008/3/31 20,000 -> 20,000 * 30.8

Capital account -> (20,000 * 34.2 + 20,000 * 33.4 + 20,000 * 30.8 )

My queries

1. What setup needs to be done to address this requirement?

2. Should user need to enter the historical rate ?

3. How system does the calculation for historical rate?

Thanks

Jerry Francis

[Former Member · Accepted solution]

There are three ways to handle this: (#1 is the default way, #2 is a variation, & #3 is a simplified version of #1 but requires modifying the currency translation script logic)

1. Setup three separate PIC accounts (e.g PIC_2006, PIC_2007, PIC_2008) and enter the individual 20,000 amounts. These would then have a parent account of PIC so you'd have the 60,000 combined for reporting. Then you would create three historical rate buckets in your rate application (e.g HIST_2006...) In your account dimension you would assign the corresponding rate types to the accounts. Then when the exchange rates are entered each month the three historical rates would just be copied forward.

2. An alternative is to use a single account and translation rate but enter a weighted average exchange rate. So you have a single account (PIC) and a single rate (HIST_PIC).

From 1/2006 > 7/2007 you would enter the rate as 34.2

From 8/2007 > 2/2008 you would enter a weighted average rate of 33.8 [(2000034.2)+(2000033.4)]/40000

From 3/2008 > you would enter a weighted average rate of 32.8 [(2000034.2)+(2000033.4)+(20000*30.8)]/60000

In your numerical example the weighted averages are also simple averages but in the real world you probably wouldn't get this. For noting the calculation of the weighted average when the rate changed in 8/2007 & 3/2008 you simple enter a comment in with the exchange rate input.

3. If you go the three account method in #1, you end up entering the same rate over and over. You may have lots of accounts (e.g. PIC, Fixed Assets, etc) that have fixed rates for individual accounts. An alternative to this is use a property on the account to store the translation rate. To do this, in the rate type property put in a common value for all the accounts (e.g FIXED). Then created a additional property (e.g HIST_RATES).

In the currency translation script logic you would modify the logic by putting in an additional WHEN statement that would test for the RATETYPE = "FIXED". If it is, then you would pull the value from the property using the field function and use it to do the translation such as: *REC(FACTOR=FLD(Account.HIST_RATES),...

Note, this method can only be used if you have a single reporting currency. If you need to have multiple reporting currencies then you have historical rates into each currency so you could not use this method.

The advantage to this is your rates are stored as structure so they cannot be accidently changed and you don't have to enter them more than once. The two disadvantages are: 1 - you have to use the script logic FXTrans rules rather than the stored procedure business rules (you cannot modify the SP's) which does run slower and 2 - it makes running a rate report slightly more complicated but not much.

In realtiy, entering the rates each month isn't any real effort because again this is all done in Excel. I'd normally (using #1 or #2) setup a rate input screen that has a separate section for the fixed historical rates from the ones that change each month. The user would type in the standard rates and simply copy and paste the block of rates that are fixed.

I personally perfer #2 because it allows you to hold a single account (which is probably coming from the GL) rather than having worry about spliting out the layers but the layering (#1 & #3) do make the presentation of additions and subtractions cleaner.

[Former Member · Accepted solution]

Thanks James. I will go for option #2.

[Former Member · Accepted solution]

James explained it so well!

In your example then, if we were to add the complexity that 6 entities have been issuing stock on those same three dates, you would be adding 18 accounts. Is that right?

[Former Member]

Two thumbs up for option #2 !!! I tried different rates for each account and also direct inputting capital accounts to the reporting currency and then only add the period's net profud but was not so straightforward.