Advanced financial modelling course

This financial modelling course is designed for participants who have some prior experience of working with Excel financial models.

This is a “hands on” program where participants spend the majority of their time on practical financial modelling tasks.

With the help of the course tutor, and through a well-proven step-by-step approach, participants depart this intensive course with the confidence and ability to apply advanced financial modelling techniques in Excel.

All through the course delegates spend their time working with their own spreadsheets, exploring the limits to which Excel can be used to solve advanced data manipulation and financial modelling challenges.

Excel ‘power modelling’ functions

Approximately 3/4 of the course is spent on exploring advanced Excel functions.

Exploring advanced Excel functionality

At the beginning of the course delegates are asked what functions they would most like help on. Each run of the course is tailored to the particular needs of the training group. Below is a sample of functions that may be covered (based on other runs).

This is an interactive program, run in extremely small group sizes, that responds to delegate’s needs. The exact functions covered will depend on what you and other delegates would like help with.

 

    • Pitfalls in Excel’s investment appraisal functions
      • Pitfalls in the standard IRR function
      • Problems with the standard NPV function
      • Solving the problems with Excel’s investment appraisal functions.
With help from the course tutor, training delegates practise getting Excel’s advanced investment appraisal functions to ‘work’ and deliver expected results.
    • Excel’s ‘What if’ functions
      • How can Excel’s ‘What if’ tools help us?
      • Can scenario manager be helpful?
      • Constructing two variable and one variable data tables
      • Warnings about data tables
Course delegates practise using Excel’s what if functions.
    • Excel’s ‘Lookup’ functions: getting Excel data in the right place
      • Alternatives to the standard Vlookup function
      • Pros and cons of the alternatives: which is best?
Course delegates practise using Excel to get data into the correct place in a financial model – using a range of the alternatives described during this part of the program.
    • Sorting data in Excel
      • Alternative ways of sorting data in Excel
      • The world’s quickest ever introduction to pivot tables
      • Solving the same problem using standard Excel functions
      • An introduction to Excel’s hidden Array Formulas: combining two formulas in one
      • 3D referencing in Excel: stacking sheets
Delegates practise sorting data sets using Excel’s standard tools. Training course delegates solve the same kind of problem using Excel’s array formulas.
    • Flexing a model in Excel
      • Best practice scenario analysis in Excel
      • Structuring your model to good effect: facilitating instant scenario switching
      • Making use of Excel’s form controls
      • ‘Sliding’ a model horizontally – incorporating contract extension or project delay
      • The usefulness of the ‘shortcut IF’ function – building in model timing flags
      • Building in escalation factors – how to get it right/ how to get it very wrong
Training course attendees practise using techniques in Excel that will help them flex a financial model
    • Other helpful best practice tips for Excel financial modelling
      • Tips for keeping an eye on key results
      • How to tell if a model has fallen over
      • Stopping an Excel model from falling over
      • Excel’s camera and watch window
      • Navigating around an Excel model: speeding the whole thing up
      • Modelling with deliberate circular references: financial modelling on the dark side
The Excel techniques above are demonstrated in training and discussed with course delegates
  • Other helpful Excel financial modelling functions
    • Excel’s date functions
    • Rolling forward dates e.g. in a valuation model
    • Excel text functions
    • Modelling tax losses with max and min functions
    • Modelling the swing between overdraft/ revolver with max and min functions

Excel macros and VBA programming

Approximately 1/4 of the course is spent showing training delegates how to get started creating Excel Macros and how to move on to programming in VBA.

Getting started with Excel macros and VBA

During this part of the course delegates practise building a simple Excel macro and getting it to run.

Training course delegates move on to look at how VBA programming can be used to extend Excel’s core data manipulation capabilities.

Delegates are provided with a set of data that is hard to manipulate automatically using Excel’s usual functions. Instead, with the guidance of the tutor, delegates on the advanced course use VBA programming and looping to solve the problem.

Download the outline for the advanced financial modelling course

Here you can download the online advanced financial modelling course outline.