Friday 20 January 2017

Basic MS Excel

Module 1 – Overview and Basic Set up
  1. Introduction – Basic Overview of the Course
  2. Understand the Basic Layout and Setup of Excel
  3. Excel Interface
Module 2 – Data Manipulation and Navigation
  1. Navigation & Anchoring Cells
  2. Adding/Deleting Rows and Columns
  3. Hiding/Unhiding Rows and Columns
  4. Group/Ungroup Rows and Columns
  5. Merge & Center
  6. Copying Values, Formats, Comments… (Paste Special)
Module 3 – Formatting
  1. Basic, Number, Currency, Date and Custom formatting
  2. Fills, Fonts, Border and Alignment
  3. Conditional Formatting
Module 4 – Filters and Drop Downs
  1. Filters and Sorting
  2. Drop Downs
Module 5 – Basic Charting
  1. Line Chart, Bar Chart and Combination Chart
  2. Pie Chart
Module 6 – Basic Excel Functions, Formulas and Lookups
  1. Autosum Shortcut Key
  2. AND
  3. AVERAGE
  4. AVERAGEIF
  5. CHOOSE
  6. CONCATENATE
  7. COUNT
  8. COUNTIF
  9. DATE, EOMONTH, TODAY, YEAR & MONTH
  10. Vlookup and Hlookup
  11. IF
  12. IRR (Internal Rate of Return)
  13. Index, Match & Index Match
  14. Max, Min and Median
  15. NPV (Net Present Value)
  16. OR
  17. IF (OR and AND)
  18. PROPER
  19. ROUND
  20. SUM
  21. SUMIF
  22. SUMPRODUCT
  23. TRIM
  24. PMT
Module 7 – Niche Functions
  1. Pivot Tables
  2. Goal Seek
Module 8 – Self Evaluation
  1. Practice Test 1
  2. Practice Test 2

Advanced MS Excel

After completing MS Excel training course, students will be able to address the following questions:
  1. Understand the basic layout and setup
  2. Get familiar with important shortcuts
  3. Comprehend how to Cut, Copy, Paste, Hide, and Unhide data in rows, columns, and sheets
  4. Overview of Merge & Center
  5. Understand Text Alignment and Text Indent
  6. Learn Custom Formatting and Conditional Formatting
  7. Learn Basic/Advanced Charting – Pie charts, Combo charts, Waterfall charts, Gantt charts, etc.
  8. Understand how to make Pivot tables
  9. Understand Data Validation and Filters
  10. Know how to utilize Goal Seek to validate assumptions
  11. Protecting and Sharing – Cells, Worksheets, and Workbooks
  12. Learn basic functions, including Sum, Sumif, And, Average, Choose, Vlookup, Hlookup, Count, Countif, Max, Median, Sumproduct, Today, Eomonth, NPV, PMT, and IRR.
  13. Learn advanced functions, including Match/Index, Offset, String functions, Date and Array functions.
  14. 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.

Finance for Non Finance Professionals

Course Objectives
After completing the course, students will be able to address the following questions:
  1. Understand the framework of Financial Statements – Income Statement, Balance Sheet and Cash Flow Statement
  2. Get familiar with the double-entry book- keeping system and journal entries
  3. Comprehend the impact of various business transactions on a company’s financials
  4. Describe financial jargon like working capital requirements, capex, shareholder’s equity, etc.
  5. Learn recognition of costs and revenue
  6. Understand complex topics covering fixed assets, inventory recognition methods, Accumulated Other Comprehensive Income, deferred taxes, marketable securities and long-term debt
  7. Develop a decision-making skillset using financial analysis
  8. Analyze a company based on ratio analysis
  9. Evaluation of investment projects – Meaning and Importance of Capital Budgeting, Techniques – NPV,IRR, Payback Period and ARR
  10. Comparison between NPV and IRR – Which is better and why?

Financial Modeling Course

Course Objectives
After completing the course, students will be able to address the following questions:
  1. Read and analyze financial statements
  2. Source information for building a comprehensive valuation model
  3. Understand the basic techniques of equity valuation
  4. Learn useful MS Excel functions and shortcuts referred to in building financial models
  5. Capture historical numbers from company filings
  6. Identify revenue drivers of any company
  7. Build a bottom-up revenue model
  8. Understand a top-down revenue model
  9. Build a fully integrated three-step financial model
  10. Build asset and debt schedules
  11. Plug the revolver facility in the cash flow statement to cover any deficit caused by an increase in the working capital
  12. Interlink the three financial statements to reconcile numbers and match the balance sheet
  13. Perform a Discounted Cash Flow analysis for valuing the theoretical price of any company
  14. Perform scenario and sensitivity analyses
  15. Perform ratio analyses and understand the different ratios and their interpretation
  16. Understand the process for identifying peer set of a company to conduct a relative valuation
  17. Understanding valuation using Transaction Comps
  18. Performing Football Field Analysis
Modules And Timelines
The program is divided into 3 modules:
  1. Financial Accounting and Valuation
  2. MS Excel
  3. Financial Modeling

OVERVIEW OF ACCOUNTING & VALUATION

  1. Overview of the income statement, balance sheet, and cash flow statement
  2. Overview of accounting adjustments like working capital changes, deferred revenue, DTA/DTL, etc. and their impact on the three financial statements
  3. Importance of equity valuation
  4. Techniques of equity valuation – DCF, Trading & Transaction Comparables
  5. Sum of the Parts Method (SOTP Analysis)

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, and Finance functions, which aid in building the model
  6. Goal seek and revolver

FINANCIAL MODELING

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

FINANCIAL MODELING

  1. Projecting interest expense and dealing with the circular referencing error
  2. Revolver adjustment
  3. Interlinkage of debt and asset schedule with projected financial statements
  4. Completing the operating model

FINANCIAL MODELING

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