Excel for Auditors

Overview

By the end of this one-day training, auditors will have better knowledge of the audit related functions within Excel that will enable audits to be performed more effectively and efficiently – especially in the area of data analysis – resulting in a better understanding of the (residual) risks and effectiveness of controls within the business processes.

Target Audience

This training course "Excel for Auditors" will help auditors of all levels to use Excel extensively, not just as a spreadsheet, but especially as a powerful auditing tool for data management, data analysis purposes and reporting.

Course Objectives

In this course auditors will learn to:

• Use Excel as audit software, and enable to perform intermediate data analytics functionalities;
• Request, extract and import data into Excel and validate data prior to analysis;
• Make use of Pivot Tables, turning Excel into an effective data mining tool to facilitate retrieving outliers and more;
• Use sampling techniques within Excel;
• Create all types of visuals and graphs to pinpoint changes over time and other chart dimensions.

Course Content

This is a non-limitative list of functionalities that will be addressed during this course:

- Make use of Excel tables instead of ranges
o Turning ranges into tables
o Discover the ‘hidden’ table-functionalities
o The 11 standard table functions
o Importing csv files
o Filling series – different approaches

- Pivot tables
o Analyzing data with pivot tables

- Subtotals
o Adding subtotals – different approaches

- Text functions
o Joining text
o Dealing with text formulas
- Date and Time functions
o Dealing with date and time in Excel
o Analyzing results by date
o Finding missing dates in data

- Lookup & Reference functions
o Looking up data with Vlookup() and Xlookup()
o Looking up data with index(match())
o Comparing two tables
o Dealing with errors in formula results
o There is a match but excel cannot find it
o Retrieving many columns from the lookup range
o Formula auditing

- Conditional formats
o Using conditional formatting
o Highlighting outliers
o Using icon sets to mark values
o Finding transactions from the last period
o Formula to conditionally format cells

- Filtering
o Finding and analyzing records using basic and complex filters
o Finding duplicates or unique values
o Creating a random sample from a dataset

- Sorting
o Sorting your data – basic and advanced, and also by color

- Importing data
o Benford analysis – example

Prerequisites 

This training is positioned at an intermediate level. Therefore, it requires that participants already have basic Excel skills, such as navigation, formulas, cell references and cell formatting.

PLEASE DON'T FORGET TO BRING YOUR OWN LAPTOP.