BookmarkSubscribeRSS Feed
Calcite | Level 5

## Calculation of consensus forecast

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!
1 REPLY 1
Super User

## Re: Calculation of consensus forecast

Hi.
With my understand about your post.
[pre]
data temp;
infile datalines truncover;
input analyst \$ company \$ year quarter eps date \$12.;
datalines;
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
run;
data result(drop=sum count);
set temp;
if quarter ne lag(quarter) then do;
Consensus=.;
sum=0;
count=0;
end;
else consensus=sum/count;
sum+eps;
count+1;
run;
[/pre]

Ksharp
Discussion stats
• 1 reply
• 1919 views
• 0 likes
• 2 in conversation