Creating financial statements from QuickBooks data can be a tricky endeavor. Yes, it’s easy enough to choose Reports, Company and Financial, and then Profit & Loss—as long as you don’t mind account numbers embedded into your P&L report. QuickBooks Premier Accountant Edition and Enterprise Solutions users have a built–in tool known as QuickBooks Statement Writer that allows deep integration between Excel and QuickBooks, as well as Microsoft Word. A third-party application called Excel FSM offers functionality similar to the QuickBooks Statement Writer to users of all versions of QuickBooks, both Desktop and Online. A fourth option is to roll your own financial statements in Excel without the benefit of any add-ons. In this article I’ll provide an overview of the pros and cons of each of these approaches.
QuickBooks Built-In Reports
Assuming that you have simple reporting needs, using QuickBooks built-in reports to generate financial statements will probably work just fine for you. To generate such a P&L report in the Desktop versions: Choose Reports, Company & Financial, and then Profit and Loss Standard. As shown in Figure 1, by default this report will include account numbers and account names.
Figure 1: The Profit and Loss Report displays both the account numbers and account names.
There is a preference you can use to suppress some, but not all of the account numbers: Choose Edit, Preferences, and then Reports & Graphs. Choose the Company Preferences tab, and then choose Description Only in the Reports – Show Accounts By section. As shown in Figure 2, the account numbers vanish for some top-level accounts, such as Equipment Rental, Job Materials, and so on, but it still shows the account number for Job Expenses. To temporarily suppress all account numbers on reports, you can choose Edit, Preferences, and then Accounting. On the Company Preferences tab you can uncheck for Use Account Numbers. However, this also turns off the ability to use account numbers within all of QuickBooks transaction screens, so you may quickly tire of toggling this setting on and off whenever you run reports.
Figure 2: Adjust preferences to hide some account numbers on. Unfortunately hiding them all affects other screens outside of reports, so it will be arduous to toggle the setting off and on.
Do keep in mind that no matter your approach for building reports, you must always ensure that the data is current. Don’t fall into the trap of thinking that just because a report is onscreen in QuickBooks that the data is up to the minute. As shown in Figure 2, it depends upon the choice you make in the Reports & Graphs section of the My Preferences tab in QuickBooks. To be safe, always click the Refresh button shown in Figure 1 if there’s any possibility any user has modified a transaction in QuickBooks while you have a report onscreen.
QuickBooks Statement Writer
The QuickBooks Statement Writer is an alternative available to QuickBooks Accountant or Enterprise users. This tool is not available in QuickBooks Pro or most Premier versions, but is designed to offer far more control over financial statements by offering deep integration with Microsoft Excel, as well as Microsoft Word. In prior versions of QuickBooks this tool was known as the Intuit Statement Writer in QuickBooks 2011, and the Financial Statement Designer in QuickBooks 2009 and 2010. The QuickBooks Statement Writer emerged in QuickBooks 2012, and is available in all subsequent versions of QuickBooks. However, as of this writing here is my understanding of what QuickBooks Statement Writer requires:
- QuickBooks Accountant or Enterprise 2012 through 2014: These versions require Excel 2003, 2007, or 2010. More specifically, these version years do not work with the 64-bit version of Excel 2010. Excel 2003 and 2007 are only available in 32-bit versions, but starting with Excel 2010 you can take advantage of today’s higher-powered computers if you have a 64-bit version of Windows. Notice that I left Excel 2013 off of the list here. Quizzically QuickBooks doesn’t provide built-in support for this version of Excel, however Charlie Russell recently revealed techniques that should enable you to use Excel 2013 with these versions.
- QuickBooks Accountant or Enterprise 2015: This version supports both Excel 2007 and 2010 in either the 32-bit or 64-bit versions. Support for Excel 2003 is not included in this version, likely because Microsoft discontinued all support for Excel 2003 as of April 2014. Built-in support for Excel 2013 isn’t provided, but you can use the aforementioned workaround.
Do note that many users mistakenly think that 64-bit Windows requires 64-bit Office, which is untrue. If you’re using any third-party apps with Excel or QuickBooks, it’s always best to use the 32-bit version of Microsoft Office. To determine what you have installed, carry out the numbered steps in Figure 3. The 64-bit versions of Microsoft Office, which includes Excel, provide faster calculation power, but like a Porsche used for a daily commute in Manhattan, most users will never tap into even a fraction of the additional power available. At this point the 64-bit versions pose more conflicts with other software than computing benefits for the average user.
Figure 3: Follow these steps to determine if you have a 32-bit or 64-bit version of Windows.
The QuickBooks Statement Writer offers a high degree of control over customized financial statements. A wizard-based interface walks you through developing your financial statements, which you can save and access on demand, so that customized reporting packages become set-and-forget. Again, it unfortunately does require either the Accountant or Enterprise versions of QuickBooks. Figures 4 and 5 offer a sense of the QuickBooks Statement Writer Interface, both within QuickBooks and Excel.
Figure 4: Open the QuickBooks Statement Writer from your opened QuickBooks company. You can immediately create professional reports.
Figure 5: The QuickBooks Statement Writer walks you through building the report in a step-by-step fashion. Once you create the report, you can easily make modifications in Excel.