Create a Seasonal Forecast with Forecast Functions

Locked lesson.

Upgrade

Quick reference

Create a Seasonal Forecast with Forecast Functions

Create a seasonal forecast using the FORECAST.ETS function. 

When to use

We create a seasonal forecast when our data has peaks and troughs throughout the year. 

Instructions

Seasonal data has peaks and troughs throughout the year. At certain months, the data might go up or down. This suggests that the data has some kind of seasonality to it. 

If we have determined that we have seasonal data, we need to use a different forecast function. 

  • Add a heading in column E and name it 'Seasonal Forecast'. 
  • Scroll down and type =FORECAST.ETS

The arguments are the same as the arguments used for FORECAST.LINEAR, they are just labeled differently. target_date is the x value (first date to predict), values is the known_ys (sales values) and timeline is the known_xs (dates). 

  • Copy the formula down. 
  • Apply Number Formatting. 

  • Select the Dates, the Values, and the FORECAST.ETS values (do not select the Linear values).
  • From the Insert tab, in the Charts group, click the Line Chart. 
  • Name the Chart, 'Seasonal Forecast'. 

Hints & tips

  • Hold down the CTRL key when making non-contiguous selections. 
Login to download
  • 00:04 In the previous lesson, we saw how we can use the forecast.linear
  • 00:08 function in Excel to perform a linear forecast on our data.
  • 00:12 And as I mentioned, the data that we're using has seasonality, so
  • 00:17 a linear forecast isn't the best way to predict future values.
  • 00:21 If we just review and remind ourselves,
  • 00:24 we created this linear forecast based off of our data.
  • 00:27 And the orange line here, which is showing the predicted values,
  • 00:31 isn't really in keeping with the peaks and troughs of our current values.
  • 00:35 So let's do this forecast again, but this time we're going to use
  • 00:39 a function in Excel that's specifically for forecasting seasonal data.
  • 00:44 So let's go back to our data.
  • 00:46 I'm going to add another heading up here,
  • 00:49 and we're going to call this Seasonal Forecast.
  • 00:53 Once again, I'm going to apply some formatting, and
  • 00:57 we'll just change the background fill of this cell to orange.
  • 01:02 So, let's scroll down to the bottom where we have our future values, and
  • 01:06 this time we're going to use a different forecast function.
  • 01:10 Now, this time we're going to use this first forecast option here, FORECAST.ETS.
  • 01:16 And ETS stands for exponential triple smoothing.
  • 01:19 Now, you don't need to remember that at all, all you need to remember is that,
  • 01:24 this is one of the functions that you can use if your data has seasonality.
  • 01:28 So let's double-click to select it, and you can see the arguments that we have.
  • 01:32 Now, target date, values, and timeline,
  • 01:35 those are pretty much the same as saying x, known x's, and known y's.
  • 01:39 So our target date is going to be our first predicted date.
  • 01:42 Remember in the previous formula that was our x value.
  • 01:45 Our values are our actual sales, so let's select all of those.
  • 01:51 And our timeline, all of the dates.
  • 01:55 So pretty much the same as the previous formula, the linear formula,
  • 01:58 just different terminology.
  • 02:00 Now notice that the next argument is the seasonality argument.
  • 02:04 So if you have particular seasonality in your data, for example,
  • 02:08 maybe your data peaks at certain quarters in the year,
  • 02:12 you could choose to add four for the seasonality.
  • 02:15 Now, if you don't add anything in here,
  • 02:18 then Excel is going to work out the seasonality for you.
  • 02:21 I will say that most of the time, it manages to do this pretty well.
  • 02:25 So we're not going to add any arguments in there.
  • 02:27 We going to hit Enter, I'm going to copy this formula down.
  • 02:32 Now once again, I'm going to add in a comma, and
  • 02:34 we're going to take those decimal places down.
  • 02:37 So now that we have this data, let's create another line chart and
  • 02:40 see if this looks better than the linear forecast.
  • 02:43 Now we want to include, our dates and our sales in millions, and
  • 02:47 we want to include our new dates.
  • 02:49 Now we don't want to include a linear date in this forecast, so,
  • 02:53 we just need to Hold Down+Ctrl, and need to select a Seasonal Forecast column.
  • 02:58 Now we can jump up to Insert, and let's insert a line chart.
  • 03:02 Now check this out, this one looks a lot better and
  • 03:06 a lot more in keeping with a data.
  • 03:08 Instead of that flat smooth linear line, we now have a line that contains peaks and
  • 03:13 troughs.
  • 03:14 So it looks like it's predicting these
  • 03:17 values based off of my known data more accurately.
  • 03:21 Now notice here that in between the actual known values and our predictions,
  • 03:26 we have a little gap just here.
  • 03:28 Now if you want to get rid of that gap, we just need to make sure that our predicted
  • 03:32 values overlap the current values.
  • 03:35 Who what I could do here is grab the last known value, Ctrl+C, and
  • 03:40 I could copy that over here.
  • 03:42 And that's going to make sure that that line joins up.
  • 03:44 Let's add a chart title.
  • 03:46 And we can Ctrl+X and we're just going to move it up the top here.
  • 03:51 And we're going to place that underneath and
  • 03:54 make sure everything is lined up so we can get a good comparison.
  • 03:59 So, far if I zoom out a little bit, so we can see all of these,
  • 04:03 you can see the original line charts are showing our original data,
  • 04:06 a linear forecast, which didn't work too well for
  • 04:09 the kind of data that we're using, but would work well if you have linear data.
  • 04:14 And now we have our Seasonal Forecast, which looks a lot better.
  • 04:18 In the next lesson,
  • 04:19 I'm going to show you how you can add confidence bounds to your forecast.
  • 04:23 So I'll see you over there.

Lesson notes are only available for subscribers.

Gift this course