In a time series, the data points are measurements taken over time. A time series can include cyclic data, perhaps due to seasonal changes. Thus, a time series can encompass numerous peaks and valleys. Excel 2016 has added five new functions for forecasting, along with a one-click forecasting capability. Joe shows you how to use these new capabilities and effectively utilize the information in time series to make predictions.
- View Offline
- [Voiceover] Let's turn our attention to forecasting. We typically make forecasts from Time Series. As the name implies, a Time Series is a sequence of measurements taken over successive intervals in time. Now if you're in business, it's especially important to understand Time Series so that you can plan for future needs. Here's an example. Monthly United States auto production from January 1991 to June 2003. The graph shows the peaks and values and I've added a regression line to show the overall trend.
As you can see, the trend is negative which might say something about the overall state of the economy during those years. That downward overall trend is one component of the Time Series. Another component is the way the peaks and values vary around the trend line. That's called the Cyclic Component. If we have patterns that repeat over time, that's a Seasonal Component and as always, an Irregular Component due to the effects of sporadic influences. Now unlike regression analysis, we won't try to find an equation that fits this series; The math is way too complicated.
Instead we use an Excel tool that examines the Time Series and uses it to make forecasts. It's new in Excel 2016 and it's called One-Click Forecasting. To use it, we select columns A and B, notice that dates are in column A. That lets the tool know we're looking at a Time Series. On the data tab in the forecast area, click on Forecast Sheet. The Create Forecast Sheet dialogue box tells you that Excel is already working on a Time Series Chart and forecast, along with 95% confidence intervals.
It's just waiting for you to make some modifications. So we'll click Options and that expands the dialogue box. Notice under seasonality Excel has automatically detected six, the number in the box next to Set Manually and your pattern that repeats over six months. You can change this value but let's go with that. Let's not get too greedy with the forecasts. I'll move the forecast and the date back to January 1st of 2005. And to check forecast accuracy, I'll have the forecast interval begin with the date for which we already have data, January 1st, 2002.
Now i'll click Create. This opens up the forecast sheet with a table in columns A through E and a Time Series Chart, which you can modify like any other Excel chart. The predictions and confidence limits are in orange and you can see the numerical values in the bottom rows of the table. In the forecast curve, the six months between values reflects the six-month seasonal component that Excel found. And you look at the overlap between the blue and the orange and you see it's pretty impressive, although a little less so at the end.
He explains how to organize and present data and how to draw conclusions using Excel's functions, charts, and 3D maps and the Solver and Analysis ToolPak add-ons. Learn to calculate mean, variance, standard deviation, and correlation; visualize sampling distributions; and test differences with analysis of variance (ANOVA). Then find out how to use linear, multiple, and nonlinear regression testing to analyze relationships between variables and make predictions. Joseph also shows how to perform advanced correlations, variable frequency testing, and simulations.
By the end of this course, you should have the foundational knowledge you need to take other statistics-related courses and perform basic analysis in the workplace.
- Using Excel's statistical functions and 3D charts
- Visualizing sampling distributions
- Performing comparisons with ANOVA
- Performing two-way analysis with ANOVA
- Analyzing linear regression
- Performing multiple regression and nonlinear regression analysis
- Making advanced correlations
- Testing variable frequencies
- Running simulations