Skip main navigation

Time Series Forecasting

Our second approach to forecasting is called time-series analysis. And we’re going to use this when we have time-series data. Time-series data is simply historical data that’s date and time stamped at regular intervals such that we can look at trends over time. And then we can infer that those trends are likely to continue. Let me pull up some time-series data on the screen. And we can see a good example. So here we have data from 1946 through 1959 of the average births per month in a certain city. So we have it looks like about 26 to 25 births per month in a given month. And we have that monthly, every month for several years.
So we can plot these values in a graph. And again, it looks something like this. So we see that the births were a little higher in the early 1940s. And then they seemed to drop a little bit as we get into the late 1940s. And then they go back up. So you see this upward general trend. But there’s another feature to this. It’s not a smooth line. There’s a certain spiky pattern that seems to be repeating over and over. In fact, it seems to be repeating every year, probably because there are certain time of year effects. So in other words, there’s a couple different things going on here. There’s an overall trend.
But there’s also a time of year effect that repeats. We sometimes call that a seasonal trend, because it reflects the passing of the seasons. With time-series analysis, we can identify both the overall trend and what the repeating portion is. And then we can just project that out into the future. I have here an illustration that shows how we might decompose this time series. So the top row here shows our initial data. And then the next three rows show a couple different things. So the second row here shows the overall trend. And here we do see that it is gradually going up. Beneath that is the seasonal trend.
We see this spiky pattern that seems to repeat every year, reflecting various time of year effects on birth and reproduction. The last row reflects every other influence in the data. So there might be just some particular months have higher or lower birth rates that aren’t explained by either the general trend or the seasonal trend. But we can decompose these three things. Now, you don’t actually have to do this part yourself. That’s fine. You can. But what you’re going to be able to do here is the software will be able to identify these three pieces. And in fact, if you add those three pieces up, you get that top row.
It’ll identify the overall trend, the seasonal trend, and any remaining residual. And it will just be able to project that out into the future. And so we’ll be able to get something that reflects all three of these going forward. So here we go now. If we imagine going past 1960, we can imagine that same upward trend continuing. And we can imagine that same seasonal trend continuing. But then we know that it’s not going to be perfect. My forecast won’t be perfect, because there’s also that random noise bit. There’s other things that are influencing the data other than those two things. That’s that bottom row here. And because of that, we add some uncertainty around our forecast.
And that is that bands of purple on the screen right there. And so in this case, the data are not so strong that I have high certainty. So I actually have– I have a projected forecast. But actually my band of uncertainty is somewhat large here. But I’m fairly confident going forward that it’s going to be somewhere in the neighbourhood of about 27 births per month with that fluctuating pattern. And this is what a time-series forecast is going to do. It’s going to take into account the overall trend, the repeating trend. And it’s going to include some uncertainty because of the amount of random noise in your data. Now, I’ve done this in some rather fancy statistical software.
But, in fact, you can do this in Excel if you have the desktop version. Sadly, this is not currently built into Excel Online. In the labs, if you have access to a desktop version of Excel, you will be able to practise this. Otherwise, you will at least be able to see the steps of this done in Excel. And there you have it. You can forecast and tell the future with just a little bit of historical time-series data.

Lesson 3: Time Series Forecasting

In this lesson, we’ll look at another forecasting method. This one is useful if we have a long history of data (for example, revenue over several quarters). In that case, we can use long-term trends in past data to predict future data. This is called time-series forecasting, and the math gets very tricky very quickly. However, there are some decent tools that will run the forecast for us in Excel.

Lab: Forecasting

One of the most powerful tools available to data analysts in Excel is the built-in time-series forecasting feature (though it’s only available in the Windows desktop version). In this lab, you’ll examine a company’s time-series data set showing output values at regular daily intervals, and then predict how those output values will look 100 days after the historical trend in the given data set.

The lab instructions can be downloaded as a PDF file here.

The data set for this lab can be viewed here. From the link, copy and paste all the data into a new worksheet in Excel Online.

Note: The forecasting tools presented in this lab are only available in the desktop version of Excel 2016 for Windows. They aren’t available in Excel Online or Excel for Mac. You can still participate in the lab if you don’t have access to the Windows version of Excel, though — see the lab instructions for details.

This article is from the free online

Essential Mathematics for Data Analysis in Microsoft Excel

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education