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

Advanced Financial Modelling in Excel

Course info

Duration : 1 day

Cost: $990.00 inc. GST.

Testimonials

"I would strongly recommend Plum Solutions financial modelling training for analysts of all types and skill levels. Well-developed, clearly structured and with loads of additional resources and support. The presenter has successfully mixed the technical aspects of modelling with clear explanations and examples. Great value for money!Online Financial Modelling in Excel Bundle, November 2015View this testimonial on Linked In"

View testimonials for this course

Download the course outline

Upcoming workshops

Take this course in-house

Online Courses

Can't make it to a workshop? Take this course online and start straight away!

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 part of the Financial Modelling Series:

Each participant on this course receives a hard copy of the book Using Excel for Business Analysis.  Reusing your copy or want to do multiple courses?  We have bulk booking discounts, or get the full suite of all three Financial Modelling courses at the discounted price of $1,800 incl GST.

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 Excel on a regular basis, and are comfortable with using its basic 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 advanced Excel tools and functions on a regular basis

Software used

Public courses are run in a computer laboratory with workstations provided. Participants may bring their own laptops if they prefer.   The course is demonstrated using Microsoft Excel 2016, but participants can use Excel 2007, 2010 or 2013 if they prefer.  Course materials contain instructions for different versions, and Excel 2011 for Mac users are also catered for. Those who would like to use a Mac are most welcome to bring their own laptops.

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.

In addition, you will learn how to:

  1. Streamline model building by applying best practice functions, tools and techniques
  2. Apply commonly used formulas in new and different ways
  3. Quantify uncertainty by applying various methods of  scenarios and sensitivity analysis
  4. Minimise manual labour and automate common tasks by building macros into your models
  5. Learn how to expedite and enhance the decision-making process 
  6. Maximise your analytical abilities by learning conventional and new approaches of modelling 
  7. Translate business concepts into logically structured models and formats
  8. Learn to rebuild, audit and validate an inherited model

 

This course builds on students' advanced knowledge of Excel tools and functions and incorporates these into a financial model.  Designed for experienced modellers, this course is recommended for those who have undertaken parts 1 and 2 of the three-part Financial Modelling series.

Course Content

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.

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 cashflows with OFFSET
  • Array Formulas
    Pros and cons of using array formulas and their use in financial modelling.  Using TRANSPOSE.
  • Form Controls
    Use of form controls such as spin buttons and combo boxes

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.

Advanced Techniques

  • Escalation Methods
    Comparison of different methods of indexation (or escalation or grow rate) and applying compounding inflation
  • 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

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.

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.  
 

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