FRX Currency Converter

[PTina]

I need to build a consolidated report that will convert totals from different companies (with different functional currencies) to USD, then compute the foreign exchange gain/loss.

I am able to generate the USD consolidated report. However I don't see how to compute the fx gain/loss at each of the various rates.

Also currently I'm using the monthly rate table and we want to go with weekly rates so I'm planning to use the daily rate table and update it weekly.

 

[PTina]

Is anybody else in this forum using the Multi-currency module? How do you report on FX Gain/Loss whether from FRX or via some other method? I'm very interested in finding out how other companies do this as this report seems like an impossible task.

 

[barbola]

We use multicurrency but we only change the rate monthly, so it's alot more basic for us, and we use a flat rate on our FRx report to convert the currency.

I'm sorry I'm not much help, but if I have a chance I will play around with it.

As I understand it, you are trying to pull the various FX rates into FRx to show all your multi currency companies in USD?

 

[PTina]

Thanks for the input Barbola. I've updated the daily frx currency tables on a weekly basis. I am already printing a consolidated report in USD but I need to take it 1 step further.

The problem I'm trying to figure out now is how to do a report like the one below. The same concept could be used for monthly rates.
Wkl rate = .5
Wk2 rate = .55


Wk1_Val Wk1_USD Wk2_Val Wk2_USD Wk2@Wk1_Fx FxGain/Loss
$100 $150 $115 $178.25 $172.5 5.75


Until I find a better approach, I'm trying a workaround which is to calculate the rate for each week and then use that to calculate the rest but the figures are not coming back.

 

[barbola]

I may be off but can you assign the rates to a row in your row format and use that as a base row?

Row:
100 CAL .50 (display in Col A)
200 CAL .55 (display in Col A)

300 CAL A=A100, C=A200, E=(A200-A100)

400 CBR 3
500 row 500 is your account amount

Column Layout:
A B C D E F
GL A/BASE GL C/BASE C/BASE D-E

Result:

Row A B C D E F
100 .50
200 .55
300 .50 .55 .05
500 100 150 115 178.25 172.5 5.75

Is that kinda sorta what you need? Or is it the "getting the rates from Great Plains" that's the problem?

Barb.

 

[barbola]

CBR should be 300 not 3

sorry about that!

 

[PTina]

Barbola your example does have potential. The problem is that I'm working with 16 different exchange rates which are updated weekly.

The row format is also linked to the tree so that I can pull different accounts for each company:


row 100 Company1 100-000-00
row 200 Company1 100-001-00
row 300 Company2 200-000-00
row 400 Company2 200-005-00

I use the column layout to filter the date ranges for each week. Maybe I'm wrong but I can't see how I will get the rates from the FRX DAX tables (or even GP) for each week that is filtered on the column layout.

 

[PTina]

Ok I opted to calculate the rates by dividing the local currency by the USD amount. However this just highlighted another problem.

The USD amounts aren't being converted using the exact rate from the DAX tables. It is not an average of all the rates entered either. I expected the report to use the rate based on the date filter entered on the report. Now I can't even figure which rate is being used.

Any help appreciated.

 

[barbola]

Although we have multi currency, we report in Cdn currency in FRx and doesn't show the US amounts. I'm unfamiliar with DAX tables?

Sorry I'm not of much help.

barb

 

[PTina]

OK I figured out what was the issue. One of the columns used in my calculation was filtered for Dec 2005.

The rates in Dec are very different from those for the past few months. This fact got lost because of all the calculations.