Show Uncertainty in Predictions with Shaded Bands
by Jon Peltier
Thursday, April 2nd, 2009
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
A reader emailed me this chart of GDP, with actual values through late 2008 and projected values for the next two years. To illustrate the uncertainty in the predictions, colored bands were drawn alongside the solid line prediction. The bands become lighter as the distance from the prediction line increases. The reader wondered how to create this effect in a chart.
The chart comes from page 34 of A Preliminary Analysis of the President’s Budget and an Update of CBO’s Budget and Economic Outlook, published in March 2009 by the Congressional Budget Office.
I’ve manually digitized the data into this tall thin tower of numbers.
Here is the data plotted in a line chart.
To capture an assumed uncertainty in the numbers, I’ve set up the following table, in which I’ve assumes a minimum and maximum for January 2009 onward, and I’ve inserted intermediate columns a, b, c, and d.
When I add these columns of data as additional lines in the chart, I get the following. The lightest gray lines are difficult to resolve, but then, they are the most uncertain. This is fine, but it doesn’t have the filled area effect.
To achieve filled regions, I will use a stacked area chart type. In an adjacent range I’ve added the following table. The dates are the same, as are the minimum and midline predictions. The rest of the data are the calculated differences between each line and the previous, which capture the height of each colored band.
Using the series from Min through Max I’ve created a stacked area chart. There are my colored bands.
I changed the bands to appropriate shades of gray, and the minimum series is not filled at all. You can use any color scheme you like, and as many different colors and data sets as you need. I used shades of gray because these were already plentiful in my custom color palette.
To get the solid line for the midline predictions, add the midline data as a new series. Initially it’s plotted as another area stacked upon the rest.
Convert the added series to a line chart type, and format it appropriately (I’ve used dark gray).
That was pretty easy, and it’s got to be one of the shortest tutorials I’ve ever written.
Related Posts:
- Excel Charts With Horizontal Bands
- Plotting Values Against Limits
- Contour and Surface Charts in Excel 2007
- Excel Chart With Irregular Horizontal Bands
- Excel Chart With Colored Quadrant Background
- Category Axis Tricks for Line and Area Charts – 2
Posted: Thursday, April 2nd, 2009 under Example Charts.
Comments: 15
Comments
Comment from Worm
Time: Thursday, April 2, 2009, 4:47 am
The original chart is from a Congressional report
http://www.cbo.gov/ftpdocs/100xx/doc10014/03-20-PresidentBudget.pdf
Page 34 of the pdf.
Comment from Jon Peltier
Time: Thursday, April 2, 2009, 5:45 am
Thanks, Worm, I’ve updated the article.
Comment from TV
Time: Thursday, April 2, 2009, 7:31 am
Looks like you’re ready to predict hurricane paths.
Comment from Rod McInnis
Time: Thursday, April 2, 2009, 7:54 am
That is really cool. It seems rare that I see something new to me these days. I like it.
Comment from Jon Peltier
Time: Thursday, April 2, 2009, 8:05 am
TV – Ha ha, sure, but I’d need wider bands than that!
Comment from AlexJ
Time: Thursday, April 2, 2009, 8:18 am
Very impressive! Should we call this a “Cat o’ Nine Tails” chart?
Comment from DMurphy
Time: Thursday, April 2, 2009, 9:08 am
I use a similar chart in our monthly executive presentation as follows:
Every month, our business units are required to update their forecasts and we use this type of chart to try to guage accuracy. We do this by comparing the y-t-d position of Actuals against the Budget, Last Year and Forecast then extrapolate the Actual line by the variance percentages of each data “version” to arrive at what you refer to as the mid-point data. We only show a single colour for the “spread” but I like the look of what you show in your tutorial and will try it out for this quarter’s reports – thanks for the idea!!!
Here is an example: http://www.box.net/shared/pb0mnbc5k0
The data is sourced from one of our Essbase cubes and the slice of data controlled by the drop-downs at the top-left of the screen.
Comment from Michel Gerday
Time: Friday, April 3, 2009, 6:21 am
Usual name (amonst economists) is “Fan chart”.
Here are example I made for DG ECFIN’s forecasts:
http://ec.europa.eu/economy_finance/publications/specpub_list12526.htm
http://ec.europa.eu/economy_finance/publications/publication_summary12532_en.htm
Other Fan Charts by the Bank Of England:
http://www.bankofengland.co.uk/publications/inflationreport/irfanch.htm
Comment from libin
Time: Tuesday, April 7, 2009, 4:05 am
It was very professional!I study a lot of things here in the past six months!
thank you very much!
a boy come from china!
Comment from Eric
Time: Friday, July 31, 2009, 10:35 am
Thanks for this tutorial. One question I have is how did you come up with the worksheet calculations to determine the max, mins, and intermediates by date?
Thanks again for your insights.
-e-
Comment from Jon Peltier
Time: Friday, July 31, 2009, 5:37 pm
Eric -
I wrote this post some time ago, so I don’t remember exactly what I did. I think I simply made up the values based roughly on the scanned image.
Comment from Fin
Time: Tuesday, January 18, 2011, 6:34 am
How did you get your base graph(projection V/s date) so smooth.
I used the same values and ended up with a weird step kind of a graph.
http://www.freeimagehosting.net/image.php?3f8fd81acb.jpg
Would be of great help if you could point out what I’m doing wrong.
Thank you
Comment from Jon Peltier
Time: Tuesday, January 18, 2011, 9:53 am
Fin -
My dates are in month-day-year format, first day of the first, fourth, seventh, and tenth months (first day of each quarter). I’ll bet yours are interpreted as day-month-year, first, fourth, seventh, and tenth days of January of each year.
Comment from Fin
Time: Friday, January 21, 2011, 5:26 am
Magnificent!
Thanks a ton Jon. Let me take this opportunity and say that your blog is an amazing find! I am totally hooked onto it and going through the archives. So I guess I be pinging you with a lot of questions :-)
Comment from JpE
Time: Thursday, January 12, 2012, 1:55 pm
Mr. Peltier.
Your tutorial is excellente, thank you. Now i have a new tool to use in my job. I achieved to make this kind of graph.
Greetings from Ecuador.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.