Financial Modeling in Excel

Course Content

Module – 1

Setting up a financial model and building projections

• Overview of best industry practices for Financial Modeling
• Importance of historic information
• Analysis of company and industry information
• Steps for creating financial models
• Proper presentation of various sheets and setting up the model input dashboard

Module – 2

Preparation of Income statement, cash flow statements and balance sheet

• Analysis of industry assumptions and their rationale for revenues & costs, assets and liabilities’ assumptions – operating cost, capex, leverage, modelling of historical ratios and trends
• Short listing assumptions and applying adjustments
• Applying various forecasting tools, etc

Module – 3

Importance of key schedules and their application

• Working capital schedule
• Fixed assets and depreciation
• Schedule of amortization of intangible assets
• Preparation of debt and interest
• Taxation
• Equity fund raising
• Equity gap and funding

Module – 4

How to create working capital schedule

• Components of cash and non-cash working capital
• Working capital ratios and their interpretation
• Forecasting working capital requirements

Module – 5

How to create Capex and funding schedules including multiple debts

• Equity and debt fund raising and repayment structure
• Debt waterfall mechanism
• Checking debt repayment ratios, leverage and covenants

Module – 6

Integration of income statement, balance sheet and cash flow statement

• Industry practices on handling exceptions
• Integration of Cash Flows, Income Statement and Balance Sheet
• How to handle Circular References in financial modeling

Module – 7

Establish scenario and sensitivity analysis

• Use of data for sensitivity analysis
• Importance of scenario manager
• Identifying suitable scenarios and sensitivities and their impact on financial analysis
• Managing key pitfalls in the analysis

Module – 8

How to analyse the data

• Data sorting and Data filtering
• Creating, customizing Pivots Tables, advanced pivot tables and analysis
• V-LOOK UP and H-LOOK UP, Macros, Goal Seek and other analysis
• Solver function and its uses

Module – 9

How to apply different financial analysis techniques on the model

• Importance of PV, NPV and IRR and their link with company valuation
• How to analysis various capital budgeting decisions
• Calculation of various ratios
• Calculate the weighted average cost of capital using capital asset pricing model (CAPM)
• Calculation of WACC
• Investment and exit analysis – equity IRR, project IRR etc

Module – 10

Approaches to apply different valuation techniques in the model

• Different methods of valuation – DCF, Multiples, comparables etc
• Discounted Cash Flow valuation and different multiples based methodologies
• Importance of terminal valuation
• Current industry practices followed in company valuation using single or multiple methodologies
• How to factor size and illiquidity discount in the final valuation
• Incorporate various adjustments to the publicly available versions of Company valuation