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...

Event Date: May 29, 2014 In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both...
No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
There is a growing trend of accountants moving away from traditional compliance work to more advisory work. Client demand is there, but it is up to the accountants to capitalize on that. What should accountants' roles be...

Upcoming CPE Webinars

Apr 22
Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
Apr 30
During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.