|
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 – 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.
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 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
information@premiercs.co.uk
|
