Sales/Tax by shipping state in Excel – The power of pivot tables, Part 2

By Chuck Vigeant

This is the second of two articles where we explore the ability to generate a Sales by Shipping State report in Excel. In the first article we created a simple example using only invoices, and showed you the mechanics of using the subtotal feature in Excel 2007 to calculate, both, total sales and sales tax by invoice.
 
In this article we will create a complete report that will give you the following: total sales, total taxable sales, total non-taxable sales, and total sales – all by shipping state. Also included in this example are filters for customer, customer taxable code, items, dates, etc. (For this example, we will not include total tax charged or collected.)
 
The steps are organized as follows:
  1. Give you a complete SQL statement that you can copy and paste into Excel – without having to understand a thing about tables, relationships, etc. I do all that work for you.
  2. Create a pivot table to get your report
  3. Demonstrate pivot table options to show you how to achieve several different views using the same query and data
 
Welcome to the power of pivot tables!
 
Connecting Excel to QuickBooks
 
From within Excel, invoke the Microsoft Query Wizard:
 
 
Choose the QuickBooks Data Source:
 
 
Make sure that the Query Wizard is off, as we will use a manual SQL query that I have generated for you.
 
You will see a list of tables. Click the Close Button to remove the dialog box.
 
 
From the Menu, click 'View' then choose 'SQL'
 
 
You will see the following window:
 
 
 
This is where you will paste in the SQL statement I have created for you.
Just cut and paste this whole thing into the SQL Statement Dialog Box:
 

SELECT "InvoiceLine"."CustomerRefFullName" AS CustomerRefFullName,
"InvoiceLine"."CustomerSalesTaxCodeRefFullName" AS CustomerTaxable,
"InvoiceLine"."TxnDate" As Date,
"InvoiceLine"."RefNumber" AS RefNumber,
"InvoiceLine"."ShipAddressState" AS ShippingState,
"InvoiceLine"."BillAddressState" AS BillingState,
{fn IFNULL("InvoiceLine"."ShipAddressState", "InvoiceLine"."BillAddressState")} AS State,
"Item"."FullName" As ItemName,
"Item"."Type" AS ItemType,
"InvoiceLine"."InvoiceLineSalesTaxCodeRefFullName" As ItemTaxable,
"InvoiceLine"."InvoiceLineAmount" As Amount,
'Invoice' AS Type
 FROM  "InvoiceLine" "InvoiceLine", "Item" "Item"
 WHERE "InvoiceLine"."InvoiceLineItemRefListID"="Item"."ListID" AND "InvoiceLine"."IsPending" = 0
 AND ("Item"."Type"='ItemDiscount' OR "Item"."Type"='ItemFixedAsset' OR "Item"."Type"='ItemInventory' OR "Item"."Type"='ItemInventoryAssembly' OR "Item"."Type"='ItemNonInventory' OR "Item"."Type"='ItemOtherCharge' OR "Item"."Type"='ItemService')
AND "InvoiceLine"."TxnDate" >= {d'2011-12-01'} AND "InvoiceLine"."TxnDate" <= {d'2011-12-15'}
 UNION ALL
 SELECT "SalesReceiptLine"."CustomerRefFullName" AS CustomerRefFullName,
"SalesReceiptLine"."CustomerSalesTaxCodeRefFullName" AS CustomerTaxable,
"SalesReceiptLine"."TxnDate" As Date,
"SalesReceiptLine"."RefNumber" AS RefNumber,
"SalesReceiptLine"."ShipAddressState" AS ShippingState,
"SalesReceiptLine"."BillAddressState" AS BillingState,
{fn IFNULL("SalesReceiptLine"."ShipAddressState", "SalesReceiptLine"."BillAddressState")} AS State,
"Item"."FullName" As ItemName,
"Item"."Type" AS ItemType,
"SalesReceiptLine"."SalesReceiptLineSalesTaxCodeRefFullName" As ItemTaxable,
"SalesReceiptLine"."SalesReceiptLineAmount" As Amount,
'SalesReceipt' AS Type
 FROM  "SalesReceiptLine" "SalesReceiptLine", "Item" "Item"
 WHERE "SalesReceiptLine"."SalesReceiptLineItemRefListID"="Item"."ListID" AND "SalesReceiptLine"."IsPending" = 0
 AND ("Item"."Type"='ItemDiscount' OR "Item"."Type"='ItemFixedAsset' OR "Item"."Type"='ItemInventory' OR "Item"."Type"='ItemInventoryAssembly' OR "Item"."Type"='ItemNonInventory' OR "Item"."Type"='ItemOtherCharge' OR "Item"."Type"='ItemService')
AND "SalesReceiptLine"."TxnDate" >= {d'2011-12-01'} AND "SalesReceiptLine"."TxnDate" <= {d'2011-12-15'}
 UNION ALL
 SELECT "CreditMemoLine"."CustomerRefFullName" AS CustomerRefFullName,
"CreditMemoLine"."CustomerSalesTaxCodeRefFullName" AS CustomerTaxable,
"CreditMemoLine"."TxnDate" As Date,
"CreditMemoLine"."RefNumber" AS RefNumber,
"CreditMemoLine"."ShipAddressState" AS ShippingState,
"CreditMemoLine"."BillAddressState" AS BillingState,
{fn IFNULL("CreditMemoLine"."ShipAddressState", "CreditMemoLine"."BillAddressState")} AS State,
"Item"."FullName" As ItemName,
"Item"."Type" AS ItemType,
"CreditMemoLine"."CreditMemoLineSalesTaxCodeRefFullName" As ItemTaxable,
("CreditMemoLine"."CreditMemoLineAmount")*-1 As Amount,
'Invoice' AS Type
 FROM  "CreditMemoLine" "CreditMemoLine", "Item" "Item"
 WHERE "CreditMemoLine"."CreditMemoLineItemRefListID"="Item"."ListID" AND "CreditMemoLine"."IsPending" = 0
 AND ("Item"."Type"='ItemDiscount' OR "Item"."Type"='ItemFixedAsset' OR "Item"."Type"='ItemInventory' OR "Item"."Type"='ItemInventoryAssembly' OR "Item"."Type"='ItemNonInventory' OR "Item"."Type"='ItemOtherCharge' OR "Item"."Type"='ItemService')
AND "CreditMemoLine"."TxnDate" >= {d'2011-12-01'} AND "CreditmemoLine"."TxnDate" <= {d'2011-12-15'}
 
(Notice that I have bolded the places where you can change the dates; and yes you must change it in all three places.)
 
Your box will look something like this:
 
 
Before we hit the OK button I am going to explain – in general terms what this Query does:
  • Combines the sales item transaction tables – Invoice, Sales Receipt, Credit Memo, Statement Charge as they were one transaction (This does not include journal entries.)
  • Included only sales items (e.g. omitted subtotal items and tax items and groups)
  • Created a condition, that if the shipping state is missing, we will use the billing state in its place
  • Omitted Pending Transactions
  • Allowed the ability to change the dates to limit the range of data you want to work with
 
For those people who are advanced database users or programmers, I have optimized this query to work with the QODBC driver; I could have used a much shorter statement using Oracle or Sybase. Besides, the important part of this exercise is to give practitioners a fairly simple way of (a) grabbing the information from QuickBooks, (b) manipulating it using pivot tables, and (c) refreshing the data without ever having to export, cut, and paste from QuickBooks.
 
Running the query
 
OK, let's run the query. Depending upon the file size, it could take anywhere from a couple of minutes to an hour. If your driver is setup properly, the first time you run the report it will take by far the longest time; each subsequent use should produce fairly quick results. (Setting up the QODBC driver is beyond the scope of this article, but the default settings should work just fine in most cases.)
 
The following message will appear. Click the OK button to continue.
 
 
Again, after a period of time, you will see results similar to the following:
 
 
From the Menu choose the 'File' option, and then choose 'Return Data to Microsoft Office Excel'.
 
 
On this dialogue box, choose 'PivotTable Report', and a cell/worksheet to place your data. Then click OK.
 
 
The query will go back to QuickBooks and retrieve the data, and then produce a worksheet as follows:
 
 
Now the magic begins. You simply drag and drop the fields you want into the boxes in the lower right-hand corner, and the data will instantaneously appear in the body of your spreadsheet.
In the following example, we will show sales by shipping state across the columns, and represent taxable vs. non-taxable sales in the rows.
 
 
NOTE: Remember that Discount items do have a tax code associated with them.
 
Next, let's add a filter. In this example we will filter by customers who are exempt. Just drag 'CustomerTaxable’ into the Report Filter Box.
 
 
Notice that Excel has placed a filter area for us at the top of the worksheet. Now all we have to do is filter by 'Non' to get the customers who were classified as non-taxable.
 
 
To get the following results:
 
 
Furthermore, we can find out who the nontaxable customer is. Let's add Customer to the rows.
 
 
We can see that Diarmuld Roche was classified as non-taxable. And if we wanted to find out what items fell under the 'Blank' designation, we can add items to rows as follows:
 
 
We see that finance charges had no taxable designation (In QuickBooks, sales tax is not added when entering transactions directly into the Customer or A/R register).
 
Data is always live
 
The last part of this exercise is to note that you can save the report and refresh the data into your spreadsheet at any time. Even when the spreadsheet is open – and you make a change in QB – the refresh operation will update your spreadsheet data in real time
 
To refresh your data you can do two things:
  • From anywhere within the data area, right click and choose 'Refresh'

       

 
  • On the 'Data' Tab select the 'Refresh All' Icon, and choose 'Refresh'. ('Refresh All' would refresh multiple queries within a single workbook).

       

 
Summary
 
In this exercise, we endeavored to give you several takeaways:
  • Pivot tables are easy to use, utilizing drag-and-drop technology
  • Using the QODBC driver for QuickBooks, you can retrieve live data and manipulate it in the way you see fit.
  • Pivot tables are dynamic, flexible, and very powerful – with a minimum of learning curve.
 
About the author:
Chuck Vigeant M.Ed., founder of ACCOUNTiGRATE, LP, is considered by many to be the foremost authority on QuickBooks integration, reporting, and Business Intelligence, and is the father of custom QuickBooks reporting using Crystal Reports and the QODBC driver now included in QuickBooks Enterprise Solutions. Vigeant also is a consultant for The Sleeter Group.
 
Related articles:

 

You may like these other stories...

Accountants who specialize in forensic and valuation services point to electronic data analysis, or big data, as the most pressing issue they’ll face in the coming months, according to results of a new survey released...
As complex as federal tax can get, at least you're only dealing with one agency: the IRS. But when you get into state and local sales tax, you're coordinating hundreds of jurisdictions that are constantly changing....
All that was needed on Tuesday was a voice vote for the House of Representatives to pass a bill that would prevent state and local governments from taxing access to the Internet.Now the ball is in the Senate’s court....

Upcoming CPE Webinars

Jul 23
We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.
Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.