Comprehensive Financial Modelling In Excel
Scope and Objectives
This is a 2-day course. Approximately one and a half days will be focused on getting the most out of the spreadsheet environment, and half a day will be giving an overview of VBA.
- It is an intensive two days, designed to take people from an introductory level towards advanced.
- Note that this course is designed by, and run by a banker, and is entirely geared towards financial modelling
- During the course participants will
Learn all the essential functions required to work in financial markets
- Uncover a range of little utilized functionality that will produce more dynamic models
- Develop skills that increase efficiency when dealing with large datasets
- Gain a good understanding of the power of Excel, even without VBA.
- Get an overview of what’s possible with VBA, and build a few simple routines
- By the end of the course delegates tend to have a very different perception of what is possible. In a sense, one of the objectives is to make people aware that Excel provides a rich model building environment … it is much more powerful than most people think.
Delegates should be familiar with:
- Entering data and simple formulae
- Use of relative and absolute references, in other words the difference between A1 and $A$1
- Standard Office concepts such as copy, cut and paste
- Building blocks of financial markets such as discounting and options at a high level, i.e. definitions of call and out options
(Note that these ideas are used in the examples in order to make them relevant, but ultimately this is an Excel course, not a finance course)
Delegates do not need to take this course if they:
- Understand the important differences between Index(), Match() and Vlookup()
- Already use DataTables, Offset(), Solver on a regular basis
- Have good knowledge of VBA
- The course is hands-on, with extensive use of exercises to reinforce the various techniques. As well as the many exercises there are a number of practical sessions, where delegates work systematically through larger scale problems.
- In addition to copies of the slides, the delegates will take away numerous spreadsheets prepared by the course director containing lists of shortcuts, a comprehensive directory of Excel functions, with worked examples to illustrate the key functions. They will also take copies of the sheets they have built themselves, together with model answers.
PART I : SPREADSHEET
KEY FUNCTIONS FOR MODEL BUILDING
Finding appropriate functions and avoiding potential pitfalls
- Lookup and interpolation of implied volatilities, taking account of tenor and moneyness of an option
- Using keyboard shortcuts to speed up standard tasks
- Tricks for efficient navigation of spreadsheets
- Changing Excel's default options
- Data Tables, 1 and 2 dimensions
- Using XY graphs efficiently
- Using data tables to create a spot-vol matrix for an option portfolio
- Creating complex scenario analysis to stress test portfolio.
GOOD MODEL DESIGN
- Defining inputs, outputs and intermediate calculations
- Creating a logical flow of information
- Demarcation of logically related blocks
- Using Audit functions, Watch and Evaluate Formula
- Finding the optimal lag for hedging monthly government price index with a liquid market prices
DATA MANIPULATION AND ANALYSIS
- Solving common problems when importing data from other sources
- Tricks to speed up data manipulation using sorting and logic functions
- Analyzing imported datasets
- Using Offset, Indirect to create dynamic references
- Advanced use of names
- Calculate historical volatility, using a parameterized window length
- Overview of statistical functions available, including Regression, Correlation and Percentile functions
- Data Analysis Toolpak
- Build a Black and Scholes option pricing model, using the interpolation procedure already built.
- Goal Seek
- Use Solver to find a hedge for a portfolio of options
MONTE CARLO SIMULATIONS
- Designing a Simulation model
- Using Random numbers in Excel
- Creating a simple Monte Carlo model to value a European option.
- Evaluate expected PV of different fee structures for asset managers.
PART II : INTRO TO VBA
- Exploring the Visual Basic Editor: Project, Properties, Code window
- Understanding VBA Toolbars
- Setting the various Options
FUNCTIONS & SUBS
- Writing a Function( ) macro
- Writing a Sub()
- Linking to buttons
- Tricks when recording macros
- Branching: If...then
- Looping: For…Next, Do While etc…
- Understanding Cells() and Range()
- Using InputBox, MessageBox, StatusBar
As every course we run is tailored to meet the specific needs of each client, we can only provide an estimate after fully understanding your specific requirements. Please complete the form below of call +44 (0) 208 894 4977 to discuss how Taylor Associates can help you.