Home // Video Tutorials // 6 - 10 (Advanced) // 6 // Waterfall chart using stock charts

Waterfall chart using stock charts

How to create an easy waterfall chart (aka Mckinsey chart) using the stock charts available in Excel.

Waterfall chart using stock charts

Stock charts are very useful for plotting what happens on the stock market with share prices. However they have other uses as well. But let’s first explain in the context of stock data. Here we have some data relating to a particular stock. We have the various days, we have what the stock opened at, what the high for the day was, what the low for the day was and what the closing price was. To turn this into a stock chart, we highlight it, we go Insert, Other charts, and you’ll see the stock charts have a couple of options. For now we are going to use the second one, which requires that you have an opening, a high, a low and a close. When I click on it, the following chart is generated. Let’s understand what this chart is saying. Let’s look at the last day, which relates to this one here. What you’ll see is the opening, in this case is the bottom line- the high is represented by a line that goes up, so you’ll see if the high is above the opening or the close, the line will point out, the low, similar, below, and the close represented by another line. So we have a box here. If in this case it is white, it means that that was the opening, it went up and closed there. There’s the high, there’s the low. If it is a dark colour, we opened over there, we closed below. So that’s how it is represents up and down. For the most part, the ability to format and customize the chart is exactly the same – all the same options exist. The one difference is, if you hover, and you hover close to the edge – you’ll see that’s a series. If we hover a little bit lower, you’ll see it tells you Up Bars. When we go to the dark colour, you’ll see it says Down Bars. If we hover over that, and right click, we can actually format the Up Bars and in this case, at the moment, it is saying it is automatically going to fill it, but let’s put a solid fill and because that’s an Up, let’s make it green. You’ll see now all the Ups turn green. The same when I format the Down – so you can use a different colour for ups and downs. I am going to make that red – close it. So maybe this is more descriptive – green is good, red is bad. You can see what is happening here. So, for example, if it turns out that this number wasn’t actually 47, but let’s make it, maybe, 40 – you’ll see what has happened the scale has changed as well. We have now shown that this last day was a bad day – negative day, opened at that number, went down all the way to this number. The high was here and the close was presumably somewhere in here. I am just going to undo that. Now that’s useful if you follow the stock market but how can this help the normal Excel user? This is where you can use this particular chart to actually create something called a Waterfall chart. The best way, let me explain, show you what it does and you can see why it is so useful. Here what we have – I have got the open, high, low, close – but what this is trying to show is we want to start with the budgeted profit – you’ll see that’s 100, we want to explain that, because of retrenchments, that profit has dropped to 90, because of, for example, electricity hikes it is now down to 80, but a selling price increase has resulted in it going back up to 110. You can use these charts to show what is causing an up or causing a down. So let’s go here – we’ll say Insert – go to Stock and in this case, because I have got open, high, low and close, I click this one. I have just put the open, high, low, close, just to help me understand what the numbers need to be, they are not actually relevant to the numbers. Just looking here you’ll see the result is open, high, low, close – it doesn’t quite look right. Let’s see what happens if we switch the rows and columns. Suddenly now we have got a graph which is a little more meaningful. Let’s look at what it is saying – it’s telling us that our budgeted profit was 100. Because of additional retrenchments, it cost us 10 so we are down to 90, because of electricity hikes another 10, we are down to 80. But we have increased the selling price and hence the budgeted profit actually goes all the way up. So that’s a very nice way of representing movements in budgets. Let’s just see how we have tricked it into doing this. You’ll see that we are able to set up a spreadsheet to handle all of these things. We know that the budgeted profit – we want it to show the full value, so we are going to start with a zero – you’ll see I have typed in a zero. The high and the low, because we don’t actually care at the moment for these lines, I am just going to make it equal to the opening. So in both cases, it just equals this cell, which means we won’t see them, and the closing is where it must close. So, if maybe, this is not correct, I can actually start at 100 and you’ll now see we have a very thin line – and perhaps that is what you want, but in most cases I would like to show it like that. So that’s the close. In order for it to flow, the close of the one needs to be the opening of the other. So therefore, you’ll see I have just set it up – you’ll see it is equal to that cell – the high and the low are just linked to this cell so there’s no real thought involved there. And the close needs to be the result of the impact – so you can see we now need to go from 100 down to 90 which implies a 10 amount cost. And so we go on. You’ll see that the most important part of setting up this graph is actually getting the spreadsheet part of it correct, in a nice usable format, so that next time something comes, we can say, for whatever reason, I just need to change this number, so let’s say that our selling price increase didn’t materialize, so the net answer is 95 and you’ll see the graph reflects it. Again, as shown previously, you can format it to show colours, just to make it a little bit more descriptive.