Like many, I use QuickBooks as a book-keeping and accounting tool for my business. The more I use QuickBooks and learn how it works, the more I realize that Excel and Power Pivot are excellent, inexpensive, self-service BI tools that complement its functionality. Here is the first of several articles on how to use Excel and Power Pivot to leverage your QuickBooks data. Show
QuickBooks (2013) does support a ready-made class report - Profit & Loss by Class - that analyzes revenues and expenses by Class and Sub-class (assuming you appropriately assigned them to your transactions). However for ad-hoc reporting I prefer to export all my QuickBooks transactions to a .CSV file that I import into Power Pivot for Excel. This affords me much greater flexibility to create analyses in PivotTables, in cube formula reports, and in charts, sometimes combining them all in a dashboard. Even without Power Pivot installed, you can import transactions into Excel and build regular (though significantly less powerful) PivotTables. This arrangement does not support cube formula reports though. But if you have a desk-top-installed version of Excel 2013+ (Office 365) without the Power Pivot add-in, you can still import your .CSV transaction file into the Excel Data Model. This is a more robust platform (uses the Power Pivot plumbing) from which to create your reports and dashboards although there are some workbook size-limitations. For more information on how to leverage Excel to analyze your QuickBooks data, there is an excellent book written pre-Power Pivot, Business Analysis with QuickBooks by Conrad Carlberg (www.wiley.com). Class and Sub-ClassQuickBooks supports Classes and Sub-Classes (the latter to several levels of sub-class). Keep in mind, though, that if you need to categorize and analyze your data in multiple ways then you may be outgrowing your current accounting software. If you plan on taking advantage of Classes give some thought as to how you might set them up. If you want to analyze sales and transactions by country, then Classes will serve that purpose well. Suppose you need a second categorization of revenues. This will likely be one of two forms that I'll demonstrate with two examples: Form 1: A hierarchical categorization such as state or province within country. Alberta rolls up into Canada and California rolls up into USA. Alberta, USA and California, Canada are meaningless combinations. Form 2: An independent categorization sometimes said to cut across other categorizations. Let's assume that you make direct sales and sales through affiliates. We'll refer to this categorization as Channel. Channels may be applicable to all countries. QuickBooks reports support form 1 above but not form 2. If Channel is Sub-class there isn't an easy way to produce a Profit & Loss Account by Channel without including the country totals. However, if you export your QuickBooks transactions to Excel or better yet to Power Pivot (or to the Excel 2013+ Data Model) then you can create reports by country or by channel or both. You can still use Sub-classes to maintain your secondary categorization even if the Sub-classes cut across Classes, as in the Country and Channel scenario (form 2). In this case Sales Territory and Channel get equal billing even though in QuickBooks one will be a Class and the other a Sub-class. You should populate all the possible Sub-classes even where only one is possible; this means that if you make only direct sales in Germany, you should still have a sub-class of Direct Sales under Germany and book your sales to it rather than simply to Germany. How Class and Sub-Classes are recorded in a QuickBooks transactionWhen you export your transactions as a .CSV file, the format for Class and Sub-class is: Class:Sub-class 1:Sub-class 2 and so on; each segment is delimited with a colon character (":"). So in form 2 above, transactions might have the following in the Class field:
If there are transactions that are not attributable to a particular country (or to the Classes you are using) you should set up a Class with a name like "Unassigned" or "Unspecified" or similar. Then the exported file might include records where the class field looks like:
If the Channel is not known or not specified then the field might look like:
This approach maintains the positioning of the class and sub-class even where one is missing or unknown. So, to recap, exporting your data to Power Pivot or to Excel means you can create reports and analyses by Country alone or by Channel alone or both; it's your choice. In QuickBooks, Sub-classes always appear with their parent classes (Channel within Country in this case) whether or not you want them to. This is more appropriate in form 1 above, though even there, you may still want to show only state/province data without country totals. Extracting classes from sub-classes in ExcelSince QuickBooks separates Classes from sub-classes and sub-sub-classes with a colon you should avoid using a colon character in your class or sub-class member names. Assume for the moment that you are using these two categorizations and no levels below sub-class. Be sure your Class and Sub-class lists include all the combinations of dimension members that are likely to occur (or add them as and when they do). So in this example your Class list would need to include:
The Excel screenshot below shows how you can parse out the Class and Sub-class member names.
The first step is to find the ":" separator. In row 2 the formula (for both sales territory and channel), broken down, looks like this: FIND(":",$B2,1) returns 4; the colon is the 4th character. So for sales territory you want, in this example, the left 3 characters: \=LEFT($B2,FIND(":",$B2,1)-1) -1 towards the end means 3 leftmost characters are returned For channel you want all characters starting, in this example, with the 5th: \=MID($B2,FIND(":",$B2,1)+1,100) the +1 gets the character after the bar and the 100 is a number larger than the likely length of any (channel) member name. In a Power Pivot table, the DAX syntax is almost identical. The LEFT and MID functions reference the names of table columns (as would also be the case in an Excel table) rather than cell references:  Once you have broken out you Class and Sub-class into separate columns it is easy to use them for a cross-tabular pivot report:
In theory, you can use this approach to assign to transactions a Class and several levels of Sub-class. However the number of permutations increases exponentially as you add more levels of Sub-class, so maintenance becomes tortuous. An alternative to Sub-classesIf you want to use sales territory and channel in your analysis without using Sub-classes, try this. Set up the combinations as a Class separating the two with an infrequently used character such as a vertical bar "|". QuickBooks won't let you use a colon character. So your classes might look like this extract:
Then when you export your transactions, you can parse out sales territory and channel using Excel's text functions as described above, but instead of FINDing the colon, you will FIND the vertical bar or whatever separator character you use. You can reverse the order (channel|sales territory), but once you decide on it you must be consistent. Final noteIn data modeling parlance Classes and Sub-classes that are independent (form 2 example above) are called dimensions. They are the word following "by" when your boss asks for sales analyzed by month, by customer, by sales territory, by product, by channel, and so forth. In the form 1 example (states/provinces within country), this is a hierarchy within a single dimension. Power Pivot does a great job of managing multi-level hierarchies - a topic for another day. |
