Whitepaper on scenario analysis Download our free whitepaper

Financial Modelling


Workshop overview

Financial modelling requires a broad skill set: Accounting, finance, spreadsheet, design, presentation and communication skills are all used. This workshop brings together those skills and shows how they can be applied to produce best-practice financial models.

Why you should attend

You will gain skills in producing models that are efficient, user-friendly, easy to adapt and feature-rich. Your models will generate key insights and support for analysis, reporting, decision-making and management.

  • Key takeaways
  • Who should attend
  • Content
  • Facilitator
  • Register

Key takeaways are:

  • Learn the objectives, principles and methods of financial modelling
  • Gain expertise in spreadsheet functions and tools used to build efficient and well-presented financial models
  • Master and apply the essential finance principles underlying the key concepts of value, risk and return
  • Discover techniques to make models easily extended, maintained and adapted
  • Develop sophisticated "what-if" and scenario analyses
  • Build clearly understandable, "bullet-proof" and user-friendly models

The following roles will benefit from the workshop:

  • Financial controllers
  • Commercial, financial, investment, planning, risk and systems analysts
  • Management, operations, commercial and financial accountants
  • Performance and planning, reporting and analysis, capital and funding, treasury, project and business and support managers
  • Principal advisors
  • Chief financial officers

Course content

Following is a description of the course content.

Introduction to course utilities

A range of course utilities is provided to maximise participants’ learning. The course utilities are introduced in this section:

  • Automatic feedback - continuous visual feedback is available to guide participants as to whether their models are correct or not.
  • Hints - workshop spreadsheets can give “hints” as to which sections in a model are most relevant in developing later sections.
  • Answer loading. A mix of learning methods are used in the workshop: Participants develop some models from scratch - other models are supplied already-built. Answer loading is used when examining already-built models.

Key financial functions - underlying finance concepts - applications

Key financial functions used in modelling are discussed, their financial bases examined and examples given of their applications. The following functions are covered:

  • PMT, PPMT, IPMT, PV,FV, RATE & NPER functions

Case study 1: Reserving for a future need. A policy is modelled in which commitments to reserve for a future need are consistent over time. Then a more complex - tailored - profile of commitments is modelled. This study illustrates linkages between present value, future value and discount rate.

  • NPV, XNPV, IRR & XIRR functions

Case study 2: Project decision - go or no go. This case study examines the effect of discount rate on NPV and IRR-based decisions as to whether a potential project is worth proceeding with. The case study illustrates linkages between NPV, IRR, discount rate and hurdle rate.


Case study 3: Modelling IRR targets. This case study models various policies splitting obligations and benefits between project partners in order to achieve mutual IRR targets. The case study is a further illustration of the linkages between NPV, IRR, discount rate and hurdle rate.

Two other classes of financial functions are discussed. These classes are required for modelling work later in the workshop. The functions are:

  • EFFECT & NOMINAL functions
  • Annuity and terminal value functions

Essential spreadsheet functions - and applications

Key non-financial spreadsheet functions are also useful in modelling. Those classes are covered in this section. Two classes described are the following:

  • Arithmetic functions
  • Logical functions

Case study 4: Analysis of improving a portfolio of assets. A model is constructed of the timings and magnitudes of the costs and benefits involved in improving a portfolio of assets. This case study is again referred to in the workshop in valuation, breakeven, scenario and optimization studies.

These other important classes of spreadsheet functions are then examined and applications shown:

  • Referencing functions - VLOOKUP, LOOKUP, MATCH, INDEX & OFFSET
  • Aggregation functions - COUNTIF, COUNTIFS, SUMIF, SUMIFS & SUMPRODUCT
  • Array functions
  • Date functions
  • Error handling functions

Charting

Charts can often show trends, patterns and anomalies better than can raw numbers. Charts are also useful to show the “big-picture”. This section shows various types of charts used in modelling and applications of each. Topics discussed are:

  • Basic charts
  • Charts with two vertical axes
  • Charts with dynamic labels and titles
  • Charts with dynamic number of horizontal points
  • Charts with trend-lines
  • Curve-fitting
  • Waterfall charts
  • Using charts to discern and find errors and anomalies

Best-practice principles

Best-practice principles are described in this section. Each example and case study later in the workshop is designed to give participants practice in one or more of these principles.

Case study 5: Business growth model. A model of business growth is developed in which these best-practice principles are illustrated and applied: Minimising complexity of individual components, minimising hard-coding, separation of model types, ensuring logical flow and thinking to the future.

Model errors can be subtle and may be triggered only for some inputs and not for others. So it is important to rigorously check for errors. A best-practice technique is to incorporate error-checking into the model so that checking is continuous and automatic.

Case study 6: Building a robust model - incorporating error-checking. This case study shows techniques of adding error checks to models and illustrates the benefits of doing that.

When models evolve through their life-cycle their integrity and consistency tends to diminish. To mitigate against that another best-practice technique is to seek consistency whenever possible.

Case study 7: Achieving consistency. One of the challenges in financial modelling can be to achieve consistency in formulae (e.g. having formulae that are “copy-and-paste-able”). This case study gives a set of challenging problems and shows how consistency can be achieved.

Scenario Analysis

Scenario and “what-if” analysis is often an important part of financial modelling. This section introduces a spreadsheet feature - the data table - that is very useful in performing scenario analysis. Various types of scenario analysis are then discussed and applied. Topics in this section are:

  • Data tables - what they are and how they can be used
  • Definitions of and differences between scenario analysis, sensitivity analysis and attribution analysis
  • Adding multiple scenarios to a model
  • Adding sensitivity analysis to a model
  • Adding waterfall / attribution analysis to a model and constructing waterfall charts
  • Advanced waterfall topics - filtering out zero contributors, how to achieve waterfall charts that cross the zero axis

Case study 8: Adding scenario analyses to an already-existing model. An already-existing model is extended by adding a comprehensive set of sensitivity, scenario and attribution analyses.

Goal-seek and Solver are other tools available in spreadsheets for what-if analysis. Both Goal-Seek and Solver are described and their respective advantages and disadvantages discussed. Problems showing the applications of the tools are then described.

Case study 9: Using the solver to find the optimal mix of resources to maximise a defined benefit. Extensions of this case study then look at more complex optimizations that include additional constraints that must be satisfied.

About the facilitator

This course's facilitator is Alex Palfi. Before joining Tykoh, Alex had ten years of experience with Macquarie Group in quantitative finance and in presenting workshops internationally. He has presented courses in financial modelling, financial mathematics, financial programming, valuation, and option pricing and risk management in Europe, North and South America, Africa, Asia and in Australia & New Zealand.

Alex has worked in Investment banking, commercial banking and software development industries. Alex's university qualifications are in engineering. He obtained Bachelors and Masters degrees in Electrical Engineering from the University of Canterbury in New Zealand.

Register

You can register your interest for any of our courses by selecting the "Register" menu item at the top of this page.

Survey

Click here to see the results of our survey on financial modeling

Competition

We are hosting a financial modeling quiz / competition. The live leader board is below.

LeaderPercentAt
NPL73.9%
Dan6904.9%
saxons2.3%
bondi0.1%

To enter the competition click here.

Video

To see a video of how to do the quiz above click here.

Self assessment

You can do an online self assessment of your financial modeling skills. Click here.

Webinar

We provide this workshop in classroom and webinar formats. Click here for information about webinars.