Create Custom Reports in QuickBooks Desktop

In this article I want to share with you my favorite method for creating a custom report in QuickBooks Desktop software. No worries about the word “custom”, as the steps provided here will make it easy for you or your clients to create the report on your own.

One of the most important reports I create for my clients using QuickBooks Desktop software is a custom report titled “Undeposited Funds Detail.” This is not a prebuilt report in QuickBooks, but it is easy to create, as you will see.

 

Get the full Create Custom Reports in QuickBooks Desktop CPE course.

 

 

 

This report is fundamental because you cannot troubleshoot a total balance for the undeposited funds account for the amount displayed on a balance sheet report without knowing the detail behind the numbers. Typically, in QuickBooks we double click on displayed total which details all of the transactions posted to that account, however, too much data will display, making it difficult to identify what is not deposited to a bank account.

How do transactions get to this Undeposited Funds Account? When you select the Undeposited Funds account as the deposit-to account, QuickBooks increases the balance in the Undeposited Funds account. Then, when you complete a make deposit, QuickBooks reduces the Undeposited Funds account balance, and behind the scenes, it marks the original increase and the new decrease as cleared.

To set the preference for using the Undeposited Funds (recommended), log into the file as the Admin user. From the menu bar, select Edit, Preferences and choose the Payments preference on the left and the Company Preferences tab at the top. Place a checkmark in the Use Undeposited Funds as a default deposit to account box. Now when you or your customer record a Receive Payment transaction for an open customer invoice, the payment amount will be debited (increasing) the balance of the Undeposited Funds, Other Current asset type of account.

Before we dig too deeply, let’s see if you agree with the detail of the Undeposited Funds balance in your QuickBooks data. You also might want to memorize this report for the convenience of reviewing the detail often, avoiding any data entry errors this report might help you uncover.

To create this Undeposited Funds Detail report, follow these easy steps:

1. From the menu bar select Reports, Custom Reports, Transaction Detail. The Modify Report dialog box opens.
2. Display the date range as All. (Hint, type “a” on the keyboard to automatically select the All range for dates)
3. For Total By, from the bottom of the list, select Payment Method.
4. For Columns, select those you want to view on the report.
5. Click the Filters tab. In the Choose Filter box, select Account, and choose the account named Undeposited Funds from the drop-down list.
6. Also from the Filters tab, select Cleared and click the No button.
7. Click the Header/Footer tab. Type Undeposited Funds Detail as the report title. Click OK to close the Modify Report dialog box.

This report provides the details of the amounts that are included in the Undeposited Funds account balance. You might want to include preparing this report at the end of each month for your records. Following are other reports you might also find useful when reviewing the balance in your Undeposited Funds account.

Caution: The modified custom report you named Undeposited Funds Detail will always show the present state of the transaction, which means when a payment is received and is included on a Make Deposits transaction, the report for a prior date will no longer show that item as undeposited. Behind the scenes, QuickBooks marks the transaction as “cleared” as of the transaction date; therefore, you cannot get a historical snapshot with this report.

You can use this same report for a variety of other purposes including:

Employee Advances

Reporting on balances in an Employee Advances, Other Current Asset Account. When an employee is provided a loan by the company and regularly pays back the loan through payroll record deductions.

To create this Employee Advances Loan Detail report, follow these easy steps:
1. From the menu bar select Reports, Custom Reports, Transaction Detail. The Modify Report dialog box opens.
2. Display the date range as All. (Hint, type “a” on the keyboard to automatically select the All range for dates)
3. For Total By, from the bottom of the list, select Employee. (this assumes that all records in this account will be assigned an Employee list name)
4. For Columns, select those you want to view on the report.
5. Click the Filters tab. In the Choose Filter box, select Account, and choose the account used for tracking Employee Advances, normally an Other Current Asset type of account.
6. Optionally, from the Filters tab, select Cleared and click the No button.
7. Click the Header/Footer tab. Type Employee Advances Detail as the report title. Click OK to close the Modify Report dialog box.

Tip: This report can get to be lengthy with time. To make the report more efficient to work with, it is recommended that you complete a Reconciliation when an employee fully pays off a loan. From the menu bar, select Banking, Reconcile and choose the Employee Advances Account. Clear the transactions that recorded the initial loan and the payments, reconciling when the total of these amounts net to -0-.

There are other uses for this report, such as reporting on Customer Deposits (Other Current Liability Account), and even when sister companies have Due To or Due From accounts on their books.

Get the full Create Custom Reports in QuickBooks Desktop CPE course.

EARN CPE