This preview shows page 1. Sign up to view the full content.
Unformatted text preview: Step 1 Build a generic model. This model will help you compute the value of profit for all possible scenarios. Step 2 Prepare a table to store all the values you need for your tornado diagram.
Tornado Diagram
Number Seeing the Movie (in millions) Average Ticket Price (in $) Distribution Costs (in $ millions) Low 63.5 26.4 47.0 Profit if... Base 34.0 34.0 34.0 High 130.0 37.8 28.0 Use your model to fill in the blanks, starting with the Base Case. Then change the values of the uncertainties to fill the rest of the table. Etc. Step 3 Sort the uncertainties in descending order of importance.
How would you quantify the importance of the uncertainties? Estimate the contribution of each uncertainty to the Variance!
Tornado Diagram
Number Seeing the Movie (in millions) Average Ticket Price (in $) Distribution Costs (in $ millions) Low 63.5 26.4 47.0 Profit if... Base 34.0 34.0 34.0 High Swing Sw^2 % of Variance 98.71% 130.0 193.5 37442 0.34% 37.8 11.4 129.96 0.95% 28.0 19 361 = 37933 1. Compute the swing in value due to the uncertainty as: abs(max(Low, Base, High) min(Low, Base, High)).
ASSUMPTIONS: (1) Profit function is linear 2. Square the swings. 3. Sum the squares and form the ratios to find the % of variance explained. (2) Each of the components are irrelevant to each other Step 3 Sort the uncertainties in descending order of importance.
How would you quantify the importance of the uncertainties? Estimate the contribution of each uncertainty to the Variance! Use the Data, Sort uncertainties Pick the command.... that account for 90% ... of you'll (or 95/99 %) and the get variance this!
Tornado Diagram
Number Seeing the Movie (in millions) Distribution Costs (in $ millions) Average Ticket Price (in $) Low 63.5 47.0 26.4 Profit if... Base 34.0 34.0 34.0 High Swing Sw^2 % of Variance 130.0 193.5 37442 98.71% 28.0 19 361 0.95% 37.8 11.4 129.96 0.34% = 37933 Step 4 Build the Tornado Diagram.
Create a bar chart with the Low Base High values...
100.0 50.0 0.0 50.0 100.0 150.0 1 Low 2 Base High ... but you're not finished yet you will still need to do a few things in order to get a nice diagram! 3 Step 4 Build the Tornado Diagram.
1. Change the overlap in the Data Series, Format dialogue box.
100.0
50.0 0.0 50.0 50.0 100.0 100.0 150.0 150.0 1 Low Low 2 Base Base High High 3 Step 4 Build the Tornado Diagram.
2. Go to the Value Axis, Format options to set the cross point of the axes at 34 (Base case NPV).
100.0 50.0 0.0 50.0 100.0 150.0 1 1 Low 2 2 Base High 3 3 Step 5 Do a little formatting.
Add titles, change the colors, etc.
My first Tornado!
Profit
100.0 50.0 0.0 50.0 100.0 150.0 Number Seeing the Movie (in millions) Average Ticket Price (in $) And that's it! Distribution Costs (in $ millions) Low Base High ...
View
Full
Document
This note was uploaded on 06/16/2010 for the course MS&E 153 taught by Professor Ronhoward during the Summer '08 term at Stanford.
 Summer '08
 RONHOWARD

Click to edit the document details