Quick Forecasts

Quick reference

• 00:04 In the previous lessons in this section, we've been using Excel functions
• 00:08 in order to create a forecast where we predict future values.
• 00:12 So now that you've learnt all of those,
• 00:15 I'm going to show you the super quick way to do exactly the same thing, and
• 00:20 that is, by using a utility in Excel called Forecast Sheet.
• 00:24 And this takes all of the hard work out of forecasting.
• 00:27 So I know what you're thinking, why on earth did you spend the last three
• 00:31 lessons showing us the difficult way when you're now about to show us the easy way?
• 00:35 Well, I always like to show people exactly how those forecast functions work,
• 00:39 because it really helps you understand exactly what you're doing when you're
• 00:44 forecasting your information.
• 00:45 So, if you ever do have to do a manual forecast, you're going to be well prepared
• 00:49 and know exactly what you're looking at, and what the different terminology means.
• 00:53 So let's take a look at how we can predict this data using Forecast Sheets.
• 00:57 Now we're using exactly the same data.
• 01:00 I still have on the bottom here, the next two years that I need to predict.
• 01:05 So what I'm going to do here, is I'm going to select all of the data, CTRL+A.
• 01:09 We're going to jump up to the Data tab.
• 01:12 And then all the way over in the Forecast Group, we have this Forecast Sheet button.
• 01:16 Now if we just pause a moment and take a look at that screen tip,
• 01:20 you can see that it says create a new worksheet to predict data trends.
• 01:24 And we can preview different forecast options before generating a visual
• 01:29 forecast worksheet.
• 01:31 So this is pretty cool, let's click on Forecast Sheet.
• 01:34 I want you take a look at that, like magic, it basically does our forecast for
• 01:39 us in one click.
• 01:40 We can see our forecast line in the middle here, and you can also see that it's added
• 01:44 an upper confidence and a lower confidence bound for us.
• 01:48 Now, can you notice one issue with the forecast that Excel created?
• 01:52 Well, if you said that this is a linear forecast and we have seasonal data,
• 01:56 then you would be correct.
• 01:58 If you take a look at that predicted values line, it's just very flat and
• 02:02 slowly sloping upwards.
• 02:04 So this is a linear forecast that it's trying to do.
• 02:08 Now we can change this, but before we do,
• 02:11 I just want to highlight to you two buttons that we have in the top corner.
• 02:15 So we can switch between showing our forecast as a line chart or
• 02:20 showing it as a column chart.
• 02:22 So again, our predicted values are showing in orange and our confidence levels
• 02:26 are these bars that you can see just here, the lower all the way up to upper.
• 02:31 Now, we're going to stick with a line chart this stage.
• 02:34 And if you take a look just underneath, we can specify when our forecast ends.
• 02:39 So, this is obviously taking it from the last data point that I have in
• 02:43 the selected data.
• 02:44 Well, we could click on the calendar here and
• 02:46 change that forecast end date to whatever we want.
• 02:49 So if I wanted to forecast the next four years, I could just change that to 2027.
• 02:54 So let's add in another year.
• 02:56 Why not?
• 02:57 Let's go for 2026.
• 02:59 Let's expand out options and
• 03:01 take a look at some of the things that we have in here.
• 03:04 Now the forecast start date, this is obviously our first predictive value date.
• 03:09 Now what you could do is, you could change this to maybe a week before so
• 03:14 that it overlaps with unknown data.
• 03:17 And this gives you a pretty good idea as to how accurate your forecast is.
• 03:21 So if we change this from October the 30th to, let's go for
• 03:25 a week before and change it to October the 22nd,
• 03:28 when I click away, take a look at the orange line in my chart.
• 03:32 As you can see, it adjusts and
• 03:34 it pretty much almost exactly overlaps our predictive values.
• 03:38 So I can see that this forecast is probably going to be pretty accurate.
• 03:43 Now, underneath we have seasonality and it says detect automatically.
• 03:48 I could leave it on that or I could set it manually.
• 03:51 So I have four different dates, so I'm just going to take this up to four, and
• 03:55 you can see how that changes my forecast.
• 03:58 If I want to, I can include forecast statistics,
• 04:01 I'm not going to in this example.
• 04:03 And then we just simply have our timeline range, and values range, and
• 04:07 we can choose how we want to fill any missing points.
• 04:09 So if we do have any missing data points, currently it's set to interpolation.
• 04:14 But what this basically means is that, if there are missing data points, Excel is
• 04:19 just going to do its best to work out how to fill those based on the previous and
• 04:23 the following data.
• 04:25 We could also change this to zeros if we just wanted to have nothing in there.
• 04:29 Now in general I always, leave this on the default option.
• 04:33 And then aggregate duplicates using an average.
• 04:35 So if we have duplicate data,
• 04:37 it's just going to take an average in order to plot that.
• 04:39 So when we're using Forecast Sheets we have quite a few additional
• 04:43 options that we can change.
• 04:44 But let's click on Create and see what we get.
• 04:48 And there we go.
• 04:49 We get something that's pretty similar to when we used our forecast functions.
• 04:54 We can drag this over.
• 04:55 I can add a chart title, I'm just going to call this Forecast.
• 05:00 And then of course, we can place this wherever we want to place it.
• 05:03 Now, notice what's happened here also, once we've created this Forecast Sheet.
• 05:07 It's basically added in all of the columns that we added in the previous examples.
• 05:12 So we have our forecast sales, our lower confidence, our upper confidence.
• 05:17 And if we take a look a bit further down,
• 05:19 we can see the values that it's predicting.
• 05:21 So if I click in the Forecast Sales column, you can see it's using forecast or
• 05:27 ETS, which is the same formula that we used.
• 05:30 And these calculations for the lower confidence bound and
• 05:33 the upper confidence bound.
• 05:34 We have a few extra things going on in here, but the resulting numbers are very,
• 05:39 very similar to what we had when we use the forecast functions ourselves.
• 05:44 So that is how you can use Forecast Sheets to create a really quick forecast without
• 05:48 having to type the functions in yourself.

Lesson notes are only available for subscribers.