Linear Trend function in Excel

Scene 1:

Welcome to the time series trend analysis tutorial. In this video we'll demonstrate the use of NumXL and NxTrend to conduct a linear trend and back-test it. We'll be using monthly sales figures of a hypothetical company for our example.

Scene 2:

We have already plotted the sample data and used the Excel linear trend feature. The trend line goes 5 months beyond the end of the sample data for forecast purposes. The line uses all the available information, but one may wonder how well does it forecast the data over the course of the 24 month period.

To answer this question we need to construct several linear trends using portions of the data. The NumX Trend function makes this task pretty straightforward. NxTrend can not only compute the forecast point, but it can also establish a standard error and the confidence interval for the forecast. In your spreadsheet select the F5 cell then type NxTrend in the formula bar and click on the FX button to show the function dialog box.

Scene 3:

For X select all the available observations up to that point. Skip POrder as it's used by polynomials, and skip Const so the function calculates the best value. For horizon select 0 for an in-sample forecast. For return type select 1 for the forecast mean and press OK.

Scene 4:

The function returns N/A because we don't have enough data to fit the trend. Copy the formula to the cell below it. Edit it and update the input cells to include all available observations to date. Log the start cell address, then copy the formula to the cells below it.

Scene 5:

For an out-of-sample forecast go to the end of the series and copy the formula to the cell below. Modify the input data range to exclude the empty cells at the end, note that a 30 is not 0 but 1 to indicate the number of steps to forecast for. Log the ending address of the input data range. Copy the formula to the cells below it.

Scene 6:

Let's repeat the same process, but now for upper and lower confidence intervals. First copy the formula from F5 to G5 and to H5, then edit it.

Scene 7:

Set the return type to two for the upper limit interval. The significance level is assumed to be at 5%, click OK. 

Scene 8:

Do the same for the lower limit interval, but set the return type to three.

Scene 9:

Copy the values to the end of the sample data. For an out-of-sample forecast we initially copy the formulas to the cells below then edit them to include only the sample data. Log the ending cell range and copy the values to the remaining cells.

Now let's plot the back test one step forecast along with a confidence interval.

Scene 10:

As we can see the forecast line did not track the sample data very well throughout the 24 month period.

Scene 11:

That is it for now, thank you for watching!