Beyond Excel

How to Use Data Analytics for

Internal Audit in the 21st Century

IDEA
Table of Contents
The Capabilities and Limits of Spreadsheets 3

The Extents of Excel: Data Analytics Exposes the Limits of Spreadsheets 4

Data Analytics a Must-Have for Today’s Auditors 5

Getting Over Excel: What’s Wrong With the Status Quo? 6

1. Crucial data gets lost in the clutter 6

2. Data loss 7

3. Data distortion and lack of audit trail 7

4. Excel lacks data analytics libraries 8

Getting Over Excel: How to Go Beyond Excel for Data Analytics Internal Audit 9

Making the Switch 9

1. Look for a low-training/no-training solution 9

2. Easy implementation 10

3. Responsive support/helpdesk 10

4. Robust import functionality (and importing PDFs, link to recent blog) 10

5. Visualization/Reporting 11

6. Tools for unlimited functionality 11

How CaseWare Can Help 12

 The Capabilities
and Limits of Spreadsheets

Excel is a powerful tool in the world of finance.

As a ubiquitous, simple-to-use, digital spreadsheet tool, Excel has obvious

benefits for accounting and auditing. The program can perform many kinds
of analytical tests including:

• Horizontal and vertical analytics.

• Ratio analysis.
• Regression analysis.
• Statistics, stratification, aging and much more.
“As advanced Likewise, Excel can also perform some types of data analysis, such as:
data analytics
have become • Append and merge.
fundamental • Cross tabulate and pivot table.
to modern • Detect duplicates.
accounting and • Extract and filter.
auditing practices, • Join and relate.
Excel has been • Sample, sort, summarize and subtotal.
found wanting.” However, as advanced data analytics have become fundamental to modern
accounting and auditing practice, Excel has been found wanting.

In this paper, we look at:

• The limitations of an otherwise useful and universal tool.

• The problems associated with the status quo.
• How to go beyond Excel for real and effective data analytics.

It’s important to note that, while we use “Excel” as shorthand in this

document, we effectively mean all of the conventional spreadsheet tools
widely available, including Google Sheets, Numbers, Apache OpenOffice,
LibreOffice, and many other Excel alternatives.
The Extents of Excel:
Data Analytics Exposes
the Limits of Spreadsheets

With modern data analytics, auditors sort through vast sums of linked and interrelated data
sets to reveal transactions that don’t fit the normal patterns Excel might be able to find. These
These transactions can include everything from material misstatements to indications of fraud.

By leveraging data analytics tools, auditors are able to add value for their clients and provide
insight on whether actions should be taken in light of findings, and what those actions should
look like.

For example, data analytics brings the following capabilities to the table-capabilities Excel
can’t deliver:

• Full dataset testing: data analytics software is capable of examining and testing full data
sets, not just a sampling of data from traditional spreadsheets. This full-set analysis allows
the most thorough audits and eliminates the possibility of error due to small sample size or
or missed transactions.

• Data from multiple sources: Data analytics software also lets you easily extract and
integrate data from multiple sources, allowing for quick, efficient analyses and higher
quality insights, providing more value to clients.

• Automated testing: Data analytics enables automated testing with audit workflows and
providing useful reports for future audits. This not only provides enhanced reliability of
results, but also reduces cost by eliminating the need for long, costly audits thanks to on-
on-gong monitoring. This can be particularly attractive to clients who require deep analysis
but lack the time and money to do it. Automated data analytics tools allow auditors to dig
deeper into data without using significantly more staff time.

LEARN MORE: DATA ANALYTICS FOR FRAUD: How is traditional fraud detection like looking for a needle in
a haystack? Learn how data analytics hypercharge fraud detection.
 Data Analytics a Must Have for Today’s Auditors
Likewise, analytics software also leverages artificial intelligence (AI) and
machine learning (ML) to quickly and accurately examine all transactions
and balance entries in a data set. The abilities of AI and ML to provide
tailored, granular results, means not only the most accurate results, but
also the possibility of revisiting areas of concern that may have been
flagged in an initial analysis.

The IIA’s recent Financial Services and Public Sector Knowledge Brief
Data analytics declared data analytics is no longer a “nice to have,” but is now an
is no longer a essential part of auditing’s digital transformation, providing executives
‘nice to have’ and stakeholders with deeper insights into risks and opportunities.
-IIA Financial Services
and Public Sector Given this reality, whatever else its positive attributes, Excel is
Knowledge Brief fundamentally ill-equipped to handle the vast amounts of data involved
in modern data analytics. It is not the solution auditors need to use
data analytics effectively or provide the kind of solutions needed for
today’s businesses.
Getting Over Excel: What’s
Wrong With the Status Quo?

Without question, we live in the era of Big Data. From Fortune 500 companies to agile
start-ups, data and the ability to learn from it are increasingly the keys to success in the
modern business world. And that’s why the ability to leverage data analytics, while once
considered just a nice-to-have, is today regarded as an essential part of an advanced,
modern internal audit.

And between Big Data, cybersecurity risks, and AI, today’s audit’s complex needs begin to
show the limitations of conventional software, like Excel.

Excel’s shortcomings manifest when you’re working with large data sets and on large-scale
projects, and when advanced audit and data analytics is required.

Here are four key drawbacks to using Excel for data analytics.

1. Crucial data gets lost in the clutter

Excel presents all raw data at once, making it difficult to quickly sort out the essential data
from what is less critical. Excel spreadsheets lack the data visualization tools that would make
it easier to draw attention to and present significant sections of the data sets.

Excel caps the number of rows at around 1 million, and columns at about 16,000. This is
ironically, a drawback in two ways.

These rows and columns are too many for any individual or group of individuals to manage
and interpret usefully. They also lack the kind of relationship to the data contained to make
the figures useful for real data analytics.

With a large volume of data represented on a single spreadsheet, analysis becomes difficult.
With flawed analysis comes faulty interpretations of the data, leading to poor decision-making
and costly mistakes.
 2. Data loss
The danger with Excel spreadsheets is that they are prone to loss caused
by human error. One errant keystroke can delete whole rows or whole
It’s remarkable
columns of data, or (more perniciously) delete one cell that throws the
any auditor would calculations of full sheets out of whack, necessitating hours of work to
feel comfortable see what’s gone wrong.
managing massive
datasets with such Excel spreadsheets are in danger of this kind of error accumulation every
fickle controls in a time they are emailed to colleagues or accessed on a shared drive by team
field so risk-averse. members. It can be impossible to know what changes have been made by
the many hands that had access to the workbook, which can cause untold
hours of work to try and untangle problems when errors have
been introduced.

It’s remarkable any auditor would feel comfortable managing massive

datasets with such fickle controls in a field so risk-averse.

3. Data distortion and lack of audit trail

As with data loss, the figures in an Excel spreadsheet are vulnerable to
distortion based on user inputs of various kinds.

Most Excel users will, at one time or another, use the program to create
graphs, pie charts, and tables to help represent their data more visually and
intuitively. However, even these attempts at visualization can distort data to
a great extent.

Updating or upgrading the spreadsheet (such as in the transition from .xls

to .xlsx formats) can result in the loss of huge amounts of historical data.
Such losses make data analysis and comparisons difficult, which in
turn makes it hard to correctly identify trends. A bad copy-and-paste,
a flawed formula, or input errors can corrupt or distort the whole Excel
workbooks’ output.

And because Excel lacks version control or any kind of record-keeping

capacity, it fails to meet the requirements of even a basic audit trail. When
human error or distortion creeps in-and it always does-there’s no mechanism
for Excel to work backward through changes to see what when wrong, and
when, things auditors are generally very interested in. Introduce multiple
data sources or add a continuous auditing environment to the scenario, and
you can imagine the headaches this lack of transparency inherent in Excel
would cause, and the lost hours (or days) it would take to fix the problems.
 4. Excel lacks data analytics libraries
One of Excel’s chief failures is that it can’t take advantage of the incredible
possibilities in audit data analytics. While some basic data analytics tests
can be run in Excel, these are time-consuming, complicated pursuits
requiring advanced user skills to program and manipulate intricate macros
The beauty or multiple pivot tables.
of dedicated
analytics software In contrast, dedicated data analytics tools-which often draw their baseline
is that it can help data from Excel spreadsheets-can sort and parse data with ease so that
users can actually draw out the incredible insights presented within.
even relatively
inexperienced The beauty of dedicated analytics software is that it can help even
users navigate relatively inexperienced users navigate a data analysis process from start
a data analysis to finish. Predefined routine tests can help users duplicate, join, or stratify
process from start data, or detect security issues in an SAP implementation, for example, all
to finish. with no coding required.

And dedicated audit data analytics software also minimizes human error.
It protects data in a centralized, secure system where the possibility of
keystroke mistakes or emailing the wrong file version is entirely eliminated.
Getting Over Excel: How to Go
Beyond Excel for Data Analytics
Internal Audit

Professional-grade data analytics tools represent a step up from Excel or similar spreadsheet
programs, offering capabilities that make audits more efficient and effective.

The good news is that with today’s robust offerings in data analytics tools, making the move
from an Excel-based workflow has never been easier.

With out-of-the-box solutions, you can quickly and easily import and combine multiple
data sets; join tables together and perform analysis with multiple tables; import a variety of
common data types; leverage powerful data visualization and back-end reporting functionality
built right into the software; and automate and dynamically run tasks. And unlike Excel,
dedicated data analytics tools don’t touch the underlying data they draw from and allow
auditors to track every change made within the platform.

Making the Switch

So, what should you look for when considering your switch to a data analytics solution? Here
Here are six key features to keep in mind.

1. Look for a low-training/no-training solution

One reason to move away from Excel is the difficulty that many users have with the
program without extensive training or certification. Leveraging the program’s more advanced
functionality requires knowledge of complex macros and pivot tables. When moving to a
dedicated data analytics solution, make sure that it is genuinely user-friendly and intuitive,
requiring little or no training to get people up and running. If your solution is complicated,
potential users will be intimidated to use it, and your organization will never gain the full
benefit of the software.
2. Easy implementation
Excel does have the advantage of being ubiquitous. It’s on every computer in every
business everywhere. When you need it it’s just a click away, no waiting. So when
considering a dedicated data analytics solution, make sure that it will be easy to install and
roll out throughout your company. No one wants a long delay or a lot of downtime in order
to implement a new solution. It’s bad for your business, and it’s bad for your clients. Favor a
solution that offers easy implementation and your users won’t resist using a new software.

3. Responsive support/helpdesk
Having step-by-step assistance available when you need it is hugely important. Does your
solution come equipped with a wide range of help features, tutorials, instructional videos, and
and reminders? When you have questions that go beyond these on-board help features, can
can you reach out and quickly get guidance from a help desk?

4. Robust import functionality

(and importing PDFs, link to recent blog)
Excel isn’t designed to handle large data sets, automate analysis, or import data from a variety
of sources-all functionalities that auditors and other financial professionals regularly need. And
And when data is pulled from multiple sources it often requires tedious and time-consuming
clean up. All of this is a recipe for error to creep into the data set.

So, one of the chief benefits of a dedicated data analytics tool is its ability to pull data from
multiple data streams and a variety of formats and quickly and seamlessly knit them together
for analysis without the need for clean-up.

Make sure your potential analytics solution can import text files, MS Access and Excel, CSV,
SAP, Oracle, SQL, and especially PDFs.
5. Visualization/Reporting
We’ve talked about the growing importance of data
visualization to the audit process, and how it allows
project managers and business owners to gain crucial
insights and leverage data to make better business
decisions. Does your potential solution include
state-of-the-art, dynamic, and effective data
visualization as a built-in feature? What is the
report functionality like?

Unlike Excel, dedicated data visualization tools

represent data in a detailed, consistent, accurate
manner-another benefit traditional spreadsheet
software can’t provide.

6. Tools for unlimited functionality

The best tools are the ones that grow with you, and that
meet the specific needs of your business. How does
your potential data analytics solution ensure unlimited
functionality for your unique set of needs?

This is what we had in mind when we designed IDEA

Lab, an innovation hub for IDEA-related resources
whose goal is to make accountant’s lives easier and their
work faster. Why get a whole new tool when you can
just make the one you have do the new thing you want?

With IDEA Lab, auditors can access, download,

comment on, and test working prototypes of
IDEA-related resources, all from with your web browser
and with no programming knowledge needed.

Create specific solutions and tools tailored to your

business and then share them for use with the wider
IDEA Lab community. With eight plug-ins currently, and
more available in the coming months, IDEA Lab is your
way to improve your day-to-day from right within a tool
you already have.

To learn more about how IDEA can help you complete

your audits quickly and effectively, click here.
 CaseWare IDEA Inc.
1400 St Laurent Blvd., Suite 500, Ottawa, ON K1K 4H4 Canada
1-800-265-4332 | ideasupport@caseware.com | idea.caseware.com

How CaseWare Can Help

If you’re ready to make the move from Excel to a dedicated data analytics solution, and you want a secure,
extensive, and efficient way to audit, CaseWare IDEA Data Analysis software is the professional data analysis
tool for you.

With more than 400,000 users in 90 countries, CaseWare Analytics technologies are the industry standard
for audit and finance professionals who want to use real data insights to create remarkable ROI and business
improvement opportunities.

©2020 CaseWare International Inc. All Rights Reserved.