A Tutorial for Using Tornado Charts

So what I’m going to do now is I’m going to walk you through an example of how to conduct a risk analysis using MS Excel. And ultimately using an add-on to Excel called Crystal Ball. Initially, we’re going to start off with Excel, just to determine what the uncertainty, or the variability that we know exists in the individual tasks. Durations, what does that do to our assumptions, or what does it do to what we can say about the overall project duration? How bad can things get to and what will our possible completion time be, or how good could things work out in our favor, and how early might we complete the project?

In order to conduct this analysis in Excel, I’ve set up the following spreadsheet. So this is slightly different than what you might see in an MS Project or other project management tools, this is just simple Excel. I’ve entered the list of my eight tasks. And concentrating first on these columns G through L, we’ll refer to columns C through E in a moment. But columns G through L show us the following information. My estimated duration. How long I think each task is going to take. My best guess. What other predecessors, we’re all familiar with this list. We’ve established these relationships already, before. And then I’m going to calculate my early start and my early finish.

My late start and my late finish. In the same way that I would calculate it in a network diagram, here I’ve gone ahead and prepared in advance the formulas in Excel to do the calculation for us. So for instance, what is the earliest start of the fundraising task? Well fundraising task depends on only the completion of creative, its predecessor. And so if I click here, I can see that this refers to the early finish of creative. Or as an additional example, when will marketing start at the earliest? Well it depends on the completion of either the creative and strategy, and so I used the maximum formula that chooses the maximum between them, or the later of the two.

And so in this instance, marketing can start at the earliest at time five, because creative has finished at time five. And so on and so forth. Having done this and prepared the spread sheet in, in such a way, now I can go ahead and look at the impact that the variability of each task has on my overall project duration, meaning, a change from three to seven in the creative. How bad will things get at a project level? In order to do this we’re going to basically plug each task’s minimum and maximum duration in our estimated duration cell and see what the implication is for the bottom line overall project duration.

We’re going to do this for each task at a time, so this analysis takes us through basically how sensitive our project duration is. For changes in one specific task, holding the other tasks at their most likely or their estimated duration. Let me show you an example. For instance, if the creative task instead of five week, which is, is most likely, takes three. What will that do? Well, my project will now be completed sooner. And so I can keep track and write down my minimum overall project duration as ten weeks. On the other side what happens if my task is gone late and it takes the pessimistic duration, it takes the full seven weeks.

Worst thing that can happen for this specific task, my overall project will be delayed as well by two weeks, total duration of 14, and so I can keep track of the impact on my overall project duration by changing each task’s estimate from the minimum to the maximum. Let’s go back to the creative being at its most likely, and see what the impact is of the strategy’s variability. My strategy task might take one week, and our project will complete. No change in our project completion. It will still take 12 weeks to complete. So we don’t gain anything from an early strategy completion. And at the maximum it might take us six weeks which imply one week delay to my project.

So my minimum here would be from 12 my base case duration all the way up to 13. The reason there isn’t as much impact to the strategy task has to do with its presence on the critical path. Since it is not a critical task it still has some slack. And until the point where it eats up all the slack. Or it makes use of all the slack. It will, even delays will not postpone the overall project duration.

I can execute on this or I can walk through this analysis for each one of the tasks and come up with my list of minimum and maximum durations, changing each one at a time and holding the others at their most likely estimate. I’ve actually gone ahead and done that and I’m moving over to another tab in which I’ve manually plugged each minimum duration for each one of my tasks and the maximum to come up with the following list that shows me what is the swing, or the range for my project duration based on the change of the individual tasks. So which is the most impactful here, which task’s variability has the most impact on the overall project variability?

I can visualize this, and my goal is to come up with a tornado chart. A tornado chart will visualize the relative swing in the overall project duration based on the change in each task. To do this, I am going to first select my table. I am going to use the Data Ribbon and actually apply a filter just to help me sort here. And I’m going to sort by the range column from the smallest to the largest. This is a technicality associated with what Excel requires from me when I start plotting this information. So I’ve sorted my entire table from the range from the smallest to the largest.

I highlight my task names and I can also highlight the min and the max which is the range, the swing which I would like to visualize, and I go to use the Insert Ribbon, and I select the Bar Chart, a 2D, the first clustered bar chart is probably your best option. Let me get rid of some information here that is not necessary to clean it up. And we’re not quite there yet. What we need to do is we need to set a few settings in our chart. For instance, I’m going to format the x axis so it’s centered around my most likely project duration, which is 12 weeks.

When I do this, I already see that I’m getting more of what I consider a tornado chart, with red indicating delay in my project and blue indicating an earlier completion. I’m also going to change the x-axis to just start from eight, so we can have a bit more of a zoom in view. And I’m going to move the labels that are associated with my y axis to be all the way at the low value, just from an aesthetic perspective, it’s easier for me to view the labels themselves.

Finally, to make sure that my tornado looks aesthetically pleasing and as easy for me to read, I’m going to click on the series themselves and I’m going ask them to be completely or 100% overlapped. And I can play around with the gap width as well, just to get the visuals that I feel are the most appealing. And so what do I see here? Well, here’s our tornado diagram. We can see, and perhaps it will be helpful if I increase the font size of, on, in this chart a little bit. You can see here the swing or which tasks seem to have the most impact on my overall project duration.

Testing them one by one and holding the others constant at their most likely value. And we are seeing that the finance gives me the biggest swing from 11 to a worst case scenario of 17. Next is sale, and so on and so forth. Giving us the entire impact of these tasks on the overall project duration.