excel forecasting
Calikush.
ForecastingPart3.pptxTime Series Forecasting: Part 3 Trend, Seasonal, and Causal Forecasting Models
Steven Coy Ph.D.
Professor of Management
Linear Trend Projection
We can use regression to project a trend line.
Linear regression minimizes the sum squared error.
Equation for the trendline: = t
Independent variable is time, t.
The variable to be forecasted (y, the actual value of the time series period t) is dependent variable
The best approach in Excel is to use the TREND() function.
2
2
Seasonality Without Trend
We can model a time series with a seasonal pattern by treating the season as a dummy variable.
k - 1 dummy variables are required to model a categorical variable that has k levels.
For example, consider a quarterly seasonal pattern.
In this case, you will need 4 – 1 = 3 dummy variables.
Qtr1t =
Qtr2t =
Qtr3t =
Here is the general form of the quarterly seasonal model:
Material from Business Analytics by Camm, Cochran, Fry, Ohlmann, Anderson, Sweeney, and Williams. 2015
1 if period t is a quarter 1
0 otherwise
1 if period t is a quarter 2
0 otherwise
1 if period t is a quarter 3
0 otherwise
=
3
Seasonality Without Trend Example
Assume that seasonality follows a quarterly pattern.
In this case, you will need 4 – 1 = 3 dummy variables.
Q1 = 1 if t is in quarter 1, 0 otherwise
Q2 = 1 if t is in quarter 2, 0 otherwise
Q3 = 1 if t is in quarter 2, 0 otherwise
From Business Analytics by Camm, Cochran, Fry, Ohlmann, Anderson, Sweeney, and Williams. 2015
=
4
Seasonality Without Trend Example
From Business Analytics by Camm, Cochran, Fry, Ohlmann, Anderson, Sweeney, and Williams. 2015
In this model, independent variables are the three dummy variables and the dependent variable is sales.
Seasonality with Trend
This model contains both seasonal components and a trend component.
The data will include a period variable like in trend projection and seasonal dummies.
Here is the regression equation takes with quarterly seasonal variables and time, which handles the trend.
= t
Material from Business Analytics by Camm, Cochran, Fry, Ohlmann, Anderson, Sweeney, and Williams. 2015
Here we will combine the dummy variable approach for handling seasonality with the approach for handling a linear trend.
In the general form of the regression equation
= forecast of sales in period t
= 1 if time period t corresponds to the first quarter of the year; 0, otherwise
= 1 if time period t corresponds to the second quarter of the year; 0, otherwise
= 1 if time period t corresponds to the third quarter of the year; 0, otherwise
t = time period (quarter)
is the dependent variable, and the quarterly dummy variables Qtr1t, Qtr2t, and Qtr3t are the independent variables.
6
Smartphone Sales Time Series with Dummy Variables and Time Period
Material from Business Analytics by Camm, Cochran, Fry, Ohlmann, Anderson, Sweeney, and Williams. 2015
In this model, the independent variables are the three dummy variables and period, and the dependent variable is sales.
Table 5.12 shows the revised smartphone sales time series that includes the coded values of the dummy variables and the time period t.
Using the data in Table 5.12 with the regression model that includes both the seasonal and trend components, we obtain the following equation that minimizes our sum of squared errors:
= 6.07 0.146t
We can now use the above equation to forecast quarterly sales for next year.
Next year is year 5 for the smartphone sales time series, that is, time periods 17, 18, 19, and 20.
Forecast for time period 17 (quarter 1 in year 5)
= 6.07 1.36(1) 2.03(0) 0.304(0) + 0.146(17) = 7.19
Forecast for time period 18 (quarter 2 in year 5)
= 6.07 1.36(0) 2.03(1) 0.304(0) + 0.146(18) = 6.67
7
Using Regression Analysis for Forecasting
High quality forecasting models can include causal variables and time series variables in the same model.
Important to get timing right
Causal variables must have a time lag.
If you use causal variables, the values must have been available before the actual was known.
Time lags are unnecessary if you are creating an explanatory regression model, but they are essential if the model is used for forecasting.
When building a model, you can keep a set of indicator variables in the model if at least one indicator category passes the t-test.
8
Determining the Best Forecasting Model to Use
Which model is best for a given time series?
Sometimes the answer to this question is obvious given the patterns observed in the data.
Trend
Seasonal model without trend
Life is rarely this simple. In most cases, we will have to try a few models on historical data to see which is best.
Criteria for selection:
Low MSE
Low MAD
Bias close to 0
Forecast makes sense given decision context
9
Which is the Best Model?
| Method | MAE |
| MA(3) | 3021.08 |
| MA(6) | 2617.42 |
| ES(.1) | 2661.98 |
| ES(.7) | 3168.56 |
| Trend | 2317.90 |
| Seasonal without Trend | 2190.80 |
| Seasonal with Trend | 516.87 |
| Seasonal with Trend and Causal Variable | 391.17 |
