This video covers: Using the Fill Handle to generate a straight-line forecast. Using the new Exponential Smoothing ETS.FORECAST function in Excel 2016. Using Forecast Sheets in Excel 2016. Seeing R-Squared using a trendline on a chart.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1972 - Forecasting What Number Comes Next!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question: We have a time series here, and values for various years, and we don't know what happened in 2014-2015, or what's going to happen in 2016.
So the question is: How can we forecast what's going to come next?
Well, you know, there's a lot of tools in Excel that help us do this.
The very first thing I want to do though is, I want to see the data, I want to take a look at a graph of the data, so I'm going to insert, a line chart would work, but a column chart works, Recommended Charts, they're offering a Line, a Scatter Chart, a Column Chart, a Column Chart to me is good.
Alright, so I'm going to click OK.
And we get our nice little chart there showing usage.
Alright, now one of the tools, that's built into Excel, it'll just look at this entire time series, and build either a straight line, or a log forecast.
That’s for something that's like going up like a hockey stick, I'm not really sure this is going up like a hockey stick.
So I'm going to choose one of these columns here, I'm going to right click, and say Add Trendline...
And you'll see that the line gets drawn in, and because of these low numbers back here, 30-40 years ago, they're forecasting that there's going to be a huge drop in 2014, and we're going to end up right there.
Now, over here on the right hand side, I always like to choose Display Equation on the chart, and most importantly, display the R-squared on the chart.
R(2) (R*R) is a statistic that ranges from -1 to 1.
Anything that's not close to the ends, either 1 or -1, is horrible.
So in this case, that line is really, really bad, 0.77 (77%)!
I mean, well, really bad would be 0, but, this is clearly something we cannot rely on.
So, I'm going to go back here to the trendline, where I just take a quick look at Exponential, see, that gets to about 85%, but still, it just doesn't look like to me, me after all these years of increasing up up up, they were, all of a sudden, going to drop off next year.
I just simply don't buy it!
You can go through the various settings here, maybe Moving Average?
But anyway, you know, the bottom line is, using this whole string of historical results, is going to result in a forecast that to me is not very accurate.
And it's also helpful just to eyeball this, and say: OK it looks to me, like these 1 2 3 4 5 6 last data points, are actually, you know, showing a clear trend.
and what if I, instead of, using all historical 30 years here, what if I use just the last six?
Alright, so let's do that.
OK a little bit of copy and paste there, brought a new heading over now.
When I select this data we're going to create a chart, insert 2-D column chart, and now we right-click, and say Add Trendline...
Again linear, again come down and choose these settings, again Display Equation on the chart, display R-squared on the chart.
Look at that, the R-squared is now up to 0.9952!
Which means that this trendline does a really good job, of modeling the past history.
OK now, at this point, Now that I know these six points are at least giving us a good historical trend, we have a couple of options!
The first option is fairly complicated, using either the forecast or the LINEST functions.
But, what I much prefer to do, is something incredibly simple, and it does the exact same thing as the complicated formulas do.
Choose your entire time series that you want to base the future on, go to the square dot in the lower right hand corner, and then, drag down!
Alright, and those three points right there, those are forecasted points, based on the LINEST function for the history.
Now it's so simple, I don't want you to have to sit here and watch me build the whole LINEST function.
So we'll speed this section of the video up.
Alright, there you go, you see that, we get the exact same results, as just simply dragging the fill handle.
So learning how to do an array formula up here with LINEST, and then building the actual slope and intercept, plus slope times of the year!
Yeah, that's a great video for another day.
But using the fill handle trick works just as well!
Now, if you happen to have Excel 2016, they've added a great new feature to Excel 2016, particularly if you have monthly data with some seasonality.
Now this is just yearly data, so I'll come here to forecast, it actually changed the dates in column A, to be 12/31 of that year, and we'll ask for a forecast sheet.
Look at that, it's just, they take a look at the data, they figure out what's going on, and they project a forecast, plus they project a confidence interval.
Right, and it can be either a line chart like this, or a column chart with little arrow bars, showing where they think it's going to be.
Some options down here, they're going to be off the screen, we're going to specify how wide of a confidence interval detects seasonality, it figured out all of those time line range and values range.
Alright, so this is all new in Excel 2016, it actually inserts a brand new worksheet showing the history, and then where they think it's going to go.
Let's see down here, their numbers for 2015, '16 and '17.
I'm just going to copy those to compare, CTRL+C, come back to the LINEST data, and E, S, V, to paste values.
And so, using the old straight-line forecast function, we had 847 for next year, 861 here.
903, 934.
959, 1008.
Two different answers, this is using brand new functions called FORECAST.ETS ! Wow, the second time one week that I went into Excel 2016 help, was up with this!
FORECAST.ETS uses an exponential smoothing algorithm.
Again, brand new in Excel 2016.
Alright, so there you have it, a few different techniques for figuring out what number is going to come next!
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question: We have a time series here, and values for various years, and we don't know what happened in 2014-2015, or what's going to happen in 2016.
So the question is: How can we forecast what's going to come next?
Well, you know, there's a lot of tools in Excel that help us do this.
The very first thing I want to do though is, I want to see the data, I want to take a look at a graph of the data, so I'm going to insert, a line chart would work, but a column chart works, Recommended Charts, they're offering a Line, a Scatter Chart, a Column Chart, a Column Chart to me is good.
Alright, so I'm going to click OK.
And we get our nice little chart there showing usage.
Alright, now one of the tools, that's built into Excel, it'll just look at this entire time series, and build either a straight line, or a log forecast.
That’s for something that's like going up like a hockey stick, I'm not really sure this is going up like a hockey stick.
So I'm going to choose one of these columns here, I'm going to right click, and say Add Trendline...
And you'll see that the line gets drawn in, and because of these low numbers back here, 30-40 years ago, they're forecasting that there's going to be a huge drop in 2014, and we're going to end up right there.
Now, over here on the right hand side, I always like to choose Display Equation on the chart, and most importantly, display the R-squared on the chart.
R(2) (R*R) is a statistic that ranges from -1 to 1.
Anything that's not close to the ends, either 1 or -1, is horrible.
So in this case, that line is really, really bad, 0.77 (77%)!
I mean, well, really bad would be 0, but, this is clearly something we cannot rely on.
So, I'm going to go back here to the trendline, where I just take a quick look at Exponential, see, that gets to about 85%, but still, it just doesn't look like to me, me after all these years of increasing up up up, they were, all of a sudden, going to drop off next year.
I just simply don't buy it!
You can go through the various settings here, maybe Moving Average?
But anyway, you know, the bottom line is, using this whole string of historical results, is going to result in a forecast that to me is not very accurate.
And it's also helpful just to eyeball this, and say: OK it looks to me, like these 1 2 3 4 5 6 last data points, are actually, you know, showing a clear trend.
and what if I, instead of, using all historical 30 years here, what if I use just the last six?
Alright, so let's do that.
OK a little bit of copy and paste there, brought a new heading over now.
When I select this data we're going to create a chart, insert 2-D column chart, and now we right-click, and say Add Trendline...
Again linear, again come down and choose these settings, again Display Equation on the chart, display R-squared on the chart.
Look at that, the R-squared is now up to 0.9952!
Which means that this trendline does a really good job, of modeling the past history.
OK now, at this point, Now that I know these six points are at least giving us a good historical trend, we have a couple of options!
The first option is fairly complicated, using either the forecast or the LINEST functions.
But, what I much prefer to do, is something incredibly simple, and it does the exact same thing as the complicated formulas do.
Choose your entire time series that you want to base the future on, go to the square dot in the lower right hand corner, and then, drag down!
Alright, and those three points right there, those are forecasted points, based on the LINEST function for the history.
Now it's so simple, I don't want you to have to sit here and watch me build the whole LINEST function.
So we'll speed this section of the video up.
Alright, there you go, you see that, we get the exact same results, as just simply dragging the fill handle.
So learning how to do an array formula up here with LINEST, and then building the actual slope and intercept, plus slope times of the year!
Yeah, that's a great video for another day.
But using the fill handle trick works just as well!
Now, if you happen to have Excel 2016, they've added a great new feature to Excel 2016, particularly if you have monthly data with some seasonality.
Now this is just yearly data, so I'll come here to forecast, it actually changed the dates in column A, to be 12/31 of that year, and we'll ask for a forecast sheet.
Look at that, it's just, they take a look at the data, they figure out what's going on, and they project a forecast, plus they project a confidence interval.
Right, and it can be either a line chart like this, or a column chart with little arrow bars, showing where they think it's going to be.
Some options down here, they're going to be off the screen, we're going to specify how wide of a confidence interval detects seasonality, it figured out all of those time line range and values range.
Alright, so this is all new in Excel 2016, it actually inserts a brand new worksheet showing the history, and then where they think it's going to go.
Let's see down here, their numbers for 2015, '16 and '17.
I'm just going to copy those to compare, CTRL+C, come back to the LINEST data, and E, S, V, to paste values.
And so, using the old straight-line forecast function, we had 847 for next year, 861 here.
903, 934.
959, 1008.
Two different answers, this is using brand new functions called FORECAST.ETS ! Wow, the second time one week that I went into Excel 2016 help, was up with this!
FORECAST.ETS uses an exponential smoothing algorithm.
Again, brand new in Excel 2016.
Alright, so there you have it, a few different techniques for figuring out what number is going to come next!
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
