Question

From the following observations of annual EPS for a company, what is the first-order autocorrelation?

Time	EPS
1	3.48
2	3.23
3	4.01
4	4.58
5	5.98
6	5

Note that using the CORREL() spreadsheet function will not produce the correct result. Though for a large sample it'll be really close, for a small sample such as this one the difference can be significant. This is because for the autocorrelation you use the variance of the full sample in the denominator, as well as use the full sample to find the mean for use in covariance. In contrast, the CORREL function will treat the two subsets of the time series as separate data, and calculate separate mean and standard deviation for each.

Please show on excel

The answer is .51

Answer 1

Answer -->

For given data, we want to calculate the first order Auto Correlation for EPS

Formula -

First order Auto Correlation = covariance at first lag /Variance of EPS

If we calculate variance of EPS

Variance of EPS = 1.05076

Covariance of first lag = Covariance (EPS data without 6th observation, EPS data without 1st observation)

Covariance of first lag = 0.56202

FIrst order Auto correlation = 0.56202 / 1.05076 = 0.53486999885797

If we calcualte correlation of EPS data (First lag),

that is correlation (EPS data without 6th observation, EPS data without 1st observation) usin CORREL () =  0.62033547682

Answer mentioned in above 0.51 is looking suspicious.

Below is the excel calculations

Time	EPS
1	3.48		=VAR(B2:B7)	1.05
2	3.23		=COVAR(B2:B6,B3:B7)	0.56
3	4.01		=E3/E2	0.53	<-- 'First order Auto correlation
4	4.58
5	5.98		=CORREL(B2:B6,B3:B7)	0.62
6	5