 |
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
Save Time by Integrating QuickBooks with the Rest of Your Office
Thomas G. Stephens, Jr., CPA, CITP
As a stand-alone application, QuickBooks is the leading accounting software application for small businesses all over the nation; presently, over 3.5 million small businesses rely on QuickBooks to record and track day-to-day transactions and for this singular purpose QuickBooks is a stellar performer. However, to truly realize all of the benefits QuickBooks has to offer, one should look into how to integrate QuickBooks with other applications, including those in the Microsoft Office suite and various tax applications offered by Intuit and other technology companies.
Integration with Microsoft Office
Microsoft Office remains the desktop suite of applications in use by approximately 90% of all PC users today. 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 works seamlessly with Word, allowing users to prepare mail-merge letters, customized invoices, and other pieces of communication for vendors, customers, and/or employees. For instance, one could use this feature 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 as required 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 create envelopes as part of the seamless integration between QuickBooks and Word.
QuickBooks integrates with Excel in a number of ways that, once learned, offer substantial productivity gains to users. For instance, most QuickBooks users already know that it is possible to export QuickBooks-based reports to Excel. What many are not aware of, however, is the ability to export these reports with Excel’s Auto-Filter feature enabled to facilitate finding critical data on the report quickly and easily. For example, suppose an auditor wanted to find all transactions entered or edited by a particular user during the period under audit. To 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 the process of 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, and a Form 943 Worksheet. Filtering and customizing these reports for specific needs are quite easy as most of these reports are PivotTables. In addition, subscribers to QuickBooks’ Enhanced Payroll for Accountants service have access to a number of Excel-based payroll reports which 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, and 941/940/State Liabilities and Payments Report. Those accountants in public practice supporting QuickBooks clients should find these reports quite useful when working with clients’ payroll data.
Most users of QuickBooks 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 on-going maintenance is time-consuming and is often overlooked. So, for example, when a customer’s billing address changes, oftentimes data is updated in either QuickBooks or Outlook, but not both. To solve this 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… 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-Intuit tax applications is beyond the scope of this article. However, most of the integration routines utilize 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 transaction 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 which integrates with QuickBooks and effectively "outsources" sales tax compliance much in the same way many companies have outsourced payroll tax compliance. This service – AvaTax 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, AvaTax provides detailed reports by taxing jurisdiction, thereby streamlining the process of preparing sales tax returns.
Summary
QuickBooks is a powerful application for small business accounting and remains the leader in its market. However, end-users and accountants alike can extend the functionality of this best-selling product by taking full advantage of the many integration opportunities provided by the software. From working seamlessly with Word, Excel, and Outlook, to minimizing the amount of time required to complete an income tax return, to easing the hassles of complying with sales tax laws and regulations, the examples cited in this article represent only a few of the integration opportunities with QuickBooks. 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.
Mr. Stephens is a member with K2 Enterprises where he develops and presents educational programs to members of the accounting profession on a variety of topics, including QuickBooks. The 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.