Financial Modelling in Excel Workshop & Introduction to the DCF Analysis
Dubai, UAE
July 8-9, 2012
Course Agenda
Day 1 - Introduction to Financial Modelling
Introduction
¨ Financial modelling as a powerful and versatile skill for finance professionals
¨ Applications of financial modelling
¨ Relevance for investment and corporate banking, equity research, project finance, private equity, corporate
budgeting, strategic planning, etc.
Getting started in financial modelling
¨ Good practices in financial modelling
¨ Financial modelling conventions
¨ Model layout and design
Accounting refresher for financial modelling
¨ Income Statement, revenue and expense items
¨ Balance Sheet, common assets and liabilities
¨ Statement of Cash Flows, sources and uses of cash
¨ Relationships between the financial statements
¨ Financial statement and ratio analysis
¨ Practical exercise
Excel finance fundamentals
¨ Importance of Excel for finance professionals
¨ Excel best practices
¨ The art of proper model formatting
¨ Commonly used Excel functions
¨ Useful techniques
¨ Excel Shortcuts
¨ Utilizing macros
¨ Pivot tables analysis
¨ Scenario analysis through data tables, what-if analysis and scenario manager
¨ Creating powerful graphs and charts
Historical financial statements
¨ Importance of historical financial statements
¨ Analysis of historical Income Statement and Balance Sheet
¨ Deriving key model drivers
¨ Identifying relevant trends and ratios
¨ Practical exercise
Modelling the Income Statement
¨ Analysing historical company Income Statement
¨ Identifying historical revenue and expense trends
¨ Analyzing the macroeconomic environment and market trends
¨ Making projections of Income Statement items
¨ Focus on key drivers and ratios
¨ Projecting revenue
¨ Analyzing and projecting geographic or business revenue segments
¨ Implementing and analyzing different growth rate scenarios
¨ Analyzing and projecting relevant expense items
¨ Practical exercise
Day 2 - Building a financial model of a company
Modelling the Balance Sheet and the supporting schedules
¨ Organization and structure of the Balance Sheet
¨ Overview of different supporting schedules used in the model
¨ Analysis of intangible assets
¨ Setting up the CAPEX schedule
¨ Creating the amortization schedule
¨ Projecting the amortization expense
¨ Creating the debt schedule
¨ Incorporating debt seniority into the model
¨ Treatment of revolver credit facility
¨ Calculating interest expense with links to the rest of the model
¨ Understanding working capital
¨ Setting up the working capital schedule
¨ Incorporating working capital drivers
¨ Integrating the working capital schedule into the model
¨ Understanding income tax dynamics
¨ Setting up the income tax schedule
¨ Calculating the income tax expense
¨ Interlinking the income tax schedule into the model
¨ Creating the shareholders' equity schedule
¨ Incorporating the treatment of dividend payouts
¨ Integrating the shareholders' equity schedule into the model
¨ Reviewing and testing connections of supporting schedules to financial statements
¨ Practical exercise
Modelling the Statement of Cash Flows
¨ Setting up the cash flows schedule
¨ Accounting for the cash flows from operating, investing and financing activities
¨ Treatment of working capital items
¨ Verifying the movement of cash each period
¨ Integrating the Statement of Cash Flows into the model
¨ Practical exercise
Balancing the projected model
¨ Use of plugs
¨ Dealing with circular references
¨ Automating the Income Statement, Balance Sheet and the Cash Flow Statement with all supporting schedules
¨ Getting rid of the undesirable error messages
¨ Practical exercise
Checking the model and managing model output
¨ Building the scenario manager
¨ Running scenarios under different operating assumptions
¨ Analysing model outputs under different scenarios
¨ Implementing automated output reporting including charts, football fields, etc.
¨ Running sensitivity analysis using data tables
¨ Practical exercise
Introduction to the DCF analysis
- Basic valuation concepts – Enterprise Value vs. Equity Value
- DCF analysis – an introduction (formulas, discount periods, terminal value)
- Discount rate considerations
- Practical exercise
