Tip:
Highlight text to annotate it
X
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.