What you’ll learn

  • Use MS Excel to create and automate the calculation of Financial ratios
  • Become proficient in Excel data tools like Sorting, Filtering, Data validations, and Data importing
  • Implement predictive ML models such as simple and multiple linear regression to predict outcomes to real world Finance problems
  • Knowledge of all the essential Excel formulas required for Financial Analysis


Welcome to the course
Course Resources


Prerequisites: Excel formulas and functions

Basic Formula Operations
This is a milestone!
Mathematical Formulas
Textual Formulas
Logical Formulas
Date-Time Formulas
Lookup Formulas_(V Lookup, Hlookup, Index-Match)


Time Value of Money

Future Value
Excel: Simple Interest and Compound Interest
Net Present Value
Internal Rate of Return
EXCEL: NPV and PV for calculating Present Value
Excel: Internal Rate of Return
Excel: NPV and IRR for particular dates


Analysis of Profit and Loss Statement

Horizontal and Vertical Analysis
Excel: Vertical Analysis
Excel: Horizontal Analysis


Loan Repayment Calculations

Loan Repayment concepts
Excel: Calculating Payments
Excel: Calculating Rate or Term


Methods of calculating Depreciation

Depreciation Methods and Straight Line Method
Double Declining Balance Depreciation
Sum of Years Digits Method
Excel: Calculating Depreciation


Financial Ratio Analysis

Theory: Financial Ratios
Excel: Building a Template for Financial Ratio Analysis


Financial Charts and Dashboards – Introduction

Financial Charts – Introduction


Charts and Dashboards using Excel

Excel Charts – Categories of messages that can be conveyed
Elements of charts
The Easy way of creating charts
Bar and column charts
Formating charts
Line Charts
Area Charts
Pie and Doughnut Charts
Why we should avoid Pie charts
Scatter plot or XY chart
Frequency Distribution and Histograms
Stock Charts
Waterfall Chart
Heat Maps


Case Study – Finance Dashboard

Financial Dashboard – Introduction
Financial Dashboard – Time Series of Profit & Profit Margin
Financial Dashboard – Trend of Revenue and Projection
Financial Dashboard – Expense Analysis using Pie Chart
Financial Dashboard – Target monitoring using Column chart
Financial Dashboard – Collating and Formatting to create a Dashboard


Pivot Tables

Pivot Tables
Pivot Charts


Predictive Analytics – Introduction

Linear Regression – Introduction


Basics of Statistics

Types of Data
Types of Statistics
Describing data Graphically
Measures of Centers
Measures of Dispersion


Machine Learning -Introduction

Introduction to Machine Learning
Building a Machine Learning Model


Getting Data Ready for Regression Model

Gathering Business Knowledge
Data Exploration
The Data and the Data Dictionary
Univariate analysis and EDD
Discriptive Data Analytics in Excel
Outlier Treatment
Identifying and Treating Outliers in Excel
Missing Value Imputation
Identifying and Treating missing values in Excel
Variable Transformation in Excel
Dummy variable creation: Handling qualitative data
Dummy Variable Creation in Excel
Correlation Analysis
Creating Correlation Matrix in Excel


Creating Regression Model

The Problem Statement
Basic Equations and Ordinary Least Squares (OLS) method
Assessing accuracy of predicted coefficients
Assessing Model Accuracy: RSE and R squared
Creating Simple Linear Regression model
Multiple Linear Regression
The F – statistic
Interpreting results of Categorical variables
Creating Multiple Linear Regression model


Finance Case Study – Linear Regression

Case Study Linear Regression – Introduction
Case study Linear Regression – Data
Case study Linear Regression – Preprocessing
Case study Linear Regression – Results
Case study Linear Regression – Prediction


Bonus Section

The final milestone!
Congratulations & About your certificate

Leave a Reply

Your email address will not be published. Required fields are marked *