Financial Modeling In Excel (Concepts And Application)

Mastering Financial Modeling in Excel: Build, Analyze, and Optimize - Learn concepts and Application of concepts

What you'll learn

Concepts related to Financial Modeling

Learning Excel basics with hands-on sessions

Understanding excel formulas and functions

Building Financial Model in excel from Scratch

Sheet, Cells ,Tabs and Ribbon, Quick Access Toolbar

Formulas - Sum, Average, Median, Count, Count if, Sum if, If error

Relative Reference and Absolute Reference Partial Absolute reference

Formating- Removing Gridlines,Font related ,Format Printer, Alignment, Merge Cells, Wrap Text, Fill Color, Font Color ,Borders

Date Related Functions, Conditional Formatting, Pivot Table, Vlookup and Hlookup

Data Validation and Cell protection, Protecting Sheet, Protect Workbook

Name Manager, Goal Seek Function, Data Tables, What if Analysis

Understanding basics and Types of Financial Statements (Concepts)

Historical Data plotting in Excel

Ratio Analysis Sheet in excel

Dupont Analysis sheet

Altman's Z Score Analysis

Common Size Statement

Forecasting Income and Expenses in excel

Forecasting Balance sheet

Calculation of Present Value, Future Value, Annuity in excel

Loan repayment schedule in excel

Project evaluation in excel using Net Present Value (NPV)

Project evaluation in excel using Internal rate of return (IRR)

Requirements

Basic understanding of Accounting policies and process

Basic Understanding of Financial Concepts – Familiarity with key financial statements (income statement, balance sheet, and cash flow statement) is helpful but not mandatory, as foundational concepts will be briefly reviewed.

Analytical Mindset – Financial modeling requires a logical approach and attention to detail, so a mindset for problem-solving and analyzing numbers will be valuable.

Description

Unlock the power of Excel and transform your financial data into actionable insights with our comprehensive Financial Modeling course. Designed for aspiring financial analysts, business students, and finance professionals, this course will equip you with the tools and techniques to build robust financial models from scratch.Through hands-on, practical exercises, you’ll learn to construct financial statements, forecast revenue, calculate costs, analyze feasibility, and optimize decision-making for real-world business scenarios. Starting with the fundamentals, you’ll dive deep into key concepts such as ratio analysis ,depreciation, tax rates, discounting cash flows, and scenario analysis, mastering the best practices for designing dynamic and accurate models.The course structure is designed in a way that will enable learner to understand concept before application of the same in excel. Following are details of course structure :Section 1: OverviewIt includes introduction of Course, understand meaning of Financial modeling, Introduction to excel and prerequisites in excelSection 2 : Excel Functions and FormulasHere we will begin with learning basic excel formulas such as sum, count, if, sum if, count if etc and then proceed to learning formatting, cell referencing, Vlookup and Hlookup, Pivot table, conditional formatting, data validation and Protection of workbook, cell protection. Here basic data sheet will be provided and it will be practical hands-on session wherein learners will be working on same datasheet along with video lectures.Section 3 : Advanced Excel Functions and FormulasMoving further we will be learning Name manager, Goal seek, Scenario Manager and data tables in excel.Section 4 : Financial Statements and Historical PlottingHere section begins with understanding Financial statements and then using basic data sheet we will be plotting historical financial statements in excel. Please note that basic data sheet will be provided and learners will be working from scratch to build financial model using this data sheet. This is hands-on session where learners will design model along with lecture.Section 5 : Ratio Analysis (Concept)Here We discuss about different types of Financial Ratios such as Liquidity Ratios (Short term solvency ratios) , Leverage Ratios (Long Term solvency ratios) ,Activity Ratios (Turnover ratios) and Profitability Ratios. Liquidity Ratios includes current ratio, quick ratio, cash ratio and Interval measure ratio. Each ratio is explained in video lecture along with illustrations.Leverage Ratios include equity ratio, debt ratio, debt to equity ratio, debt to total assets ratio, proprietary ratio, capital gearing ratio, debt service coverage ratio, dividend coverage ratio, interest coverage ratio, fixed charges coverage ratio etc. Each ratio is explained in video lecture along with illustrations.Turnover ratios include fixed assets turnover ratio, net assets turnover ratio, current assets turnover ratio, working capital turnover ratio, inventory turnover ratio, receivables turnover ratio, payables turnover ratio etc. Each ratio is explained in video lecture along with illustrations.Profitability ratios include gross profit ratio, net profit ratio, operating profit ratio, expenses ratio, return on assets, return on capital employed, return on equity, earning per share, dividend per share, dividend payout ratio, price earning ratio, dividend and earning yield ratio, market value by book value ratio, Q ratio. Each ratio is explained in video lecture along with illustrations.DuPont Analysis on ROI (Return on Investment) , ROA (Return on Assets)  and ROE (Return on Equity)This module also includes a comprehensive solved illustration that explains how to calculate all types of ratios and how to use these ratios for analysis and decision making.Section 6 : Ratio Analysis in Excel (Application of Concepts)Here we will be preparing a detailed Ratio Analysis of the entity in excel. We will be preparing Ratio analysis report, Dupont analysis report , Altman's Z Score report along with Charts in excel. Section 7 : Time Value of Money (Concepts)Here we discuss about the concept of Time Value of Money and how to use concept of time value of money. The relationship between inflation, purchasing power and Time value of money is separately discussed. Other topics included are Difference between Simple interest and compound interest, Present value and Future value of money, Formula for present value and future value, Discount Factor, Annuity, Present Value and Future Value of Annuity. All topics are explained in video lecture along with examples.Section 8 : Capital Budgeting (Concepts)It begins meaning of Capital Budgeting and purpose of Capital Budgeting. This is followed by process of capital budgeting and types of Capital budgeting decisions - Replacement and Modernization decisions, Expansion decisions, Diversification decisions, Mutually Exclusive decisions, Accept or Reject decision, Contingent decision.Other terms such as incremental cashflows, Tax Benefit on Depreciation, Opportunity cost and Sunk cost, Working capital costs, allocated overhead costs are also explained in separate tutorials along with illustrations. This is followed by types of cashflows for new project and replacement project along with basic principles of calculating cashflows.All Capital Budgeting Techniques i.e Payback Period Method, Payback Reciprocal Method, Accounting Rate of Return (ARR) Method, Discounted Payback period method, Profitability Index method (PI) , Net Present Value Method (NPV) , Internal Rate of Return Method (IRR) and Modified Internal Rate of Return (MIRR) are discussed in detail along with meaning , Illustrations , advantages and disadvantages. Reinvestment assumptions and anomalies in Net Present Value Method (NPV) and Internal Rate of Return Method (IRR) method along with reasons and examples are discussed separately.Capital Rationing Meaning and Capital Rationing for Divisible and Indivisible projects is discussed along with solved illustrations.Methods to analyze Mutually exclusive projects with different tenures - i.e Replacement chain Method and Equivalent annualized criterion method are also included along with solved examples.Section 9 : Time Value of Money and Capital Budgeting (In Excel) - ApplicationHere we will be learning how to calculate present value and future value, IRR, Annuity in excel. Then we will proceed further and learn project evaluation in excel using net present value (NPV) method and Internal rate of return (IRR) method.Section 10 : Forecasting in ExcelHere we will be preparing and learning how to make forecast in excel. We will be learning how to make Revenue and expense forecast using Arithmetic mean, Geometric mean, Moving average and Regression. We will also learn Balance sheet forecasting on basis of Revenue.

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 What is Financial Modeling ?

Lecture 3 Introduction to Excel

Lecture 4 Pre-requisites in Excel

Section 2: Excel Functions and Formulas

Lecture 5 Basic Excel Formulas

Lecture 6 Cell Referencing

Lecture 7 Basic Formatting in Excel

Lecture 8 Cell Formatting in Excel

Lecture 9 Date related formulas in excel

Lecture 10 Conditional Formatting

Lecture 11 Pivot Table

Lecture 12 Vlookup, Hlookup, Concatenate and Transpose

Lecture 13 Data Validation in Excel

Lecture 14 Protect Sheet Protect Workbook Protect Cell

Section 3: Advance Excel functions and Formulas

Lecture 15 Name Manager

Lecture 16 Goal Seek

Lecture 17 Scenario Manager

Lecture 18 Data Tables

Section 4: Financial Statements and Historical Data Plotting

Lecture 19 Financial Statements

Lecture 20 Historical Data Plotting

Lecture 21 Historical Data Plotting Version 2

Section 5: Ratio Analysis (Concept)

Lecture 22 Ratio Analysis (Introduction)

Lecture 23 Liquidity Ratios

Lecture 24 Liquidity Ratios - Current Ratio

Lecture 25 Liquidity Ratios - Quick Ratio

Lecture 26 Liquidity Ratios - Cash Ratio

Lecture 27 Liquidity Ratios - Interval Measure Ratio

Lecture 28 Liquidity Ratio - Net working Capital Ratio

Lecture 29 Solved Illustrations for Liquidity Ratios

Lecture 30 Leverage Ratios - Introduction

Lecture 31 Leverage Ratio - Equity Ratio

Lecture 32 Leverage Ratio - Debt Ratio

Lecture 33 Leverage Ratio - Debt to Equity

Lecture 34 Leverage Ratio- Debt to Total Assets Ratio

Lecture 35 Leverage Ratio - Proprietary Ratio

Lecture 36 Leverage Ratio - Capital Gearing Ratio

Lecture 37 Leverage Ratios - Debt Service coverage ratio

Lecture 38 Leverage Ratios - Interest Coverage Ratios

Lecture 39 Leverage Ratios - Fixed Charges coverage ratio

Lecture 40 Leverage ratios - Dividend Coverage ratios

Lecture 41 Solved Illustration on Leverage ratios - Capital Structure Ratios

Lecture 42 Solved Illustration on Leverage ratios - Coverage Ratios

Lecture 43 Activity Ratio Introduction

Lecture 44 Activity Ratio - Total assets turnover ratio

Lecture 45 Activity Ratio - Fixed assets turnover ratio

Lecture 46 Activity Ratio - Net Assets turnover Ratio

Lecture 47 Activity Ratio - Current Assets turnover ratio

Lecture 48 Activity Ratio - Working Capital turnover ratio

Lecture 49 Activity Ratio - Inventory turnover ratio

Lecture 50 Activity Ratio - Receivables turnover ratio

Lecture 51 Activity Ratio - Payables turnover ratio

Lecture 52 Solved Illustration - Activity Ratios

Lecture 53 Profitability ratios introduction

Lecture 54 Profitability ratios - Gross profit Ratio

Lecture 55 Profitability Ratios - Net Profit Ratio

Lecture 56 Profitability Ratios - Operating Profit Ratio

Lecture 57 Profitability Ratios - Expense Ratio

Lecture 58 Profitability Ratio - Return on Investment

Lecture 59 Profitability Ratio - Return on Asset

Lecture 60 Profitability Ratio - Return on Capital Employed

Lecture 61 Profitability Ratio - Return on Equity

Lecture 62 Profitability Ratios - Dupont Analysis

Lecture 63 Profitability Ratio - EPS Ratio

Lecture 64 Profitability Ratios - DPS

Lecture 65 Profitability Ratios - Dividend Payout Ratio

Lecture 66 Profitability Ratio - Price Earning Ratio

Lecture 67 Profitability Ratio - Dividend and Earning Yield ratio

Lecture 68 Profitability Ratio - Market value by book value

Lecture 69 Q Ratio

Lecture 70 Solved Illustration - Profitability Ratios

Section 6: Ratio Analysis (In Excel) - Application of Concepts

Lecture 71 Ratio Analysis in Excel (Part 1)

Lecture 72 Ratio Analysis in Excel (Part 2)

Lecture 73 Ratio Analysis in Excel (Part 3)

Lecture 74 Dupont Analysis in Excel (Part 1)

Lecture 75 Dupont Analysis in Excel (Part 2)

Lecture 76 Altman's Z Score Concept

Lecture 77 Alman's Z Score Calculation in Excel

Lecture 78 Common Size Statement in Excel

Section 7: Time Value of Money (Concepts)

Lecture 79 What is time value of Money

Lecture 80 Use of Concept - Time value of money

Lecture 81 Time Value of Money - Relationship between Inflation and Purchasing power

Lecture 82 Simple Interest and Compound Interest

Lecture 83 Present Value and Future Value

Lecture 84 Present Value and Future value (for single payments) without Formula

Lecture 85 Present Value and Future value (for single payments) with Formula

Lecture 86 What is discount factor ?

Lecture 87 What is annuity ?

Lecture 88 Present value of Annuity without Formula

Lecture 89 Present value of Annuity with Formula

Lecture 90 Present Value of Ordinary annuity using Formula

Lecture 91 Present Value of Annuity due using formula

Lecture 92 Future Value of Annuity without Formula

Lecture 93 Formula for future value of annuity

Lecture 94 Future value of annuity calculation using formula

Lecture 95 Single payment compounded annually, Semi Annually, Quarterly, Monthly and daily

Lecture 96 Annuity compounded annually, Semi Annually, Quarterly, Monthly and daily

Lecture 97 Annuity Factor

Lecture 98 Internal Rate of Return (IRR)

Section 8: Capital Budgeting - Concepts

Lecture 99 Capital Budgeting basics

Lecture 100 Purpose of Capital Budgeting

Lecture 101 Process of Capital Budgeting

Lecture 102 Types of Capital Investment decisions

Lecture 103 Replacement and Modernisation decisions

Lecture 104 Expansion decisions

Lecture 105 Diversification decisions

Lecture 106 Mutually exclusive decisions

Lecture 107 Accept or Reject decision

Lecture 108 Contingent decisions

Lecture 109 Accounting Profit and Cashflow

Lecture 110 Incremental Cashflows

Lecture 111 Depreciation and Tax Benefit

Lecture 112 Opportunity Cost

Lecture 113 Sunk Cost

Lecture 114 Working Capital Cost

Lecture 115 Allocated Overheads cost

Lecture 116 Types of Cashflows for new project

Lecture 117 Types of Cashflow for replacement project

Lecture 118 Basic Principles for calculating cashflows

Lecture 119 Block of assets and depreciation concepts

Lecture 120 Financing costs treatment

Lecture 121 Interest and Depreciation

Lecture 122 Post Tax Principle

Lecture 123 Capital Budgeting techniques

Lecture 124 Payback period introduction

Lecture 125 Payback period when cashflow is same

Lecture 126 Payback period when cashflow is not same

Lecture 127 Advantages and disadvantages of payback method

Lecture 128 Payback reciprocal

Lecture 129 Accounting Rate of return

Lecture 130 Accounting rate of return - Solved example

Lecture 131 Advantages and disadvantages of Accounting rate of return

Lecture 132 How to determine discount rate

Lecture 133 Discounted payback period

Lecture 134 Discounted payback period illustration

Lecture 135 Advantages and disadvantages of discounted payback period

Lecture 136 Profitability Index

Lecture 137 Solved illustration - Profitability index

Lecture 138 Profitability index - advantages and disadvantages

Lecture 139 Net Present value method

Lecture 140 Net Present value (Example)

Lecture 141 Advantages and disadvantages of Net Present Value method

Lecture 142 Internal Rate of Return

Lecture 143 Reading Present value annuity factor table

Lecture 144 IRR using present value annuity factor table

Lecture 145 Solved illustrations on IRR (When cashflows are uniform)

Lecture 146 Solved illustrations on IRR (When cashflows are not uniform)

Lecture 147 IRR and NPV (Reinvestment assumption)

Lecture 148 IRR and NPV (Mutually Exclusive projects ) - Illustration 1

Lecture 149 IRR and NPV (Mutually Exclusive projects ) - Illustration 2

Lecture 150 IRR and NPV (Mutually Exclusive projects ) - Illustration 3

Lecture 151 Advantages and Limitations of IRR method

Lecture 152 Multiple IRR

Lecture 153 Modified internal rate of return

Lecture 154 Modified internal rate of return - solved illustration (How to calculate it)

Lecture 155 Capital rationing - Divisible and non divisible projects

Lecture 156 Methods to analyze mutually exclusive projects

Lecture 157 Replacement chain method

Lecture 158 Equivalent annualized criterion method

Section 9: Time Value of Money and Capital Budgeting (In Excel) - Application

Lecture 159 Basic formulas for Time Value of Money in excel

Lecture 160 Loan amortization schedule in excel

Lecture 161 Project evaluation by NPV Method in Excel

Lecture 162 Project evaluation by IRR method in excel

Section 10: Forecasting in Excel

Lecture 163 Introduction - Revenue and expense forecasting

Lecture 164 Revenue and expense forecasting using arithmetic mean

Lecture 165 Revenue and expense forecasting using geometric mean

Lecture 166 Revenue and expense forecasting moving average

Lecture 167 Basics of Regression

Lecture 168 Revenue and expense forecasting using regression

Lecture 169 Calculating regression in excel

Lecture 170 Balance sheet Forecast basics

Lecture 171 Balance sheet Forecasting in Excel

Professionals who are keen to learn how to develop Financial model in excel from scratch,Aspiring Financial Analysts – Build a strong foundation in financial modeling to support career goals in finance, investment banking, and equity research.,Finance and Business Students – Gain practical, hands-on skills that complement academic knowledge and prepare you for internships and job placements.,Entrepreneurs and Business Owners – Learn to forecast financial performance, assess project feasibility, and make informed decisions to drive business growth.,Corporate Finance Professionals – Enhance analytical capabilities for budgeting, forecasting, and strategic planning within an organization.,Consultants and Financial Advisors – Gain valuable Excel modeling skills to analyze business opportunities and present data-driven insights to clients,Anyone Seeking Excel Proficiency in Financial Contexts – Improve data handling, analysis, and model-building skills for any role requiring financial data-driven decision-making.