Save time by integrating QuickBooks with the rest of your Office.

As a stand-alone application, QuickBooks is the leading accounting software application for small businesses nationwide. Over 3.5 million small businesses rely on QuickBooks to record and track day-to-day transactions. To truly realize all that QuickBooks offers, CPAs should investigate integrating QuickBooks with other applications, such as Microsoft Office suite and various tax applications offered by Intuit and other technology companies.

Integration with Microsoft Office

The Microsoft Office desktop suite of applications is used on 90% of all PCs. Included in the Office suite are desktop standards such as Word, Excel, and Outlook--three applications most accountants use on a daily basis. Fortunately, QuickBooks integrates very well with each of these three applications, allowing users to realize substantial gains in productivity.

QuickBooks and Word

QuickBooks works seamlessly with Word, allowing users to prepare mail-merge letters, customized invoices, and other communications to vendors, customers, and employees. For example, this feature can be used to prepare a letter to all employees advising how much accrued vacation time is available. In fact, such a letter is available as one of many pre-defined templates in QuickBooks. Users can customize these templates and create new ones to meet specific needs.

To prepare mail-merge letters integrating QuickBooks with Word, select Company and Prepare Letters with Envelopes from the menu. Next, choose the nature of the letter--collection, customers, vendors, employees, etc.--and select the names for whom the letter will be prepared. Select the base template for the letter, enter appropriate signature information, press Next, and QuickBooks will create the letters in Word. Users can even edit individual letters if necessary before printing and creating envelopes as part of the seamless integration between QuickBooks and Word.

QuickBooks and Excel

QuickBooks integrates with Excel in many ways that offer substantial productivity gains.

For instance, most QuickBooks users know they can export QuickBooks-based reports to Excel. A lesser known capability is that reports can be exported with Excel's Auto-Filter feature enabled to facilitate finding critical data on the report quickly and easily.

For example, an auditor seeking to locate all transactions entered or edited by a particular user during the period under audit can complete this task in mere seconds. Simply export the QuickBooks Audit Trail report to Excel with Auto-Filters enabled and select the desired user from Excel's drop-down filters. To export the Audit Trail or any other report to Excel with Auto-Filters enabled, click the Auto Filtering check box on the Advanced tab of the Export Report window.

Excel is also useful when importing data into QuickBooks. In fact, QuickBooks directly integrates with Excel to streamline importing customer, vendor, account or item information.

To begin this process, select File, Utilities, Import, and Excel files ... from the menu. Then select the Excel file to import and define a "map" of the data; in this instance, the map tells QuickBooks where to look for specific fields in the Excel data. Finally, establish preferences for how QuickBooks should handle error and duplicate records, click Import, and QuickBooks will import the selected data.

One additional way in which QuickBooks integrates with Excel is in the area of payroll reporting. The Summarize Payroll Data in Excel report option provides users with a number of Excel worksheets and PivotTables of key payroll data. Included in this workbook are:

* Employee Journal

* Year-to-Date Summaries

* Hours Worked Report

* Rates & Hours by Job Report

* State Wage Listing

* Deferred Compensation Report

* Form 943 Worksheet.

It is easy to filter and customize these reports for specific needs. Most are PivotTables. In addition, subscribers to QuickBooks' Enhanced Payroll for Accountants service have access to a number of Excel-based payroll reports that are useful in analyzing clients' payroll costs. Newly-available in 2007, these reports include:

* Paycheck Detail Report

* Payroll Register Report

* Payroll Expense Summary and Chart Report

* Employee Year-to-Date Summary Report

* Employee Cost Comparison Report

* 941/940/State Liabilities and Payments Report.

Accountants in public practice supporting QuickBooks clients will find these reports useful when working with clients' payroll data.

QuickBooks and Outlook

Most QuickBooks users maintain name and contact information for customers and vendors in at least two locations--QuickBooks and Outlook.

Not only is initial entry of this data repetitious, but ongoing maintenance is time-consuming and often overlooked. When a customer's billing address changes, data is often updated in either QuickBooks or Outlook, but not both. To solve the problem of inconsistent data, QuickBooks includes a utility to synchronize data from the Customer List, Vendor List, and Other Names List with Outlook.

To begin this process, select File, Utilities, and Synchronize Contacts from the menu to launch the Synchronize Contacts function. This will cause a two-way synchronization of information between QuickBooks and Outlook. As a two-way synchronization, if data on a particular contact in QuickBooks is newer than corresponding data in Outlook, then the Synchronize Contacts function will update the Outlook data; conversely, if Outlook contains the most recent data, then the update occurs with the QuickBooks data.

Integration with Income Tax Applications

It's no secret that Intuit publishes QuickBooks along with ProSeries and Lacerte professional tax preparation software. It should also be no secret that QuickBooks integrates quite well with both of these applications to dramatically reduce the amount of time required to complete a tax return for a QuickBooks-based company. This integration reduces the amount of time accountants require to complete a tax return for a QuickBooks-based company by up to 75%. In fact, integrating with tax applications is so powerful that other leading tax software applications--including ProSystem fx and UltraTax CS--have built utilities which allow QuickBooks data to be imported into their software.

When integrating QuickBooks with ProSeries tax software, users establish a Tax-Line Mapping for each account in the Chart of Account List. This map essentially tells ProSeries on what line of the tax return the balance in the specified account belongs. Once established, these mappings roll forward to the next year, further minimizing the amount of time required to complete subsequent year returns. After all accounts have been mapped to the appropriate lines on the tax return, select Accountant, Export Balances to ProSeries 2006 \'85 from the menu to export the balances to ProSeries.

QuickBooks integrates into Lacerte tax software through Lacerte's SmartMap technology. SmartMap establishes most tax line assignments automatically upon importing QuickBooks data into Lacerte. Users can then use drag-and-drop technology to make adjustments as necessary to the SmartMap automatic assignments.

A full discussion of the steps required to integrate QuickBooks with non-lntuit tax applications is beyond the scope of this article. However, most of the integration routines use some type of trial balance application, such as Trial Balance CS or ProSystem fx Trial Balance, as an intermediary application. Regardless of the exact steps required to integrate QuickBooks into a tax application, the benefits are usually well worth the effort.

Integration with Sales Tax Applications

Sales tax compliance is one of the most difficult issues facing small business owners and their accountants today. With over 8,000 taxing jurisdictions in the United States and a maze of ever-changing regulations to contend with, knowing what the appropriate sales tax rate is for a particular transactions presents a formidable challenge. Fortunately, for QuickBooks users seeking to ease the administrative hassles of charging and reporting sales taxes, there is a service available that integrates with QuickBooks and effectively "outsources" sales tax compliance. This service--Ava Tax ST from Avalara--integrates with QuickBooks over the Web to validate customer addresses and charge the correct amount of sales tax to the customer based on the validated address. Further, Ava Tax provides detailed reports by taxing jurisdiction, thereby streamlining the process of preparing sales tax returns.

Summary

End-users and accountants alike can extend the functionality of QuickBooks by taking full advantage of the many integration opportunities provided by the software. The examples cited in this article represent only a few of the integration opportunities. The QuickBooks Solutions Marketplace (http://marketplace.intuit.com) is one of many great resources to learn more about specialized applications with rich QuickBooks integration features. Take advantage of these integration opportunities today to truly leverage your investment in QuickBooks software.

[ILLUSTRATION OMITTED]

Thomas G. Stephens, Jr., CPA, CITP has presented over 700 continuing education courses to over 18,000 participants. He is the recipient of the 2004 Discussion Leader of the Year Award from the Nebraska Society of Certified Public Accountants. He is also a member of the AICPA and the Georgia Society of CPAs.