1. Category
  2. Duration
  3. Global coverage
  4. Navigation
Green vertical standing travel suitcase with extended handle

Our bags are packed

Our courses are intended for a global audience, with each one being completely customisable to suit the needs of the individuals it is being delivered to.

They can be delivered in London or in your home town. You decide.

Get in touch

  • Home > 
  • Training > 
  • Comprehensive Financial Modelling In Excel

Comprehensive Financial Modelling In Excel

  • Banking and Financial Markets, Bonds & Fixed Income Markets, Corporate Finance, Financial Modelling, Risk and Credit
  • Duration: Two days
  • Information

    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

    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.


    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


    • 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

    Case studies:

    • Using data tables to create a spot-vol matrix for an option portfolio
    • Creating complex scenario analysis to stress test portfolio.


    • 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


    • 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
    • Arrays

    Case studies:

    • Calculate historical volatility, using a parameterized window length


    • 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.


    • Goal Seek
    • Solver

    Case studies:

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


    • 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.


    • Exploring the Visual Basic Editor: Project, Properties, Code window
    • Understanding VBA Toolbars
    • Setting the various Options


    • 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

    Register interest

    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.

    * indicates a required field

    Personal details
    Company details
    Your enquiry