Back to Courses

Advanced Financial Modeling, Forecasting & Analysis

ENDED

Training by Customized Training Solutions (CTS) Pte. Ltd.

Inquire Now

On-Site / Training

SGD 2,985.00

Inquire Book Now

RESPONSE RATE

Details
Outline
About the Provider

Book Now

Inquire

Details

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

Outline

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

Speaker/s

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).

Special Offer

Early bird: Less SG$100 for registration on or before 20th April 2016

Group discount: 10% discount for 3 or more participants

Reviews

Be the first to write a review about this course.

Write a Review

Customized Training Solutions is Asia's leading events services focused on training, conferences and other methods possible on our goal to providing effective and practical learning solutions to enhance your business’ most valuable assets, your employees.

We believe that people are the greatest assets and we are committed to their professional growth. All our training are built to fit according to organization specifications and needs to ensure that these will provide impact and solutions according to their objective.

Customized Training Solutions has been built to provide services to companies, as we understand the importance of education and training for overall business success.

We achieve this as we work in partnership with both clients and trainers to be able to deliver and meet both expectations and goals.

View Complete Profile

Accounting Financial Management Financial Management