• From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Topic: Foreign currency transactions

Hello,

I'm trying to setup FrontAccounting to deal with our situation. We are a Canadian business with both Canadian dollar and US dollar bank accounts. Most day to day activities are done in the Canadian account, no problem. We do collect payments from customers in US dollars and which are deposited in our USD account. I've set up the currencies in the "Currencies" section, the USD bank account, the customer currencies and set up the "Exchange Variances Account" to be our "Realized Exchange Gain/Loss" account on the "System and General GL Setup" page.

Maybe something is wrong but this is what happens when the trasactions take place.

Let's say a customer orders $100 USD worth of items and the exchange rate is 1USD = 1.20CAD. Everything is fine when the invoice is entered, the journal entry report shows Sales of $120 CAD and Accounts receivable of $120 CAD. Let's say a month later the customer pays the $100 USD but the US currency has depreciated such that 1USD = 1.10CAD.  The journal entry report will show the USD Bank debit of $110 (in CAD) and the Accounts receivable will decrease by $110 but will still show $10 remaining in Accounts receivable.  In other words, $10 CAD in Accounts receivable even though there really is no accounts receiable because the full invoice of $100 USD was paid.

Finally, let's say the exchange rate changed again to 1USD=1.05CAD when the $100 USD cash is transfered to the Canadian bank account using the "Bank Account Transfers" page. The journal entry report will then shows the $105 credited from the USD bank account and the $1.05 debited to the CAD bank account. However, a balance sheet report shows $5 CAD worth left in the USD bank account. A bank account inquiry reort for the USD bank account shows $0 as the ending balance in the USD bank account. So we have one report (balance sheet) saying there's $5 CAD worth of money in the USD bank account and another report (USD Bank Statement) saying there is $0 in the same USD bank account.

To summarize, when it's all done, the Balance Sheet shows:
- CAD Bank Account $105 (fine)
- USD Currency Bank $5 (this should be $0)
- Accounts Receivable $10 (this should be $0)

The "Profit and Loss Statement" shows:
- $120 in sales revenue
- nothing ($0) in the "Realized Exchange Gain/Loss" account.

I'm not sure if or what I'm doing wrong but any help would be greatly appreciated. Sorry for the long post and if it needs clarifying, please let me know.

Thanks in advance.

MozMan's Website

2 Reply by t2webby (edited by t2webby 12/19/2008 06:47:35 pm)

  • From: Canada
  • Registered: 06/04/2008
  • Posts: 72

Re: Foreign currency transactions

Hi, I too run a canadian company and have been testing FA for a month now so maybe we can help each other out.

Ok so it seems like you have two accounts right. A Canadian Account and a USD account. Do you usually apply payments to from your USD customers into your USD account? Or mix and match?

The gain/loss will only recorded at the point where you apply an allocation of the payment to the invoices. Did you do this first? Cause when you apply the payment, the gain/loss doesn't get posted yet and the A/R , A/P will not get adjusted until an the actually invoice is allocated. Let me know if you've done this yet.

  • From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Re: Foreign currency transactions

Thank you very much for your reply t2webby.

That's right, we have two bank accounts, one in CAD and one in USD. Most of our customers are in the US and pay in USD which we deposit in our USD account. Most of our expenses are in CAD so at some point we transfer some of the USD funds into our CAD bank account.

Initially, I didn't "Allocate Customer Payments or Credit Notes" because I'm not quite sure what the purpose of this is. Maybe you could enlighten me? However, now that I have done it, the financial statements (balance sheet & profit/loss statment) remain unchanged.

I tried a new series of transactions with fluctuating exchange rates, similar to the previous ones. This time I allocated the customer payment after the payment was entered but the financial statements are the same as before. Nothing is posted to the gain/loss account and the balance sheet shows amounts still in Accounts Receivable and some money in the USD bank account while the bank statement report shows $0 in the USD bank account.

Could it be something wrong with my settings somewhere? I went though them carefully but maybe I missed something? Any other clues?

Thanks again.

MozMan's Website

4 Reply by t2webby (edited by t2webby 12/20/2008 06:01:48 am)

  • From: Canada
  • Registered: 06/04/2008
  • Posts: 72

Re: Foreign currency transactions

When you make a payment to a US customer, how are you doing it? Do you apply the payment of your US customers to your USD account directly? If so... is this usually payed at par so that the difference is put against the gain/loss account?

From what I know our USD bank account GL always shows the exact amount  of USD on the balance sheet, any conversions differences would be put against the gain/loss. So in order to achieve that. Try changing it so that when you apply the payment, go to the bank and general ledger setup => Go to the exchange rate and for the day of payment make the exchange rate 1.00. Then go back and make the payment for the US customer to the USD account, this will debit USD bank and credit A/R for the USD amount. Now here, there's still a difference left from the exchange rate left by the invoice initially, so by allocating it against an invoice, it will make the adjustment and credit/debit the A/R accordingly and post it to the gain/loss of the differences. 

The main purpose behind the allocation is for the system to know which invoice to based the payment from, this is where it would knows to look for the exchange rate information at the time invoice so that it knows what adjustments to make to correct the A/R due to currency conversions made initially from a US invoice created. Cause remember, it converts it to home currency when posting to the A/R and sales, so when you pay, it should correct itself.

Ideally, when we pay a foreign account using the respective foreign bank, we should be able to change the rate as well in this case. If this works for you as well, FA should make this small change to allow people to post at par when they are applying a payment to a bank account that is the same currency as the customer. Cause in our case it makes more sense. This works the same way for Suppliers with A/P as well.

Let me know if this works for you.

  • From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Re: Foreign currency transactions

Thanks again t2webby. I've tried what I think you suggested.

1) Created a sale and invoice. In my example, I made a sale of $100USD to a US customer at $1.00USD=1.20CAD

2) Changed the currency rate to $1.00USD=1.00CAD and applied the customer payment.

3) "Allocated" the full amount of the payment $100USD.

4) Changed the currency rate to $1.00USD=1.10CAD (as an example of the rate changing when bank transfer takes place) and made a bank transfer of the $100USD to the CAD bank account.

Still no luck getting anything to show up in the "Realized Exchange Gain/Loss" account. Balance sheet now shows (negative) $-10.00 USD bank balance and an account receivable of $20. USD bank statement shows $0.0 as a balance.

Here are screenshots of the transactions which might help understand my post:

Journal entry report:
http://www.freeimagehosting.net/uploads/th.d34d6ed480.gif

Customer allocations:
http://www.freeimagehosting.net/uploads/th.35de28fe50.gif

Balance sheet report:
http://www.freeimagehosting.net/uploads/th.617e7376f4.gif

Profit and loss report:
http://www.freeimagehosting.net/uploads/th.ac477fd097.gif

USD bank statement:
http://www.freeimagehosting.net/uploads/th.c121b6469a.gif

My System and GL setup:
http://www.freeimagehosting.net/uploads/th.31f3d5141b.gif

Thanks again for your time, greatly appreciated.

MozMan's Website

  • From: Canada
  • Registered: 06/04/2008
  • Posts: 72

Re: Foreign currency transactions

Hmm strange that it didn't put into the gain/loss account at the point of allocation. Are you paying a US customer using your USD account?

7 Reply by MozMan (edited by MozMan 12/22/2008 09:02:02 pm)

  • From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Re: Foreign currency transactions

Hi,

In this example what I did was make a sale to a US customer and billing in USD (at $1.00USD=1.20CAD). Then the payment from the customer is in USD and put in our USD bank account (at $1.00USD=1.00CAD as per your suggestion). The $100 was then "allocated". Finally, the USD funds from our USD bank account are transferred into our CAD account (at $1.00USD=1.10CAD).

You asked "Are you paying a US customer using your USD account?" The answer is:
1) we are billing the customer in USD
2) collecting the payment from the customer into our USD account
3) transferred the collected USD funds to our CAD account

In practice, the three above steps could take place on different dates with different exchange rates. This is what I'd like to figure out how to accomplish properly.

Please let me know if I should clarify further.

Thank you again!

MozMan's Website

  • From: Canada
  • Registered: 06/04/2008
  • Posts: 72

Re: Foreign currency transactions

ok, so with this setup, at the point of allocation, the gain/loss should of been debited/credited based on the invoice's exchange rate amount. After you allocated the payment to the invoice, did you look at the GL transaction of the payment? Did the differences go to the gain/loss? Try invoicing it at a different date and then paying it at a different date and see as well.

  • From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Re: Foreign currency transactions

Thanks for the help. Using different dates for each exchange rate has let the difference in currency value between the dates be posted to the "Realized Exchange Gain/Loss" account" when the customer payment is allocated to the invoice. This is great!

One problem remains. It is when funds are transferred from the USD to CAD bank account on a day that the exchange rate is different from the day the USD funds were deposited.

Let's say there is $100 USD deposited into the USD account on January 1st and the exchange rate is $1.00USD=1.20CAD. The deposit could be from a sale, a loan or whatever. The Balance Sheet will show $120 CAD worth of cash in the USD bank account. The USD "Bank Statement" report will show $100 USD in the account. This makes sense.

Let's say that a day later, on January 2nd the $100USD is transferred from the USD bank account to the CAD bank account. However, on this day the exchange rate is $1.00USD=1.10CAD. The USD bank statement report will show a $0 balance which is correct because the USD was transferred to the CAD account. The balance sheet however still shows $10CAD worth of money in the USD bank account. How could it be that the USD bank balance shows two different totals ($0 and $10) depending on which report you generate? Maybe there's a step I'm missing? Do I have to use the same exchange rate for both dates and then manually credit the CAD account and debit the "Realized Exchange Gain/Loss" account?

I'd love to figure out the proper way to transfer money between accounts and have everything match up. Any help would once again be appreciated.

MozMan's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Foreign currency transactions

Hello, I think I must enter this discussion.
You made a deposit into the USD account, $100, and the balance sheet shows 120 CAD. Everything ok.
On the next day you make a transfer of 100 USD into the CAD account with this day exchange rate 110 CAD. Everything is ok. FrontAccounting doesn't know anything of the relationship between these transactions. The USD account reports 0 USD as a balance which is correct. But the balance sheet shows 10 CAD still there in the USD account. This is calculated based on the GL transactions. The first transaction was 120 CAD and the second one was 110 CAD. Due to that there are no relationship between the two transactions this would show up.
In this case the 10 CAD is really an exchange variance, but FA wouldn't know. On the other hand the balance sheet for the CAD account should show an evened out responding to the 10 CAD in the USD account. So the total for the cash/bank would be ok in domestic value.
I am not sure of how to solve this in a better way because of the missing relationship between the transactions.
A way around this would be to allow entrance of the exchange rate in the Bank payments/deposit/transfer forms so you can use the origin exchange rate when doing the transfer, but then again you have to remember the exchange rate.

When you use the customer/supplier payments the situations are eliminated due to the relationship between the transactions during allocation.

/Joe

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Foreign currency transactions

Janusz and I have found a solution to this. The last transaction where a foreign account is involved it will update the GL account and put differencies on exchange variations. The CVS will later be updated.

/Joe

  • From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Re: Foreign currency transactions

Thanks again for the help. Great that you've found a solution to the matter of the bank transfers.

Sorry to bother you again but I'd like to ask where I will be able to find the files to update? I found the CVS repository (https://frontaccounting.cvs.sourceforge.net/viewvc/frontaccounting/) but I don't know which files to replace.

I ask because we need this functionality before we go live with Frontaccounting as our accounting system.

Thank you!

MozMan's Website

13 Reply by t2webby (edited by t2webby 12/23/2008 09:45:19 pm)

  • From: Canada
  • Registered: 06/04/2008
  • Posts: 72

Re: Foreign currency transactions

It doesn't appear that its been updated yet. They will probably have an update soon.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Foreign currency transactions

The CVS repository (MAIN) is now updated. Affected file: /gl/includes/db/gl_db_banking.inc.
Don't be afraid when you see a greater exchange variance in the GL the first time. FA will sync the total balance in accordance with the respective foreign account the next time you make a bank /payment/deposit/transfer entry (no supplier/customer, they will be handled during allocations).

FA will always sync the GL account when entrying bank deposits/payments/transfer. This is done by comparing the foreign account balance multiplied by the rate of the entry date, and the GL account balance .

/Joe

  • From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Re: Foreign currency transactions

Thank you all very much! I've found the file and will try it. It really helps to have such a helpful forum. I will try to contribute to it regularly.

MozMan's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Foreign currency transactions

For easier navigating the General Ledger for Foreign Currency Accounts, we now stamp the memo field with 'Exchange Variance'.
CVS updated. Same file /gl/includes/db/gl_db_banking.inc

/Joe

  • From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Re: Foreign currency transactions

I've conducted some test transactions and can say that it looks like everything works perfectly. Thank you again!

MozMan's Website

  • From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Re: Foreign currency transactions

Hello,

Congrats for the release of 2.1RC. It seems the foreign currency transactions no longer work as they do in 2.0.7. I've conducted some tests again and an incorrect balance shows for the foreign currency in the balance sheet.  The "Exchange Variance" account seems off also.

Below are the journal entries for the same transactions with the same exchange rates for the two different versions so that you can see what I mean:

2.0.7
http://img99.imageshack.us/img99/7064/s … entrie.jpg

2.1RC
http://img217.imageshack.us/img217/7064 … entrie.jpg

2.1B2 has the same problem as 2.1RC

If you need any additional information from me, please feel free to ask.

Kind regards.

MozMan's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Foreign currency transactions

Oops!

Thank you so much for finding this bug. We did some change in the Bank Account and transaction tables to release 2.1.0, and this bug slipped through.
I will be back when the CVS repository is updated.

/Joe

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Foreign currency transactions

Hello again,
Now this is fixed. The CVS repository is updated. Only one file affected. /gl/includes/db/gl_db_banking.inc

Thanks again MozMan

/Joe

  • From: Canada, London, Ontario
  • Registered: 11/10/2008
  • Posts: 30

Re: Foreign currency transactions

Thanks for fixing it so quickly. It's working well again.

MozMan's Website