Smart Reports - Step by Step Instructions

Smart Reports for QuickBooks Step by Step Instructions

One of the biggest complaints we hear from accountants with QuickBooks clients is the time and aggravation associated with issuing financial statements for QuickBooks. The interface to Excel has helped, but the process is still tedious.  Accountants are always trying to figure out how to make the process more efficient. We have found an answer!

 

The Smart Reports for F/S Prep tool, developed by Big Red Consulting, was designed to offer Accountants and others flexibility not available in QuickBooks alone without the cumbersome, time-consuming process required to make the modifications in Excel.  We have worked extensively with them on this tool based on years of experience as CPAs trying to issue financial statements from QuickBooks, as well as working with countless Accountants as they struggle with the same issues.  During 2003 many releases have become available that greatly increase the ease of use and functionality of the tool. 

 

The tool works with Excel 97, 2000, and XP for Windows.  It is accessed from within Excel using menus it creates.  It does not change Excel in any way, it only adds to the functionality.

 

The Smart Reports Add-in is a tool that integrates with Excel and QuickBooks. It is intended for the accountant or other QuickBooks user who needs to massage and change QuickBooks financial reports for presentation or reporting purposes. It permits quickly producing reports in an automated fashion not available within QuickBooks. The tool can be customized using each company"s format and requirements, and the changes are "memorized" in profiles which can be applied to QuickBooks reports multiple times. This way the tool is set up once and then at each reporting period the same modifications can be applied saving any hours of manual work per month.

 

The tool can be customized using each company"s format and requirements, and the changes are "memorized" in profiles which can be applied to QuickBooks reports multiple times. This way the tool is set up once and then at each reporting period the same modifications can be applied saving any hours of manual work per month.

 

One of the major advantages of the tool is that is not version specific (although QuickBooks Pro 99 or higher is recommended).  The tool is designed to work with QuickBooks cross tab reports sent to Excel.  A cross tab report is a report that has a summary field of data laid out in rows and columns.  The report can handle reports with single and with multiple columns.  The reports are manipulated in Excel (using profiles that can be saved a re-used) so the tool works with the Accountants' Review Copy as well as a fully functioning version of the software.

 

Another advantage of the tool is that the Accountant can choose to attach Word documents for the Title Page and Accountants' Report making the process of printing the financial statements coordinated and efficient.



How to obtain the tool

 

The tool is a downloadable product that can be obtained by clicking on the following links:

 

For the free trial version that permits a use of the tool for 30 days or the fully functioning version:  http://www.4luvofbiz.com/succ_quic_fsprep.html

 

Once you add the product to the cart, view the cart, and proceed to the checkout, an e-mail with the link to download the product will be received.

 

  1. Download the zip file to your local machine keeping track of where it has been saved.
  2. Unzip the file and pay attention to where the file is located.  WinZip or StuffIt Expander is required to unzip the zip file. Both programs are available for free in Mac and PC formats. PC Users: To download a free evaluation version of WinZip 8.1, go to: http://www.winzip.com/ddchomea.htm Mac Users: To download a free version of StuffIt Expander, go to: http://www.stuffit.com/expander/index.html
  3. Open Excel
  4. Choose Tools > Add Ins
  5. Browse to find the file
  6. Click on OK then OK again.  You should now see an additional pull down menu option across the top of Excel.

 

Note: After the Smart Reports add-in is installed and used, a file names "Saved Smart Reports Profiles.xls" is created in the same folder as the add-in.  This protected file contains your profiles.  If you rename or delete it, your profiles will be unavailable to you.  If you re-install the add-in from another folder, your profiles will also be unavailable.  When updating the add-in with a new release, be sure to keep your saved profiles in the same folder as the add-in.

 



How to use the tool

 

Step 1 – Open Excel

When QuickBooks automatically launches Excel, any Add In products are disabled.  If Excel is opened prior to sending the report from QuickBooks this issue is eliminated.

 

Step 2 – Create the Report(s) in QuickBooks

It is possible to start with a standard report in QuickBooks and use the tool.  However, if additional or custom information is needed, modify the report in QuickBooks first.  If this same report will be needed in the future, memorize the report in QuickBooks to permit use of the template in the future.  The tool is designed to work with Balance Sheet, Profit and Loss, Trial Balance, Budget versus Actual, or any other similarly structured cross-tab reports (summary reports).

 

Step 3 – Click on the Excel Button

Click on the Excel button to transfer the report from QuickBooks into Excel.  It is possible to send the report to a new spreadsheet or an existing spreadsheet.  Click on the advanced tab to confirm that the header will be in the page set up, not on the screen.  The advanced tab will also permit other changes such as removing space between columns.

 

 

Note: If everything is not one report, it is possible to repeat this process for all the report to be used and/or combined.

 

Step 4 – Create a Profile

The tool has many features that provide the flexibility that is not available anywhere.  The various features and changes are stored in a "profile" to make future use of the tool for a specific report and/or client automated.  It is possible to enter a unique profile name (such as ABC Co Balance Sheet or ABC Co Income Statement) then save or delete it as needed.  The profile is automatically saved (unless the box is unchecked) when the option to close or update the report is chosen to be sure to change the name first if necessary.  Included in the step by step process below is an overview of the feature and several suggestions as to how it can be used.

 

From within Excel, click on the menu pull down labeled "SmartReports for QuickBooks."  The first option is "Transform Report."  This provides numerous ways to manipulate the data.

 

 

a.       Merge Rows – For financial statement presentation, it is often preferred to merge accounts.  An example would be merging undeposited funds into the checking account, or possibly all the cash and cash equivalents are individual accounts in QuickBooks, but are a single line on the issued financial statements.  Numbers from the two rows are merged together, using smart logic for the result, i.e. if you merge numbers with the natural opposite sign, the result is correct.  E.g. if you merge an expense into another expense the numbers are added, but if you merge an expense into an income account, the expense is subtracted from the income.

b.      Departmentalize Columns – This feature permits "splitting" the report into several columns.  The column names can be created within the tool by simply typing it into the appropriate box.  A perfect example is a Balance Sheet by location (to match the Profit & Loss by Class).  Each individual account can be distributed into a specific column (or allocated based on a percentage to several columns).  Note: Often the most efficient way to deal with this issue is using sub-accounts in QuickBooks that can then be distributed using the tool.

 

 

c.       Rename Row Labels – If the account name is not appropriate for financial statements, it is possible to change it.  For example, a non-profit would not have "equity" it would have "fund balance."  Maybe the client has an account called "Depreciation Allowance," but the Accountant would prefer it be called "Accumulated Depreciation" for presentation purposes.  Another example would be instead of Total Checking/Savings to change the label to be Cash and Cash Equivalents.

d.      Move Rows – The row can be moved to another position on the report.  For example, Accounts Receivable can be moved before the first account in the Other Current Asset section, or Accounts Payable can be moved before the first account in the Other Current Liability section.  The resulting subtotals will be updated accordingly.

e.       Manage Subtotals – This feature adds the ability to modify any group of accounts on the report.  This works nicely because the total of a group of accounts is essentially a restatement of its contents, so reports where either the detail or the subtotaling is removed remain essentially intact.  The detail can be removed (this permits sending the expanded report from QuickBooks and then removing only those sub-accounts that are not be presented) as well as removing the leading and subtotal rows.  For example, Accounts Receivable is moved into the Other Current Asset section then the heading and subtotal rows can be removed using the delete subtotals option.  Below is another example that shows how either option works:

 

Before:

 

Delete the detail:

 

Delete the subtotals:

 

Entertainment

    Teambuilding

    Restaurant

Total Entertainment

 

50.00

45.00

95.00

 

Entertainment

95.00

    Teambuilding

    Restaurant

50.00

45.00

 

f.        Rename & Reorder Columns – With this feature, it is possible to remove column heading by simply making them blank.  It is also possible to rename and/or change the order of the columns QuickBooks uses for the report.  Does the date showing as the column title on the Balance Sheet make you crazy?  With this feature, you can change it to say Month or whatever you want instead.  Another use for this feature is for personal financial statements where the Budget column has been used for Market Value.  If this feature is used with department columns above, create two profiles, one to do the major manipulations on the report first, then a second to re-order the columns once they have been created, if needed.

g.       Manage report headers and footers – Quickly and easily change fonts, bold, size, and wording on the report header and footers.  Want the title of the report to read Income Statement instead of Profit & Loss?  Want to add an Accountant report statement at the bottom of the report?  With this feature automating the change each time the financial statements are issued is a breeze.

h.       Round to the nearest dollar – Get rid of the distracting cents (and comply with the suggestion from the CA State Board of Accountancy).  Typically this is $1 for removing the cents from the report.  For example, it is possible to round to the nearest $5, $10, whatever for projections.

i.         Force Balance Sheet – choose the account that will be used to keep the Balance Sheet in balance when it is manipulated.  This is especially important if the departmentalize column feature will be used on the Balance Sheet.

j.        Delete rows where all amounts are 0.00

k.      Remove account numbers – eliminate the annoying process of turning off the account number preference to print financial statements and then turning it back on to complete more work.  No time consuming process is needed to manually remove the account number from each line of the report, the tool does it with a single click in the profile.

l.         Add % of income columns to Income Statement – for comparative or multiple column reports, the tool will automatically add the column with the calculations for you.  No more annoyance with QuickBooks not providing % on all the columns or not next to the column of results the percentages relate to when creating a month and year to date Profit and Loss report.

m.     Summarize Balance Sheet or Income Statement – this feature "rolls up" all detail with result being only the "Total" rows.

 

 

n.       Smart Currency Formatting – This feature allows you to automatically add currency symbols to the report using your currency symbol of choice.  Currency symbols are added on the first and last numbers of major account groupings.  When checked, % symbol is used sparingly.  It is also possible to have automatically format negative numbers like (50.00)

o.      Tone down formatting – reduces the bold underlining defaults from QuickBooks for a more professional report.

p.      Create a copy of the report – this feature is very helpful, especially the first time through on setting up the profile for a client.  The "transformed" report is a new sheet in the workbook.  This makes it quite easy to return to the original report (by clicking on the appropriate sheet), make any necessary changes to the profile and "try again."

Step 6 – Review the results and make changes

Review the report that is created by the tool to confirm that there are no other changes that should be made to the report presentation.  If changes are needed, return to the original report, make the changes to the profile and try again.  This is an important step each time the reports are created due to changes to the chart of accounts that may have been made since the last time the statements were issued.  One trick that saves time in the long run is to use the tool to create the profile using the year end statements from the prior year.  Typically the year end statements include all the accounts (where as the first month, for example, may not have all the expense accounts for the year) plus the reconciliation for the formatting is typically easier since there is a specific example to match.

 

Step 7 – Merge Similar Reports

This feature alone is well worth the price of the tool. 

 

Have you ever wanted a report that QuickBooks does not quite handle?  For example, a month, year to date, and previous year to date comparative Profit and Loss report?  Or how about a month budget versus actual and year to date budget versus actual all on one page wide?  Maybe there are two locations, each with their own QuickBooks file, but one legal entity, wouldn't it be nice to merge these two reports together?  That is the purpose of this feature.

 

In addition, it is possible to print the report as one page wide.  As the reports are merged, the progress is displayed in Excel's application status bar.  It is possible to sort the columns of the resulting merged report by title, placing like titles next to each other.

 

Step 8 – Make Financial Reports Collection

By using this feature, it is possible to print all the reports, including Word documents for the title page, and report, all in one easy step.

 

 

It is also possible to print one copy by checking the box as the reports are collected into the one workbook.

 

Step 9 – Print active workbook as report

If additional copies are needed, it is possible to print this new workbook (with all the components combined as individual sheets) by clicking on the print active workbook as reports option from within the tool pull down menu.  When using this option, do not check the box for numbering the pages or each page will include a page number (for example, the title page will be 1 of x, the report will be 2 of x, etc).  From this screen it is also possible to designate the number of copies to be printed.

 

 



Appendix A

 

 

For a chart that compare the four methods of issuing statements from QuickBooks, visit http://www.4luvofbiz.com/quic_news_91.html

 

 

More information on this topic

Smart Reports