I’ve combed through countless comments on exchange rate and not found the specific answers I seek (apologies if it is dealt with somewhere I didn’t look, send the link or explain again).
I already know how to single enter and batch upload exchange rates, so my questions are not about how to do that.
Context:
I have both customers and vendors using a mix of AUD & USD.
e.g.
Overseas vendor provides product CIF to Australia in USD and a local vendor forwarder provides custom clearance and local freight in AUD.
My client is invoiced USD for the goods CIF, and AUD for the customs clearance & local freight.
98% of the time AUD is paid in AUD and USD is paid in USD, however there are a few rare cases where say a USD invoice is paid in AUD equivalent.
First question specific to Australia is, what is the best source of exchange rate data to use to batch populate Manager with say 2-3 years of data? I found this historical data on RBA
Second is it necessary to have exchange rates in for every day to allow the Manager to convert all USD transactions to AUD for the P&L, BAS , and any other reports to be correct?
or is it only needed for those days an actual exchange happened? (i.e. invoiced in USD, paid in AUD)
This seems pointless unless you are preparing financial statements every day. More typically, you would do this once per month, quarter, or year. Remember, transactions were entered in exact amounts for the currencies they were in. So they were all correct. The purpose of exchange rates is for conversion to your base currency for the financial statements. They don’t really affect day to day accounting. When they do, it for forcing equivalence so payables and receivables are fully cleared. The supplemental foreign currency entries take care of that.
My opinion on your first question answers your second one.
No. Exchange rates are like stock prices. Except in limited cases where a government sets a statutory rate, they are based on what willing buyers and sellers of the currency have agreed to. They are almost never what you as an individual could obtain. A common practice is to use rates published by major financial news services on the last trading day of an accounting period. Pick one recognized and accessible in your country.
Bear in mind that changing rates have absolutely no impact on how much money you actually made on any transaction. That is already history. They affect calculations of equivalencies in your base currency. But you won’t be holding foreign currency assets in your base currency. And as the rate changes, differences get taken up in the forex gains/losses account.
This may be one of the hardest concepts in accounting to get into your mind. Assets change value without changing numerical balances. But that is actually true when you operate in a single currency, too, as inflation or deflation makes your money buy more or less. You just don’t see it when you operate in one currency.
There are two types of foreign exchange adjustments, transaction and translation. Transaction foreign exchange occurs when you convert (buy or sell) a foreign currency to your base currency. The rate is the actual rate as determined by your bank or forex service provider, including any spread (margin).
For translation forex (e.g. revaluing foreign currencies held, forex contracts, or payables/receivables), I use figures from xe.com, which was also the source used by the company I used to work for (a 3,000 person global consulting firm using a daily subscription feed). Oanda.com is also a common source, as is Bloomberg.com, Google Finance and Yahoo Finance. Differences between these sources will be very small reflecting differences in timing and the exchange markets they are drawing their data from.
I only enter data periodically when I have material balance sheet amounts in foreign currency, and on the last day of each tax year. As a matter of policy, I use closing mid-point rates (my previous company used midday mid-point rates).
Some tax authorities publish monthly average forex rates that they will accept in lieu of other official sources (such as those mentioned above – but there will be many others). However, these deemed figures are usually only available after the tax year has closed and is probably not a suitable source for management accounting.