Sensitivity Analysis in Excel (Financial Modelling) and Advanced Excel Formulas
Sensitivity Analysis in Excel (Financial Modelling) and Advanced Excel Formulas
Course description part 1 – Sensitivity Analysis in Excel (Financial Modelling)
Learn to perform a sensitivity analysis in Excel with this module on advanced financial modelling. The material focusses on practical applications suited to professionals working in investment banking, equity research, financial planning & analysis (FP&A) and other finance related areas. Based on many years of practical experience in performing model sensitivity analysis, this module has been designed as a step-by-step guide using real life templates and examples.
‘A formula to be reckoned with’
This course offers a blended learning experience that combines training expertise of international experts with hands-on knowledge and experience of a local facilitator
Direct and indirect methods
This course module uses direct and indirect methods in measuring financial sensitivity in line with both operating and financial stresses and our sensitivity analysis template can be downloaded and used as your own example upon completion of the module.
Content and overview
- Introduction
- Why perform a sensitivity analysis?
- Financial Model integration – direct and indirect methods
- Analysing results
- Gravity sort table
- Tornado charts
- Presenting results
Critical elements
Form and function are critical elements that enable you to perform rapid changes and information requests when working on a live transaction. Upon completion of this module you will have gained an in-depth understanding of how to build a comprehensive sensitivity analysis system into your Excel financial model.
Module curriculum
- Module overview
Overview of final product
- Introduction
Why perform sensitivity analysis?
Downloadable files
- Model Integration
Overview
Direct Method
Setting Up the Data Table
Linking the Data Table
Indirect Method
Setting Up the Data Tables
Quiz
Linking the Data Tables
- Analysing and presenting results
Overview
Gravity Sort Overview
Gravity Sort Linkup
Tornado Chart Overview
Tornado Chart Linkup
Presenting Results
Quiz
- Wrap up
Module wrap up
Course description part 2 – Advanced Excel Formulas
The next level of Excel
The material in this module builds upon the starting ground already covered in the basic course module: Excel spreadsheet formulas for Corporate Finance and takes you to the next level of Excel where you will learn the most advanced functions and formulas as well as types of financial analysis that will enable you to rapidly advance your skills in becoming an Excel expert.
Open doors
Specifically designed for those who are already proficient in Excel the material in this advanced course will enable you to open doors in your quest to become a world-class financial analyst in the realms of investment banking, corporate development, private equity, equity research and FP&A.
Content and overview
In this course you will learn to perform dynamic analysis starting with a blank spreadsheet. You will rapidly advance to using combinations of functions and formulas with video based tutorials that are easy to follow and that will help guide you through the process.
We also cover a number of functions and formulas in detail, including:
- INDEX and MATCH
- IF with AND/OR
- OFFSET combined with other formulas
- CHOOSE for creating scenarios
- INDIRECT combined with other functions
- XNPV and XIRR
- CELL, COUNTA and MID functions combined together
- PMT, IPMT and principal payment calculations
The following main types of data analysis are also included:
- Data tables
- Pivot tables
- Column and line charts
- Stacked column charts
- Waterfall charts
- Gauge charts
What will you achieve in mastering this module?
- Everything you need to know in Excel to become a world-class financial analyst
- Advanced Excel spreadsheet training
- How to master functions and formulas
- To produce sophisticated outputs such as tables, graphs and charts to be used in financial analysis
Module curriculum
- Introduction
Welcome to Advanced Excel Formulas
Excel course outline
Module learning objectives
- Excel template set-up
Download Excel files and course notes
Template setup
Dynamic dates and time periods with IF statements
Dynamic totals and averages with the OFFSET function
Scenarios with the VLOOKUP and CHOOSE functions
Template formatting
Quiz
- Financial Statements
Dynamic financial statements
Debt schedule with PMT, IPMT, and IF formulas
XNPV and XIRR with DATE and IF functions
Formatting with sections and grouping
Adding OFFSET to XNPV and XIRR
- Financial Analysis
Financial analysis
Summary data with VLOOKUP
INDEX function
MATCH function
INDEX MATCH MATCH
Goal seek (what-if analysis)
Dynamic totals with INDIRECT and SUM formulas
CELL function
COUNTA function
MID function
Combining CELL COUNTA MID and OFFSET in a formula
Combining IF with AND, OR formulas
Quiz
- Tables
Tables in Excel
Download pivot table files
Setting up a data table
Linking and formatting the data table
Setting up a pivot table, Sum and Count
Pivot table filters
Adding calculated fields
Slicer and Timeline features
- Charts
Charts in Excel
Combination column and line charts
Stacked column chart
Waterfall chart
Waterfall chart template download
Gauge chart setup
Gauge chart – doughnut and pie series
Gauge chart – final steps
- Macros
Macros in Excel
Recording macros
Adding a form control
- Final Product
Formatting the spreadsheet
Review of the final product
Download the final product
Course Wrap up
Upcoming Public Courses
Leadership Mastering Emotional Intelligence, Refining Interpersonal Skills & Dealing with Conflict Resolution