• What it is: Tornado charts are a standard output of a probabilistic Monte Carlo analysis tool such as Crystal Ball or At Risk. The charts display the sensitivity impact of the variability of factors utilized in calculating an NPV, IRR, or other financial measures. The chart displays how the variability of a factor (e. g. units sold) changes the financial measure (e. g. NPV). These charts can also be made in Excel for a simple Best, Worst, and Base case scenarios probabilistic model. Completing a Financial Analysis of a business decision based on only a single base case leaves the recommendation open to numerous uncertainties. By using a range of inputs as part of a probabilistic analysis reveals risks and opportunities. The Tornado chart highlights these risks and opportunities.
• What does it do: The Tornado chart displays the level of sensitivity of each factor and allows the analyst to see the descending impact of each factor. It also allows the analyst to see the relevant positive and negative impact differences within the foreseeable relevant forecasted variations. This can allow the analyst to see if some of the factors have more than an acceptable impact on the forecasted financial measurement to allow the management decision to be made. In that case, additional research could be completed to narrow the range of predictability for the factor to decrease the potential variation of the financial measurement.

Uses:

• How is it used: The Tornado chart is used to see if the variability of the financial measurements provides a clear indication of the management of the desired decision. The analysts use the level of variation to determine if a management recommendation can be made. If the variations of the financial measurement make the management decision unclear, then the level of variation needs to be reduced until the financial measure indicates a clear management decision. Seeing which factors create the highest sensitivity indicates which factors may require additional research to narrow the relevant forecasted range.
• Where: Tornado charts are used to help identify the sources of variability in probabilistic analysis. The tornado charts indicate the levels of variability and display the sensitivity in descending order of impact on the financial measure. Examples of where a tornado chart can be valuable include:
  • Introduction of a new product: Creating an NPV analysis with variable initial unit sales, volume growth rates, variable price, variations in fixed costs, and variable costs. This analysis will help determine under what conditions the product launch will be profitable. It will also indicate which factor variations will most likely drive the product launch to an unprofitable outcome. This factor needs to be tested to determine how likely the low-end values will occur.
  • Launching of a New Business: In this analysis factors to be analyzed should include factors such as Revenues, Number of Workers, Worker Pay, Benefit costs, Cost of Goods Sold (COGS), Shipping Costs, Growth, Contingencies, etc. The analysis will see under what conditions the business launch will return a profit.
  • Merger or Acquisition of a company: Determine if an acquisition or merger at a certain price will create or destroy value. The uncertainty of how customers and competitors will respond and the normal uncertainty around industry growth and maturation will create uncertainty around: Price, volume, market share, customers leaving, internal product cannibalization, projected cost savings, loss of critical employees, etc.
  • Repair or replace equipment: This analysis could consider the uncertainties around: a life of existing equipment, on-going repair of old equipment, maintenance of new equipment, savings from technology improvement, life expectations of the new equipment, etc.
• Why: It is very risky to use a single case measurement for a decision. The base case may indicate a positive recommendation, but the probabilistic analysis may uncover some specific risks that were not indicated by the base case. Further analysis is often indicated by the variability highlighted in the tornado chart.

Limitations:

• Where it shouldn't be used: If all of the factors in the financial analysis are set and there is no uncertainty, the tornado chart will not provide any additional insight.
• Any restrictions: None
• Warnings: When considering the range of uncertainty for a factor it is best to have a worst-case where there is only a 10% chance the actual answer could be lower and for the best case where there is only a 10% chance the actual outcome could be higher. By using this protocol there is an 80% chance that the actual answer will fall within the range of your probabilistic model. In reality, it is unlikely that all of the factors will end up at the lowest possible outcome, so the likelihood that all worst-case factors will end up being true has probably less than a 1% chance of occurring.

Step-by-step process:

• Gathering data
  • Running a Monte Carlo Simulation like Crystal Ball or At-Risk which will create the Tornado Chart for you
  • Run the base case modifying one factor at a time to see how much the NPV changes for the worst case of a factor and then for the best case of that factor. Record all of the findings in a table so you eventually have the sensitivities for each factor that has uncertainty
  • Determine which financial measurement you will use to indicate the recommended outcome of the management decision (e. g. NPV)
  • Identify the factors that will be used in the calculation of the financial measurement
  • Determine which factors contain uncertainty and will have a range of projections and not a single forecast
  • For each factor determine a forecast base case number that has a 50% likelihood of being too high and a 50% chance of being too low
  • For all of the factors which are classified as uncertain include a best-case forecast that has only a 10% chance of being too high and a worst-case forecast that has only a 10% chance of being too low.
  • Build the financial model to pull factors from an input table that can vary the factors between worst, base, and best cases.
  • Complete the bases case and find the financial metric (e. g. NPV) for the base case
  • Then find the probabilistic answers by:
  • If you did not run a Monte Carlo simulation, then create the Tornado Chart following the instructions under Analysis of Data
• Analysis of data (As described by MBA Charts)
  • Create a horizontal clustered bar chart using the worst and best case NPV outcomes for each factor from the sensitivity analysis
  • Format the X-axis, so the Vertical axis crosses at your Base Value:  This is the key step in creating the Tornado Chart.  You want the High Value and Low Value to “straddle” the vertical axis to create a tornado-like effect.
  • Format Y-axis so the data labels are positioned as “Low”
  •  Format X-axis data range-bound:  Changing the x-axis values will center the Tornado Graph for better presentation
  •  Format data series to overlap 100%:  Right-click any bar and select format data series.  Got to series options and change Series Overlap to 100%.  This will align the High and Low-Value series.
  • Be sure data is sorted so that factors with the greatest variation are at the top of the chart and the smallest variation is at the bottom of the chart
• Interpretation of results
  • Factors at the top are the factors that need to be further researched or managed to make the most positive outcome possible. This activity can become a major source of recommendations from your analysis.
  • If all the uncertainty factors still indicate that a project is positive it should be recommended.
  • If all of the outcomes are negative then the recommendation should be for declining the project
  • All other scenarios will require a determination of the risk tolerance of the management group to determine if the risk-reward profile will be acceptable to them.
• Presentation of results
  • The Tornado chart itself is the best form of presentation.

Template for capturing data:

Tornado Chart Template 

Output representation and recommendations: