Search This Blog

Saturday, September 21, 2013

Tornado Diagram

Definition:

Tornado Diagram is one of the tools used to complete sensitivity analysis which also know as tornado chart or tornado graph. It is one type of stacked vertical bar chart. Usually contains 3 type of information (bars) stacked together and then sorted and displayed from higher to lower. The final output of this process is a diagram similar to tornado. I will go through how we can benefit from tornado diagram in sensitivity analysis, Many examples on how to create tornado diagram using excel, Some PMP exam questions related to that topic, and finally a use case study on why we need to complete sensitivity analysis using tornado diagram to reach to our final decision in that case study. But before we start lets first identify our exact position in PMI process group and which knowledge area.

how-to-draw-a-tornado-step-4_1_000000009187_5

 

 

 

 

 

 



 

Tool Name:

Tornado Diagram

Process Group:

Planning

Knowledge Area:

Risk Management

Process:

Quantitative Risk Analysis

Method:

sensitivity analysis

Process:

Step # 1: Determine variables affecting your identified risk or your business case.

Step # 2: Determine the formula that can be used to link all identified variables with their effect on the identified risk.

Step # 3: Estimate High, Medium and low value for each variable.

Step # 4: Calculate your base by applying the formula identified in Step # 2 on all variables medium values.

Step # 5: calculate variance for each variable by applying the identified formula for High and Low value for each variable.

Step # 6: Sort them from higher to lower.

Step # 7: Draw the tornado chart.

Step # 8: Perform sensitivity analysis.

Case Study # 1:

The best way to understand tornado diagram, why we need tornado diagrams, how to use it and how to create it. is to start with an example or case study and see how we will start with a business case going through step by step till we construct the tornado diagram using Microsoft excel and then do the required sensitivity analysis.

a publishing company want to release a new book. they want to make sure that they will get a good net profit based on the forecasted sales. First thing that we need to do is to determine the factors that will affect our net profit which are (Step # 1):

  • Total number of sold items (Volume).
  • unit price.
  • unit cost.

Step # 2: The net profit formula should be:

Net Profit = # of sold items * (unit price – Unit cost)

Step # 3: The following table displays the estimated figures

Factor Low Medium High
Volume 1000 2000 3000
Unit Price 5 10 15
Unit Cost 1 2 3

Step # 4: Now we need to calculate our base by compute our formula using the medium values for our variables

Net Profit (Base) = Volume(2000) * [unit price (10) – unit cost (2)]

Net Profit (Base) = 16000 $

Step # 5: calculate variances for each factor:

Factor Low Base High
Volume (A) 8000 16000 (B) 24000
Unit Price (C) 6000 16000 (D) 26000
Unit Cost (E) 18000 16000 (F) 14000

(A) : Net Profit (Base) = Volume(1000) * [unit price (10) – unit cost (2)]=8000

(B): Net Profit (Base) = Volume(3000) * [unit price (10) – unit cost (2)]=24000

(C): Net Profit (Base) = Volume(2000) * [unit price (5) – unit cost (2)]=6000

(D): Net Profit (Base) = Volume(2000) * [unit price (15) – unit cost (2)]=26000

(E): Net Profit (Base) = Volume(2000) * [unit price (10) – unit cost (1)]=18000

(F): Net Profit (Base) = Volume(2000) * [unit price (10) – unit cost (3)]=14000

Variance Table:

Factor Low High Variance
Volume (A) 8000 (B) 24000 24000-8000=16000
Unit Price (C) 6000 (D) 26000 26000-6000=20000
Unit Cost (E) 18000 (F) 14000 18000-14000=4000

Step # 6: Sort factors based on their variances from higher to lower:

Rank Factor Low High Variance
1 Unit Price (C) 6000 (D) 26000 20000
2 Volume (A) 8000 (B) 24000 16000
3 Unit Cost (E) 18000 (F) 14000 4000

Step # 7: Draw Tornado Diagram

Tornado

Step # 8: it is clear from our chart that unit price is the most important factor that we need to take care of

Note(s):

  • Tornado diagrams can be used to do the sensitivity analysis not only for risk, but for any business case as well
  • You can do tornado diagram for equation with 3 variables manually. But you may need to use a specific program to do it for 100’s of factors.

No comments:

Post a Comment