Advanced Features

Advanced Features

If you’re like most QuickBooks users, you rely on the Profit & Loss Standard report to monitor how your business is doing. However, you may have overlooked an even more valuable report: the Statement of Cash Flows.

The Profit & Loss Standard (P&L) report is important in its own right, but it only provides partial insight into the health of your business. While the P&L shows what you earned and spent, the Statement of Cash Flows shows you where the cash came from and went to, also known as sources and uses.

As you’ll see in this article, you can use the Statement of Cash Flows to determine the how various activities increased or decreased your cash balance during a given report period.

Cash versus Accrual

Unlike some accounting packages, QuickBooks allows you to run most reports on either the cash or accrual basis.

Cash-basis means that transactions don’t appear on your Profit & Loss statement until either your customer pays their invoice or you pay a vendor (or employee). So, if you enter a bill in QuickBooks to be paid later, the expense won’t immediately appear on a cash-basis P&L.

Similarly, invoices that you send to customers won’t immediately appear on a cash-basis P&L. The expense appears when you write a check to the vendor, and the revenue appears when the customer honors their invoice. Accordingly, cash-basis reports don’t necessarily report a company’s true financial performance.

You could have a stellar looking Profit & Loss Report, but a list full of unpaid bills in QuickBooks. Accordingly, many accountants prefer that business owners use accrual-basis reports.

Accrual-basis reports recognize the effect of every transaction on your P&L immediately. Customer invoices appear on accrual-basis P&L reports as soon as you save the transaction, as do unpaid vendor bills. You can easily see the significance of these differences in Figures 1 and 2.

Cash-basis reports only reflect paid transactions.

Accrual-basis reports include all transactions – both paid and unpaid.

Accrual-basis reports provide a much better picture of where the business stands, but can make it harder to understand your current cash position. However, a cash-basis P&L isn’t a panacea for managing cash flow, as your business has many transactions that don’t affect the P&L.

For instance, loan payments, owner distributions, and owner contributions affect your balance sheet, which tracks assets, liabilities, and equity. Fortunately, the Statement of Cash Flows reflects these types of transactions and more, so it’s a great companion to both cash-basis and accrual-basis P&L reports.

Set Your Preference

You can instruct QuickBooks to always display your reports on either cash or accrual basis:

  1. Choose Edit, and then Preferences.
  2. Choose Reports & Graphs, and then Company Preferences.
  3. Specify either Cash or Accrual, and then click OK.
  4. You can set either cash or accrual as your default report format.

Of course, at any time you can change a report to the other format. For instance, if your preference is set to accrual, but you may sometimes want to view a cash basis P&L:

  • Choose Reports, Company & Financial, and then Profit & Loss Standard.
  • Click the Modify Report button, and then choose Cash in the Report Basis section, as shown in Figure 4.

You can change the accounting method for your P&L on the fly.

  • NOTE: Most, but not all, reports in QuickBooks allow you to change between cash and accrual. When a report is onscreen, choose Modify Report.
  • If you don’t see the Report Basis section, shown in Figure 5, then you’ll know that you can’t toggle the report basis. Now that you understand the ins-and-outs of running cash and accrual basis reports, let’s explore the Statement of Cash Flows.

The Statement of Cash Flows

Let’s say that your cash balance at the beginning of your fiscal year was $100,000, and today it is $75,000. The net income figure on your P&L won’t give you the full details on why your cash balance decreased, but the Statement of Cash Flows will. To do so, choose Reports, Company & Financial, and then Statement of Cash Flows.

Report periods: As shown in Figure 5, this report automatically defaults to This Fiscal Year-To-Date, but you can choose another time period if you wish. To do so, make a choice from the Dates drop-down list, or modify the From and To dates, and then click the Refresh button.

The Statement of Cash Flows defaults to the current fiscal year.

Your Statement of Cash Flows report will include up to three major sections:

  • Operating Activities
  • Investing Activities
  • Financing Activities

Don’t worry if your report only includes one or two of these sections – sections only appear when you had relevant transactions during the report period. Let’s explore each of these sections individually.

Operating Activities

The Operating Activities section of the Statement of Cash Flows recaps activities related to running your business. This section will always start with Net Income, followed by an adjustments section.

The adjustments reconcile your net income with the net cash provided by the operating activities. For instance, refer to Figure 5. Net income s $112,999 but the Net Cash Provided by Operating Activities is $42,584. Accordingly, the statement of cash flows identifies the $70,415 difference. Let’s investigate a couple of the items:

Accounts Receivable (-$71,759): During the report period we sent invoices to our customers, of which $31,503.08 remain unpaid. These unpaid invoices are reflected in the Net Income figure, so QuickBooks deducts these because we haven’t received this cash yet.

Inventory Asset (-$17,354): Amounts that we spend on inventory don’t become part of Net Income until we’ve sold the items. At that point QuickBooks posts the expense to cost of good sold, and reduces our inventory account accordingly. Purchasing inventory is a use of cash, so it appears as a negative amount on our Statement of Cash Flows.

Remember: The purpose of the Statement of Cash Flows is to reconcile our net income with the actual change in our cash account. Thus non-cash activities, such as unpaid customer invoices or amortized prepaid expenses get subtracted or added from Net Income, so that you can get a clear picture of where cash went during the report period.

Employee Advances (-$62): We paid $62 to an employee as an advance, which has not yet been repaid. This amount isn’t included in Net Income, but is a use of cash, so the amount is deducted. When our employee repays the advance, our Statement of Cash Flows will reflect a positive amount, since at that point we’ll have a $100 source of cash.

Prepaid Insurance ($893): During the report period we amortized, or used up, $893 of prepaid insurance. This expense is included in our Net Income figure, but we didn’t write a check for it during this report period, so QuickBooks adds this expense back.

Accounts Payable ($13,537): We’ve entered bills into QuickBooks totaling $13,537 that we haven’t paid yet. In effect, we’re temporarily borrowing this money from our vendors, so it’s a source of cash. Later, our Statement of Cash Flows will show a use of cash when we pay the vendor bills. This same treatment applies to credit cards and other liabilities.

As you look through the Statement of Cash Flows, you may also see Investing and Financing activities. Investing activities may include owner contributions as a source of cash, or in the case of the report in Figure 5, the purchase of $11,500 in furniture as a use of cash.

Financing activities will show borrowing on a line of credit or other loan as a source of cash, while loan repayments (net of interest) will appear as uses of cash. In the end, you’ll see exactly what caused your cash balance to increase or decrease during the report period.

Research: You can easily investigate why amounts appear on your Statement of Cash Flows. As shown in Figure 6, the QuickZoom icon appears when you hover over an amount. Double-click to display a detailed report, as shown in Figure 7.

The QuickZoom icon indicates that you can drill-down within a QuickBooks report.

A detailed report appears when you double-click on an amount within a QuickBooks report.

Organizing the Statement of Cash Flows

QuickBooks makes an educated guess at what accounts in your chart of accounts should appear on the Statement of Cash Flows. However, you may encounter instances where activities appear in the wrong section, or don’t appear at all on the report. You can easily remedy such situations:

  • Choose Edit, and then Preferences.
  • Choose Reports & Graphs, and then Company Preferences.
  • Click the Classify Cash button.

Place a checkbox in the appropriate column. You cannot remove balance sheet accounts from the statement, but you can optionally include income and expense accounts. However, keep in mind that this is not a typical need, and you should only proceed under the guidance of your accountant or tax advisor.

QuickBooks allows you to classify accounts as operating, financing, or investing activities.

Did You Know?

QuickBooks has a Product Information window that can provide a dizzying array of information. Press Ctrl-1 to display the window shown in Figure 9. Some key elements on this screen include the product number shown at the top.

Each QuickBooks user in your office should have the same release number. The size and location of your QuickBooks file is shown in the File Information section, while you can use the List Information section to determine how many customers and vendors you have in QuickBooks.

Press Ctrl-1 to view the Product Information window.

The price of gasoline is just one of many factors putting pressure on our economy as a whole. Now it’s more important than ever to keep a close eye on your company’s performance. Many business owners compare financial results to an annual budget. If you don’t have your budget in place yet, we’ll show you how to get started. Even if you have, we’ll show you how to use last year’s results as a measuring stick with comparative financial reports. Once you understand these techniques, we’ll explain why you should create a monthly appointment with yourself to ensure that your results continue to measure up and take action if they don’t.

TIP: Keep in mind that tough financial years do have a silver lining-you’ll likely pay less in income taxes. If revenues are down or expenses are up, don’t forget to trim your withholding or estimated tax payments accordingly. Doing so enables you to boost your cash flow now, rather than waiting around on a tax refund next spring.

Budget Basics

The QuickBooks Planning & Budgeting menu gives you the ability to create budgets and forecasts. In reality, both features work the same way, so we’ll use creating a budget as our example. But which one should you use? You might find it helpful to use the Forecast feature as an alternate budget and as a best-case scenario, while the Budget feature offers a better expectation of reality. Either way, here’s how to create a budget in QuickBooks:

  1. Choose Company, Planning & Budgeting, and then Set Up Budgets.
  2. When the Set Up Budgets window appears, click the Create New Budget button in the upper right-hand corner.
  3. Select the year that you’d like to create a budget for (such as 2010 or 2011), select Profit and Loss, and then click Next.

Balance sheet budgeting: QuickBooks offers the ability to create a budget for balance sheet accounts, such as planning for expected levels of cash, inventory, accounts receivable, liabilities, and so on. However, most small business owners find that just a Profit and Loss budget is sufficient for their needs.

  1. Most users will choose No Additional Criteria on the next screen. However, QuickBooks does provide the option for a more granular budget that you break down to the customer, job, or class level. Click Next once you make a selection.
  2. The next screen asks if you want to start with a blank budget from scratch or if you want to use last year’s actual data as a starting point. Most users will find it helpful to use the previous year as a starting point. Click Finish after you make a choice.
  3. At this point you’re presented with a screen similar to Figure 1. You won’t see any numbers if you chose the From Scratch option in step 5.

Set Up Budgets

Starting with prior-year actual numbers can jumpstart your budget process.

  1. Proceed with entering or updating your budget. Click the Save button as needed to preserve your work as you go, and then click the OK button when you’re finished.

Budget Tips: The Copy Across button enables you to copy the same amount across all twelve months. As shown in Figure 2, the Adjust Row Amounts button provides a quick way to adjust existing numbers up or down by either a percentage or dollar amount. You can edit your budget at any time: choose Company, Planning & Budgeting, and then Set Up Budgets. Choose your budget from the Budget list, and then make changes as needed.

Adjust Row Amounts

The Adjust Row button makes it easy to quickly increase or decrease budget figures by a dollar amount or percentage.

Budget Reports

QuickBooks offers four budget and two forecast reports. You’ll use these steps to run most of these reports:

  1. Choose Reports, Budget & Forecasts, and then the report of your choice.
  2. A three-screen wizard appears, asking you first which budget or forecast you wish to use. Once you’ve made a selection, click Next.
  3. The next screen asks which report layout to use – you may only choose one, Account by Month – click Next after you confirm your choice.
  4. Click Finish to display your report:
    • Budget Overview – As shown in Figure 3, this report provides a twelve-month view of your budget.
    • Budget vs. Actual – This 52 column report can be tricky to navigate, as the default format shows these columns for each month, as well as a 12-month total.

Profit & Loss Budget Overview

Budget overview gives you a birds-eye view of your 12-month budget.

Report Taming Tips: There are a couple of ways to bring this report down to size. First, most users can eliminate the % of Budget column. To do so, click the Modify Report button, and then deselect % of Budget in the Add Subcolumns For section. Next, you can shrink the width of the columns. To do so, drag the diamond between the first actual and budget columns to the left, as shown in Figure 4. When you release the left mouse button, choose Yes when asked if you want to resize all of the columns. Alternatively, click the Export button to send the report to Excel.

Profit & Loss Budget vs. Actual

Narrow column widths can condense particularly wide reports.

Profit & Loss Budget Performance – This report compares your month and year-to-date actuals to the budgeted amounts, and also displays the 12-month budget. Although this report doesn’t display dollar or percentage variances, you can easily add these columns. Click the Modify Report button, and then select $ Difference and/or % Of Budget in the Add Subcolumns For section, as shown in Figure 5.

Modify Report

It’s easy to add or remove columns on any QuickBooks report.

Budget vs. Actual Graph – This report doesn’t enable you to choose a budget – the current year budget is displayed automatically. As shown in Figure 4, this report enables you to get a graphic view of how your results measure up to your budget. You can choose between different budget views:

  • P&L By Accounts – This view compares your Profit & Loss accounts, also known as income and expense, to the corresponding budgets. The report automatically sorts variances by difference, and you can view up to six accounts at a time.
  • P&L By Accounts and Jobs – This view compares your P&L accounts on a job-by-job basis. Jobs with the largest total variance from budget will be presented first, and as with accounts, you can view six at a time.
  • P&L By Accounts and Classes – This view compares your P&L accounts on a class basis. As with the other views, you can view up to six classes at a time. This button appears even if you haven’t set the Enable Class Tracking preference.

Class Tracking: Classes allow you to you track costs by department, project, or other categories. To enable class tracking, choose Edit, Preferences, and then Accounting. On the Company tab, select Enable Class Tracking.

Graph Printing limitation: You cannot print more than one page of the budget graphs at a time, so you’ll have to click Next Group and then click Print to create a hard copy of each report group. QuickBooks doesn’t provide a way to print all of the graphs in one fell swoop. You also can’t modify the graph format, other than to choose a different date range.

Comparative Reports

Regardless of whether you use budgets in QuickBooks or not, it’s always helpful to compare this year’s results to last year. Doing so enables you to see trends in your data, such as how automobile expenses have increased. Such a report is just a couple clicks away:

  1. Choose Reports, Company and Financial, and then Profit & Loss Prev Year Comparison.
  2. By default you’ll see this year compared to last year. However, you can easily create a multi-year comparison:
    1. Click the Modify Report button.
    2. In the Columns section, choose Year from the Display Columns By drop-down list, and then click OK.
    3. On the report screen, choose a date range, such as 1/1/04 through 12/31/08, and then click the Refresh button. As shown in Figure 6, a multi-year comparison will appear onscreen. If you find this format helpful, click the Memorize button to save this report for later use.

Profit & Loss Prev Year Comparison

You can convert the Profit & Loss Prev Year Comparison into a multi-year report.

Summary

In this article we discussed how you can use the budget and forecast feature in QuickBooks to plan the future of your business. As each month rolls by, you can compare your plan to actual results. In addition, you can compare this year’s results to last year, or even the last several years.

Did you know?

Did you know that an accountant’s copy of a QuickBooks file can be converted to a normal QuickBooks company, i.e. a .QBW file? There are limited circumstances where you’d want to do so, because it’s not possible to merge two .QBW data files together. However, let’s say that you lose access to your QuickBooks company because your hard drive crashes or someone steals your laptop. These are situations where a converted accountant’s copy would be better than starting from scratch. If you need to do this, ask your accountant to carry out these steps in their version of QuickBooks:

  1. Choose File, Utilities, and then Convert Accountant’s Copy to Company File (QBW).
  2. Choose the Accountant’s Copy to convert.
  3. Click OK on the prompt shown in Figure 7.
  4. Assign a name to the new company file, and then click Save.

A final warning prompt will appear to confirm that this copy will overwrite any existing client copy of the books.

Quickbooks Information

Converting an accountant’s copy to a working QuickBooks company can serve as a disaster recovery method.

Of course, the best defense is to make frequent back-ups of your QuickBooks data on removable media, such as the USB flash drives that often cost less than $10. These easily allow you to carry your QuickBooks back-up offsite, such as in your purse or briefcase. But, it’s good to know that your accountant might be able to provide a working QuickBooks company – as long as you recently sent your accountant’s copy along to them.

Many businesses can benefit from tracking revenue and expenses by project. Known as job tracking in QuickBooks parlance, it’s ready for your immediate use. For instance, let’s say that your company installs residential elevators. Several different builders contract with you to install your product in their high-end homes. In such cases the builder would be the customer, while each house that you install an elevator in would be a job. You can associate as many jobs with a customer as you wish. As you’ll read in this article, you can not only associate revenues, but also expenses with jobs, as well as create a budget so that you can track your prognostication against reality.

Jobs versus Classes

Users sometimes confuse jobs with classes. Both allow you to track revenue and expenses, but are typically used for different purposes:

  • Jobs are helpful when you want to track activity for a specific project for any of your customers. In addition, jobs allow you to create estimates and utilize progress invoicing.
  • Classes are effective when you want to track costs by department, for an internal project, or other activities where you’re not necessarily billing a customer. In addition, you must enable classes in QuickBooks by choosing Edit, Preferences, Accounting, and then choose Use Class Tracking on the Company Preferences tab. For instance, a medical office might create a class for each doctor, so that expenses can be allocated fairly among practitioners. Once you enable class tracking, you’ll notice Class fields appear on various transaction screens that you can use as needed.

Note that you can associate customer invoices and expenses with classes, but you’ll find that jobs typically work better for that purpose. However, you can also use jobs and classes in conjunction with each other, for even greater tracking capabilities.

Create a Job

Unlike classes, no special set up is required for jobs, which are always associated with a customer. Since you probably already have numerous customers established in QuickBooks, let’s see how to add a job:

  • Click on the Customer Center button on the toolbar (or press Ctrl+J).
  • Right-click on a customer name in on the Customers & Jobs tab, and then choose Add Job. Alternatively, you can choose Add Job from the Edit menu after you select a customer name.

Beware: It’s possible to create a job within a job, so be sure that you click on the customer name, and not a job name, when you click Add Job.

The Add Job choice appears amidst many other choices.

At this point the New Job window appears. Assign a job name, and then complete the Address Info tab. Much of this should carry forward from the associated customer record, but you can override the information as needed for this job.

The New Job dialog box is almost a mirror image of the New Customer dialog box.

  • The Additional Info and Payment Info tabs work in the same fashion as with customers, but you’ll note that jobs have an additional Job Info tab. This allows you to maintain these fields:
  • Job Status: This field lets you assign one of these labels to the job: None, Pending, Awarded, In Progress, Closed, and Not Awarded. You cannot add additional choices to this list, but you should change the status as the job works through the various stages of its lifecycle.
  • Start Date: Enter the date the job begins.
  • Projected End: Enter the expected end date for the job. Doing so will later let you compare how your projected job end dates match up to the actual job end dates.
  • End Date: Leave this field blank until the job is completed.
  • Job Description: This field allows you to assign an additional description beyond what appears in the Job Name field at the top of the screen.
  • Job Type: This user-definable field allows you to assign types to jobs as you wish-choose Add New from the list to add a new type. You can edit or delete this list by choosing Lists, Customer & Vendor Profile Lists, and then Job Type List.

Job Info tab: Confusingly, the Job Info tab also appears when you add a new customer. However, you cannot add a customer and a job at the same time. You can use the Job Info tab to store information about that customer, but it’s best to first add the new customer, and then add the job in the fashion described above.

Assign Jobs to Transactions

Estimates, Sales Orders, and Invoices can only be assigned to a single job, but you can split other transaction types among multiple jobs. Therefore you’ll choose a job name from the Customer:Job drop down at the top of the Invoice screen-estimates and sales orders work in the same fashion. Other transaction types have either a Customer:Job column or a Name column where you can assign line items to specific jobs. For instance, Figure 3 shows the Customer:Job field on the Enter Bills screen. In addition, each transaction screen includes a Billable column where you can indicate whether an expense is reimbursable by your customer. You’ll leave the Billable field blank if you’re simply tracking costs for a given job, or click in the field to place a checkmark that will indicate that your customer should be billed for the charge.

The Customer:Job column on the Enter Bills window is one way to assign expenses to a job.

Payroll: The paycheck detail screen allows you to assign payroll to a job, but it’s best to use QuickBooks time tracking feature to do so. The paycheck detail screen doesn’t allow you to specify whether time is billable, but the other screens do. Choose Employees, Enter Time, and either Use Weekly Timesheet or Time/Enter Single Activity. The Enter Time window is shown in Figure 4.

Enable Time Tracking: Choose Edit, Preferences, Time Tracking, and then Yes to enable time tracking.

Use the Enter Time window to assign billable hours to a job.

Apply payments: Remember to apply customer payments against the job and not the customer itself. Otherwise you might encounter situations where the customer has a credit balance and the job has a debit balance that net out to zero.

Budgets

QuickBooks enables you to create a budget for your job, but there’s a catch: you can only budget within a single fiscal year. So, if your job spans more than 12 months, or crosses fiscal years, you may have to use some creativity, such as splitting the job budget between two fiscal years. However, with that caveat in mind here’s how you’ll create a budget for your jobs:

  • Choose Company, Planning & Budgeting, and then Set Up Budgets.
  • Click the Create New Budget button to launch the Create New Budget wizard.
  • Specify a fiscal year, choose Profit and Loss, and then click Next.
  • Choose Customer:Job on the Additional Profit and Loss Criteria screen, and then click Next.
  • Choose Create Budget from Scratch on the Choose How You Want To Create a Budget screen, and then click Finish.
  • You’re now presented with the screen, where you can select a customer and/or job, and then enter your budget on an account by account basis.

All customer and job budgets are stored within a single budget screen.

Consolidated Budget: Keep in mind that you only need to run the Create New Budget wizard once for a given fiscal year. Simply change the Customer:Job field on the budget screen to budget additional jobs. Once you’ve run the wizard, you can view or maintain your budget by choosing Company, Planning & Budgeting, and then Set Up Budgets. If necessary, choose your budget from the drop-down list. QuickBooks automatically assigns a name like FY2009-Profit and Loss by Account and Customer :Job.

Reports

Although job tracking is a helpful way to ensure that you bill your customers for reimbursable expenses, most business owners would agree that the primary benefit is the report capability. As you would expect, QuickBooks provides a variety of job-specific reports-choose Reports, and then Jobs, Time & Mileage to access these choices:

  • Job Profitability Summary: This report compares actual costs to actual revenues and shows a dollar difference. You can optionally display a percentage difference-this is helpful if you want to determine margins. To do so, display the report onscreen, and then click Modify Report. Click % Difference, and then click OK. By default this report summarizes all jobs, but you can limit the time frame or filter the report to view selected jobs or types of jobs.
  • Job Profitability Detail: This report allows you to view one customer or job at a time, and provides much more detail than the Job Profitability Summary report. However, this job shows data by inventory item. Note that if you select a customer, then all jobs for that customer are summarized together, but you can also select a single job instead.

The Job Profitability Detail report provides the best overview of a single job.

  • Profit & Loss by Job: This report provides a column for every job for the specified report period, and details activity by account-as opposed to the Item ID approach used by the Job Profitability Detail report.
  • Unbilled Costs by Job: This key report enables you to track any expenses that you’ve marked as billable but haven’t yet passed along to customers. For instance, the Enter Bills and Write Checks screens enable you to assign expenses to a job. An adjacent field enables you to also mark the charge as billable. In turn, QuickBooks notifies of pending unbilled charges when you invoice the customer.

TIP: It’s easy to disable the ‘unbilled charges available’ prompt on QuickBooks invoicing screen, so it’s a good practice to click the Add Time/Costs button on the Invoicing screen and look for unbilled charges on each tab.

  • Open Purchase Orders by Job: Most small businesses don’t use purchase order tracking, but those that do can use this report to determine what items are still on order for jobs in progress.
  • Mileage by Job Summary and Detail: Most small businesses simply have to absorb the cost of mileage, and therefore don’t choose to track mileage by job. However, if tracking this level of detail is helpful to your business, these reports will be a key tool. In such cases, use Enter Vehicle Mileage on the Company menu to log your travel, as shown in Figure 7.
  • You can assign vehicle mileage to jobs-and specify whether the miles are billable.

    The Job Is Done

    In this article we helped you get up and running with QuickBooks job tracking feature. We discusses how job tracking differs from class tracking, and showed how you can use job tracking to allocate revenue and expenses to jobs, as well as track billable charges. We helped you establish budgets for your jobs, and then we wrapped up the article with an overview of QuickBooks job tracking reports.

Although the Employees & Payroll reports menu in QuickBooks offers numerous reports that let you slice and dice your payroll data, you may sometimes yearn for more flexibility. When you do, the Summarize Payroll Data in Excel and Tax Form Worksheets in Excel features will transform your payroll data with just a couple of mouse clicks. The Summarize Payroll Data in Excel feature helps you review historical payroll transactions; while the Tax Form Worksheets in Excel lets you peer into the summary numbers that appear on the tax forms that you generate from QuickBooks.

Excel required: Note that you must have Microsoft Excel installed on your computer to use these features. If you don’t already have Microsoft Excel, you can download a free 60-day trial from www.trymicrosoftoffice.com.

Summarizing Payroll Data in Excel

This feature is available in QuickBooks 2004 and later, and enables you to generate numerous payroll reports in Excel with just a couple of mouse clicks. Keep in mind that the mix of reports that you see may vary, based on your version of QuickBooks. In addition, the Excel-based reports take two different formats:

Pivot table-based: Excel’s pivot tables feature summarizes rows of data into a concise format. In this case the rows of data are in QuickBooks, so the resulting spreadsheet becomes an extension of QuickBooks. In general, pivot tables offer several special benefits:

You can rearrange the pivot table by dragging and dropping fields

  • You can double-click on any number within the pivot table to drill down to the underlying detail
  • Certain fields in the pivot table include drop-down lists, from which you can exclude certain items or employees.
  • You can set the pivot table to put a page break between each employee or item, which enables you to easily print a separate report to share with each person on your team.

We’ll explore some of these capabilities later in this article.

Worksheet-based: The worksheet-based reports that QuickBooks generates are static in nature, meaning you can’t double-click on any numbers to view the underlying detail. These reports are similar in nature to reports that are generated when you use the Send to Excel feature to analyze any of QuickBooks built-in reports. You can, however, copy and paste portions of the reports into other workbooks, or modify the reports to meet your needs.

QuickBooks’ Summary Payroll Data Reports

The following payroll data reports appear in the 2007 and 2008 versions QuickBooks Pro or higher – other versions of QuickBooks might not include some of these standard reports:

  • Employee Journal – This pivot table-based report lists payroll transactions by employee, including pay date, check number, and deductions.
  • YTD Summary – This worksheet-based report summarizes compensation and withholding by employee. This report appears on a worksheet labeled YTD Summary 1.
  • Hours – This pivot-table based report summarizes hours by employee, by type – this enables you to see regular, overtime vacation, and sick hours in a concise format.
  • Rate & Hours by Job – This report summarizes payroll data on a job by job basis. Make a choice from the Customer:Job drop down at the top of the worksheet to see costs for a particular customer or job.
  • State Wage Listing – This report breaks out state-assessed taxes, such as unemployment, so that you can see the wages, excess wages, wage base, and tax amount for the specified report period.
  • Quarterly – This report provides summary level wage and tax data sorted by Social Security Number by quarter. If you choose to see payroll for an entire year, this report will include a column for each of the four quarters.

Depending upon your QuickBooks version, you may also be able to generate these reports:

  • 8846 Worksheet – This worksheet helps employers calculate the tax credit that can be collected on employer taxes paid on certain types of employee tips.
  • Effective Rates By Item – This pivot table-based report appears in a worksheet labeled Calculated %, and shows the percentage used to calculate various taxes, including Social Security, Medicare, and unemployment, as well as other payroll items such as disability and workers’ compensation.
  • YTD Recap – Similar in nature to the YTD Summary, this worksheet-based report lists employee activity on a payroll item-basis. This report will appear on a worksheet labeled YTD Summary 2.
  • Deferred Compensation – This report displays any compensation and deferred compensation by tax-tracking type, and is sorted by date.
  • 943 Worksheet – This worksheet helps employers that are required to file Form 943, Employer’s Annual Tax Return for Agricultural Employees.

You can easily analyze your payroll data in Excel:

  1. In QuickBooks choose Reports, Employees & Payroll, and then Summarize Payroll Data in Excel.
  2. Instructions will appear onscreen in Excel if you need to enable macros. Think of macros as custom programming embedded in an Excel workbook. QuickBooks ships with prebuilt Excel workbooks that contain the programming necessary to generate the Excel worksheets, but you must first instruct Excel to enable macros.
  3. Once macros are enabled,a dialog box will appearl that allows you to determine what reports should be generated in Excel from QuickBooks.
  4. Choose a time period, such as this Month, and then choose any of the optional reports.

The export from QuickBooks may take a couple of minutes, depending upon the size of your QuickBooks file – you’ll see onscreen progress indicators. A workbook with several reports will appear automatically. As discussed previously, you can double-click on any number within a pivot table-based report and view the underlying detail.

The Summarize Payroll Data in Excel feature creates a variety of reports with just a couple of mouse clicks.Double-click on any number within a pivot table to view the underlying detail on a new worksheet.

How To Print One Employee Per Page

You can set a pivot table-based report, such as the Employee Journal, to print one employee per page:

  1. Right-click on the Grand Total row, and choose Hide Row. Otherwise the grand total will appear on the last employee’s report.
  2. In the case of the Employee Journal, right-click on the Transaction Name heading, and then choose Field Settings.
  3. In Excel 2003 and earlier, click the Layout button, or in Excel 2007 click the Layout & Print tab. Choose Insert Page Break After Each Item, as shown in Figure 4.

Pivot table-based reports can be printed on a per-item or per-employee basis.You can apply this technique to any of the pivot table-based reports.

How To Create Payroll Tax Forms Worksheets in Excel

This feature – if present in your version of QuickBooks – makes it easy to view the underlying detail for the tax forms that QuickBooks can generate for you. As you’re probably aware, it’s easy to print payroll tax forms:

  1. Choose Employees, Payroll Tax Forms & W-2s, and then Process Payroll Forms.
  2. Choose either Federal or State, and then click OK.
  3. Choose a form from the resulting list, as shown in Figure 5, and then follow the onscreen prompts.

Most payroll tax forms can be printed directly from QuickBooks.

If you have questions about the numbers that appear on these reports, or you want to audit the figures, the Tax Form Worksheets in Excel provides the underlying detail with just a couple of mouse clicks.

    1. Choose Reports, Employees & Payroll, and then Tax Form Worksheets in Excel.
    2. After a moment an Excel workbook will appear onscreen. If a Welcome screen appears, follow the onscreen prompts to enable macros in Excel. Once macros are enabled, you’ll see a dialog box. QuickBooks can show you the underlying detail for several payroll tax forms.
    3. As you can see in Figure 6, you can generate one tax worksheet at a time:
      • Quarterly 941 – This worksheet summarizes the figures you need to complete your quarterly From 941, which you use to inform the Internal Revenue Service of the total income taxes withheld from employee paychecks, as well as the employee and employer share of Social Security and Medicare taxes.
      • Annual 944 – In certain instances the IRS will notify an employer in writing that Form 944 can be filed annually instead of filing Form 941 on a quarterly basis. Do not file Form 944 unless you receive instructions from the Internal Revenue Service.
      • Annual 940 – This worksheet provides the detail required to file your Federal Unemployment Tax Return.
      • Annual 943 – This worksheet provides the detail needed to compile the Employer’s Annual Federal Tax Return for Agricultural Employees.
      • Annual W2/W3 – This choice gives you the underlying detail for each employee’s W2 form, as well as the summary figures that make up your W3 form.Download IRS Forms: All IRS forms are available for free download. For instance, you can download Form 941 at www.irs.gov/pub/irs-pdf/f941.pdf. Simply replace 941 with the corresponding form number in the preceding Internet address.
      • State SUI Wage Listing – This form provides the details behind your State Umployment Insurance (SUI) form.

Once you choose a tax form, choose a report period from the list, or enter the dates of your choice.

  1. The Options/Settings button displays the dialog box shown in Figure 7, which lets you fine-tune the results provided by QuickBooks:

Most users won’t find it necessary to do so, but you can refine how QuickBooks generates the tax forms and payroll summary worksheets.

  • By default QuickBooks lists the company name and report dates in the page headers of your Excel worksheet. You won’t typically see these onscreen, but you will when you choose to print the worksheet, or display it in Print Preview mode.
  • Hide Detailed Data Returned from QuickBooks – depending upon the tax form you choose, you may see more or less detail onscreen. Typically you’ll want to leave this choice selected.

How To Change Report Options

You don’t have to return to QuickBooks if you decide that you want to generate a different tax form worksheet, or perhaps change the report dates. The steps differ slightly, depending upon your Excel version:

  • Excel 2003 or earlier: Choose Get QuickBooks Data or Update Tax Worksheet from the floating QuickBooks Link or QuickBooks Tax Link toolbars, respectively. These toolbars are easy to restore if you inadvertently close them: right-click on any of your Excel toolbars, and then choose QuickBooks link or QuickBooks Tax link.
  • Excel 2007 – Click on the Add-Ins tab of the ribbon, choose QB Payroll Summary Reports in the Custom Toolbars section, and then choose either Get QuickBooks Data to update the payroll summary, or Update Tax Worksheet to update a tax form.

Note that if you simply change the dates for the payroll summary or a tax form, your existing worksheet will be overwritten. However, if you choose a different tax form, an additional worksheet will appear within your workbook.

QuickBooks Pro can be used to manage many different files. In your case, you could have a QuickBooks file called “Business”, and a completely separate QuickBooks file called “Home” to manage the separate checkbooks. For more information on the creation of a new company file, click on File in the menu bar, then New Company.

The New Year’s here, the Christmas bills are rolling in, and income taxes loom. Maybe you can’t save money just now, but how about an easy way to save time and keystrokes? If you use Microsoft Outlook 2003, 2007 or 2010 for contact management and QuickBooks Pro, Premier, or Enterprise 2005 and up for financial management, you can synchronize data to avoid entering the same contact information twice.

It’s easy, but you need to take care to follow instructions precisely anytime you’re integrating multiple databases. You can’t unring that bell. Start by backing up your data in each program, as shown in Figure 1. For Outlook, check your help files; the data file format changed in 2003. QuickBooks users should use the program’s standard built-in tools by clicking File|Save Copy or Backup. You can either save your QuickBooks file locally (to a CD or USB flash drive) or use QuickBooks Online Backup (30-day free trial; starts at $4.95/month for 5 GB).

It’s very important that you back up your Outlook and QuickBooks files before you synchronize.

Get in Sync

QuickBooks lets you synchronize three kinds of contact information with Outlook:

  1. Customer contact information contained in your Customer & Jobs list
  2. Vendor contact information from your Vendor list
  3. Contact information on your Other Names list

Note: You can’t synchronize employee contact information.

To get started, click File|Utilities|Synchronize Contacts. QuickBooks Contact Sync must be installed on your PC before you do your first sync. When the window shown in Figure 2 opens, click OK and follow the instructions for downloading and installing.

QuickBooks provides a wizard that walks you through the process of downloading and installing QuickBooks Contact Sync.

QuickBooks will prompt you to shut down Outlook before you start, if you haven’t already done so. When the installation is finished, you’ll see the window shown in Figure 3. And you’ll notice that the installation has added a new toolbar to your copy of Outlook.

QuickBooks tells you when your QuickBooks Contact Sync has installed properly.

Click Finish and restart Outlook. You’ll see a window titled QuickBooks Contact Sync for Outlook (this can be disabled once you’ve gone through the initial import by unchecking the box in the lower left corner). Make sure you’re logged into QuickBooks as the Administrator and that the company file you want to synchronize is open.

Click Get Started. A box that says Connecting to QuickBooks will open, and there’ll be a short delay. After the connection is made, the Begin Setup window opens. Click the Setup button to launch the wizard. If you have more than one Outlook contact file (for example, if you use Outlook with Business Contact Manager), you’ll have to select the file you want to sync.

Click Next. The next screen asks you to specify which contact types you want to sync (customers, customer jobs, and/or vendors). If any of your contacts are personal, you can choose to exclude those. Click Next after each of those screens.

QuickBooks Contact Sync includes a mapping tool, which helps ensure that the correct fields in each program are matched. For example, Company in one program should “map” to Company in the other.

QuickBooks Contact Sync helps you make sure that fields in each program “map” accurately to each other.

The final step in the setup process is critical if you don’t want to lose important data, so choose the next option carefully. You need to tell QuickBooks Contact Sync what to do if the same contact exists in both programs but their properties are not exactly the same. Your options:

  • Let the Outlook data win
  • Let the QuickBooks data win
  • Decide in each individual case

Once you’ve made your selection, click Save. If you want to go back over any of these settings, click Setup. Otherwise, click Cancel or Sync Now.

After you’ve completed the first synchronization, you’ll need to perform a manual sync each time you want to make the databases match. To do so, click the QuickBooks ContactSync menu in Outlook. This menu provides a number of options, including Preferences.

Sync Now and Save Time Later

Saving time these days is saving money. You can use those extra minutes (or hours) to build your business instead of always having to worry about running it. QuickBooks Contact Sync can give you some of those extra minutes, help you avoid frustration, and aid in keeping your databases clean and up to date.

2012 is soon drawing to a close. Do you know where your physical inventory items are? Whether you keep them in a closet, in an unused office, or a warehouse, you need to keep a close watch on how many products you have, how many have been ordered, and when it’s time to reorder. Fortunately, QuickBooks has tools that help you track all of those numbers. If you’re conscientious about making use of them, you should have a good sense of the state of your inventory, wherever you store it.

Note: These tools are not available in Simple Start or QuickBooks Online.

Inventory 101

Let’s take a look at the life of an inventory item. First, you have to tell QuickBooks that you will be selling products. It asks for this information during the EasyStep interview, but if for some reason you didn’t set this up, you can still do it. Click Edit/Preferences, then Items & Inventory, and then Company Preferences. Make sure the first line is checked, as well as any others you want active, as seen in Figure 1.

You can have QuickBooks track your inventory by selecting this option in the Preferences window.

Next, check your Chart of Accounts to see if you need to add any accounts to meet your inventory needs. This is easy. Go to Lists/Chart of Accounts, or click the icon on the home page. The Chart of Accounts is simply a list of the accounts your company uses, and the balance for each. QuickBooks sets a chart up for you based on the type of company you have, but as your business grows, you may need to add more. Figure 2 shows an example of the Chart of Accounts window.

The Chart of Accounts window shows you the balance for each of your accounts.

To add a new account, click on the arrow next to Account and click New. Select the correct type of account, and answer the questions in the Add New Account window. If you have any questions here, consult QuickBooks’ help file.

See Your Inventory In High Definition

Next you’ll have to define your company’s products. Click Items & Services on the home page. The Item list opens. You can always come back here when you need to edit an item, but you may want to create one now. So click Item/New. You’ll see a screen thats fields will be blank. Item records in QuickBooks contain a good amount of information about each item, which will be used in forms like invoices and documents like reports.

An item record allows you to define your company’s product.

Fill out the information in each item record for each item you sell. You won’t be able to alter the numbers in the lower right corner; these come from other parts of the program. As for the other values, the need to make changes depends on the field. When you defined the item, you entered an On Hand amount. This of course will change as you sell, so you can change the reorder point. Conversely, the average cost (value of your inventory) is calculated by the program; it’s the total cost of items in stock divided by the number of items in stock. On P.O. and On S.O. simply indicate how much of your inventory is promised on purchase orders and sales orders.

Get Better Organized

QuickBooks lets you create assemblies, groups of items that are sold together as a kit. If you want to create one, click Lists/Item List, then click the arrow next to Item and click New. Click the arrow under Type, and select Inventory Assembly. Fill in the blanks on the window as you would for a single item, and select the individual inventory items in the box at the bottom.

Once you’ve defined your items and assemblies, you can use them in two places primarily: transactions forms and records. Let’s say you’re creating an invoice. As you’re filling out the form, you’ll be able to click the arrow under ITEM and view a list of the items you’ve created. Click on one, and the details you’ve entered (like price) will appear. Click Reports/Inventory to customize and run the reports available.

Maintaining an adequate inventory-not keeping too much or too little on hand-is critical to your company’s financial balance. QuickBooks’ tracking tools can help you meet that ongoing goal.

Some of the financial crystal ball-types are telling us there are signs that the recession may be drawing some of its last breaths. But those bills are still coming in, and you may have had a long, dry summer and less income that you can use to meet those business obligations.

The desktop versions of QuickBooks can help. They can’t magically make more money appear in your coffers, but they can help you manage your bills so you’re always aware of what’s coming up and don’t get any nasty surprises. This keeps both you and your vendors happy, and minimizes the chance of affecting your credit report adversely. You can also maximize cash flow by being hyper-aware of when each bill is due and timing them appropriately.

(These bill-paying tools are available in all QuickBooks versions above Simple Start.)

Enter First, Then Pay

Of course, you can mimic your old manual method of bill paying by simply using QuickBooks’ check-writing convention. But if you do this, you risk paying the bill twice. If you follow the process shown in Figure 1 by entering and the then paying, you’ll ensure that you record the expense in the same period it occurred.

To start, click the Enter Bills or Vendors/Enter Bills icon. The Enter Bills dialog box opens. If you received a bill, be sure that box in the upper right is checked, and that the Bill radio button is filled in.

You’ll find these icons on QuickBooks’ graphical flow chart.

The Enter Bills dialog box.

Next, click the arrow next to the Vendor line to select an existing vendor or add a new vendor. Change the date if necessary, and enter a reference number (this may avoid confusion later). Then, enter the amount due.

When you initially set up vendors, you either set up terms for each vendor or accepted the default. So the Terms field should already be filled in, and will generate the correct bill due date. Enter a descriptive memo in that field if you’d like.

Tip: Use the right-click menu when you’re entering bills to see more options.

Since this was an expense, you’ll want to record it as such. Make sure the Expenses tab is highlighted, and click in the Account field. Click the arrow that appears to drop down the list, and select the appropriate expense type. Fill in the rest of the field on the line, making sure to check the Billable box if this is something you can bill back to a customer. If the expense needs to be split into separate categories, create a new line and amount for each.

Click the Items tab and fill out the fields there if your expense involves products. You must have Inventory turned on to do this. Click Save & Close or Save & New. QuickBooks now works in the background, increasing Accounts Payable and dropping the bill into several reports.

Make sure your completed bill entry screen is as complete as possible.

Paying Your Debts

When it’s time to pony up, click on the Pay Bills icon, or click Vendors/Pay Bills. You’ll see a screen. Check the radio button next to the correct preference to view all bills, or to limit the list to those on or before a specific date. Put a check mark next to the bill(s) you want to pay. The correct amount should fill in by default, but you can change this to make a partial payment.

If you want to view the bill, take a discount, or use credits, click on those buttons. Select a payment date, method (check or credit card), and toggle to the correct account if it’s not showing.

The Pay Bills dialog box. Make sit easy to finish the job.

Once you’ve paid a bill, your Accounts Payable and checkbook balances decrease, and the vendor balance and reports are updated. QuickBooks stamps a PAID watermark on the bill to avoid confusion later on.

Tip: To find bills you’ve already paid, go to the Vendor Center.

So stop stacking your bills on an old spindle and ruffling through them every day to see what’s due. You’ll find that there are numerous benefits to using QuickBooks’ bill-paying features, such as an improved credit rating, a dearth of past-due notices, and better cash flow.

We are living in a period of “accelerated change”. Indeed, the ground does seem to be shifting beneath us almost faster than we can comprehend, so it’s important to stay nimble in these difficult times.

One way you can do so is to closely manage your prices. In some cases you may need to ratchet your prices up to cover a commodity cost-spike. Or, you may want to offer special deals to your best customers to help retain their business.

In this article we’ll discuss four methods you can use to manage prices (and change) within QuickBooks.

Create Discount Calculations

Studies have shown that it’s far easier to get additional sales out of existing customers rather than from new customers. Targeted discounts are just one way to try to encourage your customers to buy more.

However, if you do offer a discount, don’t just type over your standard prices on the QuickBooks invoice, create a discount calculation instead. This accomplishes two things:

  • Your customers see on their invoice exactly how much of a break you’ve given them.
  • You can track how successful your campaign was.

It’s easy to set up a discount calculation:

  • Choose Lists, and then Item List.
  • Click the Item button, and then choose New from the menu (or press Ctrl+N).
  • As shown in Figure 1, Choose Discount from the Type list.
  • Assign an item name, complete the description field, and then enter an amount or a percentage.
  • Choose an account from the list-you may wish to create a separate account so that you can easily track the amount of discounts that you’ve offered.
  • Choose Tax or Non-tax to indicate whether the discount is applied before or after sales tax, and then click OK.

A discount item allows you to create and track percentage or amount based discounts.

Keep in mind that discounts only apply to the previous row of the invoice or sales receipt. To apply the discount to multiple items, you must create a Subtotal item:

  • Choose Lists, and then Item List.
  • Click the Item button, and then choose New from the menu (or press Ctrl+N).
  • Choose Subtotal from the Type list, and then assign an Item Name and Description.

Use Price Levels

Price negotiations are becoming more prevalent and you may find that you have to offer a standard discount to one or more customers in order to keep their business.

In such cases, you might find the price level feature helpful, so that you don’t have to remember to include a discount item on each invoice:

  • Choose Lists, and then Price Level List.
  • Click the Price Level button, and then choose New (or press Ctrl-N).
  • Assign a name to the price level, such as 10% Discount.
  • QuickBooks Pro users can only establish Fixed % price levels, which are applied globally to all products. QuickBooks Premier and Enterprise users also have the option to create Per Item discounts, where you can selectively discount only certain items.
  • Specify whether to increase or decrease item prices, and optionally choose a rounding method.

Price levels allow you to apply automatic discounts to everything a customer purchases.

Note: You can use price levels to increase or decrease prices.

Change Item Prices

Competitive or other pressures may mean that you need to globally change all of your prices at once. Fortunately, you can use the Change Item Prices feature to do so:

  • Choose Customers, and then Change Item Prices.
  • As shown in Figure 5, select an Item Type from the list, and then select the items you wish to change, or click the Mark All checkbox.
  • Indicate a percentage or dollar amount to increase prices by. This can be based on the current price or current cost of the item. Enter a positive number to increase the price, or negative number to decrease the price.
  • Click the Adjust button to see the impact of your changes in the New Price column, and then click OK to make the changes permanent.

Timesaver: You can also manually fill-in the New Price column if you prefer to make targeted adjustments to selected items. This is easier than manually opening each item one at a time.

The Change Item Prices feature allows you to adjust multiple prices at once.

Add a Surcharge

We’re fortunate that gas prices are currently far less than were they were just a few months ago. However, who knows how far they may go this summer during peak driving season.

At some point you may need to consider adding a fuel or other type of surcharge to help recover costs beyond what you’ve factored into your existing prices:

  • Choose Lists, and then Item List.
  • Click the Item button, and then choose New from the menu.
  • As shown in Figure 6, choose Other Charge from the Type list.
  • Assign an item name, complete the description field, and then enter an amount or a percentage.
  • Choose an account from the list, and then click OK. As shown, you may wish to create a separate account so that you can easily track the amount you earn from the surcharge.

Important: As with discounts, Other Charge items only apply to the preceding row on an invoice or sales receipt. Be sure to add a Subtotal item to your invoice if you want the surcharge to apply to multiple rows of your invoice or sales receipt.

The Other Charge feature allows you to compute fuel and other surcharges.