11 tips to optimize QuickBooks reporting

Paraphrasing the Kieso, Weygandt, and Warfield textbook Intermediate Accounting, the point of an accounting system is to summarize voluminous data to produce financial reports that management can use to run the business.

Despite the textbook’s position, it seems that many QuickBooks users have missed this message as they appear to use the product for little more than getting money in and out the door (via invoices and checks). To encourage better financial reporting among QuickBooks users, the JofA is presenting tips to help users streamline the reporting process and get the most out of QuickBooks’ financial reporting capabilities. This article provides general tips for printing, memorizing, organizing, and distributing groups of QuickBooks reports. Next month, the JofA will publish another set of more specific tips for maximizing QuickBooks’ reporting capabilities.

1. Generating and printing a collection of reports. To achieve proper reporting, companies typically need to produce dozens of weekly reports; however, when those reports are produced piecemeal or one at a time, the process is inefficient. To streamline the process, QuickBooks allows you to group dozens of reports together so they can all be printed in a single step. To use this feature, select Reports, Process Multiple Reports, select the report group to be printed, and then select Print, Print. The next several tips will walk you through the steps necessary to use this feature.

2. Creating multiple report groups. To use the Process Multiple Reports feature, you must first create the necessary report groups such as “Monday Reports” and “Month End Reports.” Alternatively, some companies choose to group reports according to user or job function, such as “Kim’s Reports” or “CFO’s Reports.” To create a new report group, select Reports, Memorized Reports, Memorized Report List; then, from the Memorized Report pop-up menu, select New Group, enter a name for the new group in the Name box, and then click OK.

  Once the new group has been created, one at a time launch each report you want to add to the group, make any necessary customizations (such as adding columns or adjusting the date range), click the Memorize button at the top of the screen, enter a report name in the Name box, select the new group from the Save in Memorized Report Group dropdown list, and click OK. 

3. Memorize reports multiple times. To distribute multiple copies of the same report, memorize that report into the same report group multiple times, customizing the report name each time. For example, you might memorize the “Accounts Receivable” report into the “Month End Reports” group three times, labeling them as “AR Report for the CFO,” “AR Report for the Sales Manager,” and “AR Report for the File Drawer.” Using this approach, not only do all three copies of the report print with just one click, but each report also identifies the recipient in the report’s title for easier distribution. To memorize a report to a group, open the report; modify the dates, columns, filters, etc. as necessary; and then press Ctrl+M to open the Memorize Report dialog box. (If prompted, click the New button to create additional copies of the same report.) Check the box labeled Save in Memorized Report Group, select the desired group, and then click OK.

4. Collate the group. As a final step to creating a new group of reports, open the new report group by selecting Reports, Memorized Reports, Memorized Report List, then drag and drop the diamonds preceding each report name to rearrange and collate them by recipient, as show below. Collating the reports by recipient makes it faster and easier to distribute the printed reports to the various users.

5. Reporting groups electronically. Companies that prefer to distribute grouped reports electronically (as opposed to paper reports) should repeat the steps described in tip No. 1, except before the final print step, select either the Adobe PDF or Microsoft XPS option from the Print Reports dialog box. Next, select a folder destination, enter a file name for each new report file as prompted, and then click OK. This process will produce separate PDF or XPS versions of each report, saved to the location you specify, for easy electronic distribution. (Be sure to refer to tip No. 6 for advice about encrypting reports before sending them via email.)

6. Password protecting electronic reports. QuickBooks provides an option for emailing unencrypted reports directly from the report screen, but I don’t recommend this option unless you have email encryption installed, due to privacy concerns. To protect the privacy of your reports, you should encrypt them before sending. Use one of the following encryption options:

a. PDF approach. Print the report (or report group) to PDF files as described in step No. 5, then open each PDF file in Adobe Acrobat ($299 on Amazon) and from the File menu, select Properties, Security, and in the Security Method dropdown box, select Password Security, as shown below. Enter an appropriate password in the Document Open Password box, and then click OK, OK. Repeat this process for each PDF report.

(Note that the free Adobe Acrobat Reader product does not support this security option. You must use the full Adobe Acrobat product.)

b. Excel approach. Export each report to Excel from the report screen by selecting Excel, Create New Worksheet, Export; the report will automatically open in Excel. From Excel’s File menu, select Save As, and in the resulting Save As dialog box, click the Tools dropdown menu and select General Options, enter a password in the Password to open box, click OK, repeat the password when prompted, and then click OK, Save. Repeat this process for each report you wish to encrypt using Excel.

(Note: Even though the Process Multiple Reports feature does not produce Excel-based reports, you can still use this feature to quickly open all of the reports in the group to export them to Excel.)

Once the reports are encrypted, they are ready to be sent safely via email. Don’t forget to call your recipient and supply him or her with the proper password(s) (because including the password in the email defeats the intended security). There are many additional encryption tools and methods you could employ, but these two solutions are often available to CPAs with no additional software purchases required.

Because the process of encrypting files can be time-consuming, an alternative approach might be to avoid using email altogether and simply have the bookkeeper copy the various PDF or XPS files to each recipient’s respective password-protected data folders on the company’s file server. Once all the financial reports have been properly copied, the bookkeeper could then send an email notifying the recipients that the latest reports have been added to their respective report folders.

7. Report Center’s custom-defined reports. QuickBooks provides a visual Report Center summarizing all reports, including Memorized, Favorite, Recent, and Contributed reports. In particular, the Contributed tab in the Report Center enables users to access hundreds of custom-defined reports created by other QuickBooks users. As pictured below, this screen provides user ratings, reviews, and an indicator that shows how many times each report has been downloaded. Why re-create new reports from scratch when a fellow QuickBooks user may have already done the work for you?

8. Consistent reporting. Many company officials seem to discount the need for producing reports on a consistent basis, perhaps because they assume company personnel are accessing the desired reports and information directly from QuickBooks as needed. The problem with this approach is that in the absence of regular reporting, too often the actual result is no reporting, which leads to company personnel making decisions without the proper information in hand. Even if company personnel are accessing QuickBooks data and reports on their own, the inefficiencies described in tip No. 1 aren’t avoided—they are merely spread across multiple personnel who receive higher paychecks. The implementation of a consistent reporting routine managed by the bookkeeper both promotes an efficient reporting process and helps ensure that personnel receive the financial reporting data and information they need on a timely basis.

9. Ensuring that reports are properly utilized. Be aware that often the implementation of a consistent reporting routine does not go far enough because there is no assurance that the delivered data and reports are being used by the recipients. Many bookkeepers complain that the reports they produce and deliver are often ignored, thereby undermining the entire reporting process. When asked about such neglects, recipients usually confirm that they ignore reports for one of three reasons:

• The information is not accurate and therefore not worth reading.
• The recipient does not understand how to read the reports or does not know how to use the information contained within the reports.
• The recipient is too busy to read the reports.

Obviously, the first response is valid; erroneous financial reports aren’t worth reading, or producing for that matter. If such errors are commonplace, then bookkeeper training is needed. If the statements are accurate, then further training is needed to help the recipients better understand the reports and how to use the information. If the recipients are indeed too busy to refer to financial reports, then either employees need to work more efficiently or new employees need to be added to help with the excess workload.

In the final analysis, a well-oiled financial reporting system exists only when all personnel are receiving and using accurate reports on a timely basis to help them best perform their job duties. Perhaps this definition should be the litmus test you apply to your company’s or clients’ financial reporting systems.

10. Suppress account numbers on financial statements. Many CPAs prefer to enable QuickBooks’ numeric account numbers to facilitate transaction data entry using the number keypad. To turn on account numbers, select Edit, Preferences, Accounting, and on the Company Preferences tab, check the box labeled Use account numbers.

An undesired consequence of this measure is that the numeric account numbers are also displayed on QuickBooks’ financial statements, but there is a simple solution to this problem. To suppress account numbers from QuickBooks financial statements, select Edit, Preferences, Reports & Graphs, and from the Company Preferences tab, select Description Only.

For this solution to work properly, you must edit each account as follows. Press Ctrl+A to display the chart of accounts, right-click on each account, select Edit Account from the pop-up menu, and then add account numbers and a description to each account (as circled in the image below). Note: You must include a description, otherwise QuickBooks displays the account number and account name on financial statements by default. Keep reading as the following tip provides a faster (albeit more complicated) alternative for adding account numbers and descriptions.

11. Use Excel to modify the chart of accounts. The process of editing hundreds of accounts to add account numbers and descriptions as described in the previous item could take hours, but QuickBooks provides a quick way to make these changes. Instead of editing each account, export the chart of accounts to Excel and edit the account numbers and descriptions there on a single screen, then import the results back into QuickBooks. This process can save several hours of editing time, as follows. Select File, Utilities, Export, Lists to IIF Files, and in the Export dialog box, select Chart of Accounts, OK.

Indicate a file name and click OK, then open the exported file in Excel (keep in mind that the exported file will have an .IIF extension). Excel should automatically recognize the file’s text-based format and open the Text Import Wizard; select Delimited, Next, Tab, Next, General, Finish to open the file. Next, copy the NAME column to the DESC column as a starting point, as highlighted in red below, and then edit the account descriptions, if necessary, as shown below.

Enter account numbers in the ACCNUM column as needed, as highlighted in green below, then save the Excel file to the same .IIF file format and close Excel. Import the results into QuickBooks by selecting File, Utilities, Import, IIF Files, browse to the appropriate file, and then click OK to complete the import process. This process will update the chart of accounts in QuickBooks with the proper account numbers and descriptions, thereby allowing you to suppress account numbers from your financial reports. (This process may sound complicated, but it’s actually rather easy. If you are unsure about this process, practice with a sample company file first.)

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

• “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; and #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, on-demand)

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.