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

Training methodology

• 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
Key functions:

• Finance
• Logic
• Dates
• Lookup

Finding appropriate functions and avoiding potential pitfalls
Case studies:

• Lookup and interpolation of implied volatilities, taking account of tenor and moneyness of an option

INCREASING EFFICIENCY

• Using keyboard shortcuts to speed up standard tasks
• Tricks for efficient navigation of spreadsheets
• Changing Excel's default options

SENSITIVITY ANALYSIS

• Data Tables, 1 and 2 dimensions
• Using XY graphs efficiently

Case studies:

• 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

Case study:

• 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

DYNAMIC DATA

• Using Offset, Indirect to create dynamic references
• Advanced use of names
• Arrays

Case studies:

• Calculate historical volatility, using a parameterized window length

STATISTICS

• Overview of statistical functions available, including Regression, Correlation and Percentile functions
• Data Analysis Toolpak

Case studies:

• Build a Black and Scholes option pricing model, using the interpolation procedure already built.

OPTIMIZATION

• Goal Seek
• Solver

Case studies:

• Use Solver to find a hedge for a portfolio of options

MONTE CARLO SIMULATIONS

• Designing a Simulation model
• Using Random numbers in Excel

Case studies:

• 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
GETTING STARTED

• 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

KEY FUNCTIONALITY

• Branching: If...then
• Looping: For…Next, Do While etc…
• Understanding Cells() and Range()
• Using InputBox, MessageBox, StatusBar