Machine Learning Forecast with Excel

I discovered a year ago a new feature in Excel going much further than simple linear regression to realize forecast based on historical data. The function is straightforward to use and lead to pleasant results. I share with you in the following lines the way to use it so you can boost up your prediction game level in a few minutes.

The function that we will play with is FORECAST.ETS(target_date, values, timeline). Available since Excel 2016, the function requires 3 parameters, the target date for which you want to do the prediction, the range of values and the timeline associated to those values on which the prediction will be based. All the rest is automatic.

If you want to follow me step by step, you can download the following file example_wp_log_peyton_manning.csv (right click save) Which is the number of view per days of the Wikipedia page of Peyton Manning, an American football quarterback. As you can imagine the case is quite compelling as the number of visitor on his page follow a high seasonality.

1. open the file in excel and delete the first useless row:

2. Now go to the last row of the dataset and extend the dates until the end of the year 2016 (12/31/2016).

3. In cell C2906 enter the following formula =FORECAST.ETS(A2906,$B$1:$B$2905,$A$1:$A$2905) Fixing the dates and values ensure the forecasted data do not drift away by cumulating the forecast error.

4. Extend the cell C2906 until the end of the column, plot the result and you are done.

As you can see in the plot, the orange forecasted period visually follow the trend pretty well. It would be interesting but quite advanced to do a statistical analysis of the prediction to determine its accuracy. This could be a future article :)

I hope that you will find in your daily job a proper usage of the tool that I shared here and that it will motivate you to start exploring the growing field of data analytics and machine learning.