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.

 

 

 

 

 

 

 

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

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.