Advanced Financial Modelling, Forecasting and Analysis

by Mr. Michael Rees

1-3 June 2016 | Singapore

 

Course Overview

This 3-day intensive course is based around a wide set of hands-on exercises that cover different aspects of building financial models and of analysing data. It is suitable for all analysts who already have some experience in working on such areas in Excel, and who wish to consolidate their knowledge, be exposed to new areas, and increase their competence in ensuring that they are approaching each situation from the appropriate angle. The exercises and practical examples move progressively from the foundations of good model design to applications of advanced Excel functionality; the use of VBA macros is also covered, and there is a review of the core aspects of most frequent modelling applications, such as valuation, financial statement modelling, project finance modelling, simulation, real options, and so on. There is tight integration between theory and practice, with all relevant financial theory or other concepts being explained at the point at which hands-on exercises are conducted.

 

The course covers tools, techniques and thought-processes to improve the flexibility, effectiveness and transparency of models and data analysis, whilst working more efficiently, including:

• Designing and aligning models with their decision-support role

• Techniques to conduct and automate sensitivity and scenario analysis

• Advanced Excel calculations

• Using Lookup and other functions to build dynamic and highly flexible models

• Best practices in function choice and their parameters; functions to avoid, functions to use only in specific circumstances; criteria for function selection and use

• Cleaning, analyzing and querying data sets using a variety of approaches, including filters, Pivot Tables and database functions

• Automating activities and working more effectively using VBA macros e.g. manipulating data sets, running sensitivities and resolving circularities

• Designing models with the right features, structure and layout; key questions to ask, tools and techniques to apply; tools to consolidate multi-data-set models

• Review of modelling issues in many core applications: cash flow valuation, financial statement modelling, project finance, Monte Carlo simulation, options and deal options modelling

• Around 50 hands-on exercises and demonstrated completed models

 

DETAILED AGENDA

DAY 1: FOUNDATIONS OF MODEL DESIGN

AM: SUPPORTING DECISIONS USING MODELLING AND SENSITIVITY ANALYSIS

Use of Models as Decision-Support Tools

• The modelling process

• Capturing decision-makers’ requirements and objectives

• Mapping requirements into model design

• Overview of best practice principles

Use of Sensitivity Techniques in Model Design

• Building complex formulae through sensitivity techniques

• Running automated sensitivity analysis

• Scenario analysis and optimisation techniques; creating scenarios, using GoalSeek and Solver; introduction to simulation techniques

Hands-on Exercises

• Creating and testing complex formulae using sensitivity techniques

• Running automatic sensitivity analysis on one or two input variables

• Conducting break-even analysis

• Running scenarios and variance analysis

• Optimizing portfolio composition, and other uses of optimisation methods

PM: ADVANCED CALCULATIONS IN EXCEL

Introduction to Selected Advanced Excel Functions

• Logical and conditional calculations in Excel

• Use of array formulae and array functions

• Statistical functions

• Mathematical and financial functions

• Selected issues and best practices in function and parameter choice

Hands-on Exercises

• Use of functions such as SUMIFS, SUBTOTAL, AGGREGATE in various contexts

• Use of array functions in cost allocation, depreciation calculations, finding first matching item in a conditional list

• Calculation of core statistical measures; regression and multiple regression; scatter plots, linear forecasting

• Calculation of confidence intervals for average and volatility estimates (including applications to calculating the cost of capital, measuring oil price volatility etc.)

• Calculations of correlations and rank correlations

• Non-linear curve fitting using optimisation

 

DAY 2: BUILDING FLEXIBLE MODELS AND MANIPULATING DATA

AM: MASTERING LOOKUP AND FURTHER FUNCTIONS

Introduction and Uses in a Variety of Modelling Applications

• Overview of Information, Date and Text functions

• Overview of Lookup functions

• Best practices in the selection of functions (incl. why to not use VLOOKUP etc.).

Hands-on Exercises

• Use of flag variables

• Finding the occurrence of an event

• Dealing with multi-currency datasets

• Consolidating, reversing, transposing data sets

• Creating time-shifting capabilities e.g. delaying planned production start

• Creating flexible, variable-sized and dynamic ranges for use within functions

• Creating models in which new data sets can be added or data sets can be deleted quickly and without major re-work; working flexibly with multi-sheet models

• Creating models which update as actuals are input

• Creating formula that handle acceptable errors or exceptions

• Detecting input data errors

• Creating dynamic labels and charts; updating cell labels and graph legends and titles

• Creating unique identifiers in order to match items in different dataset

• Planning the date of maintenance activities (e.g. last Friday in each month)

PM: DATA MANIPULATION AND ANALYSIS USING ADVANCED EXCEL

Introduction

• Excel Tables: definitions, benefits, advantages and limitations

• Database functions

• PivotTables and PivotTable analysis

• Comparison of possible approaches e.g. finding the conditional maximum using Excel arithmetic functions, database functions, or array functions

Hands-on exercises:

• Splitting data, extracting data, separating fields using Text-to-Columns menu (e.g. from internet download)

• Cleaning data and finding errors using filters, advanced filters, conditional formatting, find/replace etc.; Inspecting for integrity, uniqueness and duplicates

• Finding unique values and unique combinations

• Checking for data consistency with information and logical functions

• Splitting text and numerical fields using functions

• Combining databases by matching fields and creation of keys (after manipulation of field data)

• Summarizing daily production by month and year; report creation; calculation of days per model period

• (Demos) More complex applications, requiring integrated use of multiple advanced functions

• Automatic sorting of data sets

• Using database functions to run multi-criteria (or changing criteria) queries of databases

• Use of PivotTable filters, slicers, and time-line slicers

 

 

DAY 3: USING VBA MACROS, CORE MODELLING APPLICATIONS AND BEST PRACTICES

AM: USING VBA AND MACROS IN FINANCIAL MODELLING

Overview and Key Practical aspects of VBA and its Functionality

• Main uses and benefits

• Topics in syntax and controlling code execution: Use of With, Set, and conditional statements

• Debugging tools: Use of break points; running parts of code; stepping over other called routines etc.

• Best practices: Comments, indenting and formatting, data types, variable declaration etc.

• Introduction object orientation

Hands-on exercises:

• Simple recording example (e.g. code to copy ranges)

• Running code and stepping through line-by-line

• Adapting recorded code through direct writing of code (looping structures, named ranges, referring to ranges)

• Comparing copy/paste with assignment statements

• Creation of buttons to run code, and of simple Input and Message Boxes

• Using macros to run sensitivity and scenario analysis

• Recording and adapting the running of GoalSeek for breakeven analysis

• Running multiple database queries using a macro

• Demo of other applications: (e.g. multiple database queries, reversing items in a data sets automatic deletion of unwanted data, repeated extraction of specific elements, consolidation of multiple data sets into one)

PM: SELECTED CORE MODELLING APPLICATIONS AND BEST PRACTICES

Modelling Structures and Best Practices: Further Discussion

• Sensitivity and flexibility requirements; balancing flexibility with complexity; ensuring transparency

• Optimal model structures. The selection of data architectures; modular versus global data architectures, optimizing layout (e.g. generic structure for input and calculation areas, and their respective benefits)

• Formulae-dominated versus data-dominated modelling approaches

• Tools to consolidate multi-sheet models

• Modelling with circular references: Their nature, type, ways how to deal with them

• Other best practices (e.g. use of named ranges, multi-sheet models, linked workbooks, formatting, conditional and custom formatting, auditing, checking for integrity and errors etc.); Selected short-cuts

Review of Key Aspects of Common Application Areas

• Economic principles: Time value of money, net present value, internal rate of return, sunk cost, cost of capital

• Cash flow valuation: Key modelling issues, including calculation of beta and confidence intervals

• Financial statement models: Selected key modelling issues, including tips and techniques to ensure integrity of model (balance sheet that balances), options to deal with circular references, etc.

• Project finance (selected areas: calculations of core coverage ratios, debt capacities, use of circular references)

• Simulation modelling, real options modelling and valuation

Hands-on exercises and Demo of Completed Models (selected based on available time and relevance)

• Sensitivity analysis of NPV and IRR calculations to project delays

• Modelling debt repayment profiles; use of corkscrew structures

• Using VBA to achieve circular logic without circular formulae

• Consolidation of multiple data sets using Excel; demo on possible approaches using VBA macros

• Using VBA for simulation modelling; demo of Excel add-in to facilitate more complex models

• Other selected modelling topics, and demo of other simple completed models

 

About the Trainer

Dr. Michael Rees provides quantitative support for major decisions. He deals with important issues in business economics, valuation and strategy, focussing on the application of financial modelling and risk modelling techniques to address these. His activities in these areas include training, consulting, book-writing, the development of customised software, and acting as an expert witness on specific topics.

 

Michael combines practical experience from top strategy consulting and investment banking firms with advanced quantitative analytic skills, an exceptional academic record, and has a number of publications to his name.

 

Michael has a Doctorate in Mathematical Modelling and Numerical Algorithms, and a B.A. with First Class Honours in Mathematics, both from Oxford University in the UK. He has an MBA with Distinction from INSEAD in France. He also studied for the Certificate of Quantitative Finance, graduating top of the class for course work, and receiving the Wilmott Award for the highest final exam mark.

 

He has over 25 years’ business and finance experience, and has worked independently since mid-2002. Prior to being independent, he worked as a Vice-President at J.P. Morgan (conducting equity valuations, publishing reports and advising fund managers and hedge funds; he was ranked as a top City analyst by all the companies under his direct coverage and received a vote in the Institutional Investor 2002 survey). Until 2000, he was a Partner (Principal) at Mercer Management Consulting (now Oliver Wyman), where he advised clients on issues of strategy, organization and change (including market and competitive analysis, partner and acquisition assessments, performance measurement and improvement, cost reduction, outsourcing, process redesign, restructuring and change management).

 

Michael is the author of Business Risk and Simulation Modelling in Practice: Using Excel, VBA and @RISK (John Wiley & Sons, 2015), of Financial Modelling in Practice: A Concise Guide for Intermediate and Advanced Level (John Wiley & Sons, 2008). He is also a contributing author in The Strategic CFO: Creating Value in a Dynamic Market Environment (Springer, 2012), and has written articles in the Wilmott Magazine (2004, 2005). He is an Associate Fellow of the Institute of Mathematics and its Applications, and is regularly invited to speak at conferences (e.g. SMI conferences in 2009 and 2010, The Danish Risk Management Conference 2011).

 

Investmetment Fee

SG$ 2,985.00 per person