Save Time: Integrate QuickBooks with the Rest of Your Office

by Thomas G. Stephens Jr., CPA, CITP
Pursuit, August 2007

More than 3.5 million small businesses use QuickBooks accounting software to record and track day-to-day transactions. Small businesses that use this software should consider integrating QuickBooks with other
applications to derive the greatest benefit possible.

QuickBooks and Microsoft Office
Microsoft Office includes the desktop standards Word, Excel, and Outlook – three applications accountants use daily. QuickBooks integrates well with these applications, allowing users to enjoy increased productivity.

With Word, QuickBooks allows users to prepare mail-merge letters, customized invoices, and other documents. QuickBooks contains several letter templates that users can customize to meet their needs.

Most QuickBooks users know that it is possible to export QuickBooks-based reports to Excel. However, it is also possible to export these reports with Excel’s Auto-Filter feature enabled, so critical data in these reports can be found quickly and easily. Using this feature, an auditor, for example, can find all transactions entered or edited by a particular user during the period under audit. Excel is also useful when importing data into QuickBooks, streamlining the process of importing customer, vendor, account, or item information.

In the area of payroll reporting, the Summarize Payroll Data report option provides users with a number of Excel worksheets and PivotTables of key payroll data. These reports include Employee Journal, Year-to-Date Summaries, Hours Worked, Rates and Hours by Job, and more. Users can easily filter and customize these reports for specific needs. In addition,
subscribers to QuickBooks’ Enhanced Payroll for Accountants can access a number of Excel-based reports for analyzing client payroll costs.

Many QuickBooks users maintain name and contact information for customers and vendors in two locations: QuickBooks and Outlook. The dual entry of this data and on-going maintenance is time-consuming and often neglected. To ensure consistent data, QuickBooks includes a utility to synchronize information from Outlook’s Customer List, Vendor List, and Other Names List.

Income Tax Applications
In addition to QuickBooks, Intuit publishes ProSeries and Lacerte professional tax preparation software. QuickBooks integrates well with these applications and can reduce the amount of time required to complete a tax return by up to 75 percent. In fact, QuickBook’s integration with these tax applications is so powerful that other leading tax software applications, including ProSystem fx and UltraTax CS, have built utilities that allow QuickBooks data to be imported into their software.

Sales Tax Applications
Sales tax compliance is a difficult issue faced by small business owners and accountants. More than 8,000 tax jurisdictions are subject to ever-changing regulations. As a result, applying the appropriate sales tax rate presents a formidable challenge. QuickBooks’ AvaTax effectively outsources sales tax compliance. This service from Avalara integrates QuickBooks to validate customer addresses over the Web and charges the correct amount of sales tax to the customer based on this address. AvaTax further streamlines the process by providing detailed reports by taxing jurisdiction.

QuickBooks is a powerful application. Financial professionals and small businesses can extend the functionality of this product by learning about
its integration capabilities. The examples above represent only a few of the integration opportunities. Take advantage of them to truly leverage your investment in this software.

How to Mail-Merge Using QuickBooks and 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 of the recipients. Select the base template for the letter, enter appropriate signature information, press Next. QuickBooks will create the letters in Word. Users can also edit individual letters and create envelopes as part of the seamless integration between QuickBooks and Word.

How to Integrate Excel and Quick Books
Select File, Utilities, Import, and Excel files from the menu. Then select the Excel file to import, and define a “map” of the data. The map tells QuickBooks where to look for specific fields in the Excel data. Finally, establish preferences for how QuickBooks should handle incorrect and duplicate records. Click Import. QuickBooks will import the selected data.

To find all transactions entered or edited by a particular user during the period under audit:

Export the QuickBooks Audit Trail report to Excel with Auto-Filters enabled. 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.

How to Update Contact Information with QuickBooks and Outlook
To launch the Synchronize Contacts function, select File, Utilities, and Synchronize Contacts from the menu. This will initiate a two-way synchronization of information between QuickBooks and Outlook. If data on a particular contact in QuickBooks is newer than the corresponding data in Outlook, this function will update the Outlook data.  Conversely, if Outlook contains the most recent data, the QuickBooks data will be updated.

Integrating QuickBooks with ProSeries and Lacerte Tax Software
With ProSeries, users must first establish a Tax-Line Mapping for each account in the Chart of Account List. This map tells ProSeries on which line of the tax return the balance in the specified account belongs. Once established, these mappings roll forward to the next year, minimizing the amount of time required to complete subsequent annual returns. After all accounts have been mapped to the appropriate lines on the tax return, select Accountant, Export Balances to ProSeries 2006, from the menu to export the balances to ProSeries.

With Lacerte, users integrate through Lacerte’s SmartMap technology. SmartMap establishes most tax line assignments automatically upon importing QuickBooks data into Lacerte. Users can drag-and-drop to make any adjustments necessary to the SmartMap automatic assignments.

Note: Integration features described in this article are based on QuickBooks: Premier Accountant Edition 2007 and Microsoft Office 2003. Your results may vary with other versions of
these applications.