Time Series Forecast Using Regression Analysis in Excel

This video demonstrates how to perform Time Series Forecast Using Regression Analysis in Excel, considering Seasonality with Trend.

Now using the regression equation, you can make predictions. For example, you can predict year 5 quarter 1 sale to be = b0 + b1*q1 + t where b0 is the intercept 0.145625 and b1 is -1.36313, q1 is 1, and t is 17, because t = 1 for year 1 quarter 1, ..., t = 16 for year 4 quarter 4.

And you can measure the model performance by comparing the predictions based on the regression equation and the actual data (e.g. year 1-4 data in this example), calculate the MSE (mean squared error) which is = sum of sqrt(actual - predicted value) for each observation/# of observations (in this case the # of observations is 16). The smaller MSE the better the model is.