book

Business Analysis with QuickBooks®

Name: Business Analysis with QuickBooks®
Author: Conrad Carlberg
ISBN: 9780470543146

by Conrad Carlberg

November 2009

Intermediate to advanced

408 pages

10h 46m

English

Wiley

Read now

Unlock full access

Copyright
Credits
About the Author
Acknowledgments
Introduction
Who This Book Is ForConventions Used in This Book
I. Exploiting Your QuickBooks Data
1. Using QuickBooks to Analyze Business Data
1.1. Advantages and Drawbacks to QuickBooks Reports1.1.1. Understanding QuickBooks reports1.1.1.1. Types of QuickBooks reports1.1.1.2. Deciding between using a summary report and a detail report1.1.1.2.1. Showing transactions1.1.1.2.2. Accounting basis1.1.1.2.3. Collapsing a report1.1.1.2.4. Selecting columns1.1.1.2.5. Filtering reports1.1.2. Balance sheets and income statements1.1.3. Using a report's Display tab1.1.3.1. Balance sheets1.1.3.1.1. Using the Dates dropdown1.1.3.1.2. Using the Columns dropdown1.1.3.1.3. Showing subcolumns1.1.3.1.4. Percent of Row and Percent of Column1.1.3.1.5. Modifying the Balance Sheet Detail report1.1.3.2. The Profit & Loss report1.1.3.2.1. Subcolumns in the Profit & Loss Standard report1.1.3.2.2. Using the Dates dropdown1.1.4. Using report filters1.1.4.1. Selecting a single instance from a field1.1.4.2. Selecting multiple instances of the same field1.2. Excel as an Adjunct to Report Analysis1.2.1. Exporting reports1.2.2. Manipulating QuickBooks report data in Excel
2. Exporting Data from QuickBooks
2.1. Exporting Reports to Excel Workbooks2.2. Formatting and Layout Problems2.2.1. Choosing a new workbook2.2.1.1. Dealing with error warnings2.2.1.2. Dealing with the text format2.2.1.3. Moving cells with formulas2.2.2. Exporting to an existing workbook2.2.2.1. Locating the workbook2.2.3. Choosing the worksheet2.2.4. Ignoring the QuickBook Export Tips worksheet2.2.4.1. Establishing links2.2.4.2. Breaking links2.2.5. Doing it the expert way2.3. Exporting to Text Files2.3.1. Understanding the CSV file structure2.3.2. Deciding on a CSV file for a report export2.3.2.1. Opening the CSV file in Excel2.3.2.2. Opening the CSV file in Access2.4. Analyzing QuickBooks Data with Pivot Tables
3. Digging Deeper with Pivot Tables
3.1. A Sample Pivot Table3.1.1. Pivot table terminology3.1.1.1. Row fields3.1.1.2. Column fields3.1.1.3. Data fields3.1.1.4. Page fields3.1.1.5. Multiple fields, one orientation3.1.1.5.1. Multiple row or column fields3.1.1.5.2. Multiple data fields3.1.1.5.3. Multiple page fields3.1.1.6. Data summaries3.1.1.7. Number formats in Data fields3.1.2. Using other totals3.1.2.1. Sorting row fields3.1.2.2. Displaying data as percents3.2. Moving Data into a Pivot Table3.2.1. Sources of data for pivot tables3.2.2. Excel lists3.2.2.1. Column labels3.2.2.2. Records in rows3.2.2.3. Custom Transaction Detail report3.2.2.3.1. All transactions included3.2.2.3.2. Most fields included3.2.2.4. Memorize the report3.2.3. Use a named range3.2.3.1. Quick and easy dynamic range names3.2.3.2. Creating dynamic range names3.2.4. Building the pivot table3.2.4.1. Using the field list3.2.4.2. Refreshing pivot tables3.2.4.2.1. Manual refreshes3.2.4.2.2. Semi-automatic refreshes3.2.4.2.3. Automatic refreshes3.2.4.3. Updating the saved workbook3.3. Special Features of Pivot Tables3.3.1. Handling pivot table fields3.3.1.1. Managing subtotals3.3.1.2. Grouping numeric fields3.3.2. Integrating pivot tables with worksheets3.3.2.1. The GETPIVOTDATA function3.3.2.2. Double-click to drill down3.3.3. Pivot table formulas3.3.3.1. Calculated fields3.3.3.2. Pivot charts3.4. Sample Pivot Tables from QuickBooks Data
II. Analyzing Financial Statements

4. Comparative Balance Sheets and Profit & Loss Statements
4.1. Reasons for Comparative Analysis4.2. Using QuickBooks to Create Comparative Financial Statements4.2.1. No single report4.2.2. Watching reports' date ranges4.2.2.1. Reversed dates4.2.2.2. Missing accounts4.3. Combining Vertical and Horizontal Analyses4.3.1. Exporting the Profit & Loss report4.3.1.1. Preparing the analysis4.3.1.2. Understanding the formula4.3.1.3. Report legibility4.3.1.4. Understanding the QuickBooks formulas4.3.2. Interpreting the Profit & Loss report4.3.2.1. Quantifying the combined effects of costs and expenses4.3.2.2. The trouble with the SIGN function4.3.3. Exporting and interpreting the Balance Sheet report4.4. Making Comparisons to Other Companies
5. Working Capital and Cash Flow Analysis
5.1. Determining Working Capital5.1.1. Current assets and liabilities5.1.2. Changes to current accounts5.1.2.1. Changes to noncurrent accounts5.1.3. Analyzing components of working capital5.1.3.1. What doesn't affect working capital5.1.3.2. What does affect working capital5.2. Tracking Changes in Working Capital5.2.1. Using the Balance Sheet Standard report5.2.1.1. Using selected accounts5.2.1.2. Beginning and ending dates5.2.1.3. Getting enough detail5.2.2. Preparing the analysis5.2.2.1. Exporting the balance sheets to Excel5.2.2.2. Deriving the working capital analysis from the account balances5.2.2.3. Interpreting the analysis5.2.3. Arranging for greater detail5.2.4. Working capital in another company5.2.5. Getting all the accounts5.3. Tracking Cash Flow
6. Ratio Analysis
6.1. Liquidity Ratios6.1.1. Current ratio6.1.2. Quick ratio6.2. Profitability Ratios6.2.1. Operating expense ratio6.2.2. Return on assets6.2.2.1. Calculating ROA6.2.2.2. ROA and leverage6.2.2.3. Calculating a loan's interest rate6.3. Leverage Ratios6.3.1. Equity ratio6.3.2. Debt ratio6.3.3. Times interest earned ratio6.4. Activity Ratios6.4.1. Inventory turns ratio6.4.1.1. The sales/week ratio6.4.1.2. The turns ratio6.4.1.2.1. Average inventory6.4.1.2.2. High turns6.4.1.2.3. Relationship to gross profit rate6.4.1.2.4. Measuring turns in days6.4.2. Average collection period
III. Controlling Costs and Planning Profits
7. Inventory Valuation and Gross Margins
7.1. Average Cost7.1.1. The mainstream approach7.1.2. The QuickBooks approach7.2. COGS and Gross Margin7.2.1. Selling negative7.2.1.1. Selling to zero7.2.1.2. Selling below zero7.2.2. Going positive7.2.2.1. Pending sales7.2.2.1.1. Pending sales in QuickBooks Pro7.2.2.1.2. Sales orders in QuickBooks Premier7.2.2.2. Recovering after the fact7.2.2.2.1. Correct your quantity on hand using an Adjustment7.2.2.2.2. Correct your quantity on hand by editing an item receipt7.2.2.2.3. Revise an existing sales record
8. Forecasting and Projections in QuickBooks
8.1. Using the Cash Flow Projector and the Cash Flow Forecast Report8.1.1. A quick overview of the Cash Flow Projector8.1.2. Working your way through the Cash Flow Projector8.1.2.1. Verifying the Cash Flow Projector8.1.2.2. Running the Cash Flow Projector8.1.2.2.1. I Want to Project Cash Receipts Manually option8.1.2.2.2. Use Last 6 Weeks option8.1.2.2.3. Same 6 Week Period Last Year option8.1.2.2.4. Use an Average of Last Six Weeks option8.1.2.2.5. Average of Same 6 week Period Last Year option8.1.2.2.6. Use a Weighted Average of Last 6 Weeks option8.1.3. The trouble with transfers8.1.4. Getting inflow estimates yourself8.1.4.1. Using the Cash Flow Forecast's Accounts Receivable8.1.4.2. Using the Cash Flow Forecast's Accounts Payable8.2. Going Outside QuickBooks to Project Receipts8.2.1. Forecasting with smoothing8.2.1.1. Creating a smoothed forecast8.2.1.2. Considerations in smoothed forecasting8.2.2. Forecasting with regression8.2.2.1. Creating a regression forecast8.2.2.2. Considerations in regression forecasting
9. Monitoring Budget Variances
9.1. Understanding Process Control Charts9.1.1. Control limits9.1.1.1. Standard deviations9.1.1.2. Ranges9.1.1.3. Moving ranges9.1.1.4. Establishing the limits9.1.1.5. Tighter control limits9.1.2. Using the rules9.1.2.1. A Rule 2 violation9.1.2.2. Two rule violations in one chart9.1.2.3. Interpreting the Rule 1 violation9.2. Getting Budget Variances9.2.1. Laying out the data9.2.1.1. Establishing the report9.2.1.2. Exporting the report9.2.2. Plotting budget variances9.2.2.1. Enabling the macros9.2.2.2. Selecting analysis options9.2.2.2.1. Show All Control Limits checkbox9.2.2.2.2. X Shift checkbox9.2.2.3. Starting point options9.2.2.3.1. Starting point: Average9.2.2.3.2. Starting point: Median9.2.2.3.3. Starting point: Zero9.2.2.3.4. Starting point: Custom value9.2.2.4. Selecting Labels and Formats options9.2.2.4.1. Chart Labels: Chart9.2.2.4.2. Chart Labels: Y-axis9.2.2.4.3. Y-axis Labels Format: Currency9.2.2.4.4. Y-axis Labels: Number9.2.2.4.5. Y-axis Labels: Linked to Worksheet9.2.3. Understanding the moving range chart9.2.3.1. Rationale for moving range charts9.2.3.2. Special aspects of moving range charts9.2.3.2.1. Absolute values9.2.3.2.2. Minimum value of zero9.2.3.2.3. Upper control limit only9.2.4. Viewing the underlying data9.2.5. Extending SPC techniques to other QuickBooks data
10. Contribution and Margin Analysis
10.1. Break-even Analysis10.1.1. Understanding types of product costs10.1.1.1. Fixed operating costs10.1.1.2. Variable costs10.1.1.3. Semi-variable costs10.1.2. Calculating the product costs10.1.2.1. Break-even quantified10.1.2.2. Using the results of the analysis10.1.3. Terms used in break-even analysis10.1.3.1. Contribution margin10.1.3.2. Contribution margin ratio10.1.3.3. About contribution margins and gross margins10.1.4. Calculating the contribution margin10.1.4.1. Getting the total contribution margin from QuickBooks10.1.4.2. Charting the break-even line10.1.4.3. Focusing on the contribution margin10.1.4.4. Interpreting the break-even chart10.1.4.5. Simplifying assumptions10.2. Looking into the Sales Mix10.2.1. Adjusting the QuickBooks report10.2.2. Calculating each product's break-even in units10.2.3. Calculating each product's break-even in dollars10.2.4. Evaluating product contributions10.2.4.1. Lowering the sales price10.2.4.2. Reducing fixed costs
IV. Designing Your Own Analysis
11. Using the QuickBooks Software Development Kit
11.1. An Example QuickBooks SDK Application11.1.1. Retrieving the data11.1.1.1. Establishing the connection and query11.1.1.1.1. The record's ID11.1.1.1.2. The record's edit sequence value11.1.1.1.3. The fields to edit in the record11.1.1.2. Establishing the code11.1.1.3. Bringing the data into Excel11.1.2. Modifying the QuickBooks data11.1.2.1. Understanding the customer modification code11.1.2.2. Assumptions in the code11.1.2.2.1. Four records only11.1.2.2.2. One first name, one last name11.1.2.2.3. No runtime errors11.2. Arranging for the Dynamic Link Library11.2.1. Accessing QuickBooks objects11.2.1.1. Extensions to Basic11.2.1.2. An overview of the DLL11.2.2. Getting the SDK11.2.3. Using the Onscreen Reference11.2.3.1. Selecting messages11.2.3.2. Types of messages11.2.3.2.1. Queries11.2.3.2.2. Reports11.2.3.2.3. Add messages11.2.3.2.4. Modification messages11.2.3.2.5. Requests and responses11.2.3.3. Information about elements11.2.3.3.1. Type11.2.3.3.2. Max (desk) and Max (QBOE)11.2.3.3.3. Implementation11.2.3.3.4. Required11.2.3.4. Elements in the CustomerQuery message11.2.3.4.1. metaData11.2.3.4.2. iterator and iteratorID11.2.3.5. The ORCustomerListQuery11.2.3.5.1. ListIDList11.2.3.5.2. FullNameList11.2.3.5.3. CustomerListFilter11.2.3.6. Using the ORNameFilter11.2.3.6.1. The NameFilter11.2.3.6.2. The NameRangeFilter11.2.3.7. The IncludeRetElementList element11.2.3.8. The OwnerIDList element11.3. Exploring the CustomerQuery Request Code11.3.1. Laying the groundwork11.3.1.1. Using Option Explicit11.3.1.2. Using Option Base 111.3.2. Fine-tuning the code11.3.3. Building the request message11.4. Exploring the CustomerQuery Response Code11.4.1. Understanding the ParseCustomerQueryRs subroutine11.4.2. Managing the ParseCustomerRet subroutine
12. Managing Reports Using the QuickBooks Software Development Kit
12.1. Deciding to Run Reports through the SDK12.1.1. Answer 1: Running reports periodically12.1.2. Answer 2: Analyzing the report's data12.1.3. Answer 3: Combining reports12.2. An Overview of a Report Message12.2.1. Report categories12.2.2. Specifying the report12.2.3. Custom summary and detail reports12.3. Examining a Report Message's Code12.3.1. Acquiring the code12.3.2. The summary report request message12.3.3. Building the report request12.3.3.1. Filtering by date12.3.3.1.1. Filtering by account12.3.3.2. Filtering by entity: customer, vendor, employee, or other name12.3.3.3. Filtering by item12.3.3.4. Filtering by class12.3.3.5. Additional report request specifications12.3.4. The summary report response message12.3.5. Finding data in the report12.3.5.1. A moving target12.3.5.2. A static target12.3.5.2.1. Dealing with the report's header area12.3.5.2.2. Picking up and writing the column titles12.3.5.2.3. Obtaining and writing the report's detail data12.3.5.2.4. Getting the subtotals12.3.5.3. Writing the total values12.3.5.4. Examining the output of the message12.3.5.4.1. No exported formulas12.3.5.4.2. No text formatting

Content preview from Business Analysis with QuickBooks®

Chapter 3. Digging Deeper with Pivot Tables

IN THIS CHAPTER

A Sample Pivot Table
Moving Data into a Pivot Table
Special Features of Pivot Tables
Sample Pivot Tables from QuickBooks Data

Chapter 2 ended with a brief description of building a pivot table from QuickBooks data. Because of the great flexibility of analysis that pivot tables offer you, and because it is so easy to port data from QuickBooks into Excel, this chapter describes the pivot tables features in much greater depth. You will see how to bring pivot tables to bear on QuickBooks data and explore patterns in your data that you simply can't uncover in the QuickBooks user interface.

Pivot tables do take some getting used to. Before you can begin to feel comfortable with them you have to learn a few special terms. And pivot tables are so powerful and flexible that you need some experience before new ways to use them become clear. The next section gets some of that housekeeping out of the way with an overview of pivot table fields.

A Sample Pivot Table

Figure 3.1 shows a mildly complicated pivot table based on QuickBooks' Rock Castle Construction sample file.

Notice in Figure 3.1 that you can see the joint effect of City and Class on the company's revenues. It's important to know whether there is a difference in how your products do according to your customers' locations. One reason is that knowing where different products perform best enables you to focus your marketing efforts.

In QuickBooks, you can view a report of sales by item. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470543146Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Business Analysis with QuickBooks®

by Conrad Carlberg

Chapter 3. Digging Deeper with Pivot Tables

A Sample Pivot Table

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.