Course Modules

Day-1: Overview of the Course

MODULE 1 – Introduction

1.1 Introduction

1.2 Overview

MODULE 2 –Planning

2.1 Target Setup

2.2 Model Formulation

2.3 Working with rows and columns

2.4 Working with sheets

2.5 Worksheets Management

2.6 Named Models/formats

2.7 Summarize all sheet name for linking

2.8 Hyperlink from Dropdown list

MODULE 3 - Macro

3.1 What is a macro?

3.2 Recording a Macro

3.3 Practical case analysis

Day-2: VBA & Functions

MODULE 4 - VBA

4.1 Introduction to VBA

4.2 Object Model

4.3 Module Worksheets Classes

4.4 Variables and Constants

DIM

SUB

END WITH

4.5 Practical case analysis

MODULE 5 - Functions

5.1 Functions

5.2 SUM Function

5.3 Copy and Drag Formula

5.4 More Basic Functions

5.5 Named Ranges

5.6 Names Constants and Formula

5.7 Shortcuts

5.8 More Shortcuts

Day-3: Functions and Formatting Technique

MODULE 6 – Conditional Formatting

6.1 Formatting Font

6.2 Formatting Alignment

6.3 Formatting Borders

6.4 Formatting Numbers

6.5 Conditional Formatting Pre-Configured

6.6 Conditional Formatting with Formula

6.7 Conditional Formatting Dialogue Box Formula Trick

MODULE 7 – Data Management

7.1 Data Sort

7.2 Filtering

7.3 Tables

7.4 Data Validation

7.5 Freeze Panes

Day-4:Data Analysis Technique

MODULE 8 – Formula Setup

8.1 Relative and Absolute Referencing

8.2 VLOOKUP

8.3 Nested VLOOKUP

8.4 IF

8.5 Nested If

8.6 Countif Countifs

8.7 SUMIF SUMIFS

8.8 IFERROR

8.9 Introducing Charts

8.10 Introducing Pivot Tables

MODULE 9 – Pivot Table

9.1 Pivot Tables

9.2 Setting up a Pivot Table

9.3 Pivot Table Field Settings

9.4 Dashboards

Day-5: Data Visualization through Charts

MODULE 10 – Chart Management

10.1 Chart Basics

10.2 Chart Series and Column Charts

10.3 Bar Charts

10.4 Pie Charts

10.5 Composite Charts

10.6 Waterfall Charts

10.7 Charts Management

MODULE 11 – Financial Statement Preparation

11.1 Introducing Financial Statement

11.2 Income Statement

11.3 Cash flow Statement

11.4 Balance sheet