DATA MINING FOR AUDIT &
ASSURANCE ANALYTICS WITH MS EXCEL
DMA32D
DATA MINING FOR AUDIT & ASSURANCE ANALYTICS WITH MS EXCEL
DMA32D
PROGRAM OBJECTIVE
To enable participants leverage advance Excel functionalities for assurance analytics (i.e., data analytics from an audit perspective), reporting, interpreting results and decision making.
WHO SHOULD ENROLL
- Chartered Accountants, Managers, team members and articled trainees in the audit function.
- Internal auditors and Information System Auditors.
TRAINING METHODOLOGY
Hands – on exercises in Excel 2010 / 2013 using learn-by-doing approach on real world (realistic) data files interspersed with brief lectures and brain storming sessions.
COURSE MATERIAL
Participants shall be given:
- Over 100 MB of realistic data files,
- Comprehensive course material and
- Stationary
INDICATIVE COURSE CONTENT
module # 1
Data Import – Getting Data into Excel
DAY 1 – DURATION: 2.5 HRS.
Learn techniques of Data Import from varied sources like
- Text files (ASCII format, including .dat files),
- Web pages (including on-line connectivity to refresh data on real time basis),
- RDBMS like SQL Server, Tally database, etc.
- PDF files.
module # 2
Multi-Dimensional Analysis of data & reporting.
DAY 1 – DURATION: 3.5 HRS
Use Pivot tables to:
- Make pivots from data exceeding one million rows!!
- Slice & dice data,
- Tabulate, cross-tabulate,
- Get running totals (e.g. running balance of a bank account),
- View amounts / quantities as % of rows / columns,
- Produce Top 3 or Top 10 reports,
- Group sales by weeks, months, quarters, etc.,
- Apply the Pareto’s (80-20) principal to identify high value transactions
- Analyse large data sets (even more than million rows).
module # 3
Statistical Sampling
DAY 2 – DURATION: 3 HRS
Introduction to sampling
Statistical v/s. Non. Statistical Sampling
Learn to use Binomial distribution to determine sample size; set tolerable rate of misstatement,
expected population exception rate, and confidence level,
Draw samples using various statistical sampling techniques like
-Random Sampling,
-Systematic Sampling,
-Probability Proportional to size Sampling,
Determine how representative are your samples to the population, and finally
Extrapolate the results of evaluation of sample to the population to manage audit risk involved in the attestation.
module # 4
Data Mining
DAY 2 – DURATION: 1 HRS.
Master the art of mining for susceptible transactions by efficiently shifting through millions of transactions and filtering out transactions that are indicative of high-value transactions, non-compliance with laws, rules or organisational policies, kick-backs etc.
module # 5
Benford’s law
DAY 2 – DURATION: 1 HRS.
Use Benford’s law to identify irrational repetition of digits so that you can zero down on suspicious transactions that may indicate irregularities or fraud.
module # 6
RSF and Sigma
DAY 2 – DURATION: 1 HRS.
Know Relative Size Factor Theory (RSF) and Standard Deviations (sigma) and how these can help you zero-down on questionable entities and identify red flag transactions. Learn to implement RSF and sigma in Excel.
ADDITIONAL / OPTIONAL MODULES
AOM1 – Macros – a definitive guide
DURATION: 3 HRS.
Learn ALL that is required to automate routine tasks in Excel
- Introduction to Macros,
- Recording Macros,
- Editing recorded Macros,
- Creating functions,
- Creating macros to automate routine tasks,
- Creating Macros that use data ranges which may vary in size,
- Finding free Macro codes, editing them & adapting to your requirement.
AOM2 – MS Excel Top 51 – Tips & Tricks
DURATION: 3 HRS.
This module is a compilation of such questions the answers to which every Excel business user is keenly interested to know. To these have been added lesser know, but extremely powerful, ‘secrets’ of Excel. The result is a listing that is titled “Top Excel Tips and Tricks”. Knowing these ‘Top Excel Tips and Tricks’ would help participant save tens of hours every month.
The tips are carefully categorized and grouped under heads like:
- Data entry and Data Validation,
- Navigation & Selection,
- Insert, Format and Copy paste,
- Creating Custom Lists,
- Filling multiple ranges (including non-adjacent ranges),
- Working with Range Names,
- Cell Comments,
- Conditional Formatting,
- Protecting your worksheet (including accessing data from protected worksheets!!),
- Use the Function Wizard, Common functions (AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, INT),
- Relative, Absolute, Mixed cell references : >,<,= operators,
- Logical functions using IF, AND, OR, NOT including nesting and combinations thereof,
- The LOOKUP function , Date and time functions ,
- INDEX and MATCH functions,
- Auto Filters and Advanced Filters.
COMMITMENT POLICY
We are committed to your success; It is our policy that every organization / individual who has ever been trained at one of our training programs is entitled to a FREE life-time email support for ideas / trouble shooting on implementing Excel for data analytics.
NO Conditions apply!!