Hi,
I'm working on a study regarding forecast accuracy among financial analysts. At this moment I'm stuck calculating the analysts deviation from the consensus.
Quote with definition of consensus: "The deviation from the consensus is determined as the difference between the analyst's forecast fikt and the consensus forecast ck concerning the earnings per share. The consensus forecast proxies for
available public information up to the point in time an analyst issues his forecast. This
consensus forecast at time T (within the quarter) is calculated as the mean of all the
analysts estimates up to T , excluding the estimate of the analyst i, who makes his
estimate for quarter t (analogous to Chen and Jiang, 2006 and Zitzewitz, 2001)
I'm having problems programming the calculation of this consensus. In the example below I will show you what the input data looks like and what the output data should be.
I'm sorry that my table doesn't look good but it doesn't recognize the space i used
If someone can tell me how to edit my table, I will edit it.
input data
Analyst Company Year Quarter EPS-estimation Forecast date
1a A 2000 1 4 2 jan 2000
2a A 2000 1 4.5 9 jan 2000
3a A 2000 1 4.9 14 febr 2000
4a A 2000 1 4.1 19 febr 2000
5a A 2000 1 4.3 27 febr 2000
1a A 2000 2 4.7 15 mar 2000
3a A 2000 2 4.7 19 mar 2000
2a A 2000 2 5 29 apr 2000
1b B 2000 1 10 etc
2b B 2000 1 12
3b B 2000 1 10.5
4b B 2000 1 11
2b B 2000 2 15
1b B 2000 2 14
3b B 2000 2 13
5b B 2000 2 14
]
output data
Analyst Company Year Quarter EPS-estimation Forecast date Consensus
1a A 2000 1 4 2 jan 2000
2a A 2000 1 4.5 9 jan 2000 4
3a A 2000 1 4.9 14 febr 2000 4.25
4a A 2000 1 4.1 19 febr 2000 4.46
5a A 2000 1 4.3 27 febr 2000 4.375
1a A 2000 2 4.7 15 mar 2000
3a A 2000 2 4.7 19 mar 2000 4.7
2a A 2000 2 5 29 apr 2000 4.7
1b B 2000 1 10 etc
2b B 2000 1 12 10
3b B 2000 1 10.5 11
4b B 2000 1 11 10.833
2b B 2000 2 15
1b B 2000 2 14 15
3b B 2000 2 13 14.5
5b B 2000 2 14 14
Thanks in advance!