Data Analytics in Excel

Overview

For managers, analyzing data is a powerful skill to enable better decision making. For auditors, data analytical skills are essential to find flaws in the process. Microsoft Excel has always been one of the most popular tools for regular data analysis thanks to its built-in pivot table functionality to report and visualize the underlying data. But nowadays Excel can do even more for you since it embedded Power Query, Data Analysis functions (DAX such as in Power BI), the Data Model and Power Pivots without any additional costs.

Target Audience 

Data analysis skills are becoming almost a prerequisite to become an auditor. In this expert course, you will learn how to perform data analysis using Excel’s most powerful and recent features.

Course Objectives

Data Analysis is a process, and it needs to be part of your Audit organization. It contains subprocesses and steps that are defined to guide you through audit testing during fieldwork and to deliver the results in the most optimal way.

Some of these steps may include:

- Understanding the infrastructure on how data moves and is stored;

- Obtaining access to coherent and complete data; 

- Downloading the data into your test environment; 

- Cleaning and preparing data; 

- Defining the tests and scenario’s that are to be performed; 

- Performing different data analysis techniques of different complexity; 

- Documenting the steps taken to be able to reperform the analysis; 

- Preparing your results for clear and understandable reporting.

Course Content

- Recap how basic pivot table functions work; 

- Demonstrate how today’s (x)lookup functions connect tables together;

- Introduce the Data Model and related Power Pivot functionalities that solves many problems, while adding and providing more complexity; 

- Get you acquainted with matrix formula’s to perform complex calculations;

- Visualize data from unstructured sources;  

- Demonstrate how VBA code can solve the most difficult problems; 

- End up with some auditing techniques embedded in Excel

Prerequisites

Participants are expected to have practical experience in using Excel, including the use of the basic set of spreadsheet functions and formulas. Familiarity with audit concepts is recommended to maximize the benefits of this advanced course on data analysis.