What is time series forecasting, and how does Excel make this task easy to do?
Time series forecasting is all about using existing data to make predictions about future events. Just as meteorologists can predict the path of a hurricane by its current path, you can use forecasting to spot trends in the data and make an educated guess as to where that data is headed.
In this article, you will learn about Excel’s built-in features that allow you to forecast (or extrapolate) from time series data to predict future values.
How to Create a Time Series
You would create a time series in pretty much the same way that you would enter data for any purpose in Excel. What makes a time series different than other data series is that the values correspond to different points in time.
For example, suppose you wanted to track the number of students in a particular class from semester to semester over a number of years. Then you would create a time series for enrollment.
Let’s open a blank Excel spreadsheet and fill in some data!
At this point, you could make a line or bar chart for this data if you wanted to. (For a refresher on making charts, you can check out: How to Make a Column Chart in Excel or How to Create Graphs in Excel.) However, our purpose is to create a time series forecast. In other words, we want to use the historic data to predict future data.
Trends, Seasonality, and Noise
This example shows a lot of variability in the data. Enrollment jumps from hundreds of students down to only dozens and back. So how can we make any predictions at all? The key is in teasing apart the underlying trend from the seasonal variation and random noise.
The theory of time series states that the data Yt is equal to the product of the trend Tt, the seasonal variation factors St, and a random noise factor Nt.
That is, Yt = Tt × St × Nt
In order to predict future values, we need to isolate the trend. Then the trend can be extrapolated by a simple linear regression. Finally, seasonality can be factored back into the data.
Using Excel’s Forecast Sheet
Fortunately, Excel does all the heavy lifting for us!
From the Data tab, click Forecast Sheet.
Time Series Forecast
If you click on Forecast Sheet without choosing any data, it will state that “Forecasting can’t be created.” Just click on the word “Options.” There, you can choose the Timeline Range and Values Range.
In our example, you will use the numerical time values found in column A as your timeline range and the enrollment data found in column D as values range.
Click create, and that’s it! Excel will reformat your data to include a few extra lines of forecasted values, including confidence bounds based on the confidence interval specified in Options.