Outlines

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 cash flows 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 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.  

Who Should Attend

 

This course is suitable to a wide range of professionals but will greatly benefit:

 

• Professionals who need to develop a greater understanding of finance
• Professionals who need to develop their advanced financial modelling skills
• Those who make decisions using financial models or are impacted by those decisions
• Those responsible for managing finance
• Those responsible for estimating sales, costs, risks and economic variables

 

Duration

5 Days