scenario+analysis

=|| Home|| Book || Featured PF Models || Featured Corp Models || Featured Acq Models || PF Exercises || Corp Exercises || Download to Excel || Scenario Analysis || Tornado Diagrams || Monte Carlo || Flexible Graphs || Real Estate || Acq Exercises || Macros || Reading PDF || Waterfall Graphs || Circular Ref || Depreciation || Financial Ratio || Value Driver || Renewable || Testimony || Pricing Case Studies ||= = This website is now obsolete because of being extinguished by horrible wikispaces. Please go to http://edbodmer.com for a much improved website. =

This scenario analysis page includes files, videos and excerpts from my book that allow you to quickly add scenario analysis to any excel file. The scenario analysis discussion begins with a basic discussion of how to build data tables. After scenario analysis is addressed using the excel data table tool, a number of exercises and videos explain how to build scenario analysis with macros instead of data tables. I have also included discussion of how to use and build the scenario reporter. The scenario reporter is a file that you can add to any of your spreadsheets and then name and record scenarios. I have divided the files into three lesson sets. The first lesson set addresses data tables which you can probably find in thousands of other videos. The second video set moves to more advanced issues with use of macros that can dramatically improve data tables and use of various techniques to make the scenario analysis more flexible. The third video set explains how you can use and create a scenario reporter.

The first video provides and example of a finished file. Other exercises walk through how to start with a simple example and ultimately make a very flexible analysis and a nice summary page.


 * Scenario Lesson Set 1: Adding Master Scenario Page to Any Model **

To make a master scenario page that has a base case, upside case and downside case, you can use the INDEX function combined with the DATA TABLE tool of excel. To do this, you should understand how a data table works and how the INDEX function can be used with a code number. The steps in this process include:

1. Set-up of a Separate Master Scenario page in Basic Model Objective of Scenario Page Understanding Data Tables for Scenario Analysis Use of Code Number and INDEX or CHOOSE Functions Presentation of Cash Flow Data with SUMIF Putting together Scenario Analysis

media type="youtube" key="fX5yUl-2uj4" width="560" height="315"

= Exercise Files for Construction of Basic Scenario Analysis =



= Files Associated with Videos =



=The above file is a template for adding scenario tabulations to any financial model=



=The above file includes a tornado diagram with the Offset function to accept flexible different series=



=The above file has a waterfall chart with a sensitivity analysis=


 * Scenario Lesson Set 2: Using Macros in Scenario Analysis **

Alternative to Data Tables in Scenario Analysis Problems with Copy and Paste Macros in Models Use of Goal Seek in Models Creating Data Tables with Macros Power of FOR loops with CELLS function

=Video Creating Data Tables and Scenario Analysis (If the music is too low, try headphones if you have them)=

media type="youtube" key="nNlYOzIkkm0" width="560" height="315"

=The above file shows how you can make effective graphs from data tables=



=The above file uses a macro to create a one way data table=



=The above file reviews data tables in the context of structured finance=



=The above file shows how to make a more flexible data table with macros=



=The above file shows how to speed up data tables with range names=



=The above file uses a macro with a goal seek and a macro table=




 * || Subject ||  || Excel Exercise File ||   || Video ||   || Chapter Reference ||   || Page Reference ||
 * || Two Way Data Table with Structured Finance Model ||  || CDO Exercise 2 ||   || https://www.youtube.com/watch?v=iGnjWYW-zIc ||   || Chapter 16 ||   || 193 ||
 * || One Way Data Table with Structured Finance Model ||  || CDO Exercise 2 ||   || https://www.youtube.com/watch?v=Tip6i7pYEt4 ||   || Chapter 16 ||   || 201 ||
 * || Basic One Way and Two Way Data Tables ||  || Exercise 1: Data Tables without Macros ||   || https://www.youtube.com/watch?v=7s2GiemfZdg ||   || Chapter 16 ||   || 193 ||
 * || Data Table with Macro - No Range Name ||  || Exercise 2: Data Tables with Macros ||   || https://www.youtube.com/watch?v=xR-NwFZ2ANU ||   || Chapter 16 ||   || 201 ||
 * || Flexible Two Way Data Tables with Macros and Range Names ||  || Exercise 3: Flexible Data Tables with Macros ||   || https://www.youtube.com/watch?v=UIeSTdiPNik ||   || Chapter 16 ||   || 203 ||
 * || Creating Graphs from Data Tables ||  || Exercise 4: Graphs from Data Tables ||   || https://www.youtube.com/watch?v=0StOCE_yjNc ||   || Chapter 15 ||   || 174 ||
 * || Flexible One Way Data Tables with Macros and Range Names ||  || Exercise 5: Flexible One Way Data Tables with Macros ||   || https://www.youtube.com/watch?v=GfhlYG5rxRc ||   || Chapter 16 ||   || 203 ||
 * || Goal Seek, Macros and Data Table Macros ||  || Exercise 6: Goal Seek Macros and Data Table Macros ||   || https://www.youtube.com/watch?v=jroLGvUXj40 ||   || Chapter 42 ||   || 541 ||
 * || Auto Open Macro to Set Calculation Method ||  || Exercise 7: Data Tables and Auto_Open Macro ||   || https://www.youtube.com/watch?v=RhXFHCp7Pic ||   || Chapter 16 ||   || 193 ||
 * || Use of Calculatebeforesave = False to Save Fast ||  || Exercise 8: Data Tables and Save ||   || https://www.youtube.com/watch?v=acW9Fo0KSFQ ||   || Chapter 16 ||   || 198 ||
 * || Basic Scenario Analysis with INDEX and Data Table ||  || Exercise 9: Basic Scenario Analyisis ||   || https://www.youtube.com/watch?v=fX5yUl-2uj4 ||   || Chapter 17 ||   || 212 ||
 * || Sensitvity Analysis with INDEX and Data Table ||  || Exercise 10: Basic Sensitivity Analysis ||   || https://www.youtube.com/watch?v=MKFx1g5vlKA ||   || Chapter 18 ||   || 234 ||
 * || Creating a Custom Scenario without Macros ||  || Exercise 11: Basic Custom Scenario Case ||   || https://www.youtube.com/watch?v=4npJiOPGop4 ||   || Chapter 17 ||   || 223 ||
 * || Creating a Custom Scenario without Macros ||  || Exercise 11: Basic Custom Scenario Case ||   || https://www.youtube.com/watch?v=4npJiOPGop4 ||   || Chapter 17 ||   || 223 ||

=Exercies that Demonstrate How to Create Scenario, Spider Diagrams and Tornado Diagrams=

This page contains exercises and explanations for adding tornado diagrams and spider diagrams to your models. Once you get used to just a couple funcitions in excel, I am convinced that you can very easily add the scenario and other types of analyses to any of your models. All of the exercise in one way or another use a combination of the INDEX and DATA TABLE functions in excel. It is fairly easy to program the INDEX fucntion and DATA TABLES in excel; the key is to know how to creatively use the commands in combination.

Due to the importance of this risk analysis, the tools below include video explanations as well as the exerciese and also documentation in power point slides.


 * Subject ||  || Excel Exercise File ||   || Video ||   || Chapter Reference ||   || Page Reference ||
 * Basic Tornado Diagram ||  || Scenario and Tornado Exercise ||   || https://www.youtube.com/watch?v=0x1sx5YKpr8 ||   || Chapter 16 ||   || 193 ||
 * Sorting a Tornado Diagram ||  || Scenario and Tornado Exercise Finished ||   || https://www.youtube.com/watch?v=kH41VDGakqM ||   ||   ||   ||   ||
 * Tornado Diagram with Two Way Table ||  || Scenario and Tornado Exercise Two Way Table ||   || https://www.youtube.com/watch?v=fdO6p22ngow ||   ||   ||   ||   ||
 * Showing Low and High Case Values in Tornado Diagram ||  ||   ||   ||   ||   ||   ||   ||   ||
 * Tornado Diagram in Project Finance Model ||  ||   ||   ||   ||   ||   ||   ||   ||
 * Tornado Diagram with Alternative Base Case ||  ||   ||   ||   ||   ||   ||   ||   ||
 * Spider Diagram with Constant Increments ||  ||   ||   ||   ||   ||   ||   ||   ||
 * Spider Diagram with Variable Increments ||  ||   ||   ||   ||   ||   ||   ||   ||
 * Spider Diagram with Variable Increments ||  ||   ||   ||   ||   ||   ||   ||   ||



Exercies for Creating Tornado Diagrams Note: the template for creating tornado diagrams is in the risk analysis templates



Combination of Scenario and Sensitivity
This file contains an example of how to combine scenario and sensitivity analysis whereby you can create a base case and then perform sensititivy analysis on different varaibles in the base case. Alternatively you can create a downside case and then perform sensitivity analysis on variables in the downside case etc. This approach allows you to retain base case variables and also analyse downside cases on individual items. To create the scenario analysis, you can use data tables, index functions and some simple VBA code. An example of the approach is shown in the file below.



Video Instructions for Scenario Manager Power point slides for Scenario Manager

Scenario Analysis using Data Table and Index
Power point slides that explain the risk analysis models Exercise for Creating Spider Diagrams Exercise for creating data tables with VBA Exercise for Creating a one-way and two way data table