Data Analysis on a budget

Warning!  Extreme auditor geek content!  This blog discusses an example of data extraction and analysis software used by auditors, and is probably of little or no interest to the general public.

My audit practice consists entirely of non-profit organizations.  Almost all of these organizations use Quickbooks; the others use or outsource to some other GL software that is exportable to Excel.  In an effort to become as efficient and effective as possible, I use data extraction and analysis software during the course of all audits; specifically in audit planning, risk assessment, and audit procedures in response to that assessment. 

There are a number of software products included under the heading of CAATS (Computer Aided Audit Tools) available for this purpose, including Caseware’s IDEA, ACL, etc.  A Wikipedia article discussing CAATS is here.  As a sole practitioner, cost is a consideration.  Also, due to the size of my NPO audit clients (revenues from $250k to $3M) and volume of transactions, Excel limitations (# of rows, etc.) are never exceeded.  Therefore, I chose ActiveData for Excel, an Excel add-in.  (Disclaimer – I am not an employee or commissioned salesman for ActiveData.  I just use their software.)

In all audits, Quickbooks transaction data is first exported to Excel, and preliminary analysis is performed.  For example, revenue sources and vendors are grouped and summarized, and the top X% are considered, then cut and paste to PPC NPO-CX-3.1.  A quick comparison of vendor address to employee/Board member address is performed.  A comparison of vendor summaries across two periods is performed and considered.  Benford’s Law analysis is performed and considered. If it is determined that internal controls will be relied upon, the ActiveData sampling functionality is used for sample selection and procedure documentation.

Other preliminary tests specific to the client are performed, and serve as input to NPO-CX-7.1 for risk assessment.  Once preliminary risk assessment is complete, ActiveData is again employed in the conduct of the audit.  For example, journal entry analysis in response to PPC NPO-AP-2 #4, search for related party transactions per PPC NPO-AP-2 #10, search for check number gaps, subsequent sampling, etc.

In addition to the familiarity of Excel, ActiveData also has very useful Help functionality, reference materials such as “Top Audit Tests Using ActiveData for Excel” by Michelle Shein and Richard Lanza (which stays in my laptop case at all times), and several YouTube instructional videos.  For example, a video discussing sampling functionality is here, and a video discussing Benford’s Law analysis is here.

ActiveData’s website is here.

Obviously, ActiveData is just one tool in the audit bag.  But, all in all, it’s a lot of functionality for $250!