Excel and Multi-Currency 1

[Scott24x7]

I'm frequently working in multi currencies for various reasons. One thing I'm wondering is, does anyone have any advice on how best to deal with this issue, especially when, say at the summary level, you need to be able to show in Euro, Yen, and USD (But not all on the same sheet... the US wants it in USD$ only, Frankfurt wants it in Euro, Japan wants only to see YEN. So, I might have data like:

              Budget          Out of Scope     Cost Plan
Servers       ¥2,446,700      ¥230,000         ¥2,676,700
Computer Room ¥8,500,000      ¥0               ¥8,500,000
HVAC          ¥4,400,000      ¥1,100,000       ¥5,500,000
Totals       ¥15,346,700      ¥1,330,000      ¥16,676,700

What I'd like to be able to do then is say, from some cell outside that range, have a drop down (Currency symbol, like ¥,£,$, etc.) that I pick it, it will display, say in sell G1 "Yen", when selecting the symbol from cell D1 in a drop down (that part I know how to do). The question is, how do I then get that symbol to apply to all my values, AND, apply the exchange rate. I can see setting up a name range that has something:

Symbol   Currency   Exchange
$        USD        .0110290
¥        YEN        1
£        GBP        .00743896
...
etc.

This would assume YEN is base currency so would be 1 to 1.

Any suggestion for this?

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Gruuuu]

I think the way I would handle that is this:
First step: Set up a separate section for unit inputs, This is where you or whomever will type in the amounts
Might look something like this

              Budget          Out of Scope     Cost Plan  |  Budget Input    Scope Input
Servers       ¥2,446,700      ¥230,000         ¥2,676,700 |  ¥2,446,700      ¥230,000
Computer Room ¥8,500,000      ¥0               ¥8,500,000 |  ¥8,500,000      ¥0
HVAC          ¥4,400,000      ¥1,100,000       ¥5,500,000 |  ¥4,400,000      ¥1,100,000
Totals       ¥15,346,700      ¥1,330,000      ¥16,676,700 | ¥15,346,700      ¥1,330,000

I would even go so far as to protect the cells that are to be displayed (the Budget, Out of Scope, and Cost Plan columns)

Then, make yourself that drop-down you were talking about. Yen, Euro, USD. Also use data validation so it can't be mucked up. Let's say, for example, this is in cell G1, and the list is in $I$1:$J$3

THEN, in those first three columns, do whatever formulas and maths, and multiply by the ratio, which you would get like thus: ="number"*INDEX($J$1:$J$3,MATCH($G$1,$I$1:$I$3,0))

Lastly, set up conditional formats for the calculated cells. Fortunately you only need three formats, so this will work in 2003. Set up formula criterion like this

Criteria 1
Formula: =$G$1="Yen"
Format: Accounting, 0 Decimals, ¥ Symbol

Criteria 2
Formula: =$G$1="Euro"
Format: Accounting, 0 Decimals, € Symbol

Criteria 3
Formula: =$G$1="USD"
Format: Accounting, 0 Decimals, $ Symbol

In 2003, this will preclude you from turning the font red if it's over budget or anything, but I'm betting the unit is more important?

Of course in 2007 you can set as many as you like.


Then, all there is to do it copy the whole row down when you need to add more data.

Oh, just thought of a point of warning! On the total row, don't apply the ratio, but DO apply the formatting. This is probably obvious, but thought I'd save you the trouble of running into the problem.

 

[Scott24x7]

Gruuu,
This is the kind of elegant solution I've been looking for. I like in partiular the ability to have "input" areas, so that I can even apply multi-currency to that. In other words, maybe my Servers are bought in the US in US$, but my computer room is rented space in Turkey, which is billed in Euro. I can use a running column next to that for "Input currency", and then display at display time summary page in actual values.... very slick.
One of the key things I was missing was the conditional formatting. I've been doing this with a very complex TEXT manipulation which makes for some very long formula values. I think this will be the trick I need.
I'm using 2010 Excel, which as you mention, has a much greater flexibility in the conditional formatting (this is why I used the text manipulation from before, and why I was hoping there was something more elegant.)
Lastly, I assume now I can use the Table function, and addition of more rows won't hurt anything. That's a major hurdle overcome for me. Will take a few days to really put this into practice, but I'm chomping at the bit now to give this a try.
Many thanks!

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Gruuuu]

Glad that this will work for you! Thanks for the star.

The only other thing I can think to add to your setup would be a web query to retrieve the most current exchange rates.

 

[Scott24x7]

I thought about that, but I actually don't want that. I want to lock an exchange rate in at the time of establishing it. Part of the point is not to muck about with fluctuating currency, but rather do a small "true up" for variance in currency at the end. Unless the currency itself swings more than 5% over the life of the project, in which case quarterly, you do a check, and see if an adjustment is warranted.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."