Hi newton_aquino,
Brad has you on the right track. The only thing I would add is this: exchange rates are not absolutely fixed. Even
renminbi, despite China's best efforts, fluctuates within a very narrow band.
Thus, you may well need a three-column lookup table (currency, as of date, and conversion factor), especially if your
expense reports cover, say, a month at a time. Of course, since your lookup would now be based on two columns
(currency and date) instead of just one, VLOOKUP is not an option any more (unless we get fancy, and use INDIRECT
or INDEX constructions to dynamically constrain the lookup table).
In my own job, I have skirted the issue by simply using an average exchange rate for the week in question, but that
may or may not conform with your employer's policy.
Regards,
Patrick
Main Topics
Browse All Topics
by: byundtPosted on 2006-05-28 at 18:42:57ID: 16781463
Hi newton_aquino,
From an auditing point of view, you will probably want to record the expenses in the local currency, then have a separate cell for the US $ equivalent. In other words, you have a look-up table with the conversion values and use a formula like either of:
=B2/VLOOKUP(C2,FOREX,2,FAL
=B2*VLOOKUP(C2,FOREX,2,FAL
B2 contains the local currency amount
C2 contains the local currency in a data validation dropdown (e.g. RMB)
FOREX is a two column look-up table. First column is local currency name (e.g. RMB) and second column is the conversion rate at the time of the expense report. Depending on whether you multiply or divide by the conversion factor, it is either local currency/dollar or dollar/local currency.
If your users only travel in one country at a time, it is possible to do this calculation only for the final total. However, the beancounters where I work need to know whether a meal costs more than $25--if so, a receipt is required. They therefore demand both local currency and US dollar equivalent for each expenditure. Since this is an IRS-imposed requirement, it wouldn't surprise me if your firm had similar needs.
Another approach is for a macro to convert the currency as soon as you input it. The problem with this is that you cannot match the receipt to the amount shown on the expense report.
Bottom line: I think you need to keep doing the reporting the hard way. About all you can do to make life easier is to make it easy to specify a currency, then have a built-in formula do the conversion calcs.
Hoping to be helpful,
Brad