Stratify

Stratify has to be one of my favourite commands.  It quickly gives more details regarding the distribution (Low, Medium, and High) of the selected numeric fields (e.g. number of low, medium, high dollar contracts).  This can help with sampling (which I don’t really like to do).  The records in the “High” categories may number only a few, but they often represent a significant portion of the amount.  A stratified sampling…

Read More >>

Value of Peer Community (formerly the ACL Forum)

Recently there was a discussion on the ACL peer community between an ACL expert user and myself over when certain commands were included in the basic set of ACL commands (e.g. Cross Tab).  I had built a cross tab script before the command existed in ACL, but was wrong on both the date I created the script and when it was included as part of the ACL command set.  This…

Read More >>

Workspaces

Always looking for ideas for my blog, I could not resist when Chris Broussard asked me to write something on ACL workspaces. Simply put, an ACL workspace is used define either physical fields or expressions.  However, instead of defining these fields in the table layout, the definitions are stored in a separate project item (i.e. a workspace).  The main advantage is that the workspace can be shared by multiple data…

Read More >>

Life Lessons

I often write about analytics and other aspects of data extraction, interpretation and the like.  Today I thought I would do something different. I am teaching a 4th year university/college course.  The students are eager to get their CMA/CPA designation and go off to work for a big accounting firm.  I admire their zeal, but worry about the lack of practical “seat of the pants” knowledge.   I tell them that…

Read More >>

Statistics

Sometimes even the simplest analytics are extremely useful.  They often tell you what is happening in the detailed transactions without you having to make assumptions about the data.  STATISTICS is one of those commands: with a couple clicks of the mouse you can get: Min and Max Highest and Lowest ‘X’ values Number of Positive, Negative and Zero valued transactions Average Median, Mode and Q25 and Q75 But like any…

Read More >>

CLASSIFY with Statistics Option

I have been using ACL software for more than 25 years – back before the Windows version even existed.  It wasn’t the first audit software that I had used, and I was pretty sure it wouldn’t be the last audit software I would use.  Previously I had used AutoAudit, Audit Analyzer, Decision Analyzer, IDEA, ReportWriter, Easytrieve and various spreadsheet packages (Mulitplan, Visicalc, and Lotus1-2-3).  I told myself that I would…

Read More >>

Extract Records Matching Criteria in another File

A common problem that presents itself when doing analysis is the need to extract detail records that match a list of values (criteria) in another file. This can range from simple to more complicated, primarily depending on the nature of the match.  The following describes three approaches, based on the objective of the analysis. Simple question: A simple question of this type is where we have a file with detailed…

Read More >>

The Data Analytics Conundrum

Studies after study have shown that data analytics is more effective and efficient at detecting risk, and identifying control weaknesses, non-compliance, and inefficient business processes.  Chief Audit Executives (CAEs) have repeated stated that data analysis expertise is a much needed skill in internal audit, and IIA surveys of software over the past 10-15 years have rated data extraction, data analysis and analytical software as critical tools for effective audit organizations.  …

Read More >>

Year 31 – Incomplete Property Tax Data

Even though I am retired, I am still getting to do interesting analysis – perhaps even more so because I get to pick and chose want I want to do.  In this case, I was asked to assist with an audit being performed by a municipal audit function.  A large city had an internal audit group that was interested in auditing revenue – primarily from property taxes.  One of the…

Read More >>

Adding Value to Compliance Audits – part2

The following posts is part 2 of “Adding Value to Compliance Audits” Given a good understanding of the current level and sources of risk, the next step is to look at the requirement for, and the adequacy and effectiveness of, the control to mitigate the risk.  This requires an understanding of the cause and source of the risk and the operation of the control.  Is the control still required?  Does…

Read More >>