Foreign exchange gains/losses

[mauroskov]

Hi. I have seen a couple of posts about this topic but with no answers, so I am not sure if this subject is maybe not so important for many users.

I have been working on a product costing for my stock. All seems to work out well. I have added my purchase invoices in 3 different currencies and all my exchange rates for the days of the purchase invoices (even if the actual exchange rate is determined by the day we pay, not the day the invoice is issued, and even if sometimes we have different purchase invoices with the same currency and same date but with different exchange rates because they were paid at different moments, a problem I solved by changing the date of some of the invoices).

Now I can see that I have some forex gains, and I really have a hard time in figuring how this works. If I click on the amount of the gain I get a list of transactions, where some are gains some are losses. The first one is a gain and it looks like this:

The open balance is the amount of my first purchase invoice, while the purchase inovoice is the amount of my second purchase invoice. This is already something I do not understand: why the use of the term “opening balance”? But also, the exchange rates I put diligently have all a lot more digits that just 0,0010 and 0,0011 - they are all like the third exchange rate in the picture with 10 digits after the 0,… Does Manager approximate automatically the exchange rates? And why only the first two? Could the approximation be the reason for the gain/loss?

[Brucanna]

@mauroskov

we have different purchase invoices with the same currency and same date but with different exchange rates

With the Purchase Invoices you don’t need to enter an exchange rate per invoice, this just seems to be creating a lot of work. Create a mid rate for a period (week, month, quarter, year) and process all Purchase Invoices to that mid rate and then review those rates at the end of each period.

This way, after the entry of each Purchase Invoice there will be no foreign exchange gains/losses. These will only arise after the invoice is paid - the variance between the purchase invoice mid rate and the invoice payment actual rate.

As to your screenshot above, the truncated exchange rates must be a “representation” of your entered exchange rates as they don’t relate at all to the monetary values, for instance, take the opening balance line with 1828.8 and a rate of 0.0010 which results in a slightly larger RWF 1856.232, yet the purchase invoice line with 1556.82 and a similar rate of .0011 results in a much smaller RWF 1362.218. They should either be both larger or both smaller if those displayed rates were being used, not one each way.

@mauroskov

why the use of the term “opening balance”?

Because you are looking at a report dated “as at” 07/03/2016 (that day only) and there were other transaction prior to that date. Let’s work through a demonstration.

Going to create two euro invoices one dated Dec 8 for 500 and one dated Dec 14 for 1000 with an exchange rate as at Dec 1 of .0010.

So the two invoices at that exchange rate equal 500,000 and 1,000,000 in RWF.

Going to pay 525,000 (Dec 12) and 990,000 (Dec 16) against each invoice in RWF. After the first payment the forex balance is 25,000 RWF

If the Invoice and payment were of the same date Dec 8 then the report would like this

Now look at the same Dec 12 report, if the Invoice dated Dec 14 is changed to Dec 11, prior to the payment. The opening balance has increased by the Dec 11 invoice and a closing balance has also been created for that unpaid Dec 11 invoice.

After the second payment there is a forex of 15,000 RWF (25,000 - 10,000)

And the report for Dec 16 only shows the related transactions.

Therefore going back to your screenshot where you only have purchase invoices and no bank transactions shown, how you managed to create forex variations from just purchase invoices is unknown unless its due to all your individual purchase invoice exchange rates being in conflict with each other as both invoices are unpaid (via the closing balance being there) yet there exists a forex variance. (???)

[mauroskov]

Hi @Brucanna

Thank you for your help. Sorry for the delay but I had to study your response a bit. It all makes more sense now.

@Brucanna

Create a mid rate for a period (week, month, quarter, year) and process all Purchase Invoices to that mid rate

This would be helpful, but then the actual cost (and hence several things including the cost of goods sold and ultimely taxes) will not be precise. Right?

@Brucanna

…and then review those rates at the end of each period.

What do you mean by reviewing the rates? Go in and update the exchange rates with the actual ones? Isn’t it easier to use the right rates from the beginning?

@Brucanna

Therefore going back to your screenshot where you only have purchase invoices and no bank transactions shown, how you managed to create forex variations from just purchase invoices is unknown unless its due to all your individual purchase invoice exchange rates being in conflict with each other as both invoices are unpaid (via the closing balance being there) yet there exists a forex variance. (???)

Yes, none of the invoices have been paid yet in my simulation, and this is because in a previous post (Switch Currencies order in Currency Exchange rates - #26 by mauroskov) I had understood that there was no need to use exchange rates for payments. So yes, I guess the gains/losses is based on the different exchange rates I have used for the several purchase invoices.

Overall, If I understand it correctly, I should add for example an average quaterly official EURO/RWF exchange rate (e.g. from the central bank or maybe the rate used by customs when we imported) for the date of each Purchase Invoice (or even the specific official rate on the date of each Purchase Invoice) and then add exchange rates based on our actual purchase of forex for the dates in which the Purchase Invoices were paid?

[Brucanna]

Ok, I didn’t realise this topic was linked to your other topic’s posts so that makes a little difference in process. The above response was based on receiving an Euro Purchase Invoice and then payment from a RWF bank, rather then payment from a Euro bank. So I will responded to your general points here and later, in a separate post, work through that model

@mauroskov

but then the actual cost . . . . will not be precise. Right?

Correct, but would be a very close approximation. Does it matter that much if the mid rate costing equalled 40.25 but the precise costing is 40.22 or 40.28. Also, if you still have some product in stock when you purchase it for a second time then Manager will have that product at an Average Cost.

Lets say for Product A that the first time you purchased costed out at 40.25 and the second time you purchased costed out at 40.29, therefore Manager will have that product at an average cost of 40.27, so the initial “preciseness” gets blurred into “averageness” over time.

@mauroskov

What do you mean by reviewing the rates? Go in and update the exchange rates with the actual ones?

No, on Dec 1 set a mid rate for the whole of Dec based on some “official” source, then on Jan1 review that mid rate and set a new mid rate if warranted. NOTE: you set a new mid rate by “adding” the rate using Jan 1, not by editing the Dec 1 rate.

@mauroskov

Isn’t it easier to use the right rates from the beginning?

Possibly if you have infrequent activity, but if you have multiple activity as you noted above, “we have different purchase invoices with the same currency and same date” then probably not - it all gets rate congested / conflicted as you noted by “solved by changing the date of some of the invoices”

@mauroskov

If I understand it correctly, . . . .

Sought of but lets clarify that with the next post, until then.

[Brucanna]

Ok here goes, an initial exchange rate was setup as follows

You have to elect which set of exchange rates you are going to use (1) the exchange rates as at the date of the purchase invoice or (2) the exchange rates as at the date of the transfer of funds but you can’t use / enter both sets of rates and you definitely can’t use any exchange rates related to the actual payment to the supplier, for instance - you wouldn’t enter an exchange rate when paying a supplier’s euro purchase invoice from a euro bank account as they are both in the same currency.

As you are entering into Manager the transfer first, prior to the purchase invoice, then its suggested you use (2), entering under Settings the “actual” exchange rates related to the transfer, eg, if you have transferred the funds for a purchase invoice to the euro bank at 0.0010, but at the date of the euro purchase invoice the exchange rate was 0.0011, then you wouldn’t use 0.0011 for that purchase invoice as the euro funds for it are already existing at 0.0010.

The following modelling is based on using (2), the transfer exchange rates, and your examples of transactions from the other topic. Lets assume the initial transfer is at the initial exchange rate as noted above.

The euro purchase invoice is this

With inventory average cost as this

Now lets change the amount of RWF to 2,600,00 required to obtain the 2,500 euros

This creates a forex variation from the mid rate but the inventory average cost remains unchanged.

But if we enter an exchange rate to match that adjusted transfer

The forex variation virtually disappears and the average cost adjusts to suit the higher RWF

But this all gets a bit messy when you have multiple transfers over lapping multiple invoices as the exchange rate for one transfer may affect the purchase invoice relating to another transfer - you can’t match / package transfers / purchase invoices neatly together.

An alternate solution would be to do the transfers as unrelated to any specific purchase invoice and then do Purchase Invoices using reviewable mid rates as per (1).

[mauroskov]

Thank you @Brucanna

It is indeed quite complicated and there seems to be no perfect solution. I think that the only solution that comes closer to reality would be to pay the Euro Purchase Invoices from the RWF account.

Basically if the real situation is the following…

1. I have an invoice for €2.450 for some goods dated March 1st;
2. We pay duties on these goods from the RWF based an exchage rate of 900 (set by authorities) on March 15th;
3. We go to a forex to buy €2.500 at a rate of 950 on March 30th, deposit them on our Euro account, and pay the Purchase Invoice on the same date from the Euro account.

…what I should do in Manager is to pay the Purchase Invoice directly from the RWF account on March 30th using the rate of 950 while at the same time I transfer 47.500 RWF (i.e. €50) to the Euro account (this is the difference between what was exchanged, i.e. €2.500, and what was paid, i.e. €2.450). When I transfer the equivalent of €50 I enter the exact amounts so Manager should calculate the exact exchange rate by itself (right?).

What do you think?

[Brucanna]

@mauroskov

It is indeed quite complicated and there seems to be no perfect solution.

Made more so by your constant connecting of the funds transfer to the purchase invoice. If you can divorce that connection just so that the funds transferred is just that - a funds transfer - unrelated to any particular purchase invoice then you can simplify it all.

E.G. - you could transfer 10,000 euros at the beginning of the month and use that to pay 3 or 4 purchase invoices for the month or purchase invoices for several months. This could occur if you took advantage of a particular positive exchange rate movement.

Also your adding of multiple exchange rates at “the date of each Purchase Invoice” and “based on our actual purchase of forex” for the one transaction is adding complexion.

Manager has 100’s of users doing importing of inventory exactly as you are and they aren’t running into these issues as you are, many just set an exchange rate and don’t adjust it unless its really warranted.

I think your ambition for costing precision is leading you down this path, but as illustrated that precision becomes distorted when that same inventory is re-purchased and becomes average cost. Also, costing precision can be more so for a spreadsheet programme rather then an accounting software, plus exchange rate fluctuation can be a factor within that costing precision.

@mauroskov

What do you think?

Well if we go back to the start of this topic you wrote “All seems to work out well. I have added my purchase invoices in 3 different currencies and all my exchange rates for the dates of the purchase invoices” - Excellent, stop there as no other exchange rate need to be considered but also consider:

Where I wrote “Create a mid rate for a period” where you responded with “This would be helpful” - especially where you wrote “different purchase invoices with the same currency and same date”.

Plus consider doing bulk funds transfer rather then per purchase invoice transfer.

Just remember, every time you enter a new exchange rate a re-valuation of other transactions can occur due to timing issues.