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:
- 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 labor and automate common tasks by building macros into your models
- Learn how to expedite and enhance the decision-making process
- Maximize 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
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.
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.
- 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.
من ينبغى أن يحضر
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