Advanced Financial Modeling

The Financial Modeling Course is rapidly becoming a highly valued credential for finance professionals in India. Individuals equipped with financial modeling skills gain a competitive advantage in roles such as equity research, investment banking, credit rating analysis, financial analysis, business analysis, project finance, and fund management.

The Financial Modeling course equips candidates with the skills to build financial models used by organizations for purposes such as performance tracking, forecasting, budgeting, risk analysis, and evaluation. Participants will learn to develop both basic and advanced financial models using Excel. Hands-on practice through case studies ensures a strong grasp of real-world application. The course provides comprehensive training in model building and covers a wide range of models, including:

• Discounted Cash Flow (DCF) model
• Comparative Company Analysis model
• Sum-of-the-Parts model
• Leveraged Buyout (LBO) model
• Merger & Acquisition (M&A) model
• Industry-specific financial models
• Option Pricing model
• Corporate Finance model

Outcomes of the training:

The Financial Modeling course paves the way for career opportunities in fields such as equity research, investment banking, credit rating analysis, financial and business analysis, project finance, fund management, and commercial banking.

Course Curriculum:

Lesson 1: Excel Tips

• Overview of Excel and its versions
• Understanding and creating a Financial Model template
• Using referencing framework (usage of $) to effectively use formulae
• Case: Product Quotation
• Identify the relevant data using conditional formatting
• Case: Candidate shortlisting (HR)
• Extracting the required information from string of data using Left, Right, Mid and Trim
• Case: Addresses for Real-Estate Marketing
• Understanding Array functions
• Case: Analyse the investment results
• Getting the relevant details from multiple tables/ sheets with Lookup/ Index/ Match functions
• Using Name Ranges to make formulae easy to implement and understand
• Case: Analysing the sales: region/ year / product wise
• Looking at multiple possibilities with Offset function
• Case: Choosing different scenarios
• Getting the desired result with Goal Seek
• Case: Breakeven Analysis
• Getting the desired result having multiple constraints using Solver
• Case: Finding a right mix of production
• Summarizing the results with Sumifs/ Pivot Tables
• Case: Analysing results Vendor/ Product wise

Lesson 2: Case Study

• Collect and Structure Data
• Scrutinize collected data and information
• Understand business and operating models
• Model Historical Statements
• Understanding Drivers
• Modeling Driver Assumptions
• Projecting future Drivers
• Modeling Revenue Build-up
• Projecting the future revenue
• Modeling Cost Build-up
• Projecting the future cost
• Modeling the Asset and Depreciation Schedule
• Book and Income Tax
• Modeling Debt and Working Capital
• Tax Calculations
• Income Tax
• MAT and MAT Credit
• Deferred Tax
• Current Tax
• Accumulated Losses
• Modeling Equity Schedule
• Paid Up Capital
• Retained Earning
• Modeling the Financial Statements, Ratio Analysis
• Modeling Valuation
• WACC and Cost of Equity Analysis
• Valuation using DCF
• FCFF, FCFE and EV
• Valuation using Comparables
• Trading Comparables
• Transaction Comparables
• Performing Sensitivity/Scenario Analysis
• Valuation Comparison
• Football Field Chart

Lesson 3: Merger & Acquisition

• Meaning and Categories of M&A
• Exploring motivations behind a merger
• Different forms of payment in a merger deal
• Hostile versus Friendly Oer
• Evaluating a Merger Bid
• Case: Estimating fair acquisition price of the target company in M&A
• Absolute Valuation
• Discounted Cash Flows
• Comparables
• Trading Multiples
• Transaction Multiples
• Case: Real-life case study on M&A
• Rationale of the Transaction
• Research Reports
• Collecting and projecting numbers
• Creating high level merger accounting for Goodwill – Pooling method
• Modeling a Financing Plan
• Performing Sensitivity Analysis on Acquirer’s EPS,
• Contribution Analysis
• Accretion/Dilution Analysis

Lesson 4: Project Finance

• Case: Real-life case on creating a Project Model for Broadband Services
• Fiber Infrastructure under build, own and operate
• GANTT Chart: Timeline of Construction
• Creation of an SPV
• Construction Schedule
• Uses and Sources
• Debt Schedule
• Interest During Construction
• Repyament Schedule
• DSRA: Debt Service Reserve Account
• DSCR: Debt Service Coverage Ratio
• Margin Calculation
• Re-CAPEX
• Equity Schedule
• Annualized Schedule
• Modeling Revenue Build-up
• Different kind of Services, Customers and Plans
• Modeling Cost Build-up
• Operating Model
• DSCR
• In-house Costs
• Outsourced Costs
• Margin in different kind of costs
• Cashflow Statement
• Project Cash flow
• Equity Cash flow
• Valulation
• NPV
• IRR
• Project
• Equity
• Company IRR

Lesson 5: Macros

• Macros
• Accessing VB editor, Developer tab
• Changing security to ‘Disable all macros with notification
• Recording your 1st macro – copy pasting values
• Formatting chart with macros
• Creating exible CAPM model using Form Controls
• Writing your 1st macro – adding a new worksheet and populating content
• Debugging and stepping into the VBA Code of your rst macro
• Correcting circular reference in IDC using VBA/macros
• Case: Removing Circular Reference in the Project Model
• Case: Populating Result for all scenarios

Lesson 6: Valuation Case Study

• Data Collection from Public Sources
• Understanding the Business Profile of the Company
• Modeling Revenue and Expenses Drivers
• Modeling Income Statement
• Modeling Implied EBITDA
• Balance Sheet Projections
• Cash Flow Statement Projection
• Performing Valuation and Sensitivity Analysis
• Calculating Enterprise Value and Implied Market capitalization

Lesson 7: Visualization in Excel

• Case: Variance Analysis
• Understanding Budget vs Actual Expenses
• Summarizing Actuals under different heads month wise
• Creating a Dashboard
• Variances under different heads
• Charting for Budget vs Actual
• Charting on Actual breakdown
• Case: Sales Dashboard
• Understanding the data
• Creating Tabular format
• Naming Tables
• Creating desired Pivot Tables
• Naming Pivot Tables
• Creating Dashboard
• Summary on Sales
• Using Picture Art
• Creating Slicers
• Report Connections
• Sizing Slicers
• Creating Different Charts
• Line Chart
• Bar Chart
• Pie Chart
• Tree Map
• Geographical Map
• Sunburst

Lesson 8: Power BI

• Power BI
• Connect to Data Source
• Transform Data
• Modify Data
• Add Column
• Different kind of charting
• Decomposition Chart
• KPI Influencers
• Filters, Advanced Filters
• Hierarchy
• Manual Data Entry
• Joining, Binning, Grouping
• Whatif Analysis
• Setup the gateway
• Schedule the Reports
• Reports and Dashboard
• Web Publishing
• Mobile BI