Simple Exponential Smoothing in R, gives us a flat-line forecast which is not much helpful. We looked into the reasons behind it in the Nigerian Crude Oil Production article.

Let’s use Excel now!

Step 1

Under Data Tab -> Under Analyse -> Click on Data Analysis.

If you cannot see the Data Analysis option, refer to this article

Step 2

Choose Exponential Smoothing

Step 3

“Input Range” is the cells containing data values (not dates, only values)

“Damping Factor” is the value of alpha we need excel to operate with. Here, we can use the optimal value of alpha calculated by R, in my case it was 0.8249.

“Labels” is ticked if the column heading is selected in the input range.

“Output Range” is the cell/ cells where we want the output

“Chart Output” is ticked because we want to see the graph of the series

Standard Errors : is ticked because we want the standard errors in our output as well so that we can construct the confidence intervals.

And we are done, let’s look at the results!

Excel first gives forecast values along with standard errors.

It also makes a very basic line graph plotting the original data and forecast values.

We add two column for calculating 95% Confidence Intervals.

This is an excerpt of how the entire table finally looks:

This is how the graph looks like after some basic editing.

We can also make a separate graph to show just the forecast for the last 21 months.

Also let’s keep the plot produced by R.

Let’s analyze what happened

The Excel-forecast seems much better than R-forecast because in the Excel-forecast, every point forecasted is based on all previous points.

\(Y_{t+1}\) is forecasted considering points including and before \(Y_t\) and similarly, \(Y_{t+2}\) is forecasted considering points including and before \(Y_{t+1}\)

Excel produces better results because unlike R, it generates Forecasts for all values and then uses them to forecast the next point. It then uses the previous forecast for the next forecast.

Let us also look at forecasts of R and Excel for 2020

Let’s see the forecast for \(Jan_{2020}\):

At this point, R did not have the value “1.6629” hence used “1.65” instead. Here, R has made an assumption that forecast for Dec 2019 is the observed value itself. So, R’s equation stands as :

Thus, R gives the same forecast for all months i.e. a flat-line forecast.

Excel starts forecasting values from Feb 2005, thus it does not have to make any assumptions like R. In doing so, Excel produces one-point forecasts.

It is not necessary to specify value of Damping Factor

Here, Excel will itself calculate the optimum value of Damping factor \((\alpha)\) and forecast accordingly.

We repeat the above steps, leaving the damping factor space with no value.

Excel does an even better job now!

The optimum value of \(alpha = 0.7\) as calculated by Excel.