The MacGyver-esque approach to tracking foreign customer deposits in QuickBooks Online

The MacGyver-esque approach to tracking foreign customer deposits in QuickBooks Online

Remember our old friend Craig of Craig’s Design and Landscaping Services? He’s tried his hand at landscaping, used car sales, and computer accessories. Last month, he decided to give construction a whirl, but he consulted his colleagues about best practices and he learned how to ask for, and track, customer deposits.

Guess what? Craig’s Construction is doing so well because he’s using it to track customer deposits that he’s decided he’s going to go international and sell in Canadian dollars to Canadians. He wants to sell to them in Canadian dollars, so that his Great White North customers aren’t put off by exchange rates.

He’s going to ask for upfront deposits from the Canucks, too, and these deposits are also going to be in Canadian dollars.

So, he’s asked me to help him set up and use Canadian dollar (CAD) deposits. Once I realized he wasn’t kidding, and he’d be tracking accounts receivable in CAD as well, we turned on multicurrency in QuickBooks® Online (QBO). I warned him first, “Craig, you know we can’t turn off multicurrency.” He was adamant, so we went into account & settings > advanced > currency and we turned on multicurrency:

Next, we set up the account for CAD customer deposits with the account type other current liabilities (and detail type trust accounts – liabilities), much the same as the regular customer deposits account. But, for this account, with multicurrency turned on, we defined it as a CAD Canadian Dollar account.

(This is an advantage that QBO has over QuickBooks Desktop: you can define any balance sheet account in foreign currency, other than equity accounts in QBO. QuickBooks Desktop, on the other hand, allows only bank accounts, credit cards, A/R, and A/P to be defined in another currency).

And, naturally, since he wants to use CAD customer deposits on sales forms, we also set up the product/service item customer deposits CAD to map to that liability account and made it non-taxable. Craig is off to the races.

Now, Craig is starting to do business with Canadian customers, offering them Canadian dollar pricing.

He got his first Canadian currency customer, Ryan Reynolds, and we set him up with the correct currency using the payment and billing tab in the customer information window:

He issued an estimate to Ryan for $750,000 CAD on January 7:

Ryan accepted the estimate and Craig took $50,000 CAD from Ryan on February 1, and recorded it as a sales receipt in CAD:

Yes, we know that there’s a rate of exchange and $50,000 CAD is worth far less in USD on that date due to the rate of exchange in place. But, keep in mind that with multicurrency turned on, QuickBooks tracks both the home currency (USD) and the foreign currency (CAD) amounts.

After saving the sales receipt, if we click on more > transaction journal, we can see the behind-the-scenes debits and credits for this sales receipt. Unfortunately, the journal displays only the home currency values (USD) by default, but we can customize it to display more columns, including the foreign debit and foreign credit (another advantage of Desktop, which can only display the foreign amount column), exchange rate, and currency:

Craig used up the entire deposit paid by Ryan Reynolds on the very first progress invoice he issued to him on February 15:

Using the same methodology that we used when reporting on the home currency’s deposits, we drilled down on the balance sheet (all dates) figure for CAD customer deposits. It’s expressed in USD (and, in fact, there’s a $0 CAD balance, but strangely enough the balance in USD is $43.50 due to the exchange rate difference between the two transactions), so we should revalue the CAD deposits by customer account. Most of the time, this is done at a period end, but the revaluation can be performed at any time:

That zeroed out the CAD customer deposits account in the home currency as well (note: the resulting journal entry from revaluing does not allow for attaching a customer name), but we still want to report on the account in the original foreign currency.

Let’s go back the balance sheet for all dates. We drilled down on the CAD customer deposits figure of $0, and we now have to make some changes to the transaction report:

We’re not interested in home currency amounts on this report (although we realized we had to make a home currency revaluation so that the $0 CAD balance showed up on the balance bheet as $0 USD), so we made some customizations to this report:

  • Group by Customer
  • Filter for Customer = specified (when we revalue this account, the difference of $43.50 in our situation gets zeroed out but there’s no customer name, and we don’t want it to appear in our report)
  • Filter for Cleared Status = Uncleared
  • Add these columns:
    • Foreign Debit
    • Foreign Credit
    • Foreign Amount
  • Remove these columns:
    • Name
    • Account
    • Split
    • Amount
    • Balance
    • Revalued Exchange Rate
    • Revalued Amount
    • Unrealized Gain or Loss
  • Give this report a title, such as “CAD deposits by customer
  • Save the customization

There is, of course, one last modification to perform: reconcile the CAD customer deposits account. We can reconcile it as often as we need to do so, and the beginning and ending balances are always $0, regardless of the actual balance in the account:

We clear out all transactions that zero each other out (and we also clear the $0 transaction related to the currency revaluation because it’s confusing just sitting there):

The difference is $0, so we click on finish now. Now, run the saved report CAD deposits by customer:

And, instead of showing all of Ryan Reynolds’ transactions adding up to $0, they disappear from the report because of the filter of cleared status = uncleared.

Now, what happens if Ryan Gosling, another Canadian customer (no, we’re not all named “Ryan” up here), comes along and pays a $50,000 CAD deposit on February 18 and $45,000 CAD of it gets used up over the course of, say, two invoices?

Here’s what our saved customized report shows:

If Mike Myers (See? He’s not named Ryan) comes along and gives his deposit of $80,000 CAD, the report will look like this:

Two observations of note here are:

  1. We haven’t zeroed out (i.e., used up) the deposit balance for Ryan Gosling or Mike Myers, so we can’t reconcile this account for them and have them disappear form the report.
  2. Notice that at this point in time, QBO won’t total figures for any columns featuring foreign currency amounts (are you reading this, Intuit®?). So that’s why I put in the foreign amount column, which displays positive and negative figures. Then, the user must export to Excel and get the subtotals there.

Yes, we have no totals or subtotals. If you want to know the total of CAD customer deposits quickly, just look at the chart of accounts:

Or, open up the CAD customer deposits account register:

Or, export to Excel and bring in totals and subtotals. Again, Intuit, please fix this. Pretty please.

OK, that last part wasn’t ideal, but at least we can track foreign deposits (or trust funds) in foreign currency by customer in QBO, and we can report on them. All it takes is understanding how to report on unused deposits, how to alter that for foreign amounts, and two Ryan’s and a Mike. And me.