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

Poor Craig. He has changed the name of his company again after failing at landscaping, used car sales, and computer accessories. This time around, he is attempting to make his way in the world of construction. He’s done his homework this time, though; he’s asked successful construction company owners about best practices. Too bad he didn’t know more about how to track customer deposits in QuickBooks® Online. I’ll explain.

Craig now knows that he should be producing estimates (aka quotes or proposals) to his potential customers with an expiration date, so that no one can accept an old estimate some time down the road and expect the pricing to be the honored. He’s even turned on Progress Invoicing in QuickBooks® Online so that he can produce multiple invoices from a single quote, and get paid along the way. So far, so good.

He’s issued an estimate to Captain James T. Kirk on Jan. 1 for a series of services with a total of $755,000.

He’s specified an expiration date of Jan. 31, so the clock starts ticking and Jim Kirk can’t wait too long before accepting it.

Let’s say Jim accepts the estimate on Jan. 10. Craig changes the status of the estimate from Pending to Accepted and specifies the date of acceptance as well as Jim’s initials, and then he saves the updated estimate.

This step of changing the status of an estimate to Accepted isn’t mandatory to perform invoicing from an estimate, but the extra information is really nice to have. For example, this new information will populate the appropriate estimate-related reports such as the Estimate by Customer report:

Craig knows from his more experienced peers that he should get some money right away, because he’s going to have to spend a lot to get this project off the ground for Jim Kirk. So he’s going to do just that: Get some money up front, but first, he has to prepare the accounting for it.

Craig creates a Customer Deposits account on his balance sheet, and it’s an Other Current Liabilities type of account. These are funds he asks clients to pay up front before he has earned their money. In accounting terms, it’s often referred to as “unearned revenue” or “deferred revenue.”

Craig has chosen to set up this account as a Detail Type of Trust Accounts – Liabilities. He could have chosen another Detail Type (such as Other Current Liabilities), but he reasoned that this account is, in essence, a “parking spot” for client trust monies that he’s hanging on to for them until he earns the revenue.

Now that he has the general ledger account set up, Craig needs to set up a product/service item that is mapped to this account so that he can refer to it on sales forms. It is a service type of item.

Now Craig is ready to ask Jim Kirk for money to help cover his up front layouts of cash to work this job.

Craig issues a sales receipt to Jim and asks for $500,000 up front on Jan, 13. Jim gives Craig check #1234 on the spot for that amount:

Craig starts the work for Kirk and also starts spending money.

He’s reached a milestone in the project and is now ready to invoice for 50 percent on Jan. 20, so he’s using the progress invoicing option:

The invoice starts out looking like this:

However, Craig is going to apply some of the deposit funds he’s received from Jim Kirk to zero out the invoice. He’s added an extra line using the Customer Deposits product/service item, he added the text “LESS:” to the beginning of the description for that item, and, most importantly, put a negative sign before the amount of the deposit he’s using up.

The invoice has been zeroed out, but it still records the earning of income and the using up of part of the customer deposits, as we in the journal behind the scenes of this invoice:

You may ask how Craig knows that he has deposit money of Jim Kirk’s to use against some invoices (or how much), especially since he’s now taking deposits from numerous customers. Let’s take a look at the Customer Deposits account to create a report that tells Craig just that.

We start off by looking at the Balance Sheet (we like to start with All Dates) and clicking on the amount for the Customer Deposits account in the liabilities section:

Now, we’re going to choose to group the transaction report by Customer, and we’ll get rid of some messy columns we don’t want to see. We’re also changing the report title to “Outstanding Deposits by Customer.”

This is a great first step: notice that Jim Kirk had the $500,000 deposit from Jan. 13, and that’s what displayed as his balance when Craig ran this report prior to creating the Jan. 20 invoice. Then he used up $377,500 of the deposit on that invoice, and Kirk’s balance is now $122,500.

So if Craig saves the customization of this report, he can display it at any time to see all customer deposit balances.

But wait! What happens when Jim Kirk’s (or anyone else’s) deposit funds are all used up and the balance for that customer is zero? Don’t we want it to disappear from the report? Yes, eventually we want it to fall off the report because this report will grow longer and longer over time, including all $0 balances.

Let’s MacGyver this issue. On Jan. 24, let’s use up the rest of Kirk’s deposit on a further invoice (and assume that Craig hasn’t asked him for any more deposit funds up front on another sales receipt). While we’re at it, let’s use up Spock’s and Chekov’s deposits as well. We keep running the report so we know how much of each customer’s deposit funds are available, and we know how much of a negative dollar amount to put on the last row of each invoice using up part or all of the balance. Here are the invoices using up the rest of those customers’ deposits in one shot each:

Kirk:

Spock:

Checkov:

Now, if we run that same report for All Dates, we get the updated balance in the Customer Deposits account, grouped by customer (or it would group by sub customer, if Craig were using those). We can run this report as of any date (perhaps to see a prior balance), but we’re choosing to see All Dates.

Notice that the deposit totals for Kirk, Spock and Chekov are $0.00. You can imagine that this report would get longer and longer over time as deposits are added and used up, and the subtotals for $0.00 balances would remain there forever. Or would they????

We’ll MacGyver this with two steps.

Step 1: Reconcile

Step 1 is to reconcile the Customer Deposits account, the same way we would reconcile a bank or credit card account. The only difference here is that we always enter an ending balance of zero, regardless of the actual balance in that account on a given date. We can reconcile this account as often as we like, not just monthly:

In the reconciliation screen that follows, sort the grid by Payee by clicking on the header for the Payee column.

Next, we check off (not “Chekov” 😉) the deposits that are created and completely used up, payee by payee. Some of the payees are used up in two clicks (like Checkov and Spock), and some are used up in more than two clicks (like Kirk). If a deposit cannot be fully used up for a payee, we leave it alone and don’t check off anything for that name.

We make sure, as with any reconciliation, that the difference is $0.00 when we’re finished, and then we click on Finish now:

Step 2: Filter the report

Step 2 is to go back to the Outstanding Deposits by Customer report and customize it a little further: click on Filter and choose a Cleared status of Uncleared, and then click on Run report.

Bam! The report is now shorter, displaying only those customers with non-zero deposit balances. Those pesky $0.00 balances are gone.

Save the customization of this report.

And now, Craig can run this report at any time to see whose money he has yet to earn, and to see how much deposit funds are available to put (as a negative amount to use up) on an invoice to reduce it when earning those funds.

Now that the report has been created with the proper filter, Craig just has to reconcile the Customer Deposits account on a regular basis to filter out the zeroes.

Craig may actually make a success of this construction gig. Time will tell.

And what if Craig has customers giving him deposits in different currencies? Check with me next time for an answer to that – MacGyver style.