Question

You are interested in the determinants of analyst following for publicly traded companies. You conduct a regression on 76 observations, specified as follows: Analystsi = b0 + b1 (D/E)i + b2 Sizei + error Where Analysts is the natural log of (1 + number of analysts), D/E is the debt to equity ratio of the company, and Size is the natural log of the company's market cap, in millions. Your regression results are as follows:

Based on this information, what would be the predicted number of analysts following a company with a debt to equity ratio of 0.33 and a market cap of 170 million dollars?

	Coefficient	Standard error
Incercept	-0.17	0.03
D/E	-0.14	0.12
Size	0.45	0.14

Correct Answer is 7.12

hint: Note that both the size and analysts variables in the regression specification are natural logs of the observed quantities. Note also that Analysts is ln(1+number of analysts), since ln(0) is undefined.

PLEASE DO IT ON EXCEL AND SHOW THE STEPS.

Answer 1

The correct answer is 7.12

Regression equation is:

Analystsi = b0 + b1 (D/E)i + b2 Sizei + error

Where Analysts is the natural log of (1 + number of analysts), D/E is the debt to equity ratio of the company, and Size is the natural log of the company's market cap, in millions.

Before we get into excel, let's convert the regression equation in the format of the variable:

ln(1 + number of analysts) = b0 + b1 x (D / E) + b2 x ln(Market Cap in $ mn)

Please see the screenshot of the excel below. In the column with heading as "Formula" i have displayed the formula used in applicable cell.

The correct answer is 7.12; it appears in the cell highlighted in yellow below.