Producing an expense report in foreign funds using the weighted average method
Next time you find yourself working on a project in, say, Haiti, and receiving funding from, say, the Canadian Government, keep in mind that exchange rates can vary wildly, and some funders can request specific methods for reporting expenses. In this article we will look at the “weighted average” method.
Keep in mind that I am by no means an accountant, so however you decide to do your accounting, please talk it over with a real accountant first.
Let’s take a simple example where funds are transferred on March 1st, and reporting is required for the period covering March.
Here are some fictional exchange rates for the month of March, 2020. CAD signifies Canadian dollars and HTG signifies Haitian gourdes.
| DATE | CAD to HTG |
|---|---|
| 2020-03-01 | 60 |
| 2020-03-15 | 70 |
Let’s keep things very simple and say we get a $1000 advance on March 1st and spend it all on March 15th. Assuming we live in a world with no bank fees or international transfer fees; and doing away with niceties such as clearing accounts; and assuming our local project does its accounting and reporting in HTG, our journal might look something like this:
| ID | DATE | DESC | AMOUNT | CUR | RATE | ACCOUNT | HTG |
|---|---|---|---|---|---|---|---|
| 1 | 3/1 | Advance | (1000) | CAD | 60 | ADVANCE | (60,000) |
| 1 | 3/1 | Deposit | 1000 | CAD | 60 | BANK | 60,000 |
| 2 | 3/15 | Expense | (60,000) | HTG | 70 | BANK | (60,000) |
| 2 | 3/15 | Invoice | 60,000 | HTG | 70 | EXPENSE | 60,000 |
If these are the only two transactions our organization made, our March account balances would look something like this:
| ACCOUNT | HTG |
|---|---|
| ADVANCE | (60,000) |
| BANK | 0 |
| EXPENSE | 60,000 |
This is all very nice and clean, and represents the world from the point of view of our local organization: we got 60,000; we spent 60,000.
However…
Our funding organization will now require a report. We could simply add a column to our journal to track how much each transaction is worth at the day’s exchange rate, in CAD; something like this:
| ID | … | AMOUNT | CUR | RATE | … | CAD |
|---|---|---|---|---|---|---|
| 1 | … | (1000) | CAD | 60 | … | (1000) |
| 1 | … | 1000 | CAD | 60 | … | 1000 |
| 2 | … | (60,000) | HTG | 70 | … | (857.14) |
| 2 | … | 60,000 | HTG | 70 | … | 857.14 |
The nice, clean world we built earlier is now starting to crumble. This report does not look very nice, and does not really reflect reality:
| ACCOUNT | CAD |
|---|---|
| ADVANCE | (1000) |
| BANK | 142.86 |
| EXPENSE | 857.14 |
Our HTG bank account statement clearly states zero, but our report to our CAD funding partner now says that our bank account contains the equivalent of 142.86 CAD.
Some funders might accept a special “exchange gain and loss” category, something like this:
| ACCOUNT | CAD |
|---|---|
| ADVANCE | (1000) |
| BANK | 0 |
| EXPENSE | 857.14 |
| EXCH. | 142.86 |
No currency exchange kiosk or bank actually invoiced us 142.86, so we do not have a receipt for this amount. This amount is related to holding funds in one currency and reporting in another: our organization loses money by holding funds in HTG while that currency is tanking. I have not found the exact accounting term for this type of exchange loss, so I’ll call it “exchange loss over time”.
The weighted average accounting method
I’ve come across a funding source who will not accept this type of exchange loss over time in reporting, and has suggested a technique called “weighted average”, which is infinitely more complicated (and fun!); and I can’t think of a single benefit of using it, but if your funders require it, then here is how it goes.
First, let’s add some real-world complexity to our journal by having two advances and two expenses. Furthermore, as in the real world, we will always leave a little money in our bank account. Forget the first example and consider this one:
| ID | DATE | DESC | AMOUNT | CUR | RATE | ACCOUNT | HTG |
|---|---|---|---|---|---|---|---|
| 5 | 3/1 | Advance | (1000) | CAD | 60 | ADVANCE | (60,000) |
| 5 | 3/1 | Deposit | 1000 | CAD | 60 | BANK | 60,000 |
| 6 | 3/15 | Expense | (50,000) | HTG | 70 | BANK | (50,000) |
| 6 | 3/15 | Invoice | 50,000 | HTG | 70 | EXPENSE | 50,000 |
| 7 | 4/1 | Advance | (1000) | CAD | 80 | ADVANCE | (80,000) |
| 7 | 4/1 | Deposit | 1000 | CAD | 80 | BANK | 80,000 |
| 8 | 4/15 | Expense | (60,000) | HTG | 90 | BANK | (60,000) |
| 8 | 4/15 | Invoice | 60,000 | HTG | 90 | EXPENSE | 60,000 |
What this journal says is that we get 60,000, then spend 50,000, then get 80,000, then spend 60,000. Here is our organization’s balance sheet for the period:
| ACCOUNT | HTG |
|---|---|
| ADVANCE | (140,000) |
| BANK | 30,000 |
| EXPENSE | 110,000 |
This reflects the reality (we got 140,000 in advances, spent 110,000, and have 30,000 left).
The weighted average method requires us to determine an exchange rate at the moment an advance is made, then track each expense against that rate. Here is an example with journal entries 5 and 6, above; consider the W-A column:
| ID | … | AMOUNT | CUR | RATE | W-A | HTG | CAD |
|---|---|---|---|---|---|---|---|
| 5 | … | (1000) | CAD | 60 | 60 | (60,000) | (1000) |
| 5 | … | 1000 | CAD | 60 | 60 | 60,000 | 1000 |
| 6 | … | (50,000) | HTG | 70 | 60 | (50,000) | (833.33) |
| 6 | … | 50,000 | HTG | 70 | 60 | 50,000 | 833.33 |
Our CAD reporting can now look like this for journal entries 5 and 6:
| ACCOUNT | CAD |
|---|---|
| ADVANCE | (1000) |
| BANK | 167.67 |
| EXPENSE | 833.33 |
So you see that for the purpose of reporting to our funding partner, every advance triggers an period where the exchange rate is tagged to the exchange rate at the moment of the advance.
If you’re saying to yourself: great, that means journal entries 7 and 8 will have a weighted average of 80 (because that’s the rate at the day of advance 7), think again.
The weighed average at journal entries is 7 and 8 is not 80, its… 77.14.
Here is why:
- At the moment of our journal entry number 7 whereby we receive 1000 CAD, we still have the equivalent of 167.67 CAD in our bank account, which is tagged to the weighted exchange rate of the previous advance.
- So, we now have the equivalent of 1,167.67 CAD in our bank account.
- 1,000 of that amount, or 85.71%, should be calculated at the day’s rate, or 80.
- 167.67 of that amount, or 14.29%, should be calculated at the previous weighted average, which we know is 60.
- Each of the exchange rates (80 and 60) and weighted according to the amounts represented, in this example 1,000 and 167.67.
You can now clearly calculate the weighted average exchange rate of journal entries 7 and 8:
85.71% * 80 + 14.29% * 60 = 77.14.
Our complete journal will now look like this:
| ID | DATE | DESC | AMOUNT | CUR | RATE | ACCOUNT | HTG | W-A | CAD |
|---|---|---|---|---|---|---|---|---|---|
| 5 | 3/1 | Advance | (1000) | CAD | 60 | ADVANCE | (60,000) | 60 | (1000) |
| 5 | 3/1 | Deposit | 1000 | CAD | 60 | BANK | 60,000 | 60 | 1000 |
| 6 | 3/15 | Expense | (50,000) | HTG | 70 | BANK | (50,000) | 60 | (833.33) |
| 6 | 3/15 | Invoice | 50,000 | HTG | 70 | EXPENSE | 50,000 | 60 | 833.33 |
| 7 | 4/1 | Advance | (1000) | CAD | 80 | ADVANCE | (80,000) | 70.14 | (1037.04) |
| 7 | 4/1 | Deposit | 1000 | CAD | 80 | BANK | 80,000 | 70.14 | 1037.04 |
| 8 | 4/15 | Expense | (60,000) | HTG | 90 | BANK | (60,000) | 70.14 | (777.78) |
| 8 | 4/15 | Invoice | 60,000 | HTG | 90 | EXPENSE | 60,000 | 70.14 | 777.78 |
And, we can now report in HTG for internal purposes; but we can also report in CAD for our funding partners, like this:
| ACCOUNT | CAD |
|---|---|
| ADVANCE | (2000) |
| BANK | 388.89 |
| EXPENSE | 1611.11 |
Why I absolutely hate this technique
Here are a few reasons why I think this technique is just plain wrong:
-
This technique requires complex spreadsheet calculations even for the simplest of transactions, making errors more likely and increasing training time for local staff with bad internet access and old computers.
-
The weighted exchange rate has no basis in fact; no one thinks about exchange rates that way. With this technique, an expense made in, say, July of a given year, can be tied indirectly to an exchange rate when an advance was made in, say, January.
-
The resulting report does not reflect reality: we do not have 388.89 CAD in our bank account; if we were to close out our bank account today, we would get the day’s exchange rate, not some fictional weighted rate based on the entire history of all the transactions of our project!
-
This technique will become increasingly complex if, say, a project gets funding or revenue in U.S. dollars, Euros, Canadian dollars, and Haitian gourdes, and has expenses in U.S. dollars and Haitian gourdes. This is exactly the situation faced by the organization I’m working with, which is not a multinational with a team of accountants – it only has about 100,000 USD of cash flow per year.
-
Transfering these “weighted exchange rates” from one fiscal year to the next can be a nightmare (but a kind of fun nightmare).
My biggest gripe with this technique is that it has no basis in fact, presents skewed numbers, all of this to avoid reporting an exchange loss over time. This does not make the exchange loss over time go away, it simply hides it! Consider a situation where a lending organization advances funds to a local organization at a given exchange rate, and then a few days later a financial catastrophe happens (this is not theorical: consider COVID-19). This will bring about major inflation and make future expenses completely divorced from the exchange rate at the time of the initial transfer. This is reality, folks, it should be reflected in our reporting!
Let’s look again at how our organization reports its balance sheet, in HTG, over the period covered by the current example:
| ACCOUNT | HTG |
|---|---|
| ADVANCE | (140,000) |
| BANK | 30,000 |
| EXPENSE | 110,000 |
Clean and simple. By calculating the exchange rate at the real day’s rate for each transaction, we can simply transfer this to CAD:
| ACCOUNT | CAD |
|---|---|
| ADVANCE | (2000) |
| BANK | 619.05 |
| EXPENSE | 1380.95 |
Now all we need to do is to look at the real amount in the bank account (30,000) on the last day of reporting, and convert it to CAD using the exchange rate of the last day of the report (90), which gives us 333.33 CAD. Then the difference (619.05 - 333.33) is an exchange loss (or gain if it’s negative). Voilà! Simpler, and a better reflection of reality.
| ACCOUNT | CAD |
|---|---|
| ADVANCE | (2000) |
| BANK | 333.33 |
| EXPENSE | 857.14 |
| EXCH. | 142.86 |
Now, when you hear the term “weighted average”, you will know to run the other way as fast as you can.