Two-Day Programme
Delegate places are limited due to the practical nature of the course. Laptops are provided.
Key Functions for Financial Model Building
- Organization and grouping of Excel functions
- Locating appropriate functions and potential pitfalls
- Useful financial functions
- Key maths functions
Keyboard Shortcuts
- Using keyboard shortcuts to speed up all standard tasks
- Tricks for efficient navigation of spreadsheets
- Changing Excel default options
Good Model Design
- Defining inputs, outputs and intermediate calculations
- Creating a logical flow of information
- Demarcation of logically related blocks
Practical Exercises
- Planning the flow of a model, including: operating assumptions, income statement, balance sheet, cashflow and debt
- Laying out the historical statements
- Common size statements and CAGR
Iterative Calculation
- Using Iterative calculation
- Circular reference breakers
- Tracing problems with circular references
Practical Exercises
- Projecting forward using iterative calculation; assuming cash is the plug item
Logic, Date and Lookup Functions
- Logic and date functions
- Lookup functions and interpolation
Practical Exercises
- Generation of Free Ccashflow
- Adding in debt and using Cash Sweep
Sensitivity Analysis and Graphs
- Data tables
- Scenarios
- Using the watch window
- XY scatter graphs
Practical Exercises
- Calculate IRR of Investment
- Create sensitivity tables for IRR
WACC and DCF
- WACC analysis
- Comparative analysis
Practical Exercises
- Calculation of WACC
- Terminal value, DCF and value of equity
- Using multiples for comparison
Customizing the Model
- Using forms (list boxes, check boxes...)
- Conditional formatting
- Data validation
- Custom views
- Different ways of using protection
Practical Exercises
- Adding drop downs to make model easier to use
Advanced Analysis
- Using solver and goal seek
- Random numbers and normal distributions
- Monte Carlo simulations
Practical Exercises
- Building a model to analyse value/cost of various fees structures
Additional Practical Exercises
There will be a large number of exercises for delegates to work through so as to understand the individual techniques.
These will include:
- NPV and IRR calculations
- Using nested IF statements and logic
- Manipulating dates