CFC515: Data Analytics with Advanced Excel

Module 1: Introduction to Data Analytics

1.1 Overview of Data Analytics

• Importance in decision-making
• Key concepts and terminology

1.2 Excel as a Data Analysis Tool

• Advantages of using Excel
• Introduction to Excel data analysis tools

Module 2: Data Cleaning and Preparation

2.1 Importing Data into Excel

• External data connections
• Importing data from different sources

2.2 Cleaning and Transforming Data

• Removing duplicates and inconsistencies
• Text to columns, data validation, and formatting

Module 3: Exploratory Data Analysis (EDA)

3.1 Descriptive Statistics

• Mean, median, mode
• Measures of dispersion

3.2 Data Visualization in Excel

• Creating charts (bar, line, pie)
• Conditional formatting for visual insights

Module 4: Advanced Excel Functions for Data Analysis

4.1 Statistical Functions

• AVERAGEIF, COUNTIF, SUMIF
• Statistical analysis with Excel functions

4.2 Lookup and Reference Functions

• VLOOKUP, HLOOKUP, INDEX, and MATCH
• Combining functions for dynamic analysis

Module 5: PivotTables and PivotCharts

5.1 Creating PivotTables

• Summarizing and analyzing data
• Grouping and filtering data

5.2 PivotCharts for Data Visualization

• Creating dynamic charts linked to PivotTables
• Using slicers for interactivity

Module 6: Power Query and Power Pivot

6.1 Introduction to Power Query

• Data import and transformation
• Merging and appending queries

6.2 Power Pivot Basics

• Creating data models
• DAX (Data Analysis Expressions) introduction

Module 7: Data Analysis with What-If Analysis Tools

7.1 Goal Seek and Scenario Manager

• Performing sensitivity analysis
• Creating and managing scenarios

7.2 Solver Add-in

• Optimization and constraint-based analysis
• Solver for linear programming problems

Module 8: Regression Analysis in Excel

8.1 Understanding Regression

• Linear regression basics
• Multiple regression analysis

8.2 Regression Analysis in Excel

• Data preparation for regression
• Interpreting regression results

Module 9: Automation with Macros and VBA

9.1 Basics of Macros

• Recording and executing macros

9.2 Introduction to VBA

• Writing simple VBA code for automation
• Integrating VBA with data analysis tasks

Module 10: Real-world Applications and Case Studies

10.1 Industry-specific Data Analytics

• Application of data analytics in various sectors
• Extracting actionable insights from real-world data

10.2 Capstone Project

• Applying learned concepts to a comprehensive data analysis project
• Peer review and feedback

GENERAL NOTES
i.   The clients have many options on where they would wish to have the training
ii. The client should communicate their preferred training venue at least seven days before the training date failure to which the training shall take place in Nairobi.
iii. This course is delivered by our seasoned trainers who have vast experience as expert professionals in the respective fields of practice. The course is taught through a mix of practical activities, theory, group works and case studies.
iii. Training manuals and additional reference materials are provided to the participants.
iv. Upon successful completion of this course, participants will be issued with a Chania finance consultancy certificate.
v.   The onsite training fee covers tuition fees, training materials, tea at 10am, lunch, tea at 4pm, training venue and a certificate on completion. Accommodation and travel may be arranged for our participants upon request.
vi.   The online training fee covers tuition fees, training materials, and a certificate on completion.