Currency Conversion issue for YTD model

[Former Member]

Hello,

Currently our Client is using YTD (year to date) model to maintain data into BPC 7.5NW, and planning to use BPC for currency conversion from LC to USD. They are not converting the values in Transactional system. When performing this all Balance sheet accounts will be using YTD model for conversion, but income statement accounts should be using Periodic values for conversion.

We have two different Entity FX types: one is USDFNC - USD functional and NUSDFNC - Non USD functional. One of the differences between these two FX types is CTA account, USDFNC is using expense Account to Book CTA and NUSDFNC is using Equity Account to book CTA.

To achieve the above mentioned requirement, I did configure BPC as follows:

1) Use RATE type AVG (average for Income statement accounts)

2) Use RATE type END (spot rate for Balance sheet Accounts)

3) In business rules for Account rate type AVG- formula would be [AVG], apply to periodic flag will be checked.

5) in business rules for Account Rate Type END - Formula would be [END], apply to periodic flag will be blank.

6) To calculate CTA for ENTITY FX type- NUSDFNC, I configured the following in Account Rate type AVG - destination account is equity account, Formula is [END]-[AVG], apply to periodic flag will be blank and Entity FX type is NUSDFNC.

7) To calculate CTA for ENTITY FX type- USDFNC, I configured the following in Account Rate type AVG - destination account is expense account, Formula is [END]-[AVG], apply to periodic flag will be blank and Entity FX type is USDFNC.

Above configuration is giving me correct converted result for all Balance sheet accounts and income statement accounts are showing the correct values when I use the formula (current period LC amount-previous period LC amount)*(current period AVG rate). But I saw difference in Balance sheet, so further analyzed CTA (currency Translation Accounts) and found that the difference is because of CTA amount only. Similar CTA differences exist for both USDFNC entities and NUSDFNC entities. So, I think my logic to calculate CTA is not working.

I am using the standard script logic to run Currency Conversion:

*RUN_PROGRAM CURR_CONVERSION

CATEGORY = %CATEGORY_SET%

TID_RA = %TIME_SET%

CURRENCY = %RPTCURRENCY_SET%

RATEENTITY = GLOBAL

*ENDRUN_PROGRAM

*COMMIT

Can any one help me to understand whether this configuration is correct? I feel that I may need to change the logic for calculating CTA. But not sure about what and where

Edited by: amarnathputta on Nov 24, 2011 5:36 AM

[Former Member · Accepted solution]

Hi

Running the Package in Dynamically or not

Can u tell me what error displays after Run package

[Former Member · Accepted solution]

Thank you surender,

I am running the package dynamically only.

When the package completes the process, I do not get any errors. Currency Conversion completes successfully.

When I check the results using EVDRE reports, I found that the results are not correct. I mean CTA is not getting generated correctly.

Thanks

Amarnath Putta

[esjewett · Accepted solution]

You've explained your setup quite thoroughly, but can you explain to us the results you are seeing? And example with the result you get vs. the result you expect would be very helpful in trying to understand your problem.

Thanks,

Ethan

[Former Member · Accepted solution]

Thank you Ethan,

Let me try to explain this with few dollar amounts:

Let us consider that I have data for 1st month (Jan) of the year 2011,

Net income for Jan. 2011 in LC = 59916152.19

Net income for Jan. 2011 in USD calculated using Average rate is = $ 9,223,013.14

CTA for the same month is (net income in LC X spot rate) - net income in USD at average rate =((59916152.19)*(0.154617))-9223013.14 = 41,042.564

This value is correct.

For the month of Feb 2011:

Net income for Feb.2011in LC = 60415519.01

Net income for Feb.2011 in USD at average rate (with periodic flag on) = 9,300,112.38 ( I calculated this value manually and it is correct after conversion)

calculated CTA for the same month is (60415519.01*0.154083)-9300112.38 = 8,892.035.

But actually BPC calculated CTA is 18,547.56 (I felt that this may be because I am not using the Flag apply periodic value while calculating CTA and when I flagged that, BPC is giving me a very big difference in CTA)

My balance sheet is exactly off with the difference in CTA.

Hope this helps you to understand my problem.

Edited by: amarnathputta on Nov 25, 2011 2:43 PM

[Former Member · Accepted solution]

Thank you for trying to resolve the issue.

I found the reason and solution for the.

We are using Rate type as AVG even for Retained earnings account. Due to this Retained earnings account was being included for CTA calculation. That was the reason why CTA was always Double the amount.

So, I am planning to use a different rate type for Retained earnings account, rate in this rate type would be same as AVG.

Thanks