In this episode from ModelOff, PW's Gavin Jordan shows how to create a confidence chart - an interesting way to show increased risk in a forecast as the forecast extends out over the next 15 years. This chart reminds me of the Hurricane Cone charts that are published in the newspaper in Florida.
Transcript of the video:
Learn from MrExcel podcast, episode 1846.
Gavin Jordan Confidence Chart (without a pivot table).
Alright! Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
We're here in, New York City with Gavin Jordan from London, right.
Gavin: Hi, yeah!
MrExcel: Hey! Gavin is one of the 16 finalists in Model of 2013, the world Excel championships.
Gavin, what are you show us today?
Gavin: So, I'm gonna show you how to make a pretty version of a confidence chart.
So, this is a chart that shows over time the likelihood of a result ending up within a particular confidence.
So, what might be the minimum, maximum and the average in the future, as we can see from the basic data here.
If you just chart these, as a line graph.
Not very exciting, not very clear.
MrExcel: Right Gavin: Yeah.
MrExcel: All right! so we have this ugly chart.
Gavin: Yeah, so I'm gonna show you how to turn that into an area chart like this, where the the light gray area, shows that there's a 95% chance of the values in the future year.
Fitting within that like gray area, right and the dark gray area, there's a 50% chance of the bad news fitting within that.
MrExcel: And then the dark line is...
Gavin: The the dark line is the average, expected in this scenario.
MrExcel: So, this chart is just horrible.
I can't figure out what the heck is going on, but this chart, it makes it very clear.
You know, what the the expected range is and while how it gets wider as time goes by, I guess that's the less.
Gavin: Yeah, so as you're projecting out into the future, you're gonna have less confidence as to where the way your outcome is gonna be.
MrExcel: Well, this chart is a lot better looking in the first year.
I would love that figure out, how to create this?
Why don't you take us through step by step?
Gavin: Yeah, let me show you.
Mr Excel: Okay!
Gavin: So, the first thing I've done, is I've manipulated the data.
Just a little bit from the table that we have up here.
So, the minimum column just stays as it was before.
So, as you can see this is just linked directly to the cells above.
MrExcel: So, it's just a straight formula equals C2, C3, C4, C5.
Gavin: To a straight forward link to exactly the cells.
MrExcel: Perfect, gotcha!
Gavin: The low is the bit of the stat, that's on top of the minimum.
So, here it's the difference between the low numbers that we had here and the minimum numbers.
MrExcel: So, 154 minus 136.
Gavin: Exactly! So, the formula very simple D2 minus C2.
MrExcel: Perfectly!
Gavin: Three minus C3.
MrExcel: Gotcha! Okay!
Gavin: So, the difference is between the mean, we're gonna do something different with we're gonna put that as a line chart.
So, we just leave that as it is before straight sets that on the on the stack.
MrExcel: Gotcha! Okay!
Gavin: The high, in a similar way to the low is now, the difference between the bit that's stacked on top of the line.
So, this is the difference between the high and the low about, between the 120 and 102.
MrExcel: Yeah!
Gavin: So, F3 minus D3, for the second one.
MrExcel: Good that's good.
Gavin: And the max.
MrExcel: Same again.
Gavin: How much of the stack is going to go on top of the high?
So,the difference between the max and the high column.
MrExcel: So, he just to interject here.
This is a little bit tricky because you can't really copy those formulas across.
I guess C and E are the exact same formula but all of the other formulas, it's you're comparing two columns that aren't necessarily adjacent.
Gavin: Absolutely, right I mean what you might, we might want to do.
If you've got lots of different content with different intervals is everything up to the mean and the ones on the other side of the mean, are okay!
But you just need to get that mean one.
So, you could start with the low copy them across and then replace the values to mean MrExcel: Okay! Great.
So, now let's see how we take this data.
Gavin: It's not gonna look great start, right but we'll get that.
MrExcel: I love those tricks, where it looks like you're going the wrong way and then all of a sudden it snaps back.
Okay!
Gavin: So we're gonna show this is a stacked area chart.
MrExcel: Stacked area, okay!
Gavin: Not quite what we were looking for...
So, the first thing we need to do is take the mean and we're gonna change that to a line.
MrExcel: So, you right click, change chart Gavin: And to a line chart.
MrExcel: Just regular, it doesn't matter whether it's stacked or...
Gavin: Just regular.
MrExcel: Gotcha!
Okay! Great.
Gavin: So, now we can start to see it MrExcel: Starting to come into play, all right.
Gavin: Now, the next thing we need to do is we need to remove the color for the...
For the minimum.
MrExcel: All right!
Gavin: And change yellow, change the other colors as well.
So, we're gonna change the colors, now to make this look good.
First thing we're gonna do, is we're going to take a minimum.
And we're going to remove the color there.
So, we go off to format, up to shape fill, and no fill.
MrExcel: There we go, okay!
Gavin: So, that takes that, piece out.
So, we're going to color in light gray the areas, that sit within the 95% confidence.
So, these outer ones here.
MrExcel: Okay!
Gavin: Blue, and the red, so we're gonna color those into going up to format, check fill and into the second MrExcel: Second light gray, okay.
Gavin: and then let's do the same, again on the red and shape fill and fill light gray.
So, it's coming together now.
MrExcel: Nice Gavin: We can change the color of the middle if we want, probably dark gray is fine.
and then we now just have to select the mean.
MrExcel: Yeah! There we go.
Gavin: And that's change.
MrExcel: Now, that's shape outline.
Gavin: That shape outline is the line of the another right area.
I'd like to change that to black.
MrExcel: Now, we still have that legend on the right hand side, that looks ugly.
Max high, low min, mean.
I'm not really sure that does anything.
What do you do with the legend?
Gavin: So, I know when you take the legend, I know you take the legend down.
I just select it and delete it and then I'll often just put some labels in below to explain or perhaps, the paragraph explaining below.
These are just, some people will be very familiar with but for others there'll be something.
MrExcel: That's a cool looking chart.
I certainly, if I had my choice between the first chart that you showed and this chart.
This explains a lot better, so yeah, right very nice.
Gavin: And you can do this with as many as many colors, as you want.
So, you might have a 50% to 90% and a 95% confidence interval.
Again shades of gray.
MrExcel: Now, of course you use this in financial modeling a lot.
I mentioned, you know our place down in Florida.
We're always watching the Hurricanes come in and they have the same kind of track.
This is where we think it's gonna hit, but there's a 10%, 20%, 30% chance.
It's like that hurricane cone chart.
Gavin: Absolutely.
MrExcel: Yeah, right.
Gavin: Practical example of where we say, so now and I'll use it.
Projecting investment returns or other forms of finance into the future.
Over one year or two years, we've got a lot more confidence.
We know that, 2015, we know there's a 95 percent confidence, that will be within in this little band here, by the time we go up to 2036.
It's a much wider about, MrExcel: Wow! That is a great chart, that is very good and I want to point out there, Gavin.
When Gavin sat down to do the podcast he said that clearly he should be doing a pivot table podcast because that's his thing, but we didn't have the the right data for that, so for all of Gavin's co-workers.
Yes, this should have it. This should've been pivot table.
Gavin: Keep asking me pivot table questions.
I can answer them, but you got something different, today guys.
MrExcel: There you go, all right!
That's great, all right! Gavin.
I've been looking on the model of Championships.
Thanks for that here in New York City.
Gavin:Thank you very much Bill, all right!
Thank you. Thank you!
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Gavin Jordan Confidence Chart (without a pivot table).
Alright! Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
We're here in, New York City with Gavin Jordan from London, right.
Gavin: Hi, yeah!
MrExcel: Hey! Gavin is one of the 16 finalists in Model of 2013, the world Excel championships.
Gavin, what are you show us today?
Gavin: So, I'm gonna show you how to make a pretty version of a confidence chart.
So, this is a chart that shows over time the likelihood of a result ending up within a particular confidence.
So, what might be the minimum, maximum and the average in the future, as we can see from the basic data here.
If you just chart these, as a line graph.
Not very exciting, not very clear.
MrExcel: Right Gavin: Yeah.
MrExcel: All right! so we have this ugly chart.
Gavin: Yeah, so I'm gonna show you how to turn that into an area chart like this, where the the light gray area, shows that there's a 95% chance of the values in the future year.
Fitting within that like gray area, right and the dark gray area, there's a 50% chance of the bad news fitting within that.
MrExcel: And then the dark line is...
Gavin: The the dark line is the average, expected in this scenario.
MrExcel: So, this chart is just horrible.
I can't figure out what the heck is going on, but this chart, it makes it very clear.
You know, what the the expected range is and while how it gets wider as time goes by, I guess that's the less.
Gavin: Yeah, so as you're projecting out into the future, you're gonna have less confidence as to where the way your outcome is gonna be.
MrExcel: Well, this chart is a lot better looking in the first year.
I would love that figure out, how to create this?
Why don't you take us through step by step?
Gavin: Yeah, let me show you.
Mr Excel: Okay!
Gavin: So, the first thing I've done, is I've manipulated the data.
Just a little bit from the table that we have up here.
So, the minimum column just stays as it was before.
So, as you can see this is just linked directly to the cells above.
MrExcel: So, it's just a straight formula equals C2, C3, C4, C5.
Gavin: To a straight forward link to exactly the cells.
MrExcel: Perfect, gotcha!
Gavin: The low is the bit of the stat, that's on top of the minimum.
So, here it's the difference between the low numbers that we had here and the minimum numbers.
MrExcel: So, 154 minus 136.
Gavin: Exactly! So, the formula very simple D2 minus C2.
MrExcel: Perfectly!
Gavin: Three minus C3.
MrExcel: Gotcha! Okay!
Gavin: So, the difference is between the mean, we're gonna do something different with we're gonna put that as a line chart.
So, we just leave that as it is before straight sets that on the on the stack.
MrExcel: Gotcha! Okay!
Gavin: The high, in a similar way to the low is now, the difference between the bit that's stacked on top of the line.
So, this is the difference between the high and the low about, between the 120 and 102.
MrExcel: Yeah!
Gavin: So, F3 minus D3, for the second one.
MrExcel: Good that's good.
Gavin: And the max.
MrExcel: Same again.
Gavin: How much of the stack is going to go on top of the high?
So,the difference between the max and the high column.
MrExcel: So, he just to interject here.
This is a little bit tricky because you can't really copy those formulas across.
I guess C and E are the exact same formula but all of the other formulas, it's you're comparing two columns that aren't necessarily adjacent.
Gavin: Absolutely, right I mean what you might, we might want to do.
If you've got lots of different content with different intervals is everything up to the mean and the ones on the other side of the mean, are okay!
But you just need to get that mean one.
So, you could start with the low copy them across and then replace the values to mean MrExcel: Okay! Great.
So, now let's see how we take this data.
Gavin: It's not gonna look great start, right but we'll get that.
MrExcel: I love those tricks, where it looks like you're going the wrong way and then all of a sudden it snaps back.
Okay!
Gavin: So we're gonna show this is a stacked area chart.
MrExcel: Stacked area, okay!
Gavin: Not quite what we were looking for...
So, the first thing we need to do is take the mean and we're gonna change that to a line.
MrExcel: So, you right click, change chart Gavin: And to a line chart.
MrExcel: Just regular, it doesn't matter whether it's stacked or...
Gavin: Just regular.
MrExcel: Gotcha!
Okay! Great.
Gavin: So, now we can start to see it MrExcel: Starting to come into play, all right.
Gavin: Now, the next thing we need to do is we need to remove the color for the...
For the minimum.
MrExcel: All right!
Gavin: And change yellow, change the other colors as well.
So, we're gonna change the colors, now to make this look good.
First thing we're gonna do, is we're going to take a minimum.
And we're going to remove the color there.
So, we go off to format, up to shape fill, and no fill.
MrExcel: There we go, okay!
Gavin: So, that takes that, piece out.
So, we're going to color in light gray the areas, that sit within the 95% confidence.
So, these outer ones here.
MrExcel: Okay!
Gavin: Blue, and the red, so we're gonna color those into going up to format, check fill and into the second MrExcel: Second light gray, okay.
Gavin: and then let's do the same, again on the red and shape fill and fill light gray.
So, it's coming together now.
MrExcel: Nice Gavin: We can change the color of the middle if we want, probably dark gray is fine.
and then we now just have to select the mean.
MrExcel: Yeah! There we go.
Gavin: And that's change.
MrExcel: Now, that's shape outline.
Gavin: That shape outline is the line of the another right area.
I'd like to change that to black.
MrExcel: Now, we still have that legend on the right hand side, that looks ugly.
Max high, low min, mean.
I'm not really sure that does anything.
What do you do with the legend?
Gavin: So, I know when you take the legend, I know you take the legend down.
I just select it and delete it and then I'll often just put some labels in below to explain or perhaps, the paragraph explaining below.
These are just, some people will be very familiar with but for others there'll be something.
MrExcel: That's a cool looking chart.
I certainly, if I had my choice between the first chart that you showed and this chart.
This explains a lot better, so yeah, right very nice.
Gavin: And you can do this with as many as many colors, as you want.
So, you might have a 50% to 90% and a 95% confidence interval.
Again shades of gray.
MrExcel: Now, of course you use this in financial modeling a lot.
I mentioned, you know our place down in Florida.
We're always watching the Hurricanes come in and they have the same kind of track.
This is where we think it's gonna hit, but there's a 10%, 20%, 30% chance.
It's like that hurricane cone chart.
Gavin: Absolutely.
MrExcel: Yeah, right.
Gavin: Practical example of where we say, so now and I'll use it.
Projecting investment returns or other forms of finance into the future.
Over one year or two years, we've got a lot more confidence.
We know that, 2015, we know there's a 95 percent confidence, that will be within in this little band here, by the time we go up to 2036.
It's a much wider about, MrExcel: Wow! That is a great chart, that is very good and I want to point out there, Gavin.
When Gavin sat down to do the podcast he said that clearly he should be doing a pivot table podcast because that's his thing, but we didn't have the the right data for that, so for all of Gavin's co-workers.
Yes, this should have it. This should've been pivot table.
Gavin: Keep asking me pivot table questions.
I can answer them, but you got something different, today guys.
MrExcel: There you go, all right!
That's great, all right! Gavin.
I've been looking on the model of Championships.
Thanks for that here in New York City.
Gavin:Thank you very much Bill, all right!
Thank you. Thank you!
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
