Advanced Financial Modelling in Excel

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.

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

• 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

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

• 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

• 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

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.

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.  
 

Danielle Stein Fairhurst, Principal Consultant at Plum Solutions,  is an MBA qualified business professional with many years' experience as a financial analyst.  She is the author of "Using Excel for Business Analysis: a Guide to Financial Modelling Fundamentals", Revised Edition, John Wiley & Sons, April 2015.   With her talent for financial modelling and professional approach, she helps her clients create meaningful financial models in the forms of business cases, pricing models and management reports. She has hands-on experience in a number of industry sectors, including telecoms, information systems, manufacturing and financial services.

Danielle is regularly engaged as a speaker, course facilitator, financial modelling consultant and analyst. She runs regular training seminars around Australia and globally, and her custom built training courses have been described by attendees as well-presented, neatly structured, informative, practical, and extremely relevant to their everyday needs. She is available for presentations, workshops, training courses, lectures and seminars. Topics include Financial Modelling in Excel, Budgeting & Forecasting and Data Analysis & Reporting.

She holds a Master of Business Administration (MBA) from Macquarie Graduate School of Management, and has taught management accounting subjects at Sydney University. Danielle lives in Sydney with her husband and two children, and enjoys the challenge of balancing a healthy family lifestyle with her passion for business.