how to calculate autocorrelation on excel

Durbin Watson statistics were obtained by dividing the squared residual value (e t^2) by (e t – e t-1)^2. The formula used to calculate the statistical Durbin-Watson value includes several components that need to be calculated first, namely predicted Y, residual (e t), the difference between the residuals in period t and the previous period (e t – e t-1), residual squared (e t^2), and (e t – e t-1)^2. In detail, the data that researchers have collected can be seen in the table below: The Formula for Calculating Durbin Watson Statistics X 2 = Marketing Staff – Person (independent variable)ī 1, b 2 = Regression estimation coefficientsīased on the specifications of the equations that the researcher has prepared, the researcher inputs the data collected in Excel.

X 1 = Advertising Cost – USD (independent variable) Y = Product sales – Units (dependent variable) Researchers took quarterly time series data from as many as 15 observations.īased on the data that has been collected, the researcher then specifies the equation as follows. Based on the case examples, the researcher aims to determine the effect of advertising costs and marketing staff on product sales. I have prepared mini research materials as a practice for the Durbin Watson test.

On this occasion, Kanda Data will discuss the Durbin-Watson autocorrelation test. Several ways can detect autocorrelation, including Durbin Watson test, Lagrange multiplier test, Breusch Godfrey test, and rank test. In comparison, cross-section data does not need to be tested for autocorrelation.

One thing that needs to be considered by researchers is that the autocorrelation test is conducted on time series data. The autocorrelation test must be conducted to obtain the best linear unbiased estimator. If there is a correlation, then it is called an autocorrelation problem. The autocorrelation test aims to test whether there is a correlation between residuals in the t period and the previous period (t-1) in the linear regression model. One of the assumption tests required in the regression is the autocorrelation test.Īutocorrelation tests can be done in both simple and multiple linear regression. Researchers who use time series data in linear regression analysis with the OLS method need to conduct some of the required assumption tests.

How to calculate autocorrelation on excel