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

There are no upcoming events at this time.
Leadership and Problem Solving
There are no upcoming events at this time.
Closing Gaps between Supervisor and New Manager
There are no upcoming events at this time.
New Manager and Middle Manager
There are no upcoming events at this time.
Senior & Executive Manager
There are no upcoming events at this time.

Book Now! View Calendar for the latest course

<