Module-1.1 Advanced Data Handling & Cleaning

Purpose: Real-world datasets are messy. Learn to clean, transform, and prepare data efficiently.

Topics Covered

• Data Cleaning Techniques
• Data Protection Techniques
• Removing Duplicates, Blanks, and Errors
• Text-to-Columns and Flash Fill (AI Functionality in Excel)

Industry Functions

• Text & String Functions: TRIM, CLEAN, SUBSTITUTE, TEXT, LEFT, RIGHT, MID, LEN
• Case Formatting Functions: UPPER, LOWER, PROPER
• Search & Replace Functions: FIND, SEARCH, REPLACE, VALUE, TEXTJOIN
• Error & Logical Validation Functions: IFERROR, ISNUMBER, ISTEXT, ISBLANK
• Date & Time Functions: DATEDIF, EDATE, EOMONTH, NOW, TODAY, DAY, MONTH, YEAR, HOUR, MINUTE, SECOND

Additional Concepts

• Using Data Validation for Input Control
• Dynamic Data Validation

Module-1.2 Advanced Formulas & Logical Functions

Purpose: Build dynamic, decision-based reports and formulas used across corporate functions.

Industry Functions

• IF, IFS, SWITCH
• AND, OR, NOT
• CHOOSE, INDEX, MATCH, XLOOKUP
• OFFSET, INDIRECT
• Dynamic Array Functions: FILTER, SORT, UNIQUE, SEQUENCE
• Nested IF and Multi-Condition Formulas

Module 1.3: Data Analysis & Business Metrics

Purpose: Learn to analyze, summarize, and interpret business data effectively.

Topics Covered

• Understanding and Creating KPIs
• Summarizing Large Datasets

Industry Functions

• SUMIFS, COUNTIFS, AVERAGEIFS
• SUBTOTAL, AGGREGATE
• LARGE, SMALL, RANK
• ROUND, ROUNDUP, ROUNDDOWN

Module 1.4: Advanced Lookup & Reference

Purpose: Master data linking across sheets and files — essential for analytics and reporting.

Topics Covered

• VLOOKUP vs XLOOKUP (with practical applications)
• Dynamic Range Referencing using INDEX-MATCH
• Cross-Workbook Linking and Handling Broken Links

Module 1.5: Data Visualization & Dashboards

Topics Covered

• Chart Design Best Practices
• Dynamic Charts with Form Controls
• Combo Charts, Sparklines, Conditional Formatting
• KPI Indicators (Traffic Lights, Data Bars, Icons)
• What-If Analysis for Business Scenario Testing

Module 1.6: Pivot Tables & Pivot Charts

Purpose: Master Excel’s most powerful tool for data summarization and analysis.

Topics Covered

• Creating and Customizing PivotTables
• Calculated Fields and Items
• Grouping by Date, Month, and Quarter
• Using Slicers and Timelines
• Creating Hundreds of Pivot Tables in Seconds
• Making Complex Pivot Tables Neat and Insightful

Module 1.7: Power Tools in Excel (for Analysts)

Purpose: Use Excel’s advanced BI tools for large-scale analytics and automation.

Topics Covered

• Power Query (Merge, Append, Clean Data)
• Power Pivot (Data Models, KPIs, Measures)
• Connecting Excel with External Data Sources (CSV, SQL)
• Introduction to RDBMS, Primary & Foreign Keys
• Building Table Relationships and Data Modelling

Module 1.8: Professional Efficiency & Productivity

Purpose: Work faster, smarter, and more accurately.

Topics Covered

• Advanced Shortcuts for Data Analysts
• Named Ranges, Dynamic Arrays
• Workbook Linking, Auditing, and Error Tracing
• Formula Debugging and Error Checking
• Version Control, Backup, and Template Creation

Module 1.9: Excel Integration & Productivity

Purpose: Integrate Excel with modern tools and leverage automation and AI.

Topics Covered

• Excel with Google Sheets (Cloud Collaboration)
• ChatGPT + Excel (AI-Assisted Formula Writing)
• Excel with SQL (Data Import & Export)
• Learning Techniques for Excel Shortcuts
• Universal vs Non-Universal Shortcuts

Module 1.10: Excel Integration & Productivity

Purpose: Build professional, interactive dashboards.

Topics Covered

• Creating Different Types of Charts
• Designing Advanced Dashboards Using Summarized Data
• Dynamic Dashboards with Form Controls

Assignments & Live Projects

Hands-On Practice

Introduction

• Flowcharts
• Step-by-Step Instructions
• Process Flow Diagrams
• KPIs
• Algorithms

VBA/Macros Basics to Advanced

Subs, Functions, Variables, Arrays, Loops, Logic,Arrays etc

• Excel Macro Language Review (VBA) Including Variables, Data Types, Constants, Arrays, Operators, Expressions, Loops, Logic Decisions and Calling
• Overview Of Commanding Excel Using VBA Including
• A Discussion Of Objects, Properties and Method
• The Power of Macros – What, When, How to use Macros.

Introduction to Object Oriented Concepts

• Objects, Its Functions, Methods and Properties Introduction to Events
• Details of Events, How & When to use of Events, Preparing to ‘Macro’ Visual Basic Editor (VBE) –Developer Tab, Security
• Introduction to the VBE, Properties window, Project Explorer, Password Protection of Code How to use the VBE – Features, Options, Intelligence Technology
• Debugging Mode, Bookmarks, Breakpoints, Watch Window, Immediate Window and Locals Window Inbuilt VBE Help Feature – Tips and Tricks
• Form Controls vs. ActiveX Controls
• Getting into the Code
• Message Box and Input Box Working with Data in Excel through VBA

Data Types

• Constants and Variables
• Different type of data type; How and When to use Variables to Store Information.

Loops

• For-Next, For-Each, Do-While, Do until, Do Loop Decision-Making and Code Branching
• If-Then-Else,
• Select-Case
• And
• Nested Conditions
• What is user’s Defined Functions?
• How to create & use them

Arrays

• Use of Arrays in VBA programming with one dimensional, two dimensional or multi-dimensional analysis

Excel VBA Power Programming For VBA Macros

• Working with Dynamic Ranges. Protecting Worksheets, Cells and Ranges. Working with Multiple Files. Opening & Saving Files
• How to Analyze Data On Multi Worksheets And Build Summary Sheets
• How to Access The Windows File And Folder System To Open And Close Workbooks
• How to Protect Your Code Against Errors
• How to Use Excel And VBA To Create Basic Dash Boards
• How to Create Your Own Custom Business Worksheet Functions In VBA
• How to Create Basic Report Generation Tools Using Excel VBA, Microsoft Word And PowerPoint
• How to Use The Excel Visual Basic Macro Recorder To Record Excel Tasks In VBA And Then Interpret The Code

Overview of User forms To Create Business Wizards

• Working with User Forms & User Forms Events like List box, Combo box, Option Buttons, Check box, Text box, Labels, Command button, Toggle button.
• How to create dynamic dashboard on user form with different controls
• How to link various user form with each other to create a complete interface between user and system

Connection between Excel VBA & other platforms

• How to Establish Connection Between VBA and Internet Explorer to Open any Internet Website through VBA
• How to Establish Connection Between Excel VBA and power presentation to create power point through VBA
• How to Establish Connection Between Excel VBA and Access database to update the data in access through VBA
• How to Establish Connection Between Excel VBA and MS Word through VBA

Testing and Debugging Your Code

• Types of Errors
• Using Breakpoints
• Debugging Techniques

Dialogue Boxes

• UsingMsgBox Function
• UsingInput Box Function
• Working with FileDialog
• Working GetOpenFile name Method
• Working GetSaveAsFilename Method

Effective Error Handling

ChatGPT AI for Excel and VBA

Live Project on VBA Macros

Module 3.1: Introduction To Databases & SQL

• What Is A Database?
• Types Of Databases (Relational, Non-Relational)
• What Is SQL? SQL Command Categories (DDL, DML, DCL, TCL)

Module 3.2: Database & Table Creation

• Creating Databases // Creating Tables
• Data Types In SQL
• Keys & Constraints: Primary Key, Foreign Key, NOT NULL, UNIQUE, CHECK, DEFAULT

Module 3.3: Insert, Update & Delete

• INSERT Statements
• UPDATE Statements
• DELETE Statements

Module 3.4: Basic Data Retrieval

• SELECT Statements
• Selecting Specific Columns
• Aliases (AS)
• DISTINCT Keyword

Module 3.5: Filtering Data

• WHERE Clause
• Comparison Operators
• Logical Operators (AND, OR, NOT)
• LIKE Pattern Matching
• IN, BETWEEN, IS NULL

Module 3.6:Sorting & Limiting

• ORDER BY
• Sorting By Multiple Columns
• LIMIT / TOP / FETCH

Module 3.7: Aggregate Functions & Grouping

• GROUP BY
• HAVING Clause
• COUNT(), SUM(), AVG(), MIN(), MAX()
• GROUP BY With Aggregates
• Filtering Aggregates (HAVING)

Module 3.8: SQL Functions – String

• CONCAT()
• LENGTH() / LEN()
• LOWER() / UPPER()
• SUBSTRING() / SUBSTR()
• TRIM(), LTRIM(), RTRIM()
• REPLACE()
• INSTR() / CHARINDEX()

Module 3.9: SQL Functions – Date/Time

• NOW(), CURRENT_TIMESTAMP
• CURRENT_DATE, CURRENT_TIME
• DATEADD(), DATEDIFF()
• EXTRACT()
• DATE_FORMAT(), FORMAT()
• DAY(), MONTH(), YEAR()

Module 3.10: SQL Functions – Math

• ABS(), ROUND()
• FLOOR(), CEILING()
• POWER(), SQRT()
• RAND()

Module 3.11: SQL Functions – Conversion

• CAST(), CONVERT()
• TO_CHAR(), TO_DATE()

Module 3.12: SQL Functions – Conditional

• CASE Expression
• NULLIF(), COALESCE()
• IIF()

Module 3.13: SQL Functions – Window

• PARTITION BY
• ORDER BY (Window)
• ROW_NUMBER()
• RANK(), DENSE_RANK()
• FIRST_VALUE(), LAST_VALUE()

Module 3.14: Working With Joins

• Inner Join // Left Join // Right Join
• Full Join // Self Join // Cross Join
• Left Anti Join // Right Anti Join

Module 3.15: Sub queries

• Subqueries In WHERE
• Subqueries In SELECT
• Subqueries In FROM
• Correlated Subqueries

Module 3.16: Views

• Creating Views
• Updating Views
• Dropping Views

Module 3.17: Introduction To RDBMS

• What Is An RDBMS?
• DBMS Vs RDBMS
• Features Of RDBMS
• Popular Systems: MySQL, PostgreSQL, SQL Server, Oracle

Database Concepts

• Tables, Rows, Columns
• Schemas & Catalogs
• Primary & Foreign Keys
• Candidate & Composite Keys

Data Models

• Relational Model
• Entities & Attributes
• 1-1, 1-Many, Many-Many Relationships
• ER Diagrams

Module 3.18: SQL Server Agent (Job Scheduling)

• Overview Of SQL Server Agent
• Creating A Job
• Adding Job Steps
• Creating A Schedule

Module 4.1: Introduction to MS Access

• What is MS Access?
• Features of MS Access
• Understanding the Access interface
• Databases, tables, objects overview
• Differences between Access and other RDBMS

Module 4.2: Database Fundamentals

• Creating a new database
• Database file types (.accdb, .mdb)
• Opening, saving, and closing databases
• Navigation Pane & object organization

Module 4.3: Tables in MS Access

• Creating tables (Design View / Datasheet View)
• Field names and data types
• Field properties (size, format, input mask)
• Primary key selection
• Creating lookup fields
• Table relationships overview

Module 4.4: Data Types & Field Properties

• Short Text, Long Text // Number, Large Number
• Date/Time, Currency // Yes/No
• Hyperlink // Lookup Wizard // Attachment
• Field validation rules

Module 4.5: Working With Data

• Adding records
• Editing records
• Deleting record
• Sorting & filtering data
• Searching records

Module 4.6: Keys & Relationships

• Primary keys // Foreign keys
• One-to-One relationships // One-to-Many relationships
• Referential integrity // Relationship window

Module 4.7A: Queries – Types of Queries

• Select Queries
• Action Queries (Append, Update, Delete, Make-Table)
• Crosstab Queries
• Find Duplicates Queries // Find Unmatched Queries

Module 4.7B: Queries – Concepts

• Query Design View // Adding criteria
• Sorting, filtering //Using expressions
• Calculated fields

Module 4.8: Forms

• Introduction to forms
• Creating forms (Form Wizard, Design View)
• Form layouts
• Controls (Text box, Combo box, Buttons)
• Binding forms to data // Navigation forms
• Subforms

Module 4.9: Reports

• Creating reports // Report Wizard // Grouping & sorting
• Headers & footers // Summary reports
• Printing & exporting reports

Module 4.10: Importing & Exporting Data

• Import from Excel // Export to Excel
• Importing text files // Linking tables (external data)
• Connecting to SQL databases

8 Major Points to Learn MS Access in Analytics

1. Easy Data Management for NonTechnical Users
2. Relational Database Capabilities
3. Connecting to SQL databases
4. Powerful Querying (SQL Support)
5. Perfect for Small & Medium Projects
6. Integration With Excel & Business Tools
7. Forms & Reports for Business Insights
8. Automation Through Macros & VBA
9. Best for Teaching Structured Data Concepts

Module 5.1 – Advanced Financial Statements Analysis

Introduction to Financial Statements and Navigating the Annual Report

• P/L, B/S, C/F statements reflecting operating, financing and investing activities of a business.
• Understanding the purpose
• Understanding of key headers and sub-headers
• Format of the financial statements
• General Accounting Principles and best practices

Common size Analysis

• Horizontal
• Vertical

Financial Ratios

• Activity ratios
• Liquidity ratios
• Solvency ratios
• Profitability ratios
• Rate of return
• DuPont Analysis
• Other important ratios
• Pro Forma Analysis and basic Projection technique

Pro Forma Analysis and Basic Projection Technique

Common Pro Forma Statements:

• Pro Forma Income Statement
• Pro Forma Balance Sheet
• Pro Forma Cash Flow Statement
• Basic Projection Techniques

Trend Analysis

• Percentage of Sales Method
• Regression Analysis
• Moving Averages & Smoothing
• Scenario Analysis

Module 5.2: Advanced Corporate Finance

Time value of Money

• NPV and IRR
• Future Value Excel formulas like NPV, XNPV, IRR, XIRR, PV, FV, etc

Understanding of Debt and Tax shield

• Tokenization Understanding schedules for debt repayment and interest repayment
• Developing the schedules on excel

Measure of Leverage

• Impact of leverage on cost of capital
• WACC Understanding tax shield

PCAPM model and Beta calculation

• Understanding of Beta
• Levered and Unlevered Beta
• Calculation of levered Beta
• Calculation of cost of equity

Free Cash Flow

• Free Cash flow to Firm
• Free Cash flow to Equity
• Future Value Excel formulas like NPV, XNPV, IRR, XIRR, PV, FV, etc

Valuation

• DCF Valuation
• Relative Valuation
• Transaction Valuation

Application of these techniques with real case studies

Module 6.1 – Advanced Financial Modeling

Introduction and Context

• Understanding an integrated financial Model
• Building Integrated Model

Financial Statements & Projections for an Equity

• Creating a template for integrated model
• Making Assumptions for forecast
• Developing various schedules
• Depreciation – Capital Expenditure Phasing, SLM and % methods
• Working capital
• Debt and Interest

Module 6.2 – Integrating all the Financial Statements

Module 6.3 – Summarizing Financial Statements Technique

Module 6.4 – Case Study

Building Integrated Model – Financial Statements

• Building Integrated Model – Valuation
• Using DCF and comparable
• Summarizing financial statements

Financial Projections

• Advanced Modelling concepts and technique
• Common Modeling errors- circular reference errors to avoid

Module 6.5 – Application of these techniques with real case studies

Module 7.1 – MS Power BI

Module 7.1.1: Microsoft Power BI – Introduction

• What is MSPBI& Scope
• Learn the common work flow in Power BI
• Building blocks of Power BIand its relations
• Quick demo how to create a business dashboard in MSPBI
• MSPBI components

Module 7.1.2: MS Power BI – Getting Business Data

• Get data in shape for use with Power BI
• Combining two or more data sets (source data) for reporting
• Tackling messy data in MS Power BI
• Clean &Transform data

Module 7.1.3: MS Power BI -Data Visualization

• Create and customize visualization
• Use combination charts
• Create and format slicers
• Map visualizations
• Visualizations utilization
• Use tables and matrixes
• Long live bubbles
• scatter charts in action
• Advanced funnel and waterfall charts
• Drive fast dashboard insights with gauges and numbers
• Color your visualization world with colors
• shapes and scales
• Adding personal touch
• logo etc. to reports and dashboards
• Display and present your dashboard in a way you want with summarize data
• Control how your report elements overlap with each other
• Learn to drill into hierarchies
• Manage how levels are shared (Z-order in reports)
• How to use R visuals in MSPBI

Module 7.1.4: MS Power BI -Data Exploring & Sharing

• Quick insights in Power BI Service
• Create and configure a dashboard
• Share dashboard with your organization
• Display and edit visuals- tiles
• full screen
• Get more space on your dashboard
• Install and configure a personal gateway
• Excel and MSPBI
• Import and excel table into Power BI
• Import excel files with data models and power view sheets
• Connect One Drive for business to MSPBI
• Excel data in Power BI summary

Module 7.1.5: MS Power BI – DAX (Data Analysis Expression) Application Setting up Data Models with DAX

• DAX for creating tables and columns
• Rows vs. query vs. filter context
• DAX for calculated tables and columns
• Creating a date table
• Calculated column for costs
• Data cleaning with DAX
• Connecting data from different tables
• Methods to create DAX measures
• Advantages of explicit measures V2
• DAX and measures
• Using variables
• Basic statistical measures
• Quick measures

Module 7.1.6: Power BI – Common DAX Measures

• Filtering and counting with DAX
• Understanding different filter functions
• Using different filters with DAX
• Filter ALL the data
• Calculating with a filter
• Analyzing across dimensional tables
• Iterating functions
• DIY iterating functions
• Iterating functions in Power BI
• Practice with iterating functions
• More iterating functions
• Use of RANKX()

Module 7.1.7: Power BI – Redefine DAX

• Logical functions
• Interpreting SWITCH()
• Logical functions in Power BI
• IF() for formatting tables
• Exploring SWITCH()
• Grouping
• Row-level security
• Applying row-level security
• Managed roles in Power BI
• Creating an email list
• Implementing RLS

Module 7.1.8: Power BI – Advanced DAX

• Table manipulation functions
• Summary of SUMMARIZE()
• Table manipulations using DAX
• SUMMARIZE() the facts
• ADDCOLUMNS()? No problem!
• Time intelligence functions
• Time intelligence functions output
• Time intelligence in Power BI
• Use of TOTALYTD()
• Use of SAMEPERIODLASTYEAR()

Module 7.2 – Artificial Intelligence

Module 7.2.1: Introduction to Artificial Intelligence

• What is AI? History and Evolution
• Types of AI: Narrow, General, Super AI
• Key Terms: Machine Learning, Deep Learning, NLP
• Real-world Applications of AI
• Future of AI

Module 7.2.2: Mathematics & Logic Foundation

• Basics of Linear Algebra (Vectors, Matrices)
• Probability and Statistics
• Logic & Set Theory
• Graph Theory Basics

Module 7.2.3: Programming for AI

• Introduction to Python
• Python Libraries: NumPy, Pandas, Matplotlib
• Basic Programming Exercises (AI-focused)

Module 7.2.4: Machine Learning Basics

• What is Machine Learning?
• Types: Supervised, Unsupervised, Reinforcement Learning
• Common Algorithms: Linear Regression, KNN, Decision Trees
• Train-Test Split & Model Evaluation

Module 7.2.5: Data Handling for AI

• Data Collection and Cleaning
• Data Preprocessing Techniques
• Feature Selection & Engineering
• Data Visualization with Matplotlib/Seaborn

Module 7.2.6: Introduction to Neural Networks

• Basics of Neural Networks
• Activation Functions
• Forward & Backpropagation (Conceptual Level)
• Introduction to TensorFlow / Keras

Module 7.2.7: AI Tools and Platforms

• Google Colab & Jupyter Notebooks
• Intro to Scikit-Learn
• Basics of ChatGPT / OpenAI Playground
• Overview of AutoML Tools

Module 7.2.8: AI Tools and Platforms

• AI Bias and Fairness
• Ethics in AI Development
• AI in Society: Jobs, Education, Healthcare
• Emerging Trends (AI + IoT, AI + Cloud, etc.)

Mini Project