Premier Home Page

Home | Training | Solutions |Contact Us

t: +44(0)20 7729 1811

About Us | IT Training | Financial Training | Management Training | Course Dates | Delegate Information

Financial Modelling using Excel

An intensive and highly practical 3 day course designed to equip managers, quantitative analysts and risk professionals with a sound, thorough understanding of spreadsheet financial modelling techniques and skills. This course offers insights into the interesting world of financial mathematics from the dual perspectives of risk and return.


Course Overview
The primary reason for interest in financial markets is undeniably the search for returns. Returns are themselves derived directly from prices, thus pricing simple instruments such as equities and fixed income products (bonds) is an important aspect of financial modelling. More complex instruments, such as vanilla equity-, fixed income- and credit derivatives will also be explored. At each step – fi-nancial mathematics will be used to indicate how the pricing operates (stochastic processes, regres-sion, probability concepts, etc).

The major cause for concern in financial markets – risk – will also be examined. What risks are in-volved with equities? Bonds? How are these risks similar, how are they different? How is risk meas-ured? Derivatives can be highly risky instruments, and these risk – or the so-called "Greeks" – will also be examined in detail.

The course is highly practical: delegates will be exposed to examples (in Microsoft Excel) throughout the 4 days. Note that this course aims to cover a very wide field in a relatively short time and will thus provide more of an overview (with examples) than an in-depth, highly technical course.

Learning Objectives

  • Master the use of Excel’s financial modelling tools and capabilities
  • Understand sensitivity testing
  • Explore investment return appraisal tools and techniques
  • Practise designing and structuring models
  • Investigate the use of macros and basic VBA tools
  • Discover portfolio analysis
  • Learn stochastic (Monte Carlo) simulation tools
  • Understand the dual importance of risk and return and acknowledge that these are the key drivers of interest in financial markets – all else flows from these two concepts
  • Understand the basics of probability theory and statistical theory (necessary for risk meas-urement and management)
  • Understand volatility and correlation and comprehend the importance of these measures in the estimation of risk and return
  • Price vanilla instruments (fixed income, equity, simple derivative products)
  • Understand the mean-variance framework and its importance in modern portfolio theory
  • Calculate the risks associated with various instruments (credit, market, operational)
  • Understand regression and its importance in modern finance
  • Manipulate data in order to extract relevant, important financial information..

Who Should Attend?

Each delegate should have a good working knowledge of laptop computers with Microsoft Excel (preferably Office 2003. Office 2007 is very different from previous versions and having both versions in use during this course effectively doubles the work) installed and bring their own computer, or have one available per delegate at the training venue.

The course is highly interactive, and formal lecture content is kept to a minimum. Modelling techniques are explained and demonstrated by using extensive case studies, adhering closely to real life examples. Throughout the programme emphasis is laid on the importance of reality-checking and the dangers of spreadsheet blindness, on a fundamental understanding of key issues and on the skills of communicating conclusions from modelling techniques to colleagues and financial partners.
Delegates will be expected to be highly participative and motivated to learn, and to express their needs and priorities clearly.

Course Content

Day One
Session 1: Basic spreadsheet tools and techniques:

  • Worksheets and workbooks
  • Cells, formulae and calculations
  • Filing, naming and protection conventions and disciplines
  • Formatting and presentation techniques
  • Financial functions
  • Statistical functions
  • Look Up functions
  • Database functions
  • Delegates will consolidate their understanding of Excel tools with a series of practical exercises.

An overview of the dual concepts of risk and return

  • A (very) brief history of financial mathematics
  • The importance of financial mathematics in today's marketplace
  • Necessary evils in the financial mathematics toolbox
  • The two BIG ideas in finance – risk and return
  • Calculus – the greatest invention since the wheel
  • Understanding the yield curve
  • Simple discounted cash flows for equities and bonds, construction of yield curves, basic functioning of risk, simple and not-so-simple calculus applications

Session 2: Trend Analysis

  • Identifying patterns
  • Seasonality and Irregularities
  • Trends
  • Using charts
  • Time series analysis
  • Basic regression analysis
  • Delegates will analyse a series of historic sales figures and draw conclusions about the implications when preparing sales forecasts.

Session 3: Financial Planning and Forecasting

  • The art of forecasting – Extrapolation and Assumptions
  • Bottom up forecasting
  • Making budgets work
  • Allowing for reality
  • Delegates will complete a fully functioning, more complex 3 year financial forecast

Session 4: Reminder of mathematical concepts

  • Maths you might have forgotten but now need to remember including exponential functions, logarithms, Taylor Series expansion, calculus again, polynomials, matrix theory, eigenvalues, eigenvectors
  • Statistics explained in English
  • What is “statistics” – why is the study of it so important?
  • How can they help us – how are they applied in modern finance?
  • Matrix manipulation and applications, Taylor Series examples, applications of logarithmic and expo-nential functions, basic statistics and how to call them up and apply them using Excel

Day Two
Session 5: Time value of money

  • Understanding perhaps one of the most important ideas in financial mathematics
  • Interest calculations – how do they work?
  • Complex interest rate calculations
  • Can we treat interest rates in a simpler way?
  • Simple and not-so-simple interest rate calculations, the beginnings of how to price bonds and fixed income instruments

Session 6: Probability – what's it all about?

  • Probability concepts
  • The idea of the law of large numbers
  • Understanding probability
  • Understanding and applying all kinds of distributions (including Binomial, Poisson, Lognormal, Gaussian, Exponential, Weibull distributions…)
  • Some probability examples and applications of how these are used in modern finance

Session 7: Fixed income valuation

  • How are fixed income instruments priced?
  • What is the inverse price law? Why is it important?
  • Understanding bonds and the way they work in practice – coupons and notional values
  • The beginnings of credit derivatives – how these are similar and different to bonds
  • Bond pricing calculation, very simple pricing of credit default swaps and other credit derivatives

Day Three
Session 8: More advanced Excel techniques

  • Data validation
  • Protection and input screening
  • Consolidation
  • Graphs and charts
  • External data sources
  • Delegates will develop a robust, error protected spreadsheet with user friendly inputs for use by un-trained users.

Session 9: Regression, correlation, ANOVAs

  • Why would we WANT to regress anything?
  • What does regression tell us and how is it done?
  • Understanding the normal distribution's relationship with regression, how to do a simple regression and a multiple regression in Excel, what the values tell us, applications, correlation examples, deriving valuable information from ANOVA tables

Session 10: Risk

  • Why is risk so important?
  • What measure do we use for risk? Why is risk so different from return?
  • Risk – it's all in the volatility and correlation, how are these quantities measured? Of the many ways to measure these, what are the optimal ways?
  • The covariance matrix – how does it work and what are the applications of the covariance matrix in finance?
  • Using the variance covariance matrix in practice, measuring volatility and correlation by various tech-niques, benefits of each method and drawback of each method, what about interest rate volatility?

Session 11: Fixed income risk

  • What is fixed income risk?
  • How is it measured?
  • What are duration and convexity measures? Why are they so important?
  • Calculating convexity and duration, application of how duration and convexity are used in practice

Session 13: Excel Macros

  • The power of macros
  • Building basic macros
  • The macro recorder
  • Editing macros
  • Error testing and stepping
  • Delegates will prepare a series of macros. These will then be swapped between delegates and error tested.

Session 12: Derivatives

  • What’s a derivative and how do they function?
  • Why the need for statistics and calculus?
  • Some simple reminders of calculus (differentiation and integration)
  • The Black and Scholes formula – what's the big deal?
  • What do we use for interest rate derivatives – i.e. when Black and Scholes fails?
  • Pricing a simple derivative using binomial theory and Black and Scholes, how the Greeks work, how these may be calculated in Excel, how to run simulations in Excel

Session 14: Monte Carlo Simulation

  • Stochastic techniques
  • The use of random numbers in modelling risk
  • Probability distributions
  • Developing a basic simulation
  • Running and interpreting results
  • Delegates will use a Monte Carlo simulation to analyse the risk levels entailed in a proposed project.

State of the art facilities
Delegates can study in our comfortable hi-tech learning environment in London, near Liverpool Street Station.

Back to top

Financial Modelling using Excel

Course Venue: London EC2