Excel for Financial Analysis and Financial Modeling

Perform financial analysis & financial modeling using Microsoft excel. For people who want to become a Financial Analyst

Created by Abhishek And Pukhraj, Last Updated 11-Oct-2020, Language:English

What Will I Get ?

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

Requirements

You will need a PC with any version of Excel installed in it

Description

How this course will help you?

A Verifiable Certificate of Completion is presented to all students who undertake this Financial Analytics: Strategies & Models in Excel course.

If you are an Finance manager or an executive, or a student who wants to learn and apply analytics techniques to real world problems of the Finance business function, this course will give you a solid base for that by teaching you the most popular Financial analytics models and how to implement it them in MS Excel.

Why should you choose this course?

We believe in teaching by example. This course is no exception. Every Section’s primary focus is to teach you the concepts through how-to examples. Each section has the following components:

Theoretical concepts and use cases of different Financial models
Step-by-step instructions on implementing Financial models in excel
Downloadable Excel files containing data and solutions used in each lecture
Class notes and assignments to revise and practice the concepts

The practical classes where we create the model for each of these strategies is something which differentiates this course from any other course available online.

Course Content

Introduction

2 Lectures 00:03:12
- Welcome to the course
  
  Preview
  
  00:03:12
- Course Resources
Prerequisites: Excel formulas and functions

6 Lectures 01:17:01
- Basic Formula Operations
  
  Preview
  
  00:13:10
- Mathematical Formulas
  
  00:19:10
- Textual Formulas
  
  00:17:25
- Date-Time Formulas
  
  Preview
  
  00:07:07
- Logical Formulas
  
  00:11:23
- Lookup Formulas_(V Lookup, Hlookup, Index-Match)
  
  00:08:46
Time Value of Money

7 Lectures 00:51:35
- Future Value
  
  Preview
  
  00:07:46
- Excel: Simple Interest and Compound Interest
  
  00:08:28
- Net Present Value
  
  00:07:40
- Internal Rate of Return
  
  00:06:15
- EXCEL: NPV and PV for calculating Present Value
  
  00:09:48
- Excel: Internal Rate of Return
  
  00:08:01
- Excel: NPV and IRR for particular dates
  
  00:03:37
Analysis of Profit and Loss Statement

3 Lectures 00:16:03
- Horizontal and Vertical Analysis
  
  00:04:06
- Excel: Vertical Analysis
  
  00:07:00
- Excel: Horizontal Analysis
  
  00:04:57
Loan Repayment Calculations

3 Lectures 00:17:45
- Loan Repayment concepts
  
  Preview
  
  00:05:38
- Excel: Calculating Payments
  
  00:08:50
- Excel: Calculating Rate or Term
  
  00:03:17
Methods of calculating Depreciation

4 Lectures 00:22:18
- Depreciation Methods and Straight Line Method
  
  00:04:37
- Double Declining Balance Depreciation
  
  00:04:36
- Sum of Years Digits Method
  
  00:03:53
- Excel: Calculating Depreciation
  
  00:09:12
Financial Ratio Analysis

2 Lectures 00:28:50
- Theory: Financial Ratios
  
  00:14:47
- Excel: Building a Template for Financial Ratio Analysis
  
  00:14:03
Financial Charts and Dashboards - Introduction

1 Lectures 00:03:31
- Financial Charts - Introduction
  
  00:03:31
Charts and Dashboards using Excel

14 Lectures 02:05:29
- Excel Charts - Categories of messages that can be conveyed
  
  Preview
  
  00:04:34
- Elements of charts
  
  00:05:32
- The Easy way of creating charts
  
  00:03:17
- Bar and column charts
  
  00:12:38
- Formating charts
  
  00:15:13
- Line Charts
  
  00:09:04
- Area Charts
  
  00:06:04
- Pie and Doughnut Charts
  
  00:13:04
- Why we should avoid Pie charts
  
  00:04:05
- Scatter plot or XY chart
  
  00:16:17
- Frequency Distribution and Histograms
  
  00:13:03
- Sparklines
  
  00:05:43
- Waterfall Chart
  
  00:12:00
- Heat Maps
  
  00:04:55
Case Study - Finance Dashboard

6 Lectures 00:31:46
- Financial Dashboard - Introduction
  
  00:05:58
- Financial Dashboard - Time Series of Profit & Profit Margin
  
  00:06:06
- Financial Dashboard - Trend of Revenue and Projection
  
  00:03:34
- Financial Dashboard - Expense Analysis using Pie Chart
  
  00:03:40
- Financial Dashboard - Target monitoring using Column chart
  
  00:05:27
- Financial Dashboard - Collating and Formatting to create a Dashboard
  
  00:07:01
Pivot Tables

2 Lectures 00:16:13
- Pivot Tables
  
  00:08:25
- Pivot Charts
  
  00:07:48
Predictive Analytics - Introduction

1 Lectures 00:01:31
- Linear Regression - Introduction
  
  00:01:31
Basics of Statistics

5 Lectures 00:30:08
- Types of Data
  
  Preview
  
  00:04:04
- Types of Statistics
  
  00:02:45
- Describing data Graphically
  
  00:11:37
- Measures of Centers
  
  00:07:05
- Measures of Dispersion
  
  00:04:37
Machine Learning -Introduction

2 Lectures 00:24:45
- Introduction to Machine Learning
  
  00:16:03
- Building a Machine Learning Model
  
  00:08:42
Getting Data Ready for Regression Model

14 Lectures 01:18:09
- Gathering Business Knowledge
  
  Preview
  
  00:03:00
- Data Exploration
  
  Preview
  
  00:03:19
- The Data and the Data Dictionary
  
  00:07:31
- Univariate analysis and EDD
  
  00:03:33
- Discriptive Data Analytics in Excel
  
  00:10:33
- Outlier Treatment
  
  00:04:15
- Identifying and Treating Outliers in Excel
  
  00:04:14
- Missing Value Imputation
  
  00:03:36
- Identifying and Treating missing values in Excel
  
  00:04:00
- Variable Transformation in Excel
  
  00:03:40
- Dummy variable creation: Handling qualitative data
  
  00:04:50
- Dummy Variable Creation in Excel
  
  00:07:44
- Correlation Analysis
  
  00:09:47
- Creating Correlation Matrix in Excel
  
  00:08:07
Creating Regression Model

9 Lectures 01:00:16
- The Problem Statement
  
  00:01:25
- Basic Equations and Ordinary Least Squares (OLS) method
  
  00:08:13
- Assessing accuracy of predicted coefficients
  
  00:14:40
- Assessing Model Accuracy: RSE and R squared
  
  00:07:19
- Creating Simple Linear Regression model
  
  00:02:41
- Multiple Linear Regression
  
  00:04:57
- The F - statistic
  
  00:08:22
- Interpreting results of Categorical variables
  
  00:05:04
- Creating Multiple Linear Regression model
  
  00:07:35
Finance Case Study - Linear Regression

5 Lectures 00:35:50
- Case Study Linear Regression - Introduction
  
  00:01:18
- Case study Linear Regression - Data
  
  00:05:26
- Case study Linear Regression - Preprocessing
  
  00:09:05
- Case study Linear Regression - Results
  
  00:11:12
- Case study Linear Regression - Prediction
  
  00:08:49

Abhishek And Pukhraj

Start-Tech Academy is a technology-based Analytics Education Company and aims at Bringing Together the analytics companies and interested Learners.
Our top quality training content along with internships and project opportunities helps students in launching their Analytics journey.

Founded by Abhishek Bansal and Pukhraj Parikh.

Working as a Project manager in an Analytics consulting firm, Pukhraj has multiple years of experience working on analytics tools and software. He is competent in MS office suites, Cloud computing, SQL, Tableau, SAS, Google analytics and Python.

Abhishek worked as an Acquisition Process owner in a leading telecom company before moving on to learning and teaching technologies like Machine Learning and Artificial Intelligence.