11 QuickBooks tips for producing stellar reports

Today’s accounting systems capture unprecedented levels of information, requiring CPAs to sort through more data than ever. In last month’s JofA (page 64), the first article of a two-part series on QuickBooks provided general tips for printing, memorizing, organizing, and distributing groups of QuickBooks reports. The software also offers many powerful features that can help you mine financial data for the information and intelligence needed to make business decisions. This second article in the series looks at some of these features, ones that CPAs can apply in their organizations or can show clients how to use, strengthening their position as a trusted business adviser. 

1. Controlling columns. QuickBooks provides a unique option for controlling the columns displayed in financial reports, and this tool is easy to use. Open any financial report, click the Columns tool dropdown menu, and then select an option, such as Month, Class, or Inventory Site.

In the example below, a basic “Profit & Loss” report has been customized to display detailed and subtotal columns for each inventory item and item category. This type of report makes it easy to review the profitability of each inventory item or each category of items. For example, on an item-by-item basis, we can easily see that the company sold $2,586.95 worth of regular Lighting, which cost $1,846.00 (generating a 28.6% profit), compared with $980.00 in sales of Fluorescent Ceiling Lights, which cost $796.32 and generated a profit of only 18.7%). By changing this same report to display data by “Item Type,” we could also see that sales for lumber products amounted to $452,312 (generating a 12.1% profit), while hardware sales amounted to $1,280,448 (producing a 22.7% profit margin). The ability to “slice and dice” financial data in this manner unlocks a potential treasure-trove of useful information.

2. Column widths. You can resize column widths on a report by dragging the diamonds located between the column headings and responding to the automatic prompt asking if you want to resize a single column or all columns, as shown below. To keep your new column width settings for future reporting, memorize the report.

3. Date range options for memorized reports. QuickBooks offers two options for modifying report dates—English-phrase date ranges such as “This Month” or “This Fiscal Year” and calendar-based date ranges such as “1/1/2014–12/31/2014” as shown below. When memorizing reports, bookkeepers should use only the English-phrase date ranges so the memorized reports print the desired period data without the need to manually edit the statement’s report dates each time the report is produced.

4. Subtotaling transaction reports. Similar to the way in which financial reports can be delineated multiple ways by columns, transaction reports can be subtotaled multiple ways by rows. To control a transaction report’s subtotals, open a transaction report and select Customize Report, and from the Display tab, select a totaling option from the Total by dropdown menu as shown below.

For example, using this method, a user might summarize a list of invoices by customer or sales representative, or by specific items included on the invoices.

5. Collapsing and expanding reports. QuickBooks allows you to display summary or detailed views of a report by clicking the Collapse or Expand button at the top of the report. However, to employ this feature, you must first edit your Chart of Accounts and classify some of the accounts as subaccounts, as pictured below.

6. Drilling reports. One of QuickBooks’ most powerful reporting features is the ability to drill reports. Double-clicking an amount on any report displays the underlying detail transactions that compose that amount, and double-clicking a transaction amount displays the underlying transaction, such as an invoice, check, or credit memo. QuickBooks’ drillability is an auditor’s dream tool, allowing users to instantly trace any amount through the accounting system to ascertain its origin.

7. Filtering reports. Another powerful tool in the QuickBooks reporting arsenal is the ability to filter reports by up to 55 built-in criteria (such as Class, Customer, Vendor, or Amount), as well as dozens of additional custom fields. Of course, this filter tool could also be used to produce a departmental report (based on reporting by Class), but it offers far greater potential. For example, a company operating in multiple states that wants to compare the revenue generated in each state could use the filter option to produce individual state reports. This type of information might be helpful in planning expansion to those states, figuring sales taxes, or calculating workers’ compensation. To apply a filter, from any report select Customize Report, from the Filters tab select a filter option (such as Name State), and add the desired filter criteria, such as “Georgia,” as used in the example pictured below.

The filters are cumulative, which means a user could apply multiple filters to further refine a report. For example, a user might produce a report that summarizes the total revenue generated by sales representative “Daniel” in the state of Georgia.

8. Cash-basis reporting. Most small businesses use accrual-basis reporting to run their companies but file their tax returns using the cash basis. To accommodate this common need, QuickBooks provides a somewhat limited tool that enables users to convert their accrual-basis reports to the cash basis, as follows. Display any accrual-basis report and select Customize Report, Display, and then in the Report Basis section, click the Cash option. This action makes four primary changes to the report:

• Income not yet received is removed.
• Expenses not yet paid are removed.
• Prior-year income received in the current year is added.
• Prior-year expenses paid in the current year are added.

While these adjustments are sufficient to convert accrual-basis reports to the cash basis accurately for many companies, this accrual-to-cash conversion tool does not fully meet the needs of all companies. Depending on the complexity of the accounting needs, many companies will also need to make further manual adjustments to accurately convert accrual-basis reports to the cash basis, as follows:

a. If using payroll, you must remove payroll taxes payable and the related payroll tax expenses (because they have not yet been paid and therefore should not be reflected in the cash-basis reports).

b. If the company pays expenses in advance (such as insurance), those prepaid items must be expensed (because they have already been paid and therefore should be reflected in the cash-basis reports).

c. If the company accepts customer prepayments, deposits, or retainers, these items must be adjusted and recognized as revenue (because they have been received and therefore should be reflected in the cash-basis reports).

d. If the company uses credit cards, the credit cards payable and the related expenses must be removed (because they have not yet been paid and therefore should not be reflected in the cash-basis reports). Note: For tax purposes, the IRS allows cash-basis taxpayers to treat credit card balances incurred in the tax year end, but not paid until the following year, as deductible in the tax year. 

e. If the company collects sales taxes, the sales taxes payable and related sales tax expenses must be removed (because they have not yet been paid and therefore should not be reflected in the cash-basis reports).

If any of these issues apply, you must adjust them manually to produce accurate cash-basis reports.

9. Exporting reports to Excel. When QuickBooks fails to deliver the precise reports you want, Microsoft Excel picks up the slack. Because Excel provides a plethora of formatting and formula tools, QuickBooks enables you to send reports to Excel, where you can further refine them to produce virtually any report or chart you envision. Not only does QuickBooks send its reports to Excel, it automatically converts all report totals and subtotals to formulas, so any above-the-line numeric changes you make in Excel automatically flow through to the bottom line. In addition, QuickBooks allows you to export reports directly to existing Excel workbooks, thereby updating previously exported data. As a result, if you build your new Excel-based reports using the right formula approach, your newly created reports in Excel will update whenever you repeat the QuickBooks-to-Excel export routine. To use the Excel update feature, from the report screen select Excel, Update Existing Worksheet as shown below; in the Select workbook box browse to the desired workbook and worksheet; and then click Export.

10. ODBC queries. Imagine being able to create a financial report in Excel that updates itself automatically by reading the QuickBooks data file on demand each time you press Excel’s Data, Refresh button. You can achieve this level of data synchronization using Excel’s built-in Open Database Connectivity (ODBC) querying capabilities. QuickBooks Enterprise includes the ODBC driver necessary to create these ODBC queries for free, and for a fee of $149, the required ODBC driver can also be added to QuickBooks Premier at qodbc.com.

To use this tool, from the QuickBooks Enterprise menu, select File, Utilities, Configure ODBC. In the resulting dialog box, adjust the sync settings as desired (typically, no setting adjustments are necessary), and then click OK. Thereafter, in Excel, select Data, Get External Data, From Other Sources, From Microsoft Query, and in the resulting Choose Data Source dialog box, select QuickBooks Data, and then click OK.

In the resulting Query Wizard—Choose Columns dialog box, in the Available tables and columns section, scroll to and select a table (such as the Customer table) and click the greater than sign button (>) to add this table to the Columns in your query section as shown below.

Click Next, Next, Next, Finish, OK to complete the ODBC query and display the results in Excel. You can use a variety of Excel tools, such as the PivotTable tool, to manipulate the queried data to produce the specific reports you desire. Thereafter, pressing the Refresh All button on Excel’s Data tab updates the query and the reports you’ve built referencing that queried data. An example ODBC query and a resulting PivotTable report are displayed below.

11. Income Tracker. QuickBooks 2014 provides a new Income Tracker report summarizing all transactions that affect income, including estimates, invoices, payments, credits, and sales orders. Filter tools located at the top of the report empower users to slice and dice the data by date, transaction type, status, customer, and job. One of the more interesting aspects of this report is the ability to manage and edit all income-related transactions directly from this single screen by using the Manage Transactions pop-up menu. Some QuickBooks users find it easier to manage the various income-related transactions in this same screen, where they can instantly see the results of their transaction entries and edits reflected in the Income Tracker report summary, an example of which is presented below.

CONCLUSION

QuickBooks offers impressive reporting capabilities not found in many higher-priced accounting and enterprise resource planning solutions, but there is always room for improvement. Following are five suggested reporting improvements:

1. The ability to control indents, so row descriptions line up.
2. The ability to schedule automatic delivery of encrypted reports to email recipients.
3. More account number segments.
4. An audit trail that fully hits the general ledger with the original, reversing, and correcting transactions.
5. Ratio reports, including comparisons to industry averages.

I encourage CPAs to assist their companies and clients that use QuickBooks to implement some of the aforementioned reporting tips in their reporting functions and better manage their businesses.

Note: Unless noted otherwise, the tips in this article apply to QuickBooks 2008 and later Premier and Enterprise editions.
 
J. Carlton Collins (carlton@asaresearch.com) is a technology and accounting systems consultant and a JofA contributing editor.

To comment on this article or to suggest an idea for another article, contact Jeff Drew, senior editor, at jdrew@aicpa.org or 919-402-4056.

AICPA RESOURCES

JofA articles

• “11 Tips to Optimize QuickBooks Reporting,” Sept. 2014, page 64
• “What Gets Monitored Gets Detected,” Feb. 2014, page 32
• “Technology Q&A: Can I Drop(box) QuickBooks?” April 2013, page 86
• “Technology Q&A: QuickBooks Job Costing,” Nov. 2012, page 75

Publications

• 10 Steps to a Digital Practice in the Cloud: New Levels of CPA Firm Workflow Efficiency (#PTX1204P, paperback; #PTX1204E, ebook)
• Principles and Criteria for XBRL Formatted Information (#APCXBRLE, ebook)

CPE self-study

• Advanced Excel: Practical Applications for the Accounting Professional (#745755, text)
• XBRL U.S. GAAP Certificate Program (#159945, two-year online access)

Conference

Digital CPA Conference, Dec. 8–10, Washington.

For more information or to make a purchase or register, go to cpa2biz.com or call the Institute at 888-777-7077.

Information Management and Technology Assurance (IMTA) Section and CITP credential

The Information Management and Technology Assurance (IMTA) division serves members of the IMTA Membership Section, CPAs who hold the Certified Information Technology Professional (CITP) credential, other AICPA members, and accounting professionals who want to maximize information technology to provide information management and/or technology assurance services to meet their clients’ or organization’s operational, compliance, and assurance needs. To learn about the IMTA division, visit aicpa.org/IMTA. Information about the CITP credential is available at aicpa.org/CITP.