FINANCIAL MODELLING USING EXCEL TRAINING COURSE  DURATION 3
DAYS
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 – financial mathematics will be used
to indicate how the pricing operates (stochastic processes, regression,
probability concepts, etc).
The major cause for concern in financial markets – risk –
will also be examined. What risks are involved with equities? Bonds?
How are these risks similar, how are they different? How is risk measured?
Derivatives can be highly risky instruments, and these risk – or
the socalled "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 indepth, 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 measurement 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 meanvariance 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.
Methodology
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 realitychecking 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 notsosimple
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 exponential 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 notsosimple 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 untrained 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 techniques, 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 hitech learning environment in
London, near Liverpool Street Station.
Back to top 
Financial Modelling using Excel
Course Venue: London EC2
information@premiercs.co.uk
