Business Analysis with QuickBooks®

Business Analysis with QuickBooks®

by Conrad Carlberg
Released November 2009
Publisher(s): Wiley
ISBN: 9780470543146

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Manage your business and make sound decisions with the help of QuickBooks

Quickbooks is a user-friendly accounting software program that can analyze data to help you make smart decisions for a small- or medium-sized business. However, few books explain how to maximize the features of QuickBooks reports for management purposes-until now. Author Conrad Carlberg guides you through the most beneficial ways to use and adapt QuickBooks reports by taking the summary data and placing it into a context that helps manage a business.

By avoiding aiming the coverage to a specific version of QuickBooks, this book is a timeless resource that clearly explains how to bring financial data together in order to help make wise business decisions.

  • Use the popular accounting software program QuickBooks to help you make wise business management decisions

  • Identify specific weak points in a business and learn how to turn them around

  • Quantify working capital and manage inventory valuation properly

  • Learn how to understand what QuickBook reports say about the state of your business now and for the future

Quickly get started converting QuickBooks accounting data into results that help you make informed business decisions and manage your business.

Table of contents

  1. Copyright
  2. Credits
  3. About the Author
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
    2. Conventions Used in This Book
  6. I. Exploiting Your QuickBooks Data
    1. 1. Using QuickBooks to Analyze Business Data
      1. 1.1. Advantages and Drawbacks to QuickBooks Reports
        1. 1.1.1. Understanding QuickBooks reports
          1. 1.1.1.1. Types of QuickBooks reports
          2. 1.1.1.2. Deciding between using a summary report and a detail report
            1. 1.1.1.2.1. Showing transactions
            2. 1.1.1.2.2. Accounting basis
            3. 1.1.1.2.3. Collapsing a report
            4. 1.1.1.2.4. Selecting columns
            5. 1.1.1.2.5. Filtering reports
        2. 1.1.2. Balance sheets and income statements
        3. 1.1.3. Using a report's Display tab
          1. 1.1.3.1. Balance sheets
            1. 1.1.3.1.1. Using the Dates dropdown
            2. 1.1.3.1.2. Using the Columns dropdown
            3. 1.1.3.1.3. Showing subcolumns
            4. 1.1.3.1.4. Percent of Row and Percent of Column
            5. 1.1.3.1.5. Modifying the Balance Sheet Detail report
          2. 1.1.3.2. The Profit & Loss report
            1. 1.1.3.2.1. Subcolumns in the Profit & Loss Standard report
            2. 1.1.3.2.2. Using the Dates dropdown
        4. 1.1.4. Using report filters
          1. 1.1.4.1. Selecting a single instance from a field
          2. 1.1.4.2. Selecting multiple instances of the same field
      2. 1.2. Excel as an Adjunct to Report Analysis
        1. 1.2.1. Exporting reports
        2. 1.2.2. Manipulating QuickBooks report data in Excel
    2. 2. Exporting Data from QuickBooks
      1. 2.1. Exporting Reports to Excel Workbooks
      2. 2.2. Formatting and Layout Problems
        1. 2.2.1. Choosing a new workbook
          1. 2.2.1.1. Dealing with error warnings
          2. 2.2.1.2. Dealing with the text format
          3. 2.2.1.3. Moving cells with formulas
        2. 2.2.2. Exporting to an existing workbook
          1. 2.2.2.1. Locating the workbook
        3. 2.2.3. Choosing the worksheet
        4. 2.2.4. Ignoring the QuickBook Export Tips worksheet
          1. 2.2.4.1. Establishing links
          2. 2.2.4.2. Breaking links
        5. 2.2.5. Doing it the expert way
      3. 2.3. Exporting to Text Files
        1. 2.3.1. Understanding the CSV file structure
        2. 2.3.2. Deciding on a CSV file for a report export
          1. 2.3.2.1. Opening the CSV file in Excel
          2. 2.3.2.2. Opening the CSV file in Access
      4. 2.4. Analyzing QuickBooks Data with Pivot Tables
    3. 3. Digging Deeper with Pivot Tables
      1. 3.1. A Sample Pivot Table
        1. 3.1.1. Pivot table terminology
          1. 3.1.1.1. Row fields
          2. 3.1.1.2. Column fields
          3. 3.1.1.3. Data fields
          4. 3.1.1.4. Page fields
          5. 3.1.1.5. Multiple fields, one orientation
            1. 3.1.1.5.1. Multiple row or column fields
            2. 3.1.1.5.2. Multiple data fields
            3. 3.1.1.5.3. Multiple page fields
          6. 3.1.1.6. Data summaries
          7. 3.1.1.7. Number formats in Data fields
        2. 3.1.2. Using other totals
          1. 3.1.2.1. Sorting row fields
          2. 3.1.2.2. Displaying data as percents
      2. 3.2. Moving Data into a Pivot Table
        1. 3.2.1. Sources of data for pivot tables
        2. 3.2.2. Excel lists
          1. 3.2.2.1. Column labels
          2. 3.2.2.2. Records in rows
          3. 3.2.2.3. Custom Transaction Detail report
            1. 3.2.2.3.1. All transactions included
            2. 3.2.2.3.2. Most fields included
          4. 3.2.2.4. Memorize the report
        3. 3.2.3. Use a named range
          1. 3.2.3.1. Quick and easy dynamic range names
          2. 3.2.3.2. Creating dynamic range names
        4. 3.2.4. Building the pivot table
          1. 3.2.4.1. Using the field list
          2. 3.2.4.2. Refreshing pivot tables
            1. 3.2.4.2.1. Manual refreshes
            2. 3.2.4.2.2. Semi-automatic refreshes
            3. 3.2.4.2.3. Automatic refreshes
          3. 3.2.4.3. Updating the saved workbook
      3. 3.3. Special Features of Pivot Tables
        1. 3.3.1. Handling pivot table fields
          1. 3.3.1.1. Managing subtotals
          2. 3.3.1.2. Grouping numeric fields
        2. 3.3.2. Integrating pivot tables with worksheets
          1. 3.3.2.1. The GETPIVOTDATA function
          2. 3.3.2.2. Double-click to drill down
        3. 3.3.3. Pivot table formulas
          1. 3.3.3.1. Calculated fields
          2. 3.3.3.2. Pivot charts
      4. 3.4. Sample Pivot Tables from QuickBooks Data
  7. II. Analyzing Financial Statements
    1. 4. Comparative Balance Sheets and Profit & Loss Statements
      1. 4.1. Reasons for Comparative Analysis
      2. 4.2. Using QuickBooks to Create Comparative Financial Statements
        1. 4.2.1. No single report
        2. 4.2.2. Watching reports' date ranges
          1. 4.2.2.1. Reversed dates
          2. 4.2.2.2. Missing accounts
      3. 4.3. Combining Vertical and Horizontal Analyses
        1. 4.3.1. Exporting the Profit & Loss report
          1. 4.3.1.1. Preparing the analysis
          2. 4.3.1.2. Understanding the formula
          3. 4.3.1.3. Report legibility
          4. 4.3.1.4. Understanding the QuickBooks formulas
        2. 4.3.2. Interpreting the Profit & Loss report
          1. 4.3.2.1. Quantifying the combined effects of costs and expenses
          2. 4.3.2.2. The trouble with the SIGN function
        3. 4.3.3. Exporting and interpreting the Balance Sheet report
      4. 4.4. Making Comparisons to Other Companies
    2. 5. Working Capital and Cash Flow Analysis
      1. 5.1. Determining Working Capital
        1. 5.1.1. Current assets and liabilities
        2. 5.1.2. Changes to current accounts
          1. 5.1.2.1. Changes to noncurrent accounts
        3. 5.1.3. Analyzing components of working capital
          1. 5.1.3.1. What doesn't affect working capital
          2. 5.1.3.2. What does affect working capital
      2. 5.2. Tracking Changes in Working Capital
        1. 5.2.1. Using the Balance Sheet Standard report
          1. 5.2.1.1. Using selected accounts
          2. 5.2.1.2. Beginning and ending dates
          3. 5.2.1.3. Getting enough detail
        2. 5.2.2. Preparing the analysis
          1. 5.2.2.1. Exporting the balance sheets to Excel
          2. 5.2.2.2. Deriving the working capital analysis from the account balances
          3. 5.2.2.3. Interpreting the analysis
        3. 5.2.3. Arranging for greater detail
        4. 5.2.4. Working capital in another company
        5. 5.2.5. Getting all the accounts
      3. 5.3. Tracking Cash Flow
    3. 6. Ratio Analysis
      1. 6.1. Liquidity Ratios
        1. 6.1.1. Current ratio
        2. 6.1.2. Quick ratio
      2. 6.2. Profitability Ratios
        1. 6.2.1. Operating expense ratio
        2. 6.2.2. Return on assets
          1. 6.2.2.1. Calculating ROA
          2. 6.2.2.2. ROA and leverage
          3. 6.2.2.3. Calculating a loan's interest rate
      3. 6.3. Leverage Ratios
        1. 6.3.1. Equity ratio
        2. 6.3.2. Debt ratio
        3. 6.3.3. Times interest earned ratio
      4. 6.4. Activity Ratios
        1. 6.4.1. Inventory turns ratio
          1. 6.4.1.1. The sales/week ratio
          2. 6.4.1.2. The turns ratio
            1. 6.4.1.2.1. Average inventory
            2. 6.4.1.2.2. High turns
            3. 6.4.1.2.3. Relationship to gross profit rate
            4. 6.4.1.2.4. Measuring turns in days
        2. 6.4.2. Average collection period
  8. III. Controlling Costs and Planning Profits
    1. 7. Inventory Valuation and Gross Margins
      1. 7.1. Average Cost
        1. 7.1.1. The mainstream approach
        2. 7.1.2. The QuickBooks approach
      2. 7.2. COGS and Gross Margin
        1. 7.2.1. Selling negative
          1. 7.2.1.1. Selling to zero
          2. 7.2.1.2. Selling below zero
        2. 7.2.2. Going positive
          1. 7.2.2.1. Pending sales
            1. 7.2.2.1.1. Pending sales in QuickBooks Pro
            2. 7.2.2.1.2. Sales orders in QuickBooks Premier
          2. 7.2.2.2. Recovering after the fact
            1. 7.2.2.2.1. Correct your quantity on hand using an Adjustment
            2. 7.2.2.2.2. Correct your quantity on hand by editing an item receipt
            3. 7.2.2.2.3. Revise an existing sales record
    2. 8. Forecasting and Projections in QuickBooks
      1. 8.1. Using the Cash Flow Projector and the Cash Flow Forecast Report
        1. 8.1.1. A quick overview of the Cash Flow Projector
        2. 8.1.2. Working your way through the Cash Flow Projector
          1. 8.1.2.1. Verifying the Cash Flow Projector
          2. 8.1.2.2. Running the Cash Flow Projector
            1. 8.1.2.2.1. I Want to Project Cash Receipts Manually option
            2. 8.1.2.2.2. Use Last 6 Weeks option
            3. 8.1.2.2.3. Same 6 Week Period Last Year option
            4. 8.1.2.2.4. Use an Average of Last Six Weeks option
            5. 8.1.2.2.5. Average of Same 6 week Period Last Year option
            6. 8.1.2.2.6. Use a Weighted Average of Last 6 Weeks option
        3. 8.1.3. The trouble with transfers
        4. 8.1.4. Getting inflow estimates yourself
          1. 8.1.4.1. Using the Cash Flow Forecast's Accounts Receivable
          2. 8.1.4.2. Using the Cash Flow Forecast's Accounts Payable
      2. 8.2. Going Outside QuickBooks to Project Receipts
        1. 8.2.1. Forecasting with smoothing
          1. 8.2.1.1. Creating a smoothed forecast
          2. 8.2.1.2. Considerations in smoothed forecasting
        2. 8.2.2. Forecasting with regression
          1. 8.2.2.1. Creating a regression forecast
          2. 8.2.2.2. Considerations in regression forecasting
    3. 9. Monitoring Budget Variances
      1. 9.1. Understanding Process Control Charts
        1. 9.1.1. Control limits
          1. 9.1.1.1. Standard deviations
          2. 9.1.1.2. Ranges
          3. 9.1.1.3. Moving ranges
          4. 9.1.1.4. Establishing the limits
          5. 9.1.1.5. Tighter control limits
        2. 9.1.2. Using the rules
          1. 9.1.2.1. A Rule 2 violation
          2. 9.1.2.2. Two rule violations in one chart
          3. 9.1.2.3. Interpreting the Rule 1 violation
      2. 9.2. Getting Budget Variances
        1. 9.2.1. Laying out the data
          1. 9.2.1.1. Establishing the report
          2. 9.2.1.2. Exporting the report
        2. 9.2.2. Plotting budget variances
          1. 9.2.2.1. Enabling the macros
          2. 9.2.2.2. Selecting analysis options
            1. 9.2.2.2.1. Show All Control Limits checkbox
            2. 9.2.2.2.2. X Shift checkbox
          3. 9.2.2.3. Starting point options
            1. 9.2.2.3.1. Starting point: Average
            2. 9.2.2.3.2. Starting point: Median
            3. 9.2.2.3.3. Starting point: Zero
            4. 9.2.2.3.4. Starting point: Custom value
          4. 9.2.2.4. Selecting Labels and Formats options
            1. 9.2.2.4.1. Chart Labels: Chart
            2. 9.2.2.4.2. Chart Labels: Y-axis
            3. 9.2.2.4.3. Y-axis Labels Format: Currency
            4. 9.2.2.4.4. Y-axis Labels: Number
            5. 9.2.2.4.5. Y-axis Labels: Linked to Worksheet
        3. 9.2.3. Understanding the moving range chart
          1. 9.2.3.1. Rationale for moving range charts
          2. 9.2.3.2. Special aspects of moving range charts
            1. 9.2.3.2.1. Absolute values
            2. 9.2.3.2.2. Minimum value of zero
            3. 9.2.3.2.3. Upper control limit only
        4. 9.2.4. Viewing the underlying data
        5. 9.2.5. Extending SPC techniques to other QuickBooks data
    4. 10. Contribution and Margin Analysis
      1. 10.1. Break-even Analysis
        1. 10.1.1. Understanding types of product costs
          1. 10.1.1.1. Fixed operating costs
          2. 10.1.1.2. Variable costs
          3. 10.1.1.3. Semi-variable costs
        2. 10.1.2. Calculating the product costs
          1. 10.1.2.1. Break-even quantified
          2. 10.1.2.2. Using the results of the analysis
        3. 10.1.3. Terms used in break-even analysis
          1. 10.1.3.1. Contribution margin
          2. 10.1.3.2. Contribution margin ratio
          3. 10.1.3.3. About contribution margins and gross margins
        4. 10.1.4. Calculating the contribution margin
          1. 10.1.4.1. Getting the total contribution margin from QuickBooks
          2. 10.1.4.2. Charting the break-even line
          3. 10.1.4.3. Focusing on the contribution margin
          4. 10.1.4.4. Interpreting the break-even chart
          5. 10.1.4.5. Simplifying assumptions
      2. 10.2. Looking into the Sales Mix
        1. 10.2.1. Adjusting the QuickBooks report
        2. 10.2.2. Calculating each product's break-even in units
        3. 10.2.3. Calculating each product's break-even in dollars
        4. 10.2.4. Evaluating product contributions
          1. 10.2.4.1. Lowering the sales price
          2. 10.2.4.2. Reducing fixed costs
  9. IV. Designing Your Own Analysis
    1. 11. Using the QuickBooks Software Development Kit
      1. 11.1. An Example QuickBooks SDK Application
        1. 11.1.1. Retrieving the data
          1. 11.1.1.1. Establishing the connection and query
            1. 11.1.1.1.1. The record's ID
            2. 11.1.1.1.2. The record's edit sequence value
            3. 11.1.1.1.3. The fields to edit in the record
          2. 11.1.1.2. Establishing the code
          3. 11.1.1.3. Bringing the data into Excel
        2. 11.1.2. Modifying the QuickBooks data
          1. 11.1.2.1. Understanding the customer modification code
          2. 11.1.2.2. Assumptions in the code
            1. 11.1.2.2.1. Four records only
            2. 11.1.2.2.2. One first name, one last name
            3. 11.1.2.2.3. No runtime errors
      2. 11.2. Arranging for the Dynamic Link Library
        1. 11.2.1. Accessing QuickBooks objects
          1. 11.2.1.1. Extensions to Basic
          2. 11.2.1.2. An overview of the DLL
        2. 11.2.2. Getting the SDK
        3. 11.2.3. Using the Onscreen Reference
          1. 11.2.3.1. Selecting messages
          2. 11.2.3.2. Types of messages
            1. 11.2.3.2.1. Queries
            2. 11.2.3.2.2. Reports
            3. 11.2.3.2.3. Add messages
            4. 11.2.3.2.4. Modification messages
            5. 11.2.3.2.5. Requests and responses
          3. 11.2.3.3. Information about elements
            1. 11.2.3.3.1. Type
            2. 11.2.3.3.2. Max (desk) and Max (QBOE)
            3. 11.2.3.3.3. Implementation
            4. 11.2.3.3.4. Required
          4. 11.2.3.4. Elements in the CustomerQuery message
            1. 11.2.3.4.1. metaData
            2. 11.2.3.4.2. iterator and iteratorID
          5. 11.2.3.5. The ORCustomerListQuery
            1. 11.2.3.5.1. ListIDList
            2. 11.2.3.5.2. FullNameList
            3. 11.2.3.5.3. CustomerListFilter
          6. 11.2.3.6. Using the ORNameFilter
            1. 11.2.3.6.1. The NameFilter
            2. 11.2.3.6.2. The NameRangeFilter
          7. 11.2.3.7. The IncludeRetElementList element
          8. 11.2.3.8. The OwnerIDList element
      3. 11.3. Exploring the CustomerQuery Request Code
        1. 11.3.1. Laying the groundwork
          1. 11.3.1.1. Using Option Explicit
          2. 11.3.1.2. Using Option Base 1
        2. 11.3.2. Fine-tuning the code
        3. 11.3.3. Building the request message
      4. 11.4. Exploring the CustomerQuery Response Code
        1. 11.4.1. Understanding the ParseCustomerQueryRs subroutine
        2. 11.4.2. Managing the ParseCustomerRet subroutine
    2. 12. Managing Reports Using the QuickBooks Software Development Kit
      1. 12.1. Deciding to Run Reports through the SDK
        1. 12.1.1. Answer 1: Running reports periodically
        2. 12.1.2. Answer 2: Analyzing the report's data
        3. 12.1.3. Answer 3: Combining reports
      2. 12.2. An Overview of a Report Message
        1. 12.2.1. Report categories
        2. 12.2.2. Specifying the report
        3. 12.2.3. Custom summary and detail reports
      3. 12.3. Examining a Report Message's Code
        1. 12.3.1. Acquiring the code
        2. 12.3.2. The summary report request message
        3. 12.3.3. Building the report request
          1. 12.3.3.1. Filtering by date
            1. 12.3.3.1.1. Filtering by account
          2. 12.3.3.2. Filtering by entity: customer, vendor, employee, or other name
          3. 12.3.3.3. Filtering by item
          4. 12.3.3.4. Filtering by class
          5. 12.3.3.5. Additional report request specifications
        4. 12.3.4. The summary report response message
        5. 12.3.5. Finding data in the report
          1. 12.3.5.1. A moving target
          2. 12.3.5.2. A static target
            1. 12.3.5.2.1. Dealing with the report's header area
            2. 12.3.5.2.2. Picking up and writing the column titles
            3. 12.3.5.2.3. Obtaining and writing the report's detail data
            4. 12.3.5.2.4. Getting the subtotals
          3. 12.3.5.3. Writing the total values
          4. 12.3.5.4. Examining the output of the message
            1. 12.3.5.4.1. No exported formulas
            2. 12.3.5.4.2. No text formatting

Product information

  • Title: Business Analysis with QuickBooks®
  • Author(s): Conrad Carlberg
  • Release date: November 2009
  • Publisher(s): Wiley
  • ISBN: 9780470543146