Data Analytics is Driving Efficiency in Higher Ed

November 1, 2017

Nearly every business operation can be infused with data analytics to reduce costs, mitigate risks and improve processes. According to a recent report from the Corporate Executive Board Company (CEB), almost half of the audit departments surveyed plan to invest resources in new or upgraded data analytics technologies in the next year.

Utah State University and Bowling Green State University are among the numerous organizations that are rapidly embracing data analytics to increase the efficiency of existing audits, cut costs and mitigate risks.

Utah State University prides itself on efficiency and is ranked fifth in the nation for lowest tuition on Forbes list of America’s Top Colleges. Over the past several years, the University’s internal audit team has developed a library of analytical tests that auditors and other departments can use to check for potential issues, ensure compliance and save valuable time.

Internal Auditor Benjamin Johnson shares his experiences using data analytics to reduce risk, save time and help departments achieve their strategic goals.

How did you select the tool you’re currently using?

We were using a data analytics tool that was a bit cumbersome and wanted something more user friendly to help us develop more automation for our work. My former supervisor used CaseWare IDEA® early in his career and we both felt it would fit our needs better, particularly with scripting and setting up dialog boxes to help others within our organization use it without much guidance. We wanted something that could benefit everyone, even beyond internal audit, so ease of use was a high priority.

What was your process for identifying where data was stored in the system?

Utah State University uses Banner for reporting, which has thousands of data tables. We’ve spent a significant amount of time learning where data is stored in the system. There is also a Banner coordination committee that includes representatives from IT, finance, human resources and others. Internal audit has a seat at that table to communicate with the various data stewards who point us in the right direction when we’re looking for specific data. We’ve also worked with IT to directly connect to our servers through Citrix to access production data while maintaining a high level of security for the University. Like any other organization, we also do our fair share of digging around and asking questions, and it has helped us build a mutually-beneficial relationship with the data stewards and IT.

What type of internal workflows have you set up using IDEA?

Over the past five years, I’ve continued the work my predecessor started to fully automate the process of retrieving data. We’ve interfaced with data stewards at different campuses, developed dialog boxes within IDEA and created pre-written tests that can be run by anyone with a minimal amount of effort. The result is a self-serve workflow where our auditors can plug information and parameters into the dialog boxes, such as employee data, transaction types, dates, etc., then run the tests.

What types of tests are you running?

Analyzing purchasing card (p-card) data was a process we wanted to streamline. There is an abundance of transactional data and it was taking us a couple of weeks to analyze data for just one department. With IDEA we can pull information from several sources, regardless of the file type. The dialog boxes offer flexibility to check specific criteria, narrow down the data population and focus on key areas.

Most of our IDEA projects have saved us substantial time – we have turned weeks of work into days, and in some cases, days into minutes. It allows us to test 100 percent of the transactions across multiple high- risk areas, and improved the consistency of our work. We can easily search merchant category codes (MCCs) and vendor names to look for expenses that are not allowed, and pinpoint specific areas to save time and get better results.

Have other departments benefitted from data analytics as well?

Some departments were only able to analyze about 10 percent of their transactional data. Using data analytics, we can look at the entire population and spot problem areas to test questionable transactions and follow up. Audit has access to data that’s not readily available to other departments. For example, we join human resource and financial data to help ensure we are in compliance with the Affordable Care Act requirements. We send weekly messages to human resources to follow up on issues we’ve found. My supervisor proactively meets with departments to identify areas where we might be able to help.

What impact has data analytics had on your organization?

We are transforming internal audit from being perceived as watch dogs to a department that adds real value to the organization. We can make that data useful to other departments and help them achieve their goals – no matter how complex they may seem. Our p-card program has improved and we are creating a culture of compliance by reminding everyone that someone is looking at every transaction.

Both Utah State University and Bowling Green State University are leading the profession in using technology to gain efficiency.

What’s on the horizon for expanding your use of data analytics?

The library of tests continues to grow, especially as we meet with other departments that can benefit from our analytics work. We also plan to integrate data visualization into our testing. It is a powerful feature for quickly identifying areas of interest and helping see trends or patterns.

Utah State University is helping other auditors tap into the power of data analytics. Benjamin has provided training for colleagues at other higher education institutions including Utah Valley University and Salt Lake Community College. He shares his experiences and encourages others to start with one area at a time and build relationships with other departments to identify needs where data analytics can be used.

Bowling Green State University has also developed a continuous monitoring program that transforms big data into big insights. From journal entries, to p-cards and payroll, their comprehensive suite of tests keeps fraud and risks at bay. The University provides experiences to prepare students for lifelong career growth – an initiative that even the Internal Audit and Advisory Services department leads through its internship program. Senior Internal Auditor Greg Boo shares his experiences with us:

How did you get started with data analytics?

IDEA is so easy to use that I taught myself the basics and picked up other tips by attending the ACUA conferences and watching webinars. There is an abundance of resources available online.

What are you using data analytics for?

We use data analytics for sorting through our data to look for anomalies and investigate potential areas of exposure. The data we gather for analysis is often thousands of lines, and requires a tool that can handle big data sets. While we do use Excel on occasion, IDEA is our go-to tool for analyzing big data such as p-card transactions, payroll and all types of financial data. We can easily look at the data in different ways to search for things like conflicts of interest, out-of-pocket reimbursements, unusual transactions, etc. Currently, we run more than 60 tests each month through our use of IDEA. Our process includes adding new tests and dropping those we find to be less effective.

With limited resources, how are you able to analyze so many areas of the organization on a regular basis?

Since we are a small department, we enlist the help of accounting students. Our interns typically start working with us their junior year so we benefit by having them for two years. Some even continue with us as they pursue their master’s degree. IDEA’s user interface is Window’s based, so the students are able to use it right away. Some tests are run monthly, while others are run quarterly or annually. We use OneNote to document our Computer Assisted Audit Techniques (CAATs), which helps the interns by giving them step-by-step instructions on how to run the tests and the theory behind them. We review their work carefully and their assistance allows our department to realize efficiencies while getting coverage throughout the University. It also gives the students hands-on experience that is invaluable when starting their careers. It’s a win-win for our department and the students.

What types of data analytics tests are included in the CAATs process?

We use field statistics and Benford’s Law for some of our analysis, and these are a few of the tests we run regularly:

P-Cards

Benford’s Law - First digit, First 2 digits, second digit
Summarize p-card transactions by employee and transactions by merchant
Summarize transactions by day – look for transactions made on the weekend or University Holiday
Compare merchant addresses to employee addresses using fuzzy matching
Use duplicate key detection to look for transactions in the same amount by the same employee for the same merchant
Use duplicate key detection to look for transactions made on the same day an employee recorded 8 hours of leave (vacation, sick, FMLA, etc.)

Accounts Payable

Summarize payments by day – look for payments made on the weekend or University Holiday
Summarize payments by vendor (# of transactions and $ paid)
Summarize payments by invoice # (look for split payments – more than one payment made on one invoice)
Compare vendor addresses to employee addresses using fuzzy matching
Use duplicate key detection looking for duplicate vendors by name or address

Journal Entries

Summarize journal entries by date (look for entries made on the weekend or university holiday)
Summarize journal entries by creation time (look for entries made before 7:00AM and after 6:00PM)
Use duplicate key detection to look for entries that were double entered

Payroll

Summarize special pays by type of pay (stipends, awards, etc)
Summarize special pays by employee
Use duplicate key detection to look for more than one special payment made to the same employee in the same amount
Use duplicate key detection to look for more than one payment to an employee in the same pay period
Use duplicate key detection to look for more than one payment to the same bank and bank account (ensure employees are married, etc. and not fictitious)
Use duplicate key detection on check payment numbers (look for duplicate checks)

Out-of-Pocket Employee Expense Reimbursements

Use duplicate key detection on employee name and amount for p-card transactions and out-of-pocket reimbursements (looking for instances where a p-card transaction was also ran through as an out-of- pocket expense)

Conflicts of Interest

Use duplicate key detection on employee addresses and department (look for instances where 2 employees are living together and working in the same department – will review reporting lines, etc.)

Both Utah State University and Bowling Green State University are leading the profession in using technology to gain efficiency, expand the use of analytics beyond internal audit and streamline processes to help achieve strategic goals. By sharing their best practices with other university auditors and students, the profession is strengthened. For more information about IDEA, visit the U.S. distributor website, www.audimation.com. Special pricing on IDEA and training is available to ACUA members.

About the Author

Carolyn Newman

Carolyn Newman has spent the past 25 years helping thousands of organizations employ technology to gain maximum value from their data and improve business processes. She brings more than 25 years of experience in public accounting, auditing and consulting to her role as president of Audimation Services. She is an active member of the AICPA, IIA, ISACA and Texas Society of CPAs.

Visit audimation.com for more information.

Articles
Data Analytics is Driving Efficiency in Higher Ed
Perks and Pitfalls of Training