There are several 3rd party apps out there design to extract data into excel and create special reports in excel using QuickBooks Data. But I will write about this very unique two-part tool:
- QB2Excel, I actually wrote another article on this one
- ActiveData for Excel, this is not a QuickBooks Add-in, but it is a compliment to QB2Excel (discussed above)
ActiveData for Excel is a low cost tool packed with power, used by External and Internal auditors to analyze financial data in excel. This blog is QuickBooks/Excel centric, so I will use some examples of analysis that I have done with this tool from my QuickBooks data file.
First Step is to export the Quickbooks Data using QB2Excel free tool available from InformationActive, afterwards we’ll export the Invoice Item Detail Table:
This basically includes all invoice line items with all available fields into excel. Next Step is to run a few of the Data Analysis/Audit tools from ActiveData Excel Addin, for the first example, I ran the Summarize / Pivot tool, which creates a Pivot Table style report.
I requested the report to analyze the the total amount of sales per item, and then per class. The results are amazing, a very easy to ready table with both the total amount and the count, we can also request statistical data such as min, max and avg.
The counts column has a hyper link that creates a subset of data that falls into that category same as how a pivot table does it!
Another tool I tested was the AGE tool. This one shows me an analysis of item sales by age (from today’s date), so I am able to see trends in my sales and items. Here is the report:
From this report I can tell that I sold more items between 150 and 180 days ago, but between 120 and 150 days I had less items sold but more revenue. Whatever this analysis means to the user…
Data Analysis is important in Audit and Non-Audit Scenarios. For example, some times patterns in financial data can reflect patterns in employee behavior.. therefore unexplained reductions in productivity or even embezzlement can be discovered by just analyzing patters in the financial information.
Learn more about InformationActive
http://www.informationactive.com/index.php?option=com_content&view=article&id=144&Itemid=489
Hector Garcia
Advanced Certified ProAdvisor
hector@garciacpa.com
