The objective of this 2-day training course is to give financial market professionals the tools they need to be more efficient and creative whilst building stable and transparent Excel models. In particular, there are two goals:

• Increase Productivity, by giving delegates the skills required to be faster and more efficient when analysing data and building models

• Reduce Risk, by discussing and illustrating the importance of design, structure and documentation

Approach

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. These include:

• Calculating discount factors from a swap curve

• Building a model for evaluating cash flows using a term structure, using lookup functions and interpolation

• Analysing imported data

• Understanding someone else’s model and developing ideas to make it more transparent

• Building a Black & Scholes option model. Creating a sensitivity matrix for small option portfolio, and using Solver to hedge the portfolio

In addition to copies of the slides, the delegates will take away numerous spread sheets 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.

Session 1:

Introduction

• Overview of Excel

• Organization of Excel functions

Session 2:

Financial Model Building

• How Financial functions are grouped

• Locating appropriate functions and potential pitfalls

• Logic and Date functions

• Lookup functions and interpolation

Session 3:

Keyboard Shortcuts

• Using keyboard shortcuts to speed up all standard tasks

• Tricks for efficient navigation of spread sheets

• Changing Excels default options

Session 4:

Sensitivity and Scenario Analysis

• Data Tables and Scenarios

• XY Scatter Graphs

Session 5:

Data Manipulation and Analysis

• Solving common problems when importing data from other sources

• Tricks to speed up data manipulation using sorting and logic functions

• Analysing imported datasets

Session 6:

Auditing and Debugging

• Finding your way around other peoples spread sheets; looking for common errors

• Using Audit; locating redundant parameters

• Using Watch and Evaluate Formula

Session 7:

Good Model Design

• Defining inputs, outputs and intermediate calculations

• Creating a logical flow of information

• Demarcation of logically related blocks

Session 8:

Statistical Functions for Pricing and Risk

• Statistical functions and Data Analysis Tools

• Using Solver and Goal Seek

Session 9:

Monte Carlo Simulations for Pricing

• Designing a Simulation model

• Random numbers

Session 10:

Add-Ins & Utilities

• Extending functionality through Add-Ins

• Sources of free utilities and help

Course Close