## 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

Introduction

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
Sparklines
Waterfall Chart
Heat Maps
Quiz

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

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!