Notifications
Announcements
No record found.
To all using FRX, the YTD average rates calculation when translating foreign currencies does not work like it should...anyone know what I am talking about??
-Rose
*This post is locked for comments
I have has success with FRx and consolidating and converting other currencies to US Dollars. I ahve never sen any problems, but let's discuss.
First, what version of GP are you on and FRx? Are you on Service Pack 10? Are you using the FRx tables to store your conversion rates or are you using GP?
I personally enter the average rate (CCAVG) and the month end rate (CCSPOT) for every period right in Frx Rate Maintenence.
What is the behavior that you are experiencing?
GP 10 with FRX67. According to FAS 52 income statements are supposed to use average rates as you know. We have one rate for the month which is allowed according to GAAP. I input the month end date and rate in the DAX table for daily rates. I have the row format set to ccavg monthly and have the spot and historical rates in FRX currency translation tab in the catalog report set to FRX. It is not usng the YTD average rate in my average rate reports (whether I have a check mark next to Use (sum of rates/number of rates entered*Balance in the catalog translation tab)
HELP!!!
Thanks
I guess that I have never tried to use their calculation methods. I actually calculate the average rate for a month in excel and enter it right into FRx. I use an excel macro to fetch the rates from Oanda.com and then enter them in FRx. I actually use this same prcess with integration manager to push the daily rates into GP. I feel like since I can't really run an FRx report as of a certain day in the month (typically end of a period unless you do some manipulation in the column layout), that it is easier to just enter the month end rate and the average rate once for a period. So my CCAVG rate is a static rate as opposed to having FRx calculate this average based on the daily rates.
Can you give us an example of what the rates are that you entered, what you expect the result to be verses what you expect the rate to be? Just to be clear, I am using the CCAVG rates for my Income Statement and for the Net Income calculation in the Balance Sheet. All I enter is the CCAVG at the first Row of the income statement and it assumes that I am using it all the way down. It is dividing whatever accont balance, in my case CNY, by the average exchange rate I have entered as the average rate for that period (somewhere around 7.00), and FRx returns with my translated amount in USD.
If I remember from the e-learning, there are two different calculation methods that FRx can use to calculate the Average rate based on daily rates. Are you aware of the other method you can choose from? If not, I'll dig up that info for you.
Thanks but I know both calculations....Basically it is getting my the average rate for the month but not the YTD average on the YTD reports....Obviously since I am only using one rate for the month it is not average but really that rate......But the YTD should be the average of all the rates inputted YTD. Supposetely the system is supposed to take the converted amount YTD divided by the foreign amount....
You mentioned above you actually entered the average rate?? Shouldnt the system calcuate the average rate?? You should just put in your rates for the periods.....whether it be daily/weekly/monthly
Maybe we can set up a call>>>is that ok?
I need to say that I am not a CPA so the way I went about figuring out what rates to use was based on the FRx examples as well as that FAS 52 document you were talking about. So I can't really give you any guidance regarding the actual rules. But I have done this for a client and explained my methodology and they didn't have a problem with it.
I think that the difference between us is that you are looking for 1average rate for a YTD balance on your income statement and I'm almost positive that FRx is using the sum of all of the periods multiplied by that period's average rate. So for example, if you are running the income statement for period ending 8/31, and you use YTD in your column Layout, it will take (Period 1 x Period 1 Rate) + (Period 2 x Period 2 Rate) + (Period 3 x Period Rate) .......+ (Period 8 x Period 8 Rate) instead of one lump sum amount x some rate.
Think about this. If you did a trended income statement period by period, what would you expect the results to be? I say that the sum of your monthly trends would be your YTD. If you did a test and had a column layout with DES, then periods 1, 2, 3, 4, 5, 6, 7, 8, then had a calc column for columns B TO I and then made another column for YTD, my guess is that the Calc Column and the YTD column would come out the same. That would mean that FRx is calculating according to my explanation above. And I wouldn't imagine that FRx would promote their software as doing currency translation and not having complient calculations. (well maybe since it is Microsoft....I'm kidding, don't ban me!!).
So I could be completely wrong here, but that is how I understood the process. Also, I like to keep the discussions on the board in case it can help someone else searching for this issue in the future.
Sure- I have no problem keeping it live....FRX is doing exacly what you are saying but I have an internal microsoft document that explains how FRX should calculate YTD average....it does what you say above
(Period 1 x Period 1 Rate) + (Period 2 x Period 2 Rate) + (Period 3 x Period Rate) .......+ (Period 8 x Period 8 Rate)
but at the end it takes the original foreign total amount YTD divided by the converted YTD amount
YTD average cannot only be average of the monthly rates because it is YTD not monthly or current.....
Thanks....
I think we might be coming to a head here because I think the debate is headed towards an accounting and financial statement discussion which I can't really give any expertise. The only thing that I can add is my opinion. From what I understand, the P&L accounts are evaluated monthly against the average rate from that month. Balance sheet accounts are evaluated at a spot rate (the period ending rate for the period you are evaluating) for the most part except for certain accounts like Fixed Assets, Accumulated Depreciation and Retained Earnings that are Historical Rates. So the P&L accounts the YTD amount is actually the Sum of the Periods because they start with a beginning balance of 0.00 whereas Balance Sheet accounts are evaluated as a YTD because they carry forward a Beginning balance. This is why you can't drill into transaction detail for Balance Sheets because this balance comes in part from the year the company formed, which could be 50 years ago. So I feel YTD is a code you would want to use for Balance Sheet and CUR is what you use for P&L acconts. Conveniently, YTD will accomplish what you want if you want to see the income Statement as of a certain period of time, but it is really just the Sum of all the current period balances.
Here's what I just picked out of the FAS 52 and I added emphasis in Italics
For assets and liabilities, the exchange rate at the balance sheet date shall be used (CCSPOT). For revenues,
expenses, gains, and losses, the exchange rate at the dates on which those elements are
recognized shall be used. Because translation at the exchange rates at the dates the numerous
revenues, expenses, gains, and losses are recognized is generally impractical, an appropriately
weighted average exchange rate for the period (CCAVG) may be used to translate those elements.
You explained everything correctly as I am a CPA very familar with FAS 52. However like you mentined that it should be the weighted average for period and when presenting YTD Income statements or quarterly financial statements, the period is all the months YTD or for the quarter. So me this means it should be the weighted average YTD not sum of wieghted average converted months.
Do you see my point?
I see your point. So I'm just not sure that FRx does this. You mentioned an internal Microsoft document that you had. Is there any way you could pass on a link to that?
So hypothetically you are saying that you want the calculation to be (for a quarter to keep is simple) (Period 1 Balance + Period 2 Balance + Period 3 Balance) X the Average of all Daily Rates (Day1 Rate + Day 2 Rate......) / 120.
If this is the calculation you are looking for, I feel like the only way you can get FRx to handle this would be to do this outside the system and then enter the rates into FRx like how I explained it above. Calculate your new YTD weighted average and edit the rates into FRx as that rate for every period in that year. Basically, Period 1 would be some rate. Then when period 2 comes, you calculate the new rate and enter it in Period 1 and Period 2 average rate. Then for period 3, do the same thing and enter that rate in period 1, 2 and 3. You would be constantly overriding prior rates. This would not give you the right results for a trended income statement, but your YTD would be as you expected.
You might even be able to create a new FRx company and point it to a different DAX table and have on company for trended, and one for YTD.
Yes send me your email address for the document....I guess my question lies with how FAS 52 and Translation should be- how they want the average rate to be calculated......
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Community Member 2
mtabor 1
Victoria Yudin 1
