The Analytic Staircase for Auditors

analytic staircase stepsBuilding a successful audit analytics program is like climbing a staircase.

The staircase is a set of steps that consist of several items having increasing levels of maturity.

The staircase steps not only help you build your program, but enable you to measure that maturity.

As you view the staircase graphic, mentally insert the word “analytics” before each step.

Other maturity models* are fairly high-level and focus on whether your program is ad-hoc, repeatable, defined, managed, or optimized. These models are helpful, but they don’t include much detail.

The analytic staircase** provides concrete details that apply to almost any audit department. By adding/changing some of the details, you can customize the staircase for use in your department.

*Other maturity models used by analytic auditors: ACLIIA, and EY.

**The staircase below is built around an audit department that uses mainly Excel and ACL, with an eye on Power BI and other tools.

The idea behind this staircase is to provide increasing steps of progression across several aspects of an analytics program. It can be used with experienced auditors and brand new auditors.

It helps to take the steps one at a time, and each step builds on the one before it. The same is true for the list of items in each step (basic items are listed first under each step, and get more advanced as you progress through the list).

You don’t have to master all the items in each step before progressing to the next step. The more items mastered at each step, and the more steps you master, the higher the maturity.

The Analytic Staircase for Auditors

Here’s the steps needed for a successful analytics program.

1 – Analytics Alignment

-Is your audit department and practices in line with the company’s strategic direction?
-Are you auditing what’s important to the company? Do you spend more time on higher risk areas?
-Are you following company policies and procedures (are you eating your own dog food–what you write other departments up for, are you doing yourself?)
-Can you name the most popular analytic tools the rest of the company is using? And why they use them?

2 – Analytics Data

Have you identified the top 5 data sets you could use across multiple audits? Examples:
–Staff names, addresses, LAN IDs, etc.
–System Access (who has what access)
–Vendor name, address, annual spend, etc.
–General Ledger accounts and entries
–List of servers, applications, databases
–Sales and commission payments
Are you progressing in HOW you get data?
–Manually request data from various departments
–Download data manually from applications or websites without assistance
–Manually run a database query written by another department
–Manually run a database query written by someone else in your department
–Manually run a database query that you wrote
–Get data from an automated, scheduled database query

3 – Analytics Hardware/Software

-Are you making good use of simple analytic tools like Excel? And the Excel addins?
-Are you using the tools that you have already purchased? Or did the excitement die off after an initial burst?
-Do you use the correct tool for the job, or are you ‘required’ to use tools X and Y only, even if another tool is simpler and faster?
-Do you have the network drives, servers, databases, and applications you need to do in-depth analytics?
-Are you managing your hardware/software appropriately? Keeping them updated, checking who has access to them regularly, etc.?

4 – Analytics Engagement

How many auditors are increasing their engagement with analytics, such as:
–Attend department analytic briefings*, training
–Learn on-the-job
–Seek self-education, focused training
–Assist and leverage other analytic teams in the company
–Train others in the department
–Consult with other staff to help them with their planning, learning, and execution
–Contribute to department analytics program (write documentation, create training, do research, obtain new data sources, etc.)

*Briefings given by auditors that completed an analytics project describing what went well, went wrong, and what would be done differently next time. You DO share this, right?

5 – Analytics Performance

What methods are used to analyze data?
-Use Excel filters, analysis driven by menus and buttons (Remove Duplicates, Text to Columns, etc.)
-Use Excel formulas
-Use Excel Fuzzy Lookup, other Excel addins, Pivot Tables, etc.
-Analyze data in ACL via menu options
-Analyze data in Power BI, Tableau, and other company tools via menu options
-Run existing ACL scripts
-Write custom ACL scripts
-Schedule and automate ACL scripts
-Automate the analysis using company tools

6 – Analytics Visualization/Dashboards

Create manual visuals/dashboards, using:
–Excel charts, graphs
–Excel Power Query, Power View, Power Pivot
–Power BI, Tableau, etc.
–Company tools
Schedule and automate the refresh of visuals/dashboards

Measuring Maturity Across the Department

The staircase doesn’t provide a numeric maturity score or apply a label like “defined” or “managed”, it just helps you determine where you are and the next items you need to accomplish to keep increase your analytic and automation capabilities.

To gain a sense of where the department is at, highlight the highest item on each step that each of your auditors performs at least occasionally. Or you can segment the department into several groups and highlight the abilities of each group.

For instance, divide your auditors into 4 groups, and highlight items that:

  • Your most experienced/expert analytic auditors can perform
  • Only the top 10% of auditors with the most analytic experience can perform
  • At at least 50% of your auditors can perform
  • 75% of your auditors can perform

For example, in step #5, let’s apply the group colors from above (see graphic below). Assume that:

  • Your most experienced auditors can Write custom ACL Scripts
  • 10% of your auditors can Analyze data in ACL via menu options
  • 50% of your auditors can Use Excel formulas
  • 75% of your auditors can Use Excel filters…

5 – Analytics Performance

What methods are used to analyze data?
-Use Excel filters, analysis driven by menus and buttons (Remove Duplicates, Text to Columns, etc.)
Use Excel formulas
-Use Excel Fuzzy Lookup, other Excel addins, Pivot Tables, etc.
-Analyze data in ACL via menu options
-Analyze data in Power BI, Tableau, and other company tools via menu options
-Run existing ACL scripts
-Write custom ACL scripts
-Schedule and automate ACL scripts
-Automate the analysis using company tools

When you apply the colors for each group across all the steps, you can easily see the gaps between your various groups, but you can also see the steps each group needs to take to increase their expertise.

What’s Next?

The final level of maturity is auditing the analytics of your business partners. I maintain you won’t be able to do it without a high level of analytic maturity. And you can’t do it with just one or two auditors.

For my earlier musings about this level of maturity, see point 4) near the bottom of No Analytics, No Audit Department.

A final note

The staircase doesn’t include the various types of analytics such as (listed in order of increasing maturity) descriptive, diagnostic, predictive, and prescriptive analytics, simply because most analytic auditors, in my experience, are doing mainly descriptive analytics.

Also, I’m not yet convinced that doing predictive or prescriptive analytics is part of the normal audit charter. Sure, doing those types of analysis will provide value to the company, but is audit the best department for that?

Especially when the profession as a whole has not yet embraced descriptive analytics.

As always, I’m interesting in your opinion…

See also:

Excel: Basic Data Analytics

10+ Signs Mgmt Doesn’t Really Support Analytics



Filed under ACL, Audit, Data Analytics, How to..., Technology, Written by Skyyler

3 responses to “The Analytic Staircase for Auditors

  1. Thanks, yet another interesting post. There’s a lot here about the how, also some good pointers to the why.

    The technology may be excellent, but the world hasn’t gained much by blurring together big data, data analysis, and computer assisted audit techniques (CAATs), as they were (back in the day). For financially focused auditors, there may be some further unspoken confusion with ‘analytical review’, which involves a calculator rather than a computer.

    Your audit uses for data are pretty basic and sensible. They should not be controversial, or even a separate initiative for debate at senior levels. Even I have used data in audits for basic audit analytical purposes and for audit tasks like multi-stage sample selection. Basic SAS, Access, and Excel: almost no new investment to ponder.

    Using data in audits is not IT auditing, and does not require the same level of insight into the black box. All auditors should be able use data in audits, or at least have a precise conversation about data requirements and links to assurance. (Back in the day, many auditors preferred to spend their planning time on planes and hotels, not so much on data.)

    ‘Big data’ is a different story. It’s over-hyped and offers very dubious or indirect benefits to auditors. Your doubt that ‘doing predictive or prescriptive analytics is part of the normal audit charter’ is well placed. I even feel that ‘descriptive analytics’ is an over-hyped term for the simple, high-value applications in audit.

    If there needs to be a shift in management support for ‘data analytics’, I’d suggest starting from basic uses of data in audits. Those basic uses should specifically including searches for anomalies that may indicate unrecognized risk or unrecognized control failures. In that way ‘data analytics’ prove their relevance to the agreed audit charter, before there is any debate about investment value.

    For that you need capable and properly motivated auditors first, technical support second. Deliver the value in audit assurance, and leave the products, visualizations and jargon for someone else to bring up.

    Liked by 1 person

    • skyyleracl

      I liked a lot of things about your comment.
      Like “All auditors should be able use data in audits”. Not everyone believes that, or more accurately, wants that. I am still shocked to find so many lazy auditors (or fearful?).

      I’m not sure too many auditors are dealing with big data. My largest data set so far was 24 million rows of 150 columns. I don’t consider that big data. It was just bigger than most I deal with.

      I agree that basic analysis is the best starting point, but some auditors can’t/won’t do even that. I keep telling everyone that even basic analysis will add value without taking that much time.

      Your statement “you need capable and properly motivated auditors first” is another gem. If you find any, send them my way :)

      I listed visualizations above because it’s the craze. Yes, a good visual or dashboard is great and helpful, but unless some decent analysis is behind it, it will never happen. Too many audit leaders want dashboards, visualization, and automation, but fail to realize most of their auditors are still rubbing sticks together to stay warm. Marshmallows, anyone?

      Thanks for your input, Roger. Look forward to more.

      Liked by 1 person

  2. Pingback: 10+ Signs Mgmt Doesn’t Really Support Analytics | ITauditSecurity

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.