Advanced Financial Modeling

Advanced Financial Modeling (Investment Banking Course) Duration: 60 Hours, Fees : INR 20,000 plus service tax

Course Objectives

1. Read and analyze income statement, balancesheet and cash flow statement
2. Work on MS Excel and use efficient formula construction
3. Use advanced MS Excel functions used at finance institutions to build dynamic templates
4. Build a comprehensive financial model from scratch
5. Identify revenue drivers and cost drivers using operating metrics
6. Build a bottom-up and top down revenue projections
7. Audit and fix circularity problems, iterations, and other reconciliation errors
8. Source information from public filings and capture historical data
9. Explicate the techniques, elements, and approaches of forecasting
10. Apply the concept of discounted cash flow using FCFF and FCFE
11. Calculate equity valuation multiples
12. Build asset and debt schedules
13. Plug the revolver facility in the cash flow statement to cover any deficit caused by an increase in the working capital
14. Interlink the three statements to reconcile numbers and match the balance sheet
15. Perform discounted cash flow analysis for valuing the theoretical price of any company
16. Perform scenario and sensitivity analyses
17. Perform ratio analyses and understand the different ratios and their interpretation & relevance
18. Understand and perform trading/transaction comparable analysis
19. Understand and walk through a simple LBO model (very popular in investment banking)
20. Understand and build a project finance model to analyze feasibility
21. Build a simple dashboard to capture the output

Modules And Timelines

The program is divided into 6 modules:

1. Accounting and Valuation
2. Advanced MS Excel
3. Advanced Financial Modeling
4. Project Finance  Overview
5. Leverage Buyout Overview
6. Mock test of Financial Modelling

The modules will be covered over 10 days

OVERVIEW OF ACCOUNTING & VALUATION

1. Overview of the income statement, balance sheet, and cash flow statement
2. Overview of accounting adjustments such as working capital changes, deferred revenue, and DTA/DTL, and their impact on income statement, balancesheet and Cash Flow Statement
3. Importance of equity valuation
4. Techniques of equity valuation – DCF. Trading & Transaction Comps
5. Sum of the parts method (SOTP Analysis)

BASIC MS EXCEL

1. Basic layout and shortcuts of MS Excel
2. Basic and Conditional Formatting
3. Basic Charting – Line & Bar charts, pie charts, combo charts etc.
4. Pivot tables
5. More than 30 Basic MS Excel functions like Logical functions, Reference functions etc which are widely used in client work
6. Goal seek and revolver

EQUITY VALUATION MODEL

1. Overview of the listed company and the sector used for the case study
2. Data sourcing and creating historical income statement  and balance sheet
3. Forecasting revenues using bottom-up revenue and top down approaches
4. Building cost buildup
5. Projecting of financial statements
6. Building an asset schedule
7. Building a debt schedule

EQUITY VALUATION MODEL

1. Projecting interest expense and dealing with the circular referencing error
2. Revolver adjustment
3. Interlinkage of debt and asset schedule with projected statements
4. Understanding common mistakes which happen during the linking process.

EQUITY VALUATION MODEL

1. Performing DCF analyses using the FCFF approach
2. Performing Sensitivity analyses (single- and two-variable tables)
3. Performing ratio analyses
4. Valuation using Trading Comparables
5. Valuation using transaction comparables
6. Football Field Analysis

ADVANCED MS EXCEL

1. Advanced functions including Match/Index, Offset, Indirect, String functions, Date functions, and Array functions
2. Understand niche functions, including Etworkdays.Intl, Yield, Transpose, Forecast and Trend, Text, Mod, Db, Sln, Ipmt and Ppmt, Find, Len, Left, Right, Mid, and Mirr.
3. Regression analysis
4. Advanced charting, including Waterfall chart, Dynamic chart, and Gantt chart
5. Building switches to incorporate different scenarios

ADVANCED FINANCIAL MODELING

1. Overview on Beta and its calculation
2. Performing sensitivity analysis using 3D tables
3. Circular error check and correction
4. Auditing and methods to correct balance sheet differences
5. What are investment banks and what do these banks do
6.  Pitch books
7. Mock Financial Modeling Test (2 hours) and its analysis to find out any weakness.

LBO MODEL

1. Overview of the global LBO market, know-how, and characteristics of an LBO target
2. Understanding the template to analyze LBO
3. Building Sources and Uses table
4. Allocating goodwill based on the purchase price

LBO MODEL

1. Creating pro-forma balance sheet to adjust for deal assumptions
2. Incorporating flexibility for different debt tranches
3. Applying adjustments for cash sweep
4. Performing LBO analysis using a case study

PROJECT FINANCE MODEL OVERVIEW

1.  Overview of the project finance model
2.  Understanding the template to analyze project feasibility
3.  Building escrow adjustments, including Cash Reserve Escrow Account and DSRA
4.  Incorporating adjustments for project phasing and government subsidy (if applicable)
5.  Building Cash Waterfall to gauge cash flows from the project
6.  Building Waterfall Distribution to gauge payoffs to investors
7.  Building a robust dashboard to capture the output