Who Should Attend?
You should attend if your work requires the use of Excel to develop financial models. In particular:
- corporate planners responsible for developing or enhancing forecasting models
- corporate finance practitioners building or auditing valuation
- those involved in structuring or auditing funding models
- analysts producing or using financial forecasts for corporates and project
Course Design
The course is designed:
- to cultivate best practice
- with the emphasis on practical applications;
- as a workshop to encourage an interactive approach;
- using case studies adapted from the real world;
- at a pace to suit all delegates
Pre-requisite Knowledge
In advance of the course, a basic knowledge of Excel is assumed. This knowledge should include at least the following:
- the Excel screen menu and toolbar
- moving around a worksheet and between worksheets in a workbook
- changing column width
- entering labels
- entering, selecting, copying and editing cell contents
- centering titles
- saving, closing & retrieving the worksheet
- auto fill
- entering formulae
- relative and absolute cell referencing
- formatting, previewing and printing
Summary
Course Objectives
To develop practical skills and concepts for:
- financial modeling within a robust financial/accounting framework
- use of Excel as an advanced modeling tool
- modeling financial forecasts
- reconciling the projected accounting statement
- financial projections, valuations and funding
DAY 1
Introduction
- Overview of course and content
- Financial framework for modeling
Review of Key Excel Functions
- Worksheets and workbooks
- Copying and naming sheets
- Linking sheets and books
- Relative and absolute references
Advanced Excel Functions and Techniques
- Array arithmetic
- Grouping sheets
- Conditional statements
- Using names
Financial Modeling Exercises
- Sales growth
- Profitability
- Capital expenditure and depreciation
- Cover ratios
Corporate Modeling Case Study
- Scope of the forecast model
- Modular layout and sub-model definition
- Income flows
- Operating expenditure
- Capital expenditure and depreciation
Basic Modeling Concepts
- “Best-practice” – modeling principles
- Advanced Excel Functions and Techniques
- Iterative calculations and alternative solutions (application to the calculation of interest)
- Tracing and auditing
- Goal Seek and Solver
- Excel financial functions
DAY 2
Corporate Modeling Case Study
- Working capital and current assets
- Tax
- Dividends
- Funding and interest payments and receipts
- Reconciling the financial statements
Advanced Excel Functions and Techniques
- Sensitivity, scenario and optimization tools
- Solver
- One-way Data Tables
- Scenario Manager
- Two-way Data Tables
- Data Validation
- Advanced Scenario Management
- Conditional formatting
Corporate Modeling Case Study
- Modeling value
- DCF Valuation
- Multiples
- Modeling Funding
- Investor returns
Corporate Modeling Case Study
- Identification, exploration and control of sensitivities
- Advanced sensitivity techniques
Corporate Modeling Case Study
- Automating the model using controls and macros
- Controls
- Recording simple macros
- Writing simple macros
- Other VBA examples and exercises