Course Outline

Module 1: Introduction to Advanced Excel

• Introduction to Excel shortcuts
• Understanding various Excel settings
• Understanding the Excel layout
• Working with Microsoft Excel fundamentals and numeric data
• Saving Excel files in different formats
• Importing data into Excel
• Case Study: Import and organize a dataset into Excel, applying basic formatting and saving it in multiple formats.

Module 2: Advanced Excel Formulas and Macros

• Working with various advanced Excel data functions
• Learning how to work with arrays and lists
• Working with advanced logical formulas in Excel
• Working with advanced text formulas in Excel
• Understanding the basics of VBAs and Macros
• Case Study: Create a complex spreadsheet that utilizes advanced formulas and a basic macro to automate repetitive tasks.

Module 3: Data Visualization and Analysis in Advanced Excel

• Creating and designing tables
• Working with charts for data presentation
• Working with slicers, sorting, and filtering data, and understanding the use of references
• Data summary and analysis using pivot tables and charts
• Linking several worksheets
• Combining several workbooks to create a summary
• Securing and reviewing your worksheet
• Tracking changes and comments
• Case Study: Develop a dynamic report that summarizes sales data using pivot tables, charts, and links across multiple worksheets.

Module 4: Management and Decision Making

• Understanding how to identify, evaluate, and manage business capital risk
• Monte Carlo Simulation for decision making
• Decision Trees for decision making
• Forecasting
• Learning payroll management
• Understanding return on investment
• Case Study: Apply Monte Carlo Simulation and decision trees to a business scenario to forecast outcomes and make informed decisions.

Module 5: The HR Dashboard and Analytics for Presentation and Reports

• Introduction to data index and page layouts
• Working with predictive attrition and animated macro charts
• Understanding the VLOOKUP and HLOOKUP analysis
• Learning BVG and leave analysis, FNF and exit analysis
• Learning how to group data and understand the Bradford Factor
• Learning the HR update and HR update calendar
• Developing operational dashboards including business KPIs
• Using pivot tables and pivot charts to efficiently perform automated report writing and analysis
• Case Study: Create an HR dashboard that tracks employee metrics, forecasts attrition, and automates report generation for management presentations.