SPECIALISTS IN FINANCIAL MODELLING
0 item(s) $0.00 AUD + GST

Advanced Financial Modelling in Excel

Date: Monday, 15 May 2017 - 9:00am - Tuesday, 16 May 2017 - 5:00pm

Location: Kuala Lumpur

Duration: 2 Days

In the ever-changing business environment, being able to accurately model and forecast the volatile economic inputs is a critical skill for business professionals. Build on your existing modelling skills and apply new techniques to better analyse financial data, predict revenues and cost and assess risks to justify business decision in the most time-efficient and effective way. 

This course is run by Danielle Stein Fairhurst and hosted by an external provider. 

Please register here and we will send you some further information.

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.

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

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
  • Deal with risk and uncertainty by creating scenarios and performing sensitivity analysis
  • Build stress-tests, what-if and sensitivity analysis into your model by incorporating economic fluctuations
  • Minimise manual labour and automate common tasks by building macros into your models
  • Measure and interpret the performance of your company using Excel modelling
  • 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 
  • Translate business concepts into logically structured models and formats
  • Select which advanced formulas are appropriate in different situations
  • Perform various advanced scenario modelling techniques

Course Content

Day 1: Financial Modelling Techniques, Theory & Model Design

Financial Modelling Best Practice

  • Excel Versions; upgrading to Excel 2013 and technical differences between versions. Considerations when building a model for users of different versions.
  • Overview of best practice financial modelling techniques
  • Model planning structure and steps in building your model
  • The technical, design, business and industry knowledge required for financial modelling
  • Selecting the formula or tool which is most appropriate for each modelling situation
  • 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.

Advanced Financial Modelling Techniques

  • Maintaining model integrity and avoiding Excel errors; Techniques to employ during the model building process to maintain model integrity and reduce the potential for formula or logic error
  • Building self-balancing error checks
  • Correcting  and suppressing errors
  • Fixing circular references
  • Goal seeking to calculate break-even point
  • Create a more efficient model with internal links
  • Dealing with external links and the potential errors they cause

Advanced Financial Modelling Tools and Functions

  • Pros and cons of using array functions
  • Useful functions in financial modelling such as INDEX, MATCH, CHOOSE , OFFSET, LOOKUP and TRANSPOSE
  • Nesting formulas
  • Tiering Tables; practical application of one of the more complex and widely used calculations in financial modelling, such as tax calculations and volume break discounting
  • Automate your financial model for the user with Macros.  Recording and modifying VBA code and creating macro buttons
  • Pivot tables – summarise, dissect and analyse large amounts of data
  • Shortcuts for data manipulation in Excel
  • Waterfall Charts; creating a simple dynamic waterfall chart with up/down bars

Day 2: Practical Financial Modelling

Bullet-proofing Your Model

  • Protect your model so that it can be used by anyone with a limited use of Excel
  • Locking and protecting cells
  • Restriction incorrect data entry with data validations
  • Creating error validation messages
  • Use of form controls such as spin buttons and combo boxes

Stress testing to deal with uncertainty and risk in Financial Modelling

  • Economic inputs to model and modelling fluctuations in external factors
  • Use of stress testing to validate your underlying assumptions and risk calculations
  • Advanced What-if analysis
  • Importance of assumptions when assessing risk

Scenarios and sensitivity analysis

Learn different methods of what-if and scenario analysis in Excel using:

  • Show multiple scenario outcomes simultaneously with one and two-dimensional  Data Tables
  • Using the Scenario Manager
  • Manual scenario building
  • In-cell drop-down boxes
  • Combo-box drop down boxes

Practical Exercise:  At each step during the course, participants build and practice each formula, tool and technique. Record your own macro with buttons, build a pivot table, and create drop-down boxes.

Practical Financial Modelling

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

Factory Rollout Model

Working on a startup funding model from start to finish we will evaluate input assumptions, calculate revenue and costs of production to calculate cash requirements.  From this, we build a full set of financial statements with particular emphasis on linking the profit & loss statement, cash flow statement and balance sheet together.  

Practical Exercises:  Utilising the tools and techniques covered in the first day, participants will build their own financial model case study, performing stress testing, scenario and sensitivity analysis on their model.

Software used

The course is demonstrated using the latest version of Excel, 2013 but participants can use whichever version of Excel they are most comfortable with.  The course materials include additional instructions for prior versions of Excel, and Excel for Mac users are also catered for.  Each delegate must bring their own laptop with their preferred version of Excel installed.

Prerequisites

The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheet users. It is designed for users who do use (or will use) Excel on a regular basis, and are comfortable with using its tools and functions.

At minimum, it is assumed that participants will know how to:

  • Navigate confidently in Excel
  • Use absolute cell references (e.g. =$A$1) 
  • Create and use Excel functions on a regular basis
This course builds on students' advanced knowledge of Excel tools and functions and incorporates these into complex and dynamic financial models.

Comments from past students 

  • The course was a good mix of theory & practice, with a good coverage of topics.  The presenter had a good knowledge, was easy to understand and follow and the venue was great.
  • This was a jam-packed course with practical content and very well-presented.
  • Evaluating this course is one of the few times I could tick "strongly agree" on all the boxes!
  • “The presenter did a great job of teaching the subject matter in a clear and easy to understand way. Even for a seasoned Excel user, I learnt new easier techniques from the initial lesson. I was able to revamp my models at work by the second lesson.”
  • “The consultant has a rare combination of expertise, understanding and skill in delivering programmes tailored around advanced business modelling and analysis techniques. This course took what I understood as "reasonably advanced" on-the-job learning to an impressively higher capability.”
  • “I liked both the information provided during the course, as well as the additional information that can be utilised once the course was over.  The Presenter's in-depth knowledge of Excel and the ability to keep the flow of the class going whilst answering individual questions.”
  • “The course was well-paced and interactive, and contained easy to follow and well thought-out options.  The presenter was excellent - very helpful and approachable.”
  • “The consultants knowledge and expertise with excel is outstanding. Her patience with the class showed years of experience in providing professional training. The book has already been a very handy addition in helping deliver financial modelling to senior management.”

This course is being hosted by an external provider.  Please register here and we will send you some further information.