Audit & Forensic Data Analysis with MS Excel
4.3 (28 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
92 students enrolled

Audit & Forensic Data Analysis with MS Excel

An exhaustive and comprehensive course on Data Analytics for Audits and Forensic Accounting
Bestseller
4.3 (28 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
92 students enrolled
Created by Nikunj Shah
Last updated 5/2020
English
English [Auto]
Price: $49.99
30-Day Money-Back Guarantee
This course includes
  • 12.5 hours on-demand video
  • 10 articles
  • 23 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • 1. Implement Digital Analysis techniques like Benford's Law and Relative Size factor Theory (RSF) in Excel to identify red flags or fraudulent transactions
  • 2. Use Pareto analysis (80-20 rule) and identify high value (and high risk) transactions
  • 3. Use Statistical measures like Standard Deviation and Coefficient of variation to zero-down on transactions of audit interest
  • 4. Re-engineer your audit processes to create dramatic efficiencies in analysis and reporting
  • 5. Perform a ‘Time Dimension’ analysis on data, learn to interpret results
  • 6. Perform a Vendor master clean-up and identify suspicious and fraudulent vendors
  • 7. Connect Excel worksheet to corporate (or Tally) database & extract any data that you want
  • 8. Connect your Excel worksheet to live data on the Internet to track (for e.g.) current value of investments
  • 9. Get Kaleidoscopic views on data by slicing and dicing the data
  • 10 Automatically create, from a single report within Excel, a report for each of the vendor's transactions (for confirmation)
  • 11. Detect non-compliance with organizational policies & compliance frame work
  • 12. Identify revenue leakages, double payments, etc.
  • 13. Work with large data sets (even more than million rows) in Excel
Requirements
  • At least 6 months experience of working on MS Excel
Description

Challenges are multifarious. Overwhelming nos. of transactions, loss of conventional (paper) audit trail, system based controls, ever increasing and complex compliance requirements are amongst the prime reasons why traditional methods of collecting and evaluating evidence (like vouching and verification) are no longer adequate. The auditor can no longer treat Information Systems as a ‘Black Box’ and audit around it. His methods and techniques have to change. This change is what the world calls today, ‘Assurance Analytics’ i.e. data analysis from an ‘audit perspective’.


Using advance features of MS Excel, the auditor can access client’s data from their databases and analyse it to discharge the onerous duty cast on him. Since over 15 years, CA Nikunj Shah has been perfecting these techniques of ‘assurance analytics’. These include digital analysis techniques like Benford’s Law, Relative Size Factor Theory (RSF) and Pareto’s 80-20 rule that have enabled auditors and forensic investigators to identify control failures and over rides, detect non-compliance with laws, zero down on questionable transactions and identify red flags lost in millions of transactions. It is like quickly finding the needle in a hay stack!! In this unique course, your favourite instructor shall share the best of his research, auditing and training experience. The participants shall learn, step-by-step, the nuts-and-bolts details of using advance features of Microsoft® Excel coupled with the instructor’s insights to apply them in real-world audit situations. Each section shall equip participants with assurance analytic techniques using real-world examples and learn-by-doing exercises.

Who this course is for:
  • 1. Chartered Accountants, Managers, Team Members and Articled Trainees in Audit function (All types of Audits like Internal, Statutory, Management, Performance, etc.)
  • 2. Heads, Managers and Team Members in Financial Accounting function
  • 3. Forensic Accountants and Financial Fraud Investigators
  • 4. Information Systems Auditors
Course content
Expand all 95 lectures 12:19:26
+ Concepts
9 lectures 01:17:01
Data Files/ Resources
00:03
Types of Analysis
08:55
Business Analytics
02:57
Audit Analysis and Forensic Data Analysis
28:45
Formal Definitions
03:39
Skill Sets & Phases
07:48
Data Integrity
20:41
Closing Remarks on Concepts
02:29
+ Introduction & Data Import From ASCII
6 lectures 52:08
Data Files/ Resources
00:03
Various Sources / File Types For Data Import
05:02
Importing Data From ASCII Files - Background
20:21
Importing Data From ASCII Files - Steps
16:48
Closing Remarks On Importing Data From ASCII Files
04:39
+ Introduction & Data Import From ODBC
8 lectures 36:08
Data Files/ Resources
00:03
Understanding Open DataBase Connectivity (ODBC)
10:03
Installing Tally & Setting Up Systems
04:05
Importing Data Using ODBC - Steps
06:10
Resources To Understand Database
04:28
Troubelshooting Excel-Tally ODBC Connectivity
05:17
Closing Remarks On Data Import From ODBC
00:39
+ Data Import from Web & PDF
6 lectures 42:23
Method I - Get Data From Webpage Into Excel On Real-Time Basis
10:52
Method II - Get Data From Webpage Into Excel On Real-Time Basis
05:45
The Challenges In Getting Data From APDF File To Excel
07:18
Four Ways To Get Data From APDF File To Excel
09:10
Closing Remarks On Data Import From Web & PDF
02:06
+ Data Cleansing
10 lectures 01:17:08
Data Files/ Resources
00:03
Challenges After Data Import - Cleaning, Formatting & Validating
02:19
Understanding ASCII Report Files & Steps To Import Data From Such Files
23:25
Cleaning Data
04:23
Converting Text To Numbers
04:24
Quickly Filling Blank Rows With Data
10:17
Importing & Formatting Dates
08:14
Ways To Audit Excel Worksheets
07:41
Extract Text From Cells
12:05
Closing Remarks On Cleansing Data
04:17
+ Multi-Dimensional Analysis Of Data With Pivot Tables-Fundamentals & Intermediate
8 lectures 01:39:47
Data Files/ Resources
00:03
Introduction To Multi-Dimensional Analysis Of Data
16:28
Understanding Pivot Table Anatomy
03:07
Basic Techniques Of Analysis With Pivot Tables
26:37
Quickly Get A Pivot Table Report For Each Item In Page / Filter Quadrant
15:35
Apply Pareto's Rule To Zero Down On High Value / Risk Transactions
30:33
Apply Pareto's Rule In Audits - Where To Apply & Where Not To Apply
06:10
Closing Remarks Multi-Dimensional Analysis Of Data With Pivot Table
01:14
+ Multi-Dimensional Analysis Of Data With Pivot Table - Advance Level 1
9 lectures 01:13:11
Data Files/ Resources
00:03
The Need To Create Pivot Tables From External Data Source
08:25
Pivot Tables From External Data Source - Dealing With Complexities
15:58
Steps To Create Pivot Table From External Data Source
21:07
Re-Engineering A Business / Audit Process By Creating Pivot Tables From External
06:29
Steps To Create Pivot Table From External Data Source - Continue
14:32
Modify A Pivot Table Created From External Data Source
01:59
Refreshing The Pivot Table Report Upon Change In Source Data
03:23
Closing Remarks On Multi-Dimensional Analysis Of Data With PivotTable Intermedia
01:15
+ Multi-Dimensional Analysis Of Data With Pivot Table - Advance Level 2
7 lectures 01:00:31
Data Files/ Resources
00:03
Pivot Tables & File Size
11:07
The Secret Of Pivot Table Data Source
14:53
Pivot Tables - Ways To Minimize File Size
08:57
Access Pivot Table Source Data Even After The Source Data Sheet Is Deleted
03:10
Make Pivot Table Range Dynamic
11:06
Recap of All Modules On Multi-Dimensional Analysis Of Data With Pivot Tables
11:15
+ Standard Deviation and It's Application in Audits
9 lectures 01:06:19
Data Files/ Resources
00:03
Revisiting Standard Deviation
13:55
Standard Deviation & Risk
01:56
Steps To Calculate Standard Deviation Efficiently
22:12
Reporting Results
03:59
Audit Areas Where Standard Deviation Analysis Can Be implemented
04:28
Why Standard Deviations Cannot Be Compared? Coefficient Of Variation
10:33
Steps To Calculate Coefficient Of Variation
06:03
Closing Remarks On Standard Deviation And It's Application In Audits
03:10
+ Benford's Law and It's Application in Audits
15 lectures 01:36:29
Data Files/ Resources
00:03
Why Are Certain Pages In My Book More Depreciated Than Certain Others?
08:47
Benford's Law
02:24
The Statistical Truth Behind Tossing A Coin
12:18
My Trust With Benford's Law
18:49
DrMark Nigrini & His Work On Application Of Benford's LawTo Identify Tax Evaders
03:55
Steps To Apply Benford's Law In Excel
14:41
Reading And Interpreting Results
03:22
Audit Areas Where Benfords Law Analysis Can Be Implemented
07:17
What If Benford's Law Does Not Apply To A Dataset
04:51
What Are Cosmic Laws?
04:25
Is There A Rationale Behind More 1s And Less 9s
07:01
Closing Remarks Pinkham's Law And What's That ?
03:11