Add Confidence Levels

Quick reference

• 00:04 When we're forecasting data in Excel,
• 00:07 something that you'll commonly see on your line chart is an upper and
• 00:12 a lower confidence bound, and the default confidence level in Excel is 95%.
• 00:18 Now what exactly does that mean?
• 00:20 Well it means that 95% of predicted values will fall between
• 00:25 the Upper Confidence Bound and the Lower Confidence Bound.
• 00:30 And we can show this on our Line Chart.
• 00:32 So let's add in another column, I'm going to call this, Confidence Level.
• 00:39 Once again, I'm just going to use the Format Painter,
• 00:42 to copy across this formatting.
• 00:44 Let's give this another color, we're going to choose, let's go for
• 00:48 a purple background fill.
• 00:50 So let's scroll down.
• 00:52 Now, I want this to overlap again so I'm just going to copy and
• 00:56 paste this one across.
• 00:57 And we're going to type in FORECAST.
• 01:00 And this is the one that we want, FORECAST.ETS.CONFINT.
• 01:05 And if you read the little screen tip, it says, returns a confidence interval for
• 01:09 the forecast value at the specified target date.
• 01:12 So if we select this, you'll notice that the arguments that we have are pretty much
• 01:17 the same as what we've had in the other two functions.
• 01:20 So our target date is the first date that we want to predict,
• 01:24 our X value essentially.
• 01:26 The values, these are our Y values so that's the sales in millions.
• 01:31 So let's grab these.
• 01:32 And then our timeline is our known Xs, so it's all of our dates in here.
• 01:38 So let's grab those.
• 01:40 And once again you'll notice that much like with the seasonality,
• 01:44 we have an option to add the confidence level, that's the next argument.
• 01:47 And if we don't add in a confidence level,
• 01:50 Excel is just going to use that default of 95%.
• 01:53 So we're going to use the default.
• 01:55 Let's close the bracket, hit Enter, and then we can drag this down and
• 01:59 apply our number formatting.
• 02:01 So I'm going to add in a comma, and remove the decimal places.
• 02:05 And in fact, for these, we don't actually need that top value in there.
• 02:09 So now that we have these confidence levels, we can use these to work out
• 02:13 the Upper Confidence Bound and the Lower Confidence Bound.
• 02:17 So let's go to the top and add two more columns.
• 02:19 We're going to say Upper Bound and Lower Bound.
• 02:26 So let's widen out these columns.
• 02:29 Once again, we're going to use the Format Painter to apply some formatting.
• 02:33 And we'll make both of these the same color as they are kind of related.
• 02:38 So let's make these a yellow color.
• 02:43 So now what we can do, is we can use the confidence level just here
• 02:47 to work out our Upper Confidence Bound.
• 02:50 And this is just a formula.
• 02:51 We're going to say equals, I'm going to use my seasonal forecasted
• 02:56 value in cell E55, and I'm going to add the confidence level.
• 03:00 Let's hit Enter and let's drag this formula down.
• 03:05 So that's now given me the Upper Confidence Bound.
• 03:09 Now we're going to do something similar for the Lower Confidence Bound.
• 03:12 Again, we're going to take our seasonal forecasted value, but
• 03:16 this time we're going to minus the confidence level, and hit Enter.
• 03:21 And this is going to give us our Lower Bound.
• 03:24 So now looking at these values, let's say for
• 03:29 this first one, I know that 95% of my predicted values
• 03:34 are going to fall between 15633 and 24774.
• 03:39 So that is how those Lower and Upper Confidence Bounds work.
• 03:42 So now we can combine all of this into a line chart.
• 03:46 Now we don't need all of our data here,
• 03:50 what we want is we want our dates and our sales values.
• 03:55 We don't want the Linear Forecast, we do want our Seasonal Forecast.
• 03:58 Let's hold down Ctrl and select this column with those values.
• 04:03 And we don't want the Confidence Level, we want the Upper and Lower Bound.
• 04:08 So we can select both of these.
• 04:11 So now if we go to Insert, we can insert a line chart and check out what we get.
• 04:18 So now you can see the original data showing in blue,
• 04:22 you can see the forecasted values showing in orange in the middle there, and
• 04:27 then we can see what our Upper Confidence Bound is and our Lower Confidence Bound.
• 04:32 So let's add a Chart Title, and then we can move this into place so
• 04:36 we can get a good comparison between all four of our line charts.
• 04:41 So let's drag this over and resize, and
• 04:43 now you can get a good comparison between the different forecast types that you
• 04:48 can create using some of those basic FORECAST functions.

Lesson notes are only available for subscribers.