Create a Linear Forecast with Forecast Functions

Locked lesson.

Upgrade

Quick reference

Create a Linear Forecast with Forecast Functions

Use Forecast Functions to create a Linear Sales Forecast. 

When to use

We create a linear forecast when we have linear data that gradually slopes up or down. 

Instructions

Creating a forecast helps us identify trends in data and predict future values. Forecasts work best with time-based data that changes over time. 

When thinking about creating a sales forecast, we need to determine if our data is linear or seasonal. Linear data is generally data that smoothly trends up or down. 

It is recommended that we create a quick line chart which will help us determine what kind of data we are using and which forecast functions are most suitable. 

  • Press CTRL+A to select all the data. 
  • From the Insert tab, in the Charts group, click Line. 

In our example, the data shows peaks and troughs, not a smooth upward or downward line. This suggests that this dataset has some kind of seasonality. 

In this example, we would create a seasonal forecast, not a linear one. However, we will create one first so we can observe the difference. 

Before we begin, we need to extend our dates downward. We are going to be creating a forecast for the next 2 years. 

Create a Linear Forecast

  • In column D, type the heading 'Linear Forecast'. 
  • Scroll down to the first date we need to predict. 
  • Type =FORECAST.LINEAR

This function has three arguments: x, known_ys, and known_xs. 

x - this is the first predicted date. 

known_ys - these are the known sales values. 

known_xs - these are the known dates. 

  • Copy down and apply number formatting. 

  • Select all data. 
  • From the Insert tab, in the Charts group, select the Line Chart. 
  • Give the chart a title, 'Linear Forecast'. 

Note how the predicted values (orange line) slope gently up which is not in keeping with the format of our known data. This is because we have seasonal data, not linear. 

Login to download
  • 00:04 In this section of the course, we're going to shift our focus and
  • 00:07 we're going to start to talk about forecasting.
  • 00:10 Now, what exactly is forecasting?
  • 00:12 Well, forecasting allows us to see trends in our data and
  • 00:16 also predict future values and forecasting very much applies to time-based data.
  • 00:22 So data that changes over time.
  • 00:25 And we have quite a few different methods that we can use in Excel to forecast
  • 00:29 future values.
  • 00:30 So let's start out in this lesson by taking a look at some of the forecast
  • 00:34 functions.
  • 00:35 Now if we look at our data, you can see here that I just some basic data.
  • 00:39 And this is perfect data really for forecasting.
  • 00:42 I have some dates and then I have some sales in millions.
  • 00:46 So you can see here that my dates run from 2011 all the way down to 2022.
  • 00:53 So the end of the year 2022.
  • 00:56 So maybe these are sales figures for a global megastore.
  • 01:00 And if you take a look at my dates, you can see that a reading of the sales has
  • 01:04 been taken at four points throughout the year.
  • 01:07 So the last day of January, the last day of April, the last day of July, and
  • 01:12 the last day of October.
  • 01:13 And that pattern repeats throughout this data set for each of those years.
  • 01:18 So what I want to do here is I want to predict what the future sales mine
  • 01:22 being within a reasonable degree of accuracy for the next two years.
  • 01:27 So the first thing I'm going to do here is I'm basically going to copy these dates
  • 01:31 down for the next two years.
  • 01:32 So I'm going to copy and paste twice and then we can simply Ctrl F and
  • 01:40 I'm going to replace 2022 with 2023.
  • 01:45 And we can do the same for these Ctrl F,
  • 01:49 I'm going to replace 2022 again with 2024 or like so.
  • 01:55 Now the first thing we really need to do here is just gain some insight into our
  • 01:59 current data because this is going to help us determine what type of forecasts we
  • 02:04 need to do.
  • 02:05 So what I'm going to do here is I'm going to select all of my current
  • 02:09 known values and we're going to quickly insert a basic line chart.
  • 02:14 Now when we insert a line chart,
  • 02:17 this gives us an idea as to what type of data we currently have.
  • 02:20 Now notice here that this line has lots of peaks and lots of troughs.
  • 02:25 So this could imply that there is some kind of seasonality to our data.
  • 02:30 Sales go up at certain points in the year and
  • 02:33 they go down at other points in the year.
  • 02:35 And this is a really important thing to determine because data that has
  • 02:40 seasonality is going to affect how we do our forecast.
  • 02:43 If this line was more flat and consistent, so maybe or just slowly sloping up or
  • 02:48 slowly sloping down, that would be what we call a linear forecast.
  • 02:53 So to know what type of data you have is really important before you start
  • 02:57 the forecast.
  • 02:58 Now we're going to do a linear forecast, but
  • 03:00 we're also going to do a seasonal forecast.
  • 03:02 So let's just add a title here, and I'm just going to call this, line chart.
  • 03:09 Let's just grab it and move it somewhere over here.
  • 03:11 Now we're going to do a linear forecast first of all, so
  • 03:15 you can see why it's not suitable for the type of data that we have.
  • 03:20 Remember linear is more of a flat line that slopes up or slopes down.
  • 03:24 So I'm going to add another column to my data set and
  • 03:28 we'll just call this one linear forecast.
  • 03:31 And let's just apply this formatting.
  • 03:34 But I am going to change the background fill color just here.
  • 03:38 So let's go with a blue color.
  • 03:41 So now if we scroll down to where we have our new dates,
  • 03:45 this is where we can use Excel's inbuilt forecast functions.
  • 03:49 Now you can see as I start to type in forecast,
  • 03:52 we have quite a few of them in there.
  • 03:54 The one at the bottom, FORECAST, that's a legacy function, and
  • 03:57 that one's available in older versions of Excel.
  • 04:00 Now we're doing a linear forecast, as you might guess,
  • 04:02 we have one in here called FORECAST.LINEAR.
  • 04:05 Let's double-click to select.
  • 04:07 Now we have three arguments.
  • 04:08 Here we have x, known ys, and known xs.
  • 04:12 Now our x values in our data set are the dates and the ys are the values.
  • 04:19 So our first argument is x.
  • 04:21 And what we want to select here is the first date.
  • 04:25 that we want to predict, so B55,.
  • 04:28 We then need to provide our known ys.
  • 04:31 So our ys are the sales.
  • 04:33 So we're going to select this range of sales,.
  • 04:37 And then we need to provide the known xs.
  • 04:40 So those are the known dates.
  • 04:42 Let's close the bracket, hit Enter and then we can drag this down.
  • 04:48 And if we want to we can remove those decimal places and
  • 04:51 we could add a comma separator, so it matches the data above.
  • 04:55 So now what we can do is we can create another line chart and
  • 04:59 include a linear forecast.
  • 05:00 So I'm going to select all of my data up to insert and
  • 05:03 let's insert another line chart.
  • 05:06 Now, this might give you a good indication as to why the linear forecast option is
  • 05:11 not appropriate for the data that we are using.
  • 05:14 Our data indicate seasonality.
  • 05:16 We have peaks and troughs, whereas a linear forecast,
  • 05:19 it is always more of a smooth line.
  • 05:20 So you can see here this orange line which is showing that forecasted data
  • 05:25 doesn't really match very well with our current data.
  • 05:29 So this isn't a particularly accurate forecast for the type of data that I have.
  • 05:33 So this is a really good way of understanding the difference between these
  • 05:37 different forecasts.
  • 05:38 So let's call this linear forecast and I'm just going to cut it and
  • 05:43 we'll just paste it up here next to our other line chart so
  • 05:48 that we can get a good comparison.
  • 05:50 So that is how you can do a linear forecast.
  • 05:53 And if you have linear data then this is going to be perfect.
  • 05:56 In the next lesson, I'm going to show you the method that I would use for seasonal
  • 06:00 data and you'll find that it integrates a lot better with the current known data you

Lesson notes are only available for subscribers.

Gift this course