Introduction (15 minutes)

1. Brief recap of basic Excel functions and concepts
2. Importance of Excel in external audit

Objectives of the course

Data Import and Cleaning (30 minutes)

1. Importing Data: CSV, TXT, and from databases
2. Handling errors: #N/A, #VALUE!, and other common issues
3. Using Text-to-Columns and Flash Fill
4. Finding and removing duplicates

Advanced Data Manipulation (45 minutes)

1. Advanced use of VLOOKUP, HLOOKUP, and introduction to INDEX- MATCH
2. Using IF combined with other functions: IFERROR, IFS, etc.
3. Date and time functions: EDATE, EOMONTH, DATEDIF, etc.
4. Array formulas and how they can be leveraged in auditing

PivotTables and PivotCharts (45 minutes)

1. Building and customizing PivotTables
2. Grouping data and creating calculated fields/items
3. Using slicers and timelines for interactive reporting
4. Introduction to PivotCharts for visual analysis

Data Analysis Techniques (45 minutes)

1. Conditional formatting for audit analysis
2. Data bars, color scales, and icon sets
3. Using Data Tables and Scenario Manager for sensitivity analysis
4. Introduction to Solver for optimization problems

Security and Tracking Changes (30 minutes)

1. Protecting worksheets and workbooks
2. Setting password and encryption options
3. Using the Track Changes feature for collaborative auditing

Documenting and reviewing comments in Excel Automation with Simple Macros (30 minutes)

1. Introduction to the concept of macros
2. Recording and running simple macros for repetitive tasks
3. Assigning macros to buttons for ease of use
4. Security considerations with macros

Q&A and Hands-On Exercise (45 minutes)

1. Participants can ask specific questions related to their work or the topics covered
2. A real-world audit scenario will be provided, and participants will be tasked to handle and analyze the data using the skills learned

Conclusion and Feedback (5 minutes)

1. Recap of what was learned
2. Feedback collection for continuous improvement of the course
3. Providing additional resources for self-study