How do you manually zero out or adjust CAD balance in USD account ?

SOLVED

Similar too this post - it seems Sage does an automatic transaction if it finds a 0 balance at year end because it is quite obvious that if an account has $0 US funds in it, it must equate to $0 CA funds...
     https://sagecity.na.sage.com/support_communities/sage_50_accounting_ca/f/157/t/66784

What are the journal entries to do this manually - zero out at any time before year end ?
i.e. We periodically transfer our USD PayPal balance to either CAD PayPal or withdraw the funds to our bank acct, leaving $0 in USD PayPal. After so doing, for example, the PayPal USD account in Sage shows as having a balance of $0 in USD but is showing CAD balance $127.52 Dr. We would want it to show $0 in CAD.

What are the journal entries to do this manually - adjust at year end when there is a non-zero balance so that USD equates to a proper CAD amount on reports showing both currencies. i.e. If at year end, we will leave a balance of $104.95 in the USD PayPal account to cover an outstanding payable, it is showing $543.03 in CAD as the balance - awkward/wrong since the exchange rate is not 500%+, we would want to have our year ends show in the neighbourhood ~$145 CAD per Dec 30 rate.

I am a bit nitpicky with this, I would actually like to do this second type of adjustment monthly when I reconcile all accounts. ... have the month end balances in USD and CAD looking correct per the current month end exchange rate.

  • 0
    SUGGESTED

    JJD said:
    it seems Sage does an automatic transaction if it finds a 0 balance at year end because it is quite obvious that if an account has $0 US funds in it, it must equate to $0 CA funds

    If you choose to revalue your balance sheet at a different exchange rate, $0 USD will display as $0 CAD:

    JJD said:
    What are the journal entries to do this manually -

    I don't think there is anything that automatically creates journal entries, so this is the only, manual way:

    https://sagecity.na.sage.com/support_communities/sage_50_accounting_ca/f/148/p/100591/280104#280104

    There is an official document somewhere that was written by Sage support, and there may even be something in the help.  I wrote that 10 days ago, without bothering to search.

    JJD said:
    I am a bit nitpicky with this, I would actually like to do this second type of adjustment monthly

    I usually set it up as a monthly, recurring, G/L transaction.   IFRS rules may only require it at year end, and perhaps only if there is 'significant impairment' of the asset / liability.

    I hope that helps, please post back.

  • 0
    verified answer
    JJD

    Yes it is true that at year end if there is a USD balance of $0.00 and a CAD balance not $0.00, the program will trigger an internal adjusting entry to zero the CAD balance. Because of this I usually teach at the consultant level a way to trigger a year end without following through with it to alter the balances, but it only works under certain circumstances, so I do not recommend that method for any normal use as you have suggested.

    The method I use to make this adjustment is different than Randy but should give the same results and not affect the bank rec in any way either.

    I prefer to be explicit in my entries. Therefore I will post two entries with the same date using the USD currency in the General Journal

    Let's say I have a US$100 that is a CAD balance of $200. But the real exchange rate is 1.3 so the real CAD balance should be $130.

    I will:

    Dr Currency Exchange and Rounding Expense US$100
    Cr USD bank $100
    Exchange Rate 2.0

    CAD equivalent is $200 and zeros the account for both CAD and USD - check your journal entry report before you post and the General Ledger report using both currencies after you post.

    Second entry
    Dr USD bank $100
    Cr Currency Exchange and Rounding Expense $100
    Exchange Rate 1.3

    This brings the USD balance to $100 again (no change) and the CAD balance to $130. The bank rec will see $100 on both the deposit and withdrawal sides on the same date with the comment that it was an exchange rate adjustment. The CAD$70 is left in the expense account.

    As Randy mentions, two recurring entries can be set up to handle this and make sure you review the journal entry report before you post so you can see both the CAD and USD equivalents. After posting you will see clearly in the general ledger report for that bank account that the balance goes to zero and back up again.
  • 0 in reply to Richard S. Ridings
    Thanks Richard, I like your method better than the Sage recommended $1 at 49,342.61:1, then reversed at 1:1 thing.
  • 0 in reply to RandyW

    Revalue only affects the balance sheet at that point in time? It's taking the balance in the currency of each acct and calculating the CAD amount on the fly, and 0 times any rate would be 0.

    Whereas other reports are showing CAD balances as they were at the time each entry was set. Are those being calculated on the fly as well using the default rate for each day? Or is it presenting the stored data from each entry. Can we systematically go through and revalue each day by day with the then current rate? Will that cause general ledger amounts to be more reasonable? Or would we have to adjust each entry which should have had an exchange rate?

    I am looking at general ledger at the PayPal USD account for the whole year in 'balance in currency of each account'. I can look down the Balance column and pick out easily which dates it was emptied, it shows - in balance column. If I switch to "balances in home and foreign currency" (more accurately "[balance in home] and [transaction amounts in home and foreign] currency" ) there is never any - in the balance column. Numbers are way off in CAD.

    I guess this is because we do not enter a rate when the entry only affects USD accounts ? ie we pay a US vendor from PayPal USD, we leave the exchange rate at 1.00000 because in our mind there's no exchange involved. Should we always enter a rate when working with USD ?

    Our "default rate for this day" are largely 1.0000 - we've been anwering no to saving it and just entering the rate on each transaction.

  • 0 in reply to JJD

    JJD said:
    Revalue only affects the balance sheet at that point in time?

      'Revalue' only affects the value displayed / printed, and the 'Unrealized Gain / Loss on Exchange' amount displayed.   There's no effect on the records.

    JJD said:
    I guess this is because we do not enter a rate when the entry only affects USD accounts ? ie we pay a US vendor from PayPal USD, we leave the exchange rate at 1.00000 because in our mind there's no exchange involved.

    There is no currency exchange, but the accounting is in CAD$ and the money you're moving is not.  You need to enter an Exchange Rate to calculate the 'home currency' value of the transaction.   Just as you wouldn't treat 100 Mexican Peso as equivalent to $100 CAD, you shouldn't record U.S. exchanges as if they are at par.   Canadian and U.S. currency are both called a 'Dollar' but that doesn't mean they are the same thing.  Maybe if we referred to each U.S. Dollar as a 'Trump'?

    JJD said:
    Should we always enter a rate when working with USD ?

    Yes.  The CRA has some guidance on which rates are acceptable to use.   We use the Bank of Canada Noon Rate, because here in Alberta it's available shortly after 10:00 AM.  I believe the CRA will allow using one rate for the entire month, which is less accurate but much simpler.

    JJD said:
    Numbers are way off in CAD.

    You can't expect the CAD balance to be correct because the exchange rates for all the transactions will vary.  If you use a mixture of 'real' and '1.0' exchange rates, you might be off by a lot more.  The accounting between all the income and the foreign exchange 'comes out in the wash', so to speak, in the final net income - but in order to tell whether you're making / losing money on sales, versus on good / bad decisions about when to buy or sell foreign currency, you have to record each transaction at a reasonable exchange rate.

    As I understand it, the accounting rationale for recording each transaction at the current exchange rate is that any exchange variation after that time is part of future exchange risk of not exchanging the currency into the CAD home currency, so therefore any prior rate variation has to be recorded as a permanent transaction because it's in the past.  It happened, it can't be changed now, it's a done deal.  The moving finger writes, and having writ moves on, and all that.  But to hold cash in a foreign currency, or a foreign currency receivable, or debt, is to take a risk on the exchange rate.

  • 0
    Thank you folks, very helpful. We'll be able to tidy up year end and be diligent with entering rates starting January 1st.
  • 0 in reply to JJD
    JJD,

    You should be aware that we have only been talking about altering the balance of the bank-type accounts. If you have any unrealised A/P or A/R you should run the Unrealised Gain/Loss report (with the appropriate exchange rate) and then post an actual general journal entry to a separate account for exchange rate differences. Most of us create an account under A/R (Assets) and another account under A/P (Liabilities) for Foreign Exchange differences (as applicable). Your year-end accountant will likely give you an entry to do this if you don't but I have seen some accountants suggest it be posted to Accounts Payable for example. This can be done in Caseware (tax software) but not in Sage 50.