Nov3rd banner
Caesar's Brook Babblings
QuickBooks News You Can Use 
August 2010 - Vol 3, Issue 8
In This Issue
Exporting Quickbooks Reports to Excel
Handling Bounced Checks
Job Costing Corner
Intuit Service Outages
Caesar's Brook

Welcome to the August issue of Caesar's Brook Babblings.
In this month's feature article we look at exporting QuickBooks reports to Excel.  I'm sure most of you are thinking, "I already know how to do that."  Most users do.  But when was the last time you took a close look at the export options?  As usual, I learned something new and I bet you will, too.
Schooner Mary DayThis month's QuickTip shows you how to handle a bounced check and the Job Costing Corner talks about the importance of using items.  Finally, I offer you a couple of pointers to information about the extended service outages that Intuit experienced in June and July. 

Schooner Mary Day

Savor the summer!  As you are reading this, we will be sailing on the Schooner Mary Day out of Camden ME for a week.
FeatureFeature Article
Quickbooks to Excel 
Exporting QuickBooks Reports to Excel
When you run a report in QuickBooks, you will find an Export button at the top.  When you click on this button it opens up a dialogue box with two tabs, Basic and Advanced.
Basic Tab
On the Basic Tab you have options to export your report to either a comma separated values (.csv) file or to an Excel workbook.   It also allows you to choose whether to export the data to an existing Excel workbook or a new one and, if you are exporting to an existing workbook, whether to overwrite the data on an existing sheet or create a new one.

If you export the same report and manipulate the data with Excel every week or month, it may be possible to create an Excel workbook with a sheet to hold the source data and a sheet with your customized report that links to the source data.  Theoretically, you can then update your customized report by exporting your new data into the existing source data sheet.  However, you need to be aware that any changes in the structure of the report you are exporting from the previous one, such as changed or additional line items, will result in your customized report displaying incorrect data.   You can mitigate this problem by modifying your QuickBooks report to display all rows and/or columns but if you add or change an item or an account so that it displays in a different location on the report, you will need to change the links in your spreadsheet.  (You will find the option to display all rows/columns under the Advanced button on the Display Tab of the Modify Report Dialogue for most reports.)  If this is something you are struggling with, please contact me to discuss possible alternative solutions.

Advanced Tab

Export -- Advanced tab

The Advanced Tab has lots of little gems you may have overlooked.  First and foremost are the options to preserve QuickBooks report formatting options.  I always turn off the options to preserve Fonts, Space between columns, and Row height.  By default QuickBooks exports using 8 point Arial font which does not match Excel's default font.  If you turn the Font option off, the report will be exported to Excel's default font which saves a lot of reformatting later.  Also, by default, QuickBooks adds extra narrow columns to create space in the report.  Turning off the Space between columns option will prevent these from exporting with the data.  Unfortunately, instead of creating extra rows to separate groupings on an exported report, QuickBooks creates taller rows.  Turning off the Row height option allows you to easily insert your own blank rows where you want them without having to fiddle with row heights.

Next is a set of Excel features that can be turned on and off.  Note that any of these features can be set on any workbook in Excel, but it saves steps to turn them on or off here.  By default, AutoFit (column width), Freeze panes, and Show Gridlines are all turned on, and I've never had occasion to turn them off, but the option is there. 

If you are interested in summarizing your data by various groupings, turn the Auto Outline feature on.  Although you can use the Collapse/Expand button on the QuickBooks report to summarize one level of detail, using Auto Outline in Excel allows you to expand and collapse at any level.   

Auto Filtering places a drop-down menu at the top of each column of data, allowing you to sort and/or filter for one or more values. Again, this is more flexible than filtering within the QuickBooks Modify Report interface.

Finally, if you don't want the QuickBooks header and footer to print on your Excel report, select the Printing option to Send header to screen in Excel where it can be easily deleted.

QuickTip of the Month
Bouncing check 
Handling Bounced Checks

Insufficient FundsIf you accept checks from your customers, sooner or later, one of them is going to be returned for Non-Sufficient Funds (NSF).   The best way to handle these checks in QuickBooks is to create an Other Charge type item in your item list called NSF or Returned Check.  In the account field, enter your bank account.
Optionally, you can set up a second Other Charge type item called NSF Fee or Returned Check Fee to pass along to the customer any service fees your bank charges you.  The account field for this item should point to either the account you use for bank service charges or to an income account.

When you receive a notice from your bank that a check has been returned: 
  1. Using the Write Checks window, post any service charges that your bank has charged you.
  2. Create an invoice for the customer using the NSF Check item and the amount of the original check.  In the description field add the check # and any additional information you wish to provide.  Add a second line for the NSF Fee.  The invoice will reduce the balance in your bank account by the amount of the returned check and create a new receivable for the customer.
  3. When you redeposit the check or receive a replacement from your customer, record the payment against the invoice and create a new deposit.
Job Costing Corner
Job Cost Wizard 
Always Use Items

The real power of QuickBooks is in setting up your items to create the reports that help you run your business.  This is especially true for anyone who tracks profitability by job.

Your rule of thumb for all transactions should be: If it is going to be assigned to a job, it must have an item, and if it is going to a Cost of Goods Sold account, it must have both an item and a job. 

Lemonade StandThis means that you will need to set up items for things that will never show up on an invoice to a customer and you may need to set up a customer:job to capture costs for things like consumable materials purchased in bulk that cannot be assigned directly to a specific job.

Assigning a job to every transaction that goes to a Cost of Goods Sold account will allow you to tie your P&L by Job to your overall P&L at the Gross Profit level.  Using an item on every transaction will allow you to use the Estimate vs. Actual and Job Profitability reports more effectively.  (Costs posted directly to an account on the Expense tab of a transaction rather than charged to an item show up on these reports lumped together under "No Item".)

Job Cost Item

If you set up items for job costing purposes that you do not want to use on an invoice, make them a double-sided item by checking the box that says "This item is used in assemblies or ...."  Put "DO NOT USE" in the Sales Transaction description box and assign the Income Account to an Other Expense Type Account called "This should be zero."  Be sure to review the "This should be zero" account periodically to ensure that the balance really is zero.
Intuit Service Outages
We'll Be Back SoonIn both June and July, Intuit experienced major service outages that lasted for several days and impacted QuickBooks customers who use a broad range of Intuit services including QuickBooks Online, Merchant Services, Payroll, Document Management, and Email.  I'm providing pointers to two sources of information about the outages. 

The first is an article written by two of my fellow QuickBooks consultants which provides a simplified explanation of how Internet computing works and some suggestions for continuing to use your desktop version of QuickBooks when Intuit's servers are not available. 
The second is a letter from Intuit's CEO, Brad Smith, talking about the outages and Intuit's plans for the future
Although these outages shouldn't have happened, we have all experienced situations where weather or infrastructure failures have resulted in lost connectivity and, as frustrating as it is, it is important to remember that these things can and will happen.  If you, as most of us do, depend on online services to run your business, it's a good idea to have a fallback plan in case those services become unavailable.
banner 2I hope you found these babblings useful.  Your feedback is important to me.  Please drop me a line and let me know what you think.
Susan Dugdale
Caesar's Brook Business Solutions, LLC
QuickBooks and QuickBooks ProAdvisor are registered trademarks and/or registered service marks of Intuit Inc.
Copyright 2010 Caesar's Brook Business Solutions-All Rights Reserved
Caesar's Brook Business Solutions LLC | PO Box 1102 | Amherst | NH | 03031