The digitisation of organisations is forcing Internal Auditors to rethink their approach to data and become more ‘hands-on’. This course takes you on a rapid tour of how to get the most out of MS Excel software. Designed around the needs of novices and newcomers to this field, you’ll learn tips and tricks to speed up your work, particularly in the control testing phase of an audit.
Who should attend?
This course is open to all.
NOTE: This is a 'hands-on' course and each delegate must bring a laptop to the course with an installed copy of Microsoft Office version 2010 or later. Each delegate must also ensure that they are authorised and able to use working data (comprising example Excel, PDF, TXT and Access files from a provided on-course memory stick or, alternatively, from data pre-supplied as an email attachment) and are able to access, at least, the standard features of a standard installation of Microsoft Excel. The use of a mouse is recommended for some exercises as some features within Microsoft Office are unable to be accessed through a touchpad. Delegates who arrive on course without these defined capabilities either will not be able to proceed or will only be able to enjoy partial course content.
What will I learn?
Upon completion you will be able to:
- check up to 100% of the data available to you electronically;
- put vital components of Office to work in the most efficient manner; and
- manage and manipulate data to create persuasive results during the fieldwork phase of an audit.
The course is accompanied by a manual that has course text and a practice data to take away.
Data analytics and handling - working directly with Excel
- checking spreadsheet integrity – common sources of error
- invalid data and formulae precedents
- recording macros
- re‐performance and virtual calculations – a key audit skills
- shortcuts and speed keys – how to remember them
- Excel statistics – what do you get out of the box?
- filtering and stratification – how to do it?
- pivot tables, Pivot charts and Slicers – what are they and how do they help?
- creating an audit workpaper within Excel
- simple and complex sortation, sorting by other than value
Data analytics and handling – extras through Excel
- fuzzy data matching – an explanation of how the tool works and how to acquire it
- replicating what other paid-for software can do by substituting EXCEL
- formulae – simple examples of how they work and how to put them into use
Data graphics – colour and imagery through Excel
- conditional formatting – correct, incorrect, high and low values, special searches
- using colour, icons, and thematic schemes
- creating charts – what might be suitable?
- embedding charts and images in your reports
External data sources
- connecting with external data – why do this?
- data import into EXCEL from usual and unusual sources
- the JET engine and using MSQRY
- creating a query from tables and fields and their interpretation
- editing queries and bringing results into EXCEL
- re-editing queries and creating query libraries
- creating dashboards using free free-standing products such as Power BI
CPE competency areas covered
- improvement and innovation
- critical thinking
- business acumen
7 CPE points
Member: £625 + VAT
Non-member: £840 + VAT
SAVE £100 when you book this course 3 months in advance