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