Financial Modeling

Foundations of Financial Modeling

·           Need for Financial Models

·         What is Financial Modeling

·         Best Practices of EXCEL

·         Understanding of Key Financial Statements (Balance Sheet & Income Statement)

·         Streamlining of the Financial Statements data gathered from Third Party Sources

·         Additional information from Annual Reports

Cash Flow Statements

·         Preparation of cash flow statement from Balance sheet & Income Statement

·         Analysis based on Cash Flow Statements

·         Computation & Interpretation of Cash Flow Ratios

Ratio Analysis

·         Profitability Analysis

·         Efficiency Analysis

·         Liquidity Analysis

·         Common size Analysis

·         DuPont Analysis

·         Altman z-Score

·         Reporting on overall Financial Health

·         Valuation Ratios

·         Working Capital Analysis

·         Observe historical trends

·         Identify determinants of earnings growth

·         Measure financial strength

·         Observe the link between ROE and implied growth

Forecasting Financial Statements

·         Using different functions of EXCEL to forecast

·         Understanding Macro economic variables having impact on Future Financial Statements

·         Impact of GDP, Inflation & other macro economic variables on Equity Market

·         Using Regression to model forecasted Financial Statements

·         Understanding the drivers for forecasting

Understanding of Key Components

·         Depreciation Schedule

·         Debt Schedule

·         Shares Outstanding Schedule

·         5-Year Financial Statement Projection Model

·         Forecasting of Geographical and Segment based Revenues and Expenses.

·         Forecasting a Company’s Income Statement

·         Forecasting Assets and Liabilities on the Balance Sheet

·         Projection of Shareholders Equity

·         Role of different Financial Ratios during Projection

·         Building an Integrated Cash Flow Statement

Supporting Schedules

·         Integrating Dividend Payments Schedule into the Model

·         Incorporating the Debt Schedule into the Model

·         Incorporating a Detailed Depreciation Schedule

Integration and Balancing of Financial Model

·         Balancing using debt schedule and debt sweep logic

·         Integrating Balance Sheet, Income Statement and the Cash Flow Statement.

·         Using cash flow either to bring down debt or build cash.

·         Integration of Financial Statements using the interest schedule?

·         Circular References in EXCEL and the mode to handle them.

Detailed Business Segment Build-Up

·         Understanding the growth Drivers and projecting the key drivers of growth.

·         In-depth growth analysis on the Historical Data.

·         Project future detailed growth assumptions that roll up into larger projection model

·         Digging into deeper layers of growth drivers

Operating & Division Segment Build-Up

·         Calculate and analyze different operating segments

·         Adjust for extraordinary items by segment based on MD&A and disclosed footnotes

·         Project future revenue and segment income and allocate for corporate overhead

·         Estimate projected COGS and SG&A on the entire base

·         Further enhance model via sensitivity & scenario modeling and analysis

Sensitivity Analysis and Multiple Cases

·         Sensitivity analysis to incorporate various assumptions and cases

·         Build multiple scenarios and cases, including Base Case, Optimistic & Pessimistic Cases

·         Sensitize profitability and cash flow of model based on various case assumptions

Enhancements to Core Integrated Financial Model

·         Build a stand-alone depreciation schedule to better estimate working capital changes and free cash flow by depreciating existing PPE as well as new capital expenditures

·         Allocate accumulated depreciation correctly

·         Depreciate existing Net PPE and new CapEx based on weighted average life

·         Create quick financial summary exhibit that summarizes key figures from financial model

·         Residual value and EVA analysis

Different Concepts of Valuation

·         Discounted Cash Flow based Valuation

·         Relative Valuation

·         Residual Based Valuation

·         Calculation of Cost of Equity & Cost of Capital

·         Sustainable growth rate

·         Terminal value and its importance.

Discounted Cash Flow Techniques

·         Understanding Dividend Patterns

·         Dividend Discounting Model

·         Calculation & Interpretation of Terminal Value

·         Computation of Free Cash Flow to Equity & Free Cash Flow to Firm

·         Valuation using FCFF & FCFE approached

·         Normalization of Earnings

·         Enterprise Value calculation

Relative Valuation Techniques

·         Understanding key valuation ratios like P/E, P/BV, P/S, P/CF and importance of each

·         Computation of each of those ratios dynamically (Leading & Trailing)

·         Using the ratios to forecast the value of the Equity/Firm

·         Understanding PEG & Dividend Yield

Residual Valuation Techniques

·         Understanding the concept of Residuals

·         Benefits of Residual Valuation Models

·         Calculating residuals like EVA & MVA

·         Valuation based on Residuals

·         Sensitivity Analysis

·         Using Advanced EXCEL Tools like Data Table & Goal Seek

·         Using Monte-Carlo simulation to forecast sales and do a sensitivity Analysis