LEARNING OBJECTIVES

During the course, participants will hone their advanced Modelling skills and experience designing solutions to real-world examples.  Learn to efficiently build an effective and robust financial model based on real-world financial Modelling challenges. Incorporate current economic uncertainties into the model in order to take calculated risks and make more informed business decisions. Financial Modeling & Advanced valuation enables professionals to handle finance models by applying advanced level tools and applications of Excel. Financial Modeling is the task of building a model representing financial asset and performance of a business. Financial Modeling skills are must for finance and non-finance professionals especially for almost all mid-senior management professionals worldwide. The added skills gained through Excel training helps these professionals to gain an in-depth understanding of financial modeling techniques to succeed in today’s demanding environment.

In addition, you will learn how to:                                   

• Streamline model building by applying best practice functions, tools and techniques
• Apply commonly used formulas in new and different ways
• Quantify uncertainty by applying various methods of  scenarios and sensitivity analysis
• Minimize manual labour and automate common tasks by building macros into your models
• Learn how to expedite and enhance the decision-making process
• Maximise your analytical abilities by learning conventional and new approaches of modelling
• Translate business concepts into logically structured models and formats
• Learn to rebuild, audit and validate an inherited model
• This course builds on trainees’ advanced knowledge of Excel tools and functions and incorporates these into a financial model.
• Build stress-tests, what-if and sensitivity analysis into your model by incorporating economic fluctuations
• Measure and interpret the performance of your company using Excel modelling
• To enable the participants to gain acumen, expertise and in-depth knowledge on various methods of valuation and therefore Master key steps to get the most out of Excel functionality to improve your financial modelling
• Learn how to expedite and enhance the decision-making process
• Maximise your analytical abilities by learning conventional and new approaches of modelling
• Select which advanced formulas are appropriate in different situations

COURSE CONTENT

(A)       Theory and Best Practice

• Excel Versions

Upgrading to Excel 2016 and technical differences between versions. Considerations when building a model for users of different versions

• Cell Referencing & Named Ranges

Applying absolute and relative cell referencing and understanding its importance in Financial Modelling.  Using named ranges for assumptions reference

• Best Practice in Financial Modelling

Overview of the six points of financial modelling best practice

• Strategies for Reducing Errors

Techniques to employ during the model building process to maintain model integrity and reduce the potential for formula or logic error

• Excel Errors

Common errors in modelling. Correcting and suppressing errors.  Locating and dealing with circular references.

 (B)      Advanced Tools and Functions

• Lookup Functions

Going further than a VLOOKUP.  Nesting Lookup functions with COLUMN or MATCH and using close match.

• INDEX/MATCH
  Quick ways to nest an INDEX and MATCH function as an alternative to LOOKUPs
• OFFSET Function

Staggering start dates, and manipulating cash flows with OFFSET

• Array Formulas

Pros and cons of using array formulas and their use in financial modelling.  Using TRANSPOSE

(C)       Rebuilding an Inherited Model

• How long should a formula be?  

Deciding when to break a complex formula in several steps to maintain transparency and allowing ease of model auditing

• Audit Tools

Summary of commonly-using audit tools to take apart others’ models

• What Makes a Good Model?

Attributes of a good model such as user-friendly and structural features

• What Makes Poor Model?

Rebuilding an Inherited Model. Critique of a poorly built financial model using model audit tools and identify why it does not follow financial modelling best practice.

(D)      Advanced Techniques

• Pivot Tables in Financial Modelling

Pros and cons of the relevance of Pivot Tables in financial modelling

• Macros in Financial Modelling

Pros and cons of automating your financial model for the user with Macros

• Waterfall Charts

Creating a simple dynamic waterfall chart with up/down bars

(E)       Scenarios and Sensitivity Analysis

• Overview of Scenario Analysis Methods

Technical methods of creating scenario and sensitivity analysis in Excel

• Data Tables

Show multiple scenario outcomes simultaneously with one and two-dimensional Data Tables

• Advanced Conditional Formatting

Learn how to automatically highlight selected scenarios using complex conditional formatting

• Using the Scenario Manager

Brief overview of how to use the scenario manager in comparison to other scenario tools

• Comparison of Scenario Methods

Practical exercise; build a property development model using three different scenario analysis methods; an in-cell drop-down box, a combo drop-down box and a data table

Practical Financial Modelling

Building on the tools and techniques covered, we will learn to apply advanced modelling skills to build a complex, yet robust and user-friendly financial model.

THE TRAINING OUTCOMES

During the course, participants will hone their advanced modelling skills and experience designing solutions to real-world examples. Participants will learn to efficiently build an effective and robust financial model based on real-world financial modelling challenges. They will incorporate current economic uncertainties into the model in order to take calculated risks and make more informed business decisions.

Short-cuts and techniques to build a financial model in less time

Become super-efficient in Excel through intensive use of keyboard shortcuts and best practices to efficiently build an effective and robust model.

Experience designing solutions to real-world examples

Create a financial model based on real-world financial modelling challenges.  Incorporate current economic uncertainties into the model in order to take more calculated risks and make more informed business decisions.

Analytical thought on fluctuating economic assumptions in financial modelling

Identify correct economic inputs to model and deal with fluctuations. Quantify uncertainty in forecasting models with stress-testing, what-if analysis and risk analysis techniques.

Knowledge on how to deal with uncertainty and risk in financial modelling

Explore the several different ways to perform scenario and sensitivity analysis in Excel and learn to model in detail the complex but most commonly used methods of showing scenarios using a combination of formulas and drop-down boxes.

WHO SHOULD ATTEND?

This course is designed for analysts and financial practitioners who want to use Excel as a tool to assist with the decision making process and is highly relevant to:

• Financial Analysts
• Business Analysts
• Budgeting and Forecasting Managers
• Accountants
• Corporate treasury Finance Managers
• Business and Strategic Planners
• Credit and Risk Managers
• And any professionals that are involved in/interested to use Excel as a tool to assist with the decision making process
• Financial controllers, managers and modellers
• Risk Managers
• Chartered Accountants
• Corporate treasury managers
• Middle office staff
• General Managers
• Fund Managers 

After completing the course participant should be able to demonstrate robust theoretical knowledge and sound practical approach to financial modeling.

WHAT ARE THE PRE-REQUISITES FOR THIS COURSE?

• Familiarity with Excel – functions and tools
• Understanding basic accounting concepts
• Basic knowledge of valuation, project finance, return concepts