Do More With Less: Data Analytics for a Small Audit Shop

Over the years, we have all heard about the benefits of using data analytics, but often the advice seems better suited for large audit shops with dedicated analytics teams or targeted at specific tools. Many of us work in small audit departments with fewer than 10 people, and we often have limited resources at our disposal. 

Regardless of the size of your audit shop, every internal auditor can get started using data analytics, even if you are not an IT or data specialist. Initial training can be simple and inexpensive — we taught ourselves using YouTube (favorite channels include Excelisfun, MrExcel, and Leila Gharani) and LinkedIn Learning (including Robin Hunt’s Data Analytics courses) as well as paid training through Excel University and My Online Training Hub. 

Audit shops using an audit management system like AuditBoard can leverage integrated analytics and automation capabilities, but every audit team likely already has access to great analytics tools to start out, including Excel, Power Query (an Excel add-in), and Power BI. In this article, we will share some things we have learned about data analytics and how you can start immediately doing more with free tools and training than you might have realized. 

Audit Planning With Pivot Tables

It often feels like Excel does not get the respect it deserves as a data analytics tool. By itself, you can use Excel to manipulate data with functions, formulas, pivot tables, VLOOKUPs, and index matches. Pivot tables are a great place to start – these are easy to learn and help you make sense of large data sets. When we were working at a credit union together, we could easily have a spreadsheet with thousands of rows of data showing, for instance, new accounts that were opened. Using a pivot table, we could condense that information down into departments to look for groups that should not have access to open new accounts or perform account maintenance. The analysis gave us a great starting point in planning to optimize our testing procedures instead of defaulting to sampling. The output from the analysis looked like the image below, which is the supporting documentation we included in our audit management software. 

Account Monitoring With Power Query

Power Query is a powerful data analytics tool you can use to extract, transform, and load data from multiple sources. It’s built into Excel, but so many people don’t know about it! We found Power Query helpful if there is a test you run repeatedly because you can use it to write scripts without having to know how to code. Monthly, we reviewed accounts for unusual or inappropriate transactions, but it required us to pull data from four different data sources. In the image below, you can see what a Power Query script looks like. You start by loading a data source and then apply a series of Excel commands to organize the data. You can integrate queries into the same data set to perform multiple tests on one data source. It took time to set up initially, but that investment soon paid off as we were completing tests in a few minutes that used to take hours. 

data-analytics-for-a-small-audit-shop-account-monitoring-power-query.png

Audit Committee Reporting With Power BI 

Another analytics tool we recommend is Power BI, which we used for data visualization. Like nearly every audit department, we used to spend way too much time creating and formatting dashboard reports for audit committee presentations. Using Power BI instead of re-creating the charts each month, we were able to use our AuditBoard data to seamlessly visualize our results with unique dashboards. We could share the graphics for the presentation with the click of a button, and everyone was impressed by how polished the presentations looked — and who doesn’t love a good dashboard?

Start Small, Keep it Simple, and Build Your Skills

The best advice we can give you is just to start trying out some simple analytics. Don’t over-complicate it! Too often, people start with a goal that is too big, get intimidated, and end up discouraged when they don’t get everything perfect on the first try. Give yourself some time to grow into more advanced analytics. We started our journey with a simple pivot table, then slowly added on more as we learned.

One key to our success was that we had the freedom and time to develop our analytics skill set. Our leadership understood the long-term value and increased efficiency these skills would bring to our small department. You already have the tools and can find free online training resources — all you need to invest is time. You’d be amazed at how much you learn by dedicating just one hour a week to building these skills!