Five tips... #3 - Understanding and Exploring Your Data

This week's 3rd tip for advancing with audit and compliance analytics is to "Understand Your Data, and Explore it Fully Before Developing Exception Queries." One common mistake that we see audit and compliance professionals make with data analytics is that they sometimes dive right into searching for transaction exceptions before exploring their data fully. This limits the effectiveness of their analysis, because they are searching for something specific and can overlook other conditions or anomalies in their data. If you've not seen the selective attention (aka Gorilla and Basketball) videos from Daniel Simons, here's a fun link.

Selective attention on exception queries seems to happen due to the strengths of traditional analytics tools like Microsoft Excel and general purpose tools like CaseWare IDEA or ACL. It is less common with Visual Reporting tools like Tableau and Qlikview, in part because these tools are designed to specifically support data exploration and interaction with click and drill-through capabilities. Visual Reporting capabilities are very effective for data exploration, and some rudimentary visual capabilities can be found in Excel, IDEA, and ACL.

During data analytics brainstorming, we categorize analytics queries as Metric Queries, Outlier Queries, and Exception Queries. When prioritizing queries to be built for client assignments, we make sure that there some of each type of query, so that sufficient data exploration takes place before we jump into exception queries or begin researching exceptions.

Metric queries are those analytics such as "Top 10 Vendors by Vendor Spend" or "Top 10 Vendors by Number of Transactions", or "Top 10 Dates of the Year for Requisitions (or Purchase Orders)." Simply summarizing number and value of transactions by different dimensions (day of week, week of quarter, or by UserID) can identify anomalies that should be questioned further. On a recent Payroll Wage and Hour project, we found unusual patterns of when people punched in and out much more frequently on some minutes (e.g. 7 or 23 minutes past the hour, vs. 8 or 22 minutes past the hour). This condition called for further inquiry and analysis about whether time rounding was fair and equitable for certain types of workers. This condition is in fact a major compliance risk and should be considered for any employers with a significant number of hourly worker. See Corporate Counsel article for more information.  

Outlier queries are comparative analytics like "Largest Invoice to Average Invoice, by Vendor," "Most Expensive Airfare by Distance," or "Most Expensive Travel / Entertainment Event per Person vs. Average Event per Person." These outlier queries are also essential, in that they help identify patterns or relationships that should be investigated further. Digital analysis such as Benford's Law is a well-known audit example of an Outlier query, but there are many more techniques that can yield insight beyond only Benford's Law.

Example of exception queries are more traditional Analytics queries such as these listed below:

• List if two (or more) invoices have been paid for the same amount to the same vendor
• List any purchase orders created after their corresponding invoice
• List any Vendors who share a Tax ID Number, Address, or Phone Number with an Employee
• List any Vendors who have had transactions posted after being Terminated or made Inactive 

In short, we recommend spending at least an hour and as much as a day or more exploring and analyzing your data, before beginning any Exception Queries. A data exploration checklist follows - any additions or other suggestions to this list are welcome. 

• Sort transactions from oldest to newest and from newest to oldest. Any unusual dates or times? Any gaps in date or time stamps? Why?
• Sort transactions from largest to smallest and smallest to largest. Any unusual negative values?
• Stratify by various status codes, reason codes, or transaction types. Are all values consistently completed. Any unusual relationships? What do each of the codes and values represent?
• Stratify by dollar value ranges. Do 20% of the transactions make up 80% of the value? Should they? The Pareto Principle says yes, but your business may vary. 
• Compute Relative Size Factor (largest to average and largest to second largest), and sort again. Do any of these RSF values cause you to want to drill into specifics? Consider whole numbers and large numbers. Why or why not?

What has been your most significant "aha" moment when exploring your data? Comments and feedback are welcomed below.

Joe Oringel
Managing Director
Visual Risk IQ
Charlotte NC

Five tips for Advancing with Audit Analytics. Tip #2 - Brainstorming

Yesterday we started a multi-part post on the importance of building audit data analytics capabilities, together with some "how-to" tips. Our first tip was how this is actually as much of a people challenge as a technical undertaking. One particular "secret" is that a combination of skills are needed to accomplish these analytics projects, and we see many departments make the mistake of assigning a single individual to carry out a project, without sufficient assistance or at least oversight from colleagues that have complementary skills.

In our data analytics consulting practice, we use a Body of Knowledge framework to identify needed skills for a particular project, and then match at least one "expert" with an "apprentice" that is looking to add to these same skills. Together our teams bring excellent qualifications in each of these domains, but it's rare that they all arrive in the form of a single consultant. That framework was published here yesterday.

Today's tip is to "Begin with the business objectives in mind, and map from these objectives to available digital data." Too often, we see compliance and audit teams request data and begin to interrogate it before understanding the data fully or taking steps to validate control totals and/or data completeness. A related mistake is to exhaustively test a single data file without considering supplemental data sources that may yield greater insight or answer related business questions. 

A recent example of why to begin with business questions was a Payroll project that we completed for a retail client. Our team was tasked with searching for "off-the-clock" work. If we had focused only on available data files, we could have answered questions about meal breaks, rest breaks, and overtime but perhaps missed other hours worked but not paid. By focusing on the business question first, we identified badge data and cash register data to identify if employees were in the store and ringing sales, yet were off the clock at the time of badge swipes or point-of-sale,

As such, the first step in any data analytics project is brainstorming. You can think of it as part of project planning. During this step, teams should identify the business questions that they want to answer with their analytics efforts, and cross-reference these business questions against available reports and digital data. If existing report(s) fully answer a business question, then a new query may not needed**. But if a report does not currently exist, then analytics should be considered and understanding data sources becomes a key next step. During brainstorming, it is very important to understand the number and complexity and number of data sources that will be needed, and to focus only on a small enough number of business objectives so that the number of data sources does not get overwhelming. It is better to have a series of "small win" analytics efforts, than a larger, less successful project

Tune in tomorrow for tips on how to understand your data better, and how to explore it before building exception queries. In the meanwhile, comments and suggestions are welcome.

Joe Oringel
Managing Director
Visual Risk IQ
Charlotte NC

** Note that some audit and compliance teams may choose to build queries that replicate existing reports, to test the report validity. For important reports, this re-performance can provide comfort or assurance that the original report is working.

Five Tips for Advancing with Audit Analytics

For many people today, Monday January 5, is the first work day of 2015. We compliance and audit professionals are like our many co-workers and friends in that we have new goals and ideas that we expect should set this year apart. We want to grow and develop personally and professionally and have even greater career success. Inside and even outside of our current roles. But how?

Even more than in previous years, 2015 is shaping up as the year that Analytics will be adopted by the audit and compliance profession, at least according to Internal Auditor, the global professional journal for internal auditors. See article titled "The Year Ahead:2015."

This article quotes several high profile Chief Audit Executives (CAE's) on the subject of Analytics. Raytheon's Larry Harrington, a frequent keynote speaker for the IIA says that "you will see greater use of data analytics to increase audit coverage without increasing costs" and that "internal audit will leverage analytics from other lines of defense," such as compliance and risk management. Increased use of Analytics will lead to greater value from audit and compliance, as measured by management. But if this was easy, wouldn't we all be doing it already? How should we overcome obstacles such as finding the right people, training, and budgets (as cited by the CAE's in this article)

Visual Risk IQ has been helping audit and compliance professionals see and understand their data since 2006. We work with all leading audit-specific tools (e.g. CaseWare IDEA, ACL, and newcomer Analyzer, from Arbutus Software), and also with general purpose analytics and visual reporting tools like SQL, Tableau, Oversight, and more. Importantly, we have completed hundreds of engagements for clients across a wide variety of industries.

These five tips are:

1) Consider skills and experience of the team, not individuals, when planning a data analytics project.
2) Begin with the business objectives in mind, and map from these objectives to available data
3) Understand your data, and explore it fully before developing exception queries
4) Consider outlier, metric, and exception queries
5) Supplement necessary skills with internal or external resources

We'll be expanding on each of these five tips in blog posts later this week, but here is some information on the first and perhaps most important one. Your people.

1) Consider skills and experience of the Team, not individuals, when planning a data analytics project.

As part of our consulting projects, and for our inward assessment of our own team members, we use an analytics-focused Body of Knowledge framework that has the following seven key components.

• Project Management
• Data Acquisition and Manipulation
• Statistical techniques
• Visual Reporting techniques
• Communication
• Audit and Compliance Domain expertise
• Change Management and Strategic Thinking

In our experience, data analytics projects succeed because of project expectations and corresponding competencies of team members in these seven areas. It's especially important to note that these body of knowledge components are rarely (if ever?) found at a high level within a single individual, and therefore a team approach is needed to accomplish successful an analytics projects.

People that have greater skills at project management or communication of issues may not have the requisite technical experience when it comes to data acquisition and manipulation, or statistical techniques. Similarly, it is common for stronger data specialists to be weaker on audit or compliance domain expertise.

So when planning an audit analytics project, be sure that you've built a team that has each of these key elements in their skill set, and that they have the incentives and team structure to work together and learn from each other's expertise.

Tune in tomorrow for more on how to begin with the business objectives when planning an audit analytics project. In the meanwhile, comments and suggestions are welcome.

Joe Oringel
Managing Director
Visual Risk IQ
Charlotte NC

Rutgers WCAS 26 - January 2013, part 1

Back again at Rutgers for the 26th World Continuous Auditing and Reporting Symposium (WCARS), which was delayed from November 2012 due to Hurricane Sandy. Yes, it's been a while since we've updated this blog, but we've become much more active in using Twitter ( @VisualRiskIQ ) to highlight what's new in the world of continuous auditing, continuous controls monitoring, compliance monitoring, and anti-fraud analytics. And though we'll be tweeting from #WCARS26 also, we feel that the Rutgers symposium is too important to cover in only 140 characters.

Presenters and sponsors at the 26th WCARS event include the AICPA and ISACA, all of the Big 4, and software providers such as ACL, CaseWare, CA, CCH, Greenlight, Oversight, and Trintech. Throughout the conference, we expect to see a wonderful mix of software and process case studies, standards reports, and academic papers, all on the subject of Continuous Auditing. The Rutgers definition of CA includes continuous data monitoring (i.e. embedded audit routines), continuous controls monitoring, and also continuous risk assessment, and presenters will cover all these topics. An important addition has been newly minted PhD Kevin Moffitt, who adds deep expertise in unstructured data analytics and text mining to Rutgers long history of data analytics of structured data.

Corporate attendees and presenters include advanced users of CA including Siemens, Verizon, Proctor & Gamble, HP, and more. More on the agenda, including selected slides, is available at: http://raw.rutgers.edu/26wcars .

The conference has begun with a nice introduction from Miklos on the status of numerous research and practical CA applications at Rutgers, and is transitioning to Eric Cohen, PwC's XBRL expert. More to come...

Joe Oringel
Harrison, NJ
January 16, 2013

Remembering 9/11/2001

It was afternoon for me in Ireland, where I was working on a project with Bristol-Myers. On a conference call with our NYC offices in midtown east. "Joe we need to reschedule the call, a small single-engine plane has hit WTC. Everybody is turning to the news to see what's going on." I wish that was what had happened. My wife was 8 months pregnant with our second child, who we named Juliana to honor the daughter and her mother, an Irish national that both lost their life on the plane that hit the towers. Juliana McCourt never saw her 5th birthday. Thankfully her uncle made it down 50+ stories of the WTC. Later that day he would learn of his sister's and niece's death. I'll never forget the outpouring of support for NYC and the whole US from the city of Dublin and the whole country of Ireland. It took me nearly a week to return home, yet given the tragedy all around us in NY/NJ, we know we were still so very fortunate. Where were you? What should we teach our children? Originally posted on 9/11/11, but an appropriate re-post.

IIA General Audit Management - highlights / action plans

Along with more than 1,200 other audit and compliance professionals, I had the pleasure of attending IIA's General Audit Management (GAM) conference last week in Orlando. The GAM Conference is the marquis training and networking event for Internal Audit executives, and as such provides excellent opportunities to interact with industry colleagues and understand the issues that are on the top of their lists for action.

Personally, I look for themes and even short sound bites from conferences such as this. Sequentially, the first memorable sound bite was Sunday evening, when IIA CEO Richard Chambers reported the results of the IIA's "Pulse of the Profession Survey." Richard reported that the two most sought after skills among Fortune 500 internal auditors are: (1) critical/analytic thinking, and (2) data mining and analytics. Being a firm that focuses on audit data analytics, that fits our sweet spot very, very well. Importantly too, his point was not only about the sought-after technical skill. Yes, knowing "how do I answer the question" is important, but also knowing "what is the right question" is critical. Asking the right question is the responsibility of audit and executive management, and is not purely a technical skill.

The second sound bite was from the same session Sunday, when Richard reported that 89% of audit executives are "definitely not getting the most from existing audit technology investments." Scary, but perhaps not surprising. Yes internal audit software vendors bring knowledge of how their tools work, but how well do they understand internal auditing? Have you been satisfied with the amount of industry knowledge and/or business acumen that your software vendor's implementation team has brought to your most recent project? Why or why not?

Finally, my favorite sound bite came from Audit Committee Chair Jim Brady during a panel on Tuesday morning on the relationship between Audit Executives and the Audit Committee. When asked "what keeps him up at night," Brady replied unequivocally that it was "Cybersecurity and Emerging IT Risks". This resonated with me because like audit data analytics, understanding Cybersecurity and Emerging IT Risks are not an "IT Audit job," but rather everyone's job.

What were your personal highlights from IIA's GAM Conference? Any sound bites more memorable than these. Plus please stay tuned for future posts on the importance of understanding Cloud Computing, Mobile Computing, and other Emerging IT Risks that we believe all internal audit professionals need to be more aware of.

Regards,

Joe Oringel
Visual Risk IQ
Charlotte NC, USA

Another Saturday Morning in Newark NJ

Very energizing sessions this morning, as we heard from a Who's Who of large, multinational firms who have implemented CA and CM solutions. Siemens Financial Services led things off with their "Road to Continuous Assurance," as Jason Gross leads a mature CM function that was born in Internal Audit and has migrated to the CFO's office. His deck is downloadable at: http://raw.rutgers.edu/23WCARS

Brad Ames from HP followed with another strong presentation on using CA / CCM for assessing both IT controls and Financial Controls. @43Chase and @debreceny observed that strong IT controls help enable strong financial controls. I was focused on their use of dashboards at HP, and have asked for examples. Stay tuned.

Dave Levin of Proctor & Gamble followed with a strong session on the use of data-driven risk assessments. They compare results of Control Self Assessment and actual audit results, using outliers and differences between management's assessment (i.e. CSA) internal audit's evaluation as input into Internal Audit's risk assessment. Dave's session is available for download at this link.