Sage 50 CA Premium -- error in how Sage 50 calculates balances re home currency vs foreign (USD) currency?

SOLVED

We have a USD account for Paypal. We enter the relevant date and exchange rate in the list used for that purpose (under Settings / Company / Currency). For example, at 30 Dec 2016, the Bank of Canada rate is C$1.3427 = USD$1.00, and in the FX list, we enter "1.3427" with the appropriate date.

In the general ledger display for the account, when the display is set to "Balances in the currency of each account", the DR and CR transaction entries, and the running account balance shows correctly (ie, in the USD actual amounts).

But when the GL display is set to show "Balances in Home and Foreign Currencies" [or in just the home currencies], then:

(a) although the DR and CR amounts are shown (in CDN) correctly -- that is, a 30 Dec 2016 transaction for USD$20 would show in the appropriate DR/CR column as 20 x 1.3427 = CDN$26.85 --,

(b) the running balance (which in that setting is shown in the home currency of CDN) reflects calculations being done with completely different (and reversed!) FX rate. And the balance sheet displays the same error. That is, the math of the DR/CR is being done correctly, but the beginning and end balances in allegedly the home currency

For example, when the GL is set to "Balances in currency of each account", the correct USD end balance is shown (say, USD$500). But in the home currency, the balance is shown as CDN $266.54! Which reflects an exchange rate of USD1.8759 = CDN1.00. [ie, the USD is worth less than the CDN]

And if we adjust things so that the home currency balance [when adjusted for the FX rate] works correctly, then the USD balance shown [when that display option is selected, whether the GL display, the balance sheet, or the trial balance], is wrong -- again, by some weird adjustment factor that bears no relation to the data in the FX list.

I suppose the work-around is to adjust towards the correct home currency balance sheet amount [using manual verification], even though it means the USD amount displayed on the balance sheet / trial balance [when that display is selected] will be wildly wrong.

  • 0
    verified answer

    dlmoir said:
    the running balance (which in that setting is shown in the home currency of CDN) reflects calculations being done with completely different (and reversed!) FX rate. And the balance sheet displays the same error. That is, the math of the DR/CR is being done correctly, but the beginning and end balances in allegedly the home currency

    The running balance of a bank account in the 'home' currency must be periodically adjusted (with a manual journal entry), in order to book any net foreign exchange gain / loss at the end of each period.

    One way to correct the CAD amount in a USD account, for example is to post $1.00 between the foreign bank account and Exchange Gain (Debit Exchange Gain for Loss, Credit for Gain) at an exchange rate equal to the difference plus $1, then post the reverse of the entry at an exchange of 1.000

    In other words, to reduce the CAD amount by $123.45, debit $1.00 to Exch Gain, Credit $1.00 to the foreign bank, at the rate of 124.4500, then do the reverse at 1.0000.

    The process can not be automated, since the accounting software can't know when you're done with a month.  I usually post an entry when doing the bank rec.

    I hope that helps, and it's what you were after.    Please post back!

    Randy Wester

  • 0 in reply to RandyW
    Hi, Randy,

    Thanks for the procedure. Interesting. Never would have thought to mess with the FX rate like that.

    1. Your procedure works to correct the home currency balance (CAD for us) for the particular USD (bank) account at any given date. (In my case, I needed to adjust a start date balance.)

    2. And as a good thing, the procedure doesn't affect the DR/CR transactions that are entered between any given start and end date.

    3. In my install of Sage 50, though, I was still left needing to do a period end adjustment to the home currency balance. (Which is fine, and probably is to be expected; I just used your procedure again.) This achieves the main goal of having a correct home currency balance show for the account on things like the balance sheet and trial balance.

    4. What your procedure doesn't affect (in this particular company in my install, at any rate -- it's a really old database, and who knows what has been done "under the hood" over the years) is the USD balance that Sage 50 shows when the "display foreign currency" setting is chosen. On my particular numbers, the home currency balance shows correctly as CAD$864.40, which per the Bank of Canada year end FX rate at 1.3427 should give USD$634.78, but Sage 50 insists on showing the USD balance as USD$999.52.

    Which is a Sage-generated FX rate of US1.1563 = CAD1.00 -- and is (so to speak) upside down and backwards from real life.

    [Per your adjustment procedure the final rate in the FX table maintained thru Setup/Settings/Company/Currency is 1.0000. But updating that rate in the FX table to the correct period-end rate does not change the result of the wrong USD amount.]

    My conclusion is that in generating the running balance amounts, Sage 50 must be pulling an FX rate from some place other than the user-accessible list of FX rates.

    5. Interestingly, when I pull the report for "unrealized exchange gain/loss" (first time I've ever noticed that one), Sage 50 applies the wrong USD amount of $999.52 to the correct year-end rate from the FX table (of 1.3427) to show a "revalue amount" of CAD$1,342.06, from which it subtracts the correct CAD end balance of $864.40, to calculate the (wrong) unrealized gain.

    So clearly that report gives primacy to the USD balance for one's USD account. Which suggests that one should adjust a foreign currency account so that it gives the correct balance when the "display foreign currency" setting is chosen. And let the home currency chips fall where they may.

    I suppose I could insert a different period end FX rate to get the correct result for this one account (ie, so that the revalue rate was equal to the original value), but at the cost of making the foreign currency AR item(s) we have display wrongly.

    6. And when the balance sheet display option of re-value foreign currency accounts is chosen, Sage 50 gives one the choice of setting the FX rate [with the default rate per your FX table], and then re-values the home currency amount per the FX rate (which on my numbers uses the correct home currency balance of $864.40, and multiplies it by the correct FX rate of 1.3427, to give me the wrong answer of $1,3xx. And using the reciprocal for the FX rate (0.7448) also gives a wrong answer ($7xx something, when a hand calculator does the math correctly at $643.81).

    So I guess the answer is to work with home currency when it comes to final balance sheet amounts.

    Regards.
  • 0 in reply to dlmoir

    dlmoir said:
    What your procedure doesn't affect (in this particular company in my install, at any rate -- it's a really old database, and who knows what has been done "under the hood" over the years) is the USD balance that Sage 50 shows when the "display foreign currency" setting is chosen. On my particular numbers, the home currency balance shows correctly as CAD$864.40, which per the Bank of Canada year end FX rate at 1.3427 should give USD$634.78, but Sage 50 insists on showing the USD balance as USD$999.52.

    It's not meant to affect the foreign currency balance.  It's specifically meant not to.  It's not really my procedure, it comes from an old Simply Accounting technical document, and that document should really be in the online program help.

    dlmoir said:
    So clearly that report gives primacy to the USD balance for one's USD account. Which suggests that one should adjust a foreign currency account so that it gives the correct balance when the "display foreign currency" setting is chosen.

    Yes, All of the entries in a foreign currency account are posted in the denomination of the foreign currency.   It's how you reconcile the account - when working in the foreign currency's bank reconciliation screen, you work entirely in that currency (except for posting bank charges, where you need an exchange rate, etc.) 

    If the statement shows $634.78 USD and the G/L report in the USD account currency shows $999.52, it's wrong, and that has to be fixed first, before touching the home currency amount.  Other than correcting entry errors and bank charges, there are no adjustments to the foreign currency itself.  

    Foreign Currency accounts work like two separate adding machine tapes, but with each transaction affecting the two tapes differently.  All the transactions' foreign currency amounts are certain - you receive $1, it goes into the account as $1.  But the home currency CAD value of that amount fluctuates, and the sum of all the random variations for all of the entries almost never equals the random value at period end.     Any variance in the foreign currency (USD) value will always need adjustment.  

    dlmoir said:
    Sage 50 applies the wrong USD amount of $999.52 to the correct year-end rate from the FX table (of 1.3427)

     It's not Sage 50 using the wrong amount, it's using the amount you told it to use.  When you check the 'revalue' box, Sage 50 calculates what the home currency value would be after adjustment.  If it's unchecked, you just get the sum of all the home currency amounts, and the foreign amount is ignored. 

    If each US dollar put in a bank account, or taken out during the month doesn't show up as one US dollar shown on a statement at the end of the month, there is an error in the recording of the US dollars - just as in the home currency CAD accounts, you need to find and fix those discrepancies FIRST. 

    For foreign currency bank accounts, do the foreign currency bank reconciliation, save it when complete, then run the G/L reports in the home and foreign currency, then adjust the home currency to its' period end value, then run the two reports again to double check.   Then finish the reconciliation by clearing the dollar in and dollar out and post.

    For foreign currency A/P or A/R amounts, the adjustments are recorded automatically as the amounts are paid / received.   

  • 0 in reply to RandyW
    Finally got it figured out. Thanks to all who replied. In summary, here is the "how to":

    1. With the foreign currency account, use the general journal, selected for the foreign currency, to adjust that balance to actual. In my case, I offset to the FX gain/loss account on the income statement. After this adjustment, when the display is selected for foreign currency, this will show the correct foreign currency balance.

    2. Then, in order to affect what the home currency displays for that account, use Randy's 2-step "fake rate" adjustment procedure. This procedure makes each side of the account (foreign currency -- home currency) talk to each other. It has nothing to do with the list of FX rates that Simply maintains (you'll be setting a rate, but then immediately overwriting it).

    a. Outside Sage, find the correct FX conversion rate at the applicable date.

    b. Use that rate to calculate the correct "should be" home currency balance based on the correct foreign currency balance shown in Sage.

    c. Using the "display home currency" settings, have Sage display the current balance (in home currency) of account in question.

    d. Using the amounts from b. and c., calculate the amount of (home currency) adjustment needed, and figure out your adjustment entries per next.

    e. Then use Randy's step 1 adjustment procedure: in the general journal, choosing the foreign currency in question, enter 1.00 for both your DR and CR and in the rate box enter (1 + amount of adjustment needed). [In my case, I needed a home currency adjustment of, say, $450.34, so I put 451.34 in the rate box.] This will affect the actual foreign currency balances by 1.00 only, but affect the home currency balance by 1 x [what is shown in the rate box].

    f. Finish with Randy's step 2 adjustment procedure: in the general journal, choosing the foreign currency in question, enter 1.00 for the DR and CR items [opposite to what you did in step 1] and enter 1.00 in the rate box. You'll be asked if you want to save that new rate; answer yes.

    Now the balances will show correctly when the display all currencies setting is chosen.

    g. Doesn't hurt to now enter the correct FX conversion rate for the chosen period end date. (And the unrealized gain/loss report will now work, too.)

    Don
  • 0 in reply to dlmoir

    dlmoir said:
    use the general journal, selected for the foreign currency, to adjust that balance to actual. In my case, I offset to the FX gain/loss account on the income statement

    Generally, FX gain/loss would never affect the foreign currency balance, so it might look unusual to an auditor.   

    If Bank Reconciliation is set up a transaction difference or missed entry is fairly easy to find and correct.  If it's a non-material amount and happened in a prior, closed period, bank charges is as good a place as any.  

    dlmoir said:
    Doesn't hurt to now enter the correct FX conversion rate for the chosen period end date. (And the unrealized gain/loss report will now work, too

    If you remember to not update the FX rate, Sage will keep the last rate for the period.  But it's really easy to miss the right button and not notice, so this should be done every time, even if we think we haven't updated the exchange rate to 369.5200 or 1.0000