MICROSOFT EXCEL COURSE MODULES:

 

MODULE 1: BASIC FORMULAS & FUNCTIONS

MODULE 2: IF CONDITIONS

MODULE 3: LOOKUP FUNTIONS MATCH & INDEX

MODULE 4: DATABASE FUNCTIONS, FINANCIAL FUNCTIONS, D FUNCTIONS

MODULE 5: ARRAY FUNCTIONS, ACCOUNTING FUNCTIONS, COMPLEX EXCEL FUNCTIONS

MODULE 6: DATA ANALYZING TOOLS

MODULE 7: ADVANCED COMMANDS

MODULE 8: EXCEL POWER UTILITIES

MODULE 9: FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

MODULE 10: FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

MODULE 11: FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

MODULE 12: FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

MODULE 13: BUSINESS INTELLIGENCE

 

 

MODULE 1: BASIC FORMULAS & FUNCTIONS

Sum, Average, Averagea, Maximum, Minimum, Large, Small, Count, Counta, Countblank, Today, Now, Hour, Minute, Second, Rept, Dollar, Exact, Upper, Lower, Proper, Replace, Roman, Address, Cell , Sumproduct, Trim, Mean, , Median, Count, Large, Concatenate, Replace, Now, Days360, Datedif, Choose, Indirect, Rank, Array & Multi-sheet Formulas

 

MODULE 2: IF CONDITIONS

Simple IF, Nested IF, IF With AND, IF With OR, IF with NOT, Sum IF Simple, Sum IFs Advance, Count IF, Sum IF, Average IF Advanced, Count IF Advanced, Sum IFs, Average IFs, Count lfs, Dated IFs, Dated IFs Advance

 

MODULE 3: LOOKUP FUNTIONS MATCH & INDEX (Xlookup, Vlookup, Multiple Vlookup, Hlookup)

• Vlookup With Commission
• Vlookup With Account Number
• Vlookup With Customer Comparison
• Hlookup With Commission
• Hlookup With Monthly Sales
• Multiple Vlookup
• Xlookup with Rows, Columns & Sheets
• Multiple Xlookup
• Disadvantage of Xlookup
• Match & Index (Simple)
• Match & Index (Advance)

MODULE 4 & 5

DATABASE FUNCTIONS

DSUM, DAVERAGE, DMAX, DAMIN, DCOUNT

FINANCIAL FUNCTIONS

PMT, PV, NPER, RATE, FV, PPMT, IPMT, NPV, DDB, SLN, SYD, DB

Using Multi-sheet Formulas

D FUNCTIONS, ARRAY FORMULAS & FUNCTIONS, ACCOUNTING FUNCTIONS, TEXT FUNCTIONS, COMPLEX EXCEL FUNCTIONS

 

MODULE 6: DATA ANALYZING TOOLS

DATA ANALYZING TOOLS

Data Analyzing Business Intelligence Business Modeling Stats Business Reporting with Pivot Tables, Working with Slicers, Advanced Charting & Visualizations, Using Quick Charts Dynamic Charts Spark line Charts, Creating interactive Dashboard Reports, Using Power View & Power Pivot Features, Filters, Auto Filter, Advanced Filter, Subtotal, Sorting, Consolidation, Conditional Formatting, Conditional Formatting Advanced

 

MODULE 7: ADVANCED COMMANDS

ADVANCED COMMANDS

Working with Named Ranges, Consolidation & List Management, Automating with Macros, Using Form Controls, , Pivot Table, Consolidation, Data Validation, Data Validation Simple & Data Validation Advanced, Using Linking, Paste Special, Text to Column, Hyperlink, Auditing, Auto fills / Flash fill / Custom lists, Transpose, Ms. Excel Linking with Ms. Word, Managing External Data Connections.

 

MODULE 8: EXCEL POWER UTILITIES

EXCEL POWER UTILITIES

Goto Special, Hide formulas, Sheet Protection, Workbook Protection Freeze panes

 

MODULE 9: FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

• Financial Analysis Fundamentals
• Building a Financial Model
• Business Valuation Model
• Budgeting and forecasting
• Forecast model
• Variance analysis

 

MODULE 10: FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

• Budgeting
• Budgeting Tools? Goal seek, solver, consolidation, pivot tables
• Scenarios and Sensitivity analysis
• Excel Macros
• VBA Macros
• Advanced Financial Modeling and valuation

 

MODULE 11: FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

• Payroll Reporting
• Simple way of Debit & Credit
• Data Table (More than 3 Approaches)
• Loan Amortization Schedule
• Sumlfs

 

MODULE 12: FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

FINANCIAL MODELING & VALUATION ANALYSIS (FMVA)

• Creation of Sales Invoice with Sales Tax
• Textile Company Sales & Commission
• Cricket Match Forecasting Project
• Create Custom Excel Functions
• Saving Certificate
• Attendance Register
• Electricity Bills

 

MODULE 13: BUSINESS INTELLIGENCE

BUSINESS INTELLIGENCE

• General Journal, Trial Balance & Income Statement
• Creating Interactive Dashboards Using Form Controls
• Dynamic Graphs Connected with Financial Models
• Power Pivot Quires with Slicer
• Loan Calculator with VBA Macros

Excel Certification Guidelines {CFI & Microsoft)

 

 

 

 

 

POWER BI COURSE MODULES:

 

Module 1 – Discover data analysis

Module 2 – Get started building with Power BI

Module 3 – Get data in Power BI

Module 4 – Create dashboards and Design Power BI reports

Module 5 – POWER QUERY – Clean, transform, and load data in Power BI

Module 6 – Add calculated columns and measures to Power BI Desktop models.

Module 7 – Use DAX functions in Power BI Desktop models

Module 8 – Database connectivity with Power BI

Module 9 – Configure Power BI report filters

Module 10 – Enhance Power BI report designs for the user experience

Module 11 – Design & Manage semantic models in Power BI

Module 12 – Perform analytics in Power BI

Module 13 – Create Title Page Using Buttons & Graphics

Module 14 – Power BI Services Integration

Module 15 – Database Concepts

Module 16 – Using Mysql Client

Module 17 – Data Constraint, Keys And Datatypes

Module 18 – Creating Databases And Tables

Module 19 – Clauses And Aggregate Functions

Module 20 – Working With The Sql Statement

Module 21 – Restricting Rows With The Where Filter

 

 

 

MODULE 1 & 2

Discover Data Analysis

Get Started Building with Power BI

• Introduction to POWER BI
• Power BI Key Features
• Power BI Web Interface
• Power BI Desktop Interface
• Power BI Mobile Application
• Download Power BI Desktop
• Installation of Power BI Desktop
• Power BI Desktop Interface

 

MODULE 3 & 4

Get Data In Power BI

Create Dashboards And Design Power BI Reports

FIRST POWER BI Dashboard:

• Understanding DATA
• Raw Data v/s Model Data
• Your First Power BI Dashboard
• Getting Data
• Load data from Excel file
• Data types
• Dashboard Page & Background Settings
• Insert Text on your Dashboard

Get Data in Power BI

Create Dashboards And Design Power BI Reports

• Card Visual
• Types of Charts/ Visuals & Play with Axis
• Column and Bar Chart
• Reference/Constant Lines
• Drill Throw UP/ Down
• Color Formatting
• Themes

 

MODULE 5

POWER QUERY – Clean, Transform, And Load Data In Power BI

Super Store Sales Analysis Dashboard

• POWER QUERY (Power Point Presentation)
• Data transformation
• Transforming Data with Power Query
• Editing queries
• Fixing dates
• Columns – Move, Remove, Rename & Duplicate
• Splitting columns
• Merging columns
• Filtering rows
• Removing Duplicates and Errors
• Sorting
• Hierarchies
• Replace and Fill
• Transforming Text and Numbers
• Transforming dates
• Insert Images & Shapes

 

MODULE 6 & 7

Add Calculated Columns And Measures To Power BI Desktop Models.

Use DAX Functions In Power BI Desktop Models

• DAX (Power Point Presentation)
• Creating Custom Calculated Columns with DAX
• Creating Custom Columns with Power Query
• Creating Custom Measures
• Creating Custom Calculated Table
• Common DAX Functions
• Aggregation Functions
• Using Variables
• Date Functions
• Filter Functions
• The ALL Function
• DISTINCT Function
• Difference Between ALL/ Distinct Function
• Calculate Function

 

MODULE 8

Database Connectivity with Power BI.

Database Connectivity:

• Connect ACCESS DATABASE with PBI
• Import Custom Visuals
• Scroller
• lnfographics
• Ring chart
• Funnel / pyramid chart
• Word cloud
• Managing and Arranging Visuals
• Top N Filtering

 

MODULE 9 & 10

Configure Power BI Report Filters

Enhance Power BI Report Designs For The User Experience

HR Analysis dashboard

• Types of Built-in Visuals (Power Point Presentation)
• HR Analysis Dashboard
• Pie Visual
• Donut Visual
• Tree Map
• Line Visual
• Map Visualization
• Funnel Visual
• Visual, Page and Report Level Filters
• Insert pictures and Shapes.
• Custom Tooltip
• Import custom visuals.

 

MODULE 11

Design & Manage Semantic Models In Power BI

RELATIONSHIPS (Power Point Slide):

• Create Model Data File using Excel
• Understanding about Relationships
• Relationship types
• Create Relationship
• Relationship Options

Financial Analysis Dashboard:

• Create Excel Model Data File
• Create Relationships
• Understand Relationship Working
• Remove Errors using Power Query
• Create Measures
• Conditional Formatting

 

MODULE 12

Perform Analytics In Power BI

Crypto Currency Dashboard:

• Download data set – Crypto currency data
• Merging Excel files with Power Query
• Trend over time
• Forecasting
• Edit interactions
• Mobile Layout
• Layer & Tab Order Selection

 

MODULE 13

Create Title Page Using Buttons & Graphics

• Title page
• Create Custom Buttons
• Create Bookmark Buttons
• Create Title Page
• View TAB

 

MODULE 14

Power BI Services Integration

• Layer & Tab Order Selection
• Sharing Dashboards and Reports
• Refreshing Power BI Service Data
• Power BI Certification Guidelines

 

MODULE 15

DATABASE CONCEPTS

• Databases
• Databases Management System (DBMS}
• Relational Database
• Characteristics of a Database
• Users of a Database

 

MODULE 16

USING MYSQL CLIENT

• Introduction to SQL
• Using MySQL Client
• Installation of MySQL
• Installing MySQL on Windows
• MySQL Client Programs
• Types

 

MODULE 17

DATA CONSTRAINT, KEYS AND DATATYPES

• Constraints
• Keys
• Primary Key
• Foreign Key
• Data types

 

MODULE 18

CREATING DATABASES AND TABLES

• Types of SQL Commands
• DDL (Data Definition Language}: create, alter, rename, truncate & drop.
• DML (Data Manipulation Language}: select, insert, update & delete.
• DCL (Data Control Language): grant & revoke permission to users
• DQL (Data Query Language): Select.

 

MODULE 19

CLAUSES AND AGGREGATE FUNCTIONS

• Group by Clause
• Aggregate Functions COUNT(), MAX(), MIN(), SUM(), SUMX(), AVG(), etc.
• Where Clause
• Having and Order by Clause
• Distinct Clause

 

MODULE 20

WORKING WITH THE SQL STATEMENT

• SQL Statements
• Select Statement in Detail
• Select DISTINCT
• Retrieving data with SELECT
• Specifying column expressions
• Sorting the result with ORDER BY
• Handling NULL values in expressions
• Writing Single Table queries: (Update, Delete, Alter Table, Add Column, Drop Column, Rename, Change Column, Modify Column)

 

MODULE 21

RESTRICTING ROWS WITH THE WHERE FILTER

• Testing for equality or inequality
• Applying wildcard characters
• Avoiding NULL value pitfalls