Ask the Expert - Customer Down Payment Report - Accounting Software Secrets
post-template-default,single,single-post,postid-124,single-format-standard,bridge-core-2.6.3,qode-page-transition-enabled,ajax_fade,page_not_loaded,,qode-theme-ver-24.8,qode-theme-bridge,qode_header_in_grid,wpb-js-composer js-comp-ver-6.5.0,vc_responsive

Ask the Expert – Customer Down Payment Report

Ask the Expert – Customer Down Payment Report

Question – I receive down payments from my customers based on invoices. I am looking for an easy way to see which customers still have a down payment in the liability account. Do you have any suggestions?

Answer – It depends on how you are recording the money when it is received.

Receive Payment Option without an Invoice:

The most efficient way to record a customer deposit or pre-payment is: Receive Payment as usual (Customers > Receive Payments). Because the invoice has not yet been created, the prepayment amount will remain unapplied. This will then show as a credit in Accounts Receivable for the customer.

With this method, it is not following Generally Accepted Accounting Principles (GAAP). However, it is used by many QuickBooks users when the down payment is only outstanding for a short period of time. Then a year end adjustment to reclassify the amount that has been received will address the issue from a tax reporting perspective.

The next step is to create the invoice for the entire amount of the job or project. You can customize the template to show the total A/R balance for the customer at the bottom so they can see any remaining credit and/or link the credit and show that on the invoice if it is more appropriate.

And then finally, the invoice and the prepayment can be “linked” together by choosing Customers > Receive Payments > Click on the box that says “Apply Existing Credits.” The software will then apply the previous payment (i.e. credit) to the outstanding invoice. This process can be done immediately with a zero in the amount column, or when the remaining balance is paid.

The advantage to using this approach is three fold:

  1. Because the procedures follow the same steps as normal invoices and receive payments, it is easy;
  2. The amount continues to appear on any Accounts Receivable reports, which makes it much easier for the small business owner to track; and
  3. A statement generated for the customer would show the payments and invoices as they were created with the appropriate amounts and dates.

There are two problems with this approach:

Generally Accepted Accounting Principles (GAAP) states that the prepayment is not truly a reduction in the Accounts Receivable balance, but rather a liability to the company that it owes to the customer; and in certain types of businesses, it is necessary to generate an invoice for the deposit amount from which the customer will pay.

Receive Payment Option with an Invoice:

To eliminate the two problems mentioned above, another alternative exists:

1. Create a new account for the tracking of the liability by choosing Lists > Chart of Accounts > Account > New > Other Current Liability > Customer Pre-payments. Depending on the business, this “Customer Pre-payments” item may be called down payments, progress payments, prepayments, customer deposits, retainer, etc. The name is not important. What is important is that this money has not been earned yet, it is simply being held to be offset against future work. For purposes of this article, we will not discuss that this money may need to be segregated into a specific “trust” account until it is earned.

2. Create a new item for recording the customer deposits on an invoice. Lists > Items List > Item > New > Other Charge > Pre-payment > Description > amount can be left as zero > the item should not be taxable > the account code should be Customer Pre-payments as created in step 1 above.

3. Create an invoice for the Pre-payment amount using the Pre-payment item.

4. When the customer remits payment on the invoice, receive payment as usual.

5. Once the job is complete, create the final invoice. The invoice should be the full amount of the sale, with an additional negative line on the invoice for the Pre-payment item. The result is an invoice with a total equal to the net amount now currently due.

6. When the final payment is received, choose Customers > Receive Payments as usual.

TRICK: When using the later approach, to ensure that the prepayment amounts balance, the Customer Pre-payment account can be reconciled by choosing Banking > Reconcile > Customer Pre-payment. The beginning and ending balances will be zero you will see the original customer deposit invoice and then the deduction on the final invoice in each column. Check each one and confirm the difference is zero. This will eliminate the customer activity that has already been completed from the report the you create below. The first reconciliation is usually easiest if you print the report based on the instructions below, even though it may be very long. Then manually place a checkmark on the printed report as you check the amounts on the screen. After that, if you do it regularly, it is relatively easy.

To create the report, choose Reports > Accountant & Taxes > Transaction Detail by Account. Next, click on the Modify Report button.  On the Display tab you can remove any columns you do not need. Typically, I have added the paid column (to show easily on the report if the customer has paid the deposit invoice or not). On the same tab it is possible for Total By to choose Customer so that the report will be subtotaled by each individual customer to make it easy to see which customers have a zero balance to be reconciled. Click on the Filter Tab. For the Account chose the Customer Pre-payment account, for the date choose All, for Cleared choose No.

Click on the Header/Footer tab and change the Report Title to something that makes more sense to you like Customer Pre-payments then click on OK. Don’t forget to memorize the report so you can easily use it in the future.