US exchange rate in reports

SOLVED

Hi, 

Can someone explain how do I make a monthly entry to adjust the US exchange rate in order to have the right information in the reports. I already use the exchange in each invoices, but I would like to adjust, monthly, with the Canadian Bank monthly rate in order to have the right information showing in the reports.

In the balance of verification my US account + my exchange account should equal the monthly rate X the balance of my bank statement but it doesn't seems to work Disappointed  The previous month changes the current month ... There is a detail that I don't get!

Thanks in advance,

Melanie

  • 0

    What I would do when I was using a foreign amount is I would take an average for the month and I would enter that average in the exchange rate field.  Then it would carry through for the month.  However I was doing entries retrospectively.

  • +1
    verified answer

    Foreign exchange reporting is a bit complex and different people recommend different options.  You have indicated you are using one method but wish to change it.

    First

    my US account + my exchange account should equal the monthly rate X the balance of my bank statement

    This would only work if you never have any outstanding entries at the end of the month on that bank account.

    This tells me that if every cheque/withdrawal/transfer/deposit clear your bank account and statement balance is $10000 at the end of Feb and the exchange rate is 1.38 then you have $3800 in another asset account for Foreign exchange on bank account and $10000 in your USD bank account.

    But you want $13800 CAD to show on your Balance Sheet with US$10,000 showing if you look at the General Ledger showing the balances in the currency of the account (or revalue your balance sheet with a 1 as the exchange rate).

    Let's say you currently have positive US$10,000 balance on the books at the end of Feb and the CAD equivalent is $13000 currently showing on the reports (rate of 1.3).  The real exchange rate is 1.40 for the end of Feb.  I'm using round numbers here but the real one's will likely have more decimals in the exchange rate, etc.

    Do a general journal entry in USD to:

    Debit Currency Exchange and Rounding Expense (your currency linked account) US$10000

    Credit USD bank US$10000

    Use the exchange rate of 1.3 because your reports showed CA$13000.

    This should zero both the USD and CAD balance of the USD bank account.  Look at the journal entry before you post so you can confirm the CAD value is going to go to zero.

    Then do another journal entry right after, posted on the same date (likely the last day of the month after all bank recs are completed).

    Debit USD Bank US$10000

    Credit Currency Exchange and Rounding Expense $US10000

    Use the exchange rate of 1.4 because that was the rate you wanted to use for the end of Feb in my example above.  You should now see CA$14000 on the balance sheet (without revaluing).  The difference stays in the currency exchange expense account and the same USD value both debits and credits the bank account so you can reconcile properly.

    To test, I would do this on a copy of your file first to make sure you get the results you are looking for and to get used to the technique and final reporting.