Exchange rate fluctuations

    Hi all,


    Here is an interesting one. I have inherited a work sheet that works out effective rate changes over two static periods (i.e. 2004/2005)but it has been an issue for me due to exchange rate changes each year.


    The worksheet is supposed to be completed in USD but Iam finding that the exchange rates are making the effective rate changes way too high i.e. -48%.


    Knowing this is incorrect I created the sheet in the original currency and when you compare the years, the change is only -16.2%, which is correct.


    To get the correct answer(-16.2%) using the correct worksheet(usd) Im having to trial an error a figure into the Subjective section which comes to 749 in my example attached.


    My question (after all the above :)) is how can I understand what this figure is or means (749). It simple to say to my boss oh it works but Im having to do two worksheets rather than the USD one! If anyone knows what this 749 is based on or how I can understand this I will be enternally grateful


    Yours,


    depressed but hopeful steve

    Re: Exchange rate fluctuations...a challenge


    I can't see the difference between the 2 spreadsheets. Can you post the first one up with the -48% in it so we can see where it could be breaking down?

    Re: Exchange rate fluctuations...a challenge


    hi, thanks for your reply.


    If you take the 749 out of the subjectivity field in the usd sheet you will see the rate jump! I would attach it but it makes more sense if yyou take it out yourself and see the change.


    The 749 has been entered by trial and error so im wondering if i could calculate this figure rather than trial and error!!


    Thanks again

    Re: Exchange rate fluctuations...a challenge


    Hi,


    I must admit I have no idea what I'm looking at! The numbers on the two sheets don't relate in any way I can see...


    I simply selected two different 'factors' from the list on the USD sheet and *poof* 16.2%.


    Attachment Factor was 2.778, I choose 1.667 from list.
    Market Capitalization Factor was 1.025. I choose 1.05 from list.


    16.2% Coincidence?


    Cheers.


    dr

    Re: Exchange rate fluctuations


    Hi as you have found by changing the factors for either attachment or market cap will change the value but you have to keep these in correct place i.e if its 100m then it should be in that bracket (i.e. 100-124).


    The issue is if the exchange rate changes as in the usd example(where the issue is) the attachment is pushed into another factor. We then have to put a trial an error figure (in this case 749) into the subjectivity table to get the correct outcome.


    The question is rather than using trial an error how can I work outthe 749 with some logic! :)

    Re: Exchange rate fluctuations


    Hi,


    So the question are <G> What's the logic on the other numbers? Where do they come from? What do they represent? I merged your two sheets and I can see no correlation in the numbers on the two sheets. ie Limit 17.5 becomes 22.4 and Limit 25 becomes 29.6. Attachment 100, 100 becomes 128, 118.


    The relationship between the different numbers is the key to the % difference I think. Is it possible to explain what these numbers represent?


    Cheers,


    dr

    Re: Exchange rate fluctuations


    Unfortunately not,


    The figures are correlated in that they the are same figures just the usd sheet is in dollars. Therefore they have been changed from euros to dollars using a different rate for each year ('05 1.28 and '06 1.18).


    I.e. on the original currency sheet the attachment is a 100 for each. When the rate of exchange is applied on usd.xls the following happends '05 =128 and '06 =118. This causes the attachments factor to change and hence the large jump in percentage.



    This applies to other figures and is a concern when the factor changes as this will have an effect on th epercentage that is found.


    In instances where the factor changes and the percentage is increased greatly we have to do this original currency spreadsheet, find out the percentage and then doc the usd.xls in the subjectivity table.


    By doing this we are using trial and error and as you can see the 749 works but is plucked without logic (except t&e).


    Therefore the question is how can I work out this 749 other than trial an error.


    I think that should answer your question :)

    Re: Exchange rate fluctuations


    Hi stevehorton09,


    A challenge indeed! Here's your answer. The proven way of calculating a percent change is: 100*((NewValue - OriginalValue)/OldValue) = %change


    Since you know what the %change is supposed to be we can do a little math. Where x is (NewValue-OldValue) from the equation above:


    If 100*(x/OldValue) = 16.2% then .162*OldValue = x


    and (NewValue+x)-OldValue = 'Adjust'


    The formula for your worksheet becomes:


    ABS(NewValue+((DesiredPerCent/100)*OldValue)-OldValue)


    I put it in the attached sheet with an IF so it won't display if "Desired %" cell is blank. You must type the returned value into the 'subjectivity' cell or better yet use Copy/PasteSpecial/Values. (The equation will cause a circular reference error if put in line with the 'SUM' formula for I33.)


    I also cleaned up the formatting of the sheet and comments slightly <G>


    Cheers,


    dr

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login