Rachel: There is no correct answer. You can do the student project a thousand ways. This candidate compared an AR(1) model and an AR(2) model using the statistical tests from the time series course. The purpose of the student project is to see if you can apply the concepts to an actual time series, not if you get the same conclusion as the authors of the textbook.

Jacob: Must we use the Durbin-Watson statistic, or is Bartlett’s test and the Box-Pierce Q statistic enough?

Rachel: The Durbin-Watson statistic is the best statistic for an AR model. It is covered in the regression analysis course, not the time series course, so it is not required.

Jacob: If the three tests indicate that the residuals are not white noise, must we find the correct ARIMA model?

Rachel: That depends on the test results and the objective of the student project. If the objective is to construct an ARIMA model and the test statistics are highly significant (not white noise), continue searching for a better model. If the test statistics suggest the residuals are close to white noise (but not completely white noise), or if you have already examined models of order 2 or less and you have examined seasonality and first and second differences, you are done. Not every time series can be perfectly modeled by an ARIMA process.

Jacob: This candidate says the residuals from the AR(2) model were much higher than those from the AR(1) model. Does that make sense?

Rachel: Some candidates assume an AR(2) model means a simple linear regression on the value lagged two periods back. This often gives higher residuals. The AR(2) model actually means a multiple regression on both one period and two period lags. This give lower residuals. Many candidates make this error. Initially, we are not rejecting student projects that have this error, since multiple regression is not taught in the time series course.

Jacob: Will the standards for the student project change over time?

Rachel: As we explain common errors and provide more illustrative Excel spreadsheets and explanations of the proper techniques, we will require more accurate student projects.

Rachel: The sample autocorrelation function is the core of the time series course. The student project should show that you know how to calculate the sample autocorrelations, form a correlogram, and construct an ARIMA model.

Jacob: What is the sample autocorrelation function?

Rachel: Suppose the time series has N elements: {1, 2, 3, …, N}. The sample autocorrelation of lag k is the correlation of the elements {1, 2, 3, …, N-k} with the elements {k+1, k+2, …, N}.

Jacob: Do we compute the sample covariance and divide by the products of the sample standard deviations?

Rachel: Excel has a built-in correl function. Excel has built-in functions for many of the statistical techniques.

Jacob: How do we compute the sample autocorrelation for all lags?

Rachel: Excel has many ways of doing this: index and offset functions, relative and absolute references, and VBA macros. We show simple code to form a correlogram on an illustrative worksheet on the web site.

Jacob: Does it make sense for the Durbin-Watson statistic to indicate white noise and the other two tests (Bartlett’s test and the Box-Pierce Q statistic) to indicate not white noise?

Rachel: These are statistical tests. They give confidence intervals, not absolute answers. The Box-Pierce Q statistic and Bartlett’s test are more strict; they are less likely to suggest that the residuals are white noise.

Jacob: Suppose we take first differences to convert a random walk to white noise. Can we use the Durbin-Watson statistic or the Box-Pierce Q statistic to test for white noise, or must we first fit an ARIMA model?

Rachel: Examine the residuals using the Durbin-Watson statistic or the Box-Pierce Q statistic. If you have not fit an ARIMA model, the residuals are the first differences minus the mean.

Jacob: Is the mean zero?

Rachel: If the random walk has a drift of zero, the mean of the first differences is zero. Otherwise, the mean of the first differences is the drift of the random walk.

Jacob: How do we form the Durbin-Watson statistic in Excel? Is there is built-in function?

Rachel: Excel has no built-in function for this, but the formula is simple; see below.

Jacob: The formula uses the residuals. How do we calculate the residuals? We can do this from the equations in the textbook, but it would take a while. Is there a simple method?

Rachel: The Excel regression add-in calculates the residuals. The add-in computes the ordinary least squares estimators and the residuals. You can copy the formula for the Durbin-Watson statistic from the illustrative spreadsheet on the NEAS web site.

Jacob: Can you give an example?

Rachel: We form an AR(1) model from the last 3½ years of Treasury bill interest rates: January 1997 through June 2000. The correlogram indicates that this time series is a random walk, which is not stationary. Your student project might use first differences, not the interest rates themselves. But we start with the interest rates and compute the Durbin-Watson statistic to see if any serial correlation is significant. We also compute the slope coefficient and use the t statistic to test if it is significantly different from one.

Jacob: How do we use the regression add-in?

Rachel: Copy the January 1997 through June 2000 Treasury bill rates to a new worksheet. Place these in cells B11:B52 and also in cells C12:C53. Column B is the Y values and Column C is the X values. We don’t use the values in B11 or C53.

On the illustrative worksheet, we eliminate rows 53 and 11, getting rid of the original cell B11 and cell C53. This gives a matrix of B11:C51 for the regression analysis.

If we use an AR(2) model, we also place these rates in cells D13 : D54. Column B is the Y values, Column C is the X₁ values, and Column D is the X₂ values. We don’t use the values in B11, B12, C12, C53, D53, or D54.

Jacob: What do we choose on the regression menu for the AR(1) model?

Rachel: The dependent variable is in cells B11:B51, after eliminating the original cell B11. The independent variable is in cells C11:C51. You can place the output on the same sheet or a new sheet. Ask for residuals. You don’t need the standardized residuals since the interest rates are all about the same value in this scenario. If interest rates change greatly over the time series, we would examine standardized residuals.

The residual output shows the observation number, the fitted Y value, and the residual. In a new worksheet, these are in Columns A, B, and C. We placed the output on the same worksheet starting in cell A61.

In column D, place the square of the residual. For cell D85, write =C85^2. Copy this formula to cells D86 : D125.

In column E, place the difference of successive residuals. For cell E86, write =D86-D85. Copy this formula to cells E87:E125.

In Column F, place the square of the difference of successive residuals. For cell F86, write =E86^2. Copy this formula to cells F87:F125.

Use Excel’s quick sum function to add the numbers in Columns D and F. Place the cursor in cell D126 and click on the quick sum icon. Do the same for cell F125. The Durbin-Watson statistic is the sum in cell F125 divided by the sum in cell D126. We place the Durbin-Watson statistic in cell G126.

Jacob: What do we expect to find?

Rachel: A Durbin-Watson statistic of 2 indicates no serial correlation. This example gives a Durbin-Watson statistic of 2.10, which is not significantly different from 2. The autocorrelation of lag 1 on the residuals is –0.06289 (cell C127), which is not significantly different from zero.

Other time periods show significant serial correlation. Your student project should explain the meaning of your results.