Question

The dean of the college of business at one of the state university systems believes that the college rankings are positively correlated to the private endowments ($1,000,000) and the annual budgets ($1,000,000) from state and private sources. To support his claim, the dean collects the following data from nine of the state’s peer institutions and the rankings of each school:

Private Endowment (in millions)	Annual Budget (in millions)	Ranking
$2.5	$8.1	87
52.0	26.0	20
12.7	7.5	122
63.0	33.0	32
46.0	12.0	54
27.1	16.1	76
23.3	17.0	103
46.4	14.9	40
48.9	21.8	98

1. Which data column do you mark as the Y range? Which data column do you mark as the X range?
2. What is the multiple regression equation? Is the overall multiple regression result reliable (statistically significant)? (hint: discuss R^2 and F test. Simply mentioning the number without elaboration will not earn full credit). Please attach your Excel computer solution to this assignment.
3. Interpret the coefficient of private endowment. Does the private endowment have statistically significant effect on college ranking? How can you tell?
4. If the annual budget decrese by 1 million, how would it affect the college ranking? Increase? Decrease? By how many? Is it statistically significant? How can you tell?
5. If a college has private endowment of $70 Million with the annual budget $40 million, use the equation in part 2 to forecast its ranking? Is the forecast result reliable?

Answer 1

Question (a)

The column Ranking should be in the Y range since it is a dependent variable

Private Endowment (in millions) and Annual Budget (in millions) should be in the X range since they are independent variables

Question (b)

The regression output and analysis is attached in the below image

We have taken 95% confidence here with = 0.05

The multiple regression equation is

Ranking = (-1.885) * Private Endowment + (0.338) * Annual Budget + 137.301

R² value is 0.65. Usually if R² value is greater than 0.5 and less than 0.7 then we consider it is a moderate fit for the model and if the R² value is more that that then we say it is a strong fit for the model

R² value is also % of the variation of the dependent variable that is explained by independent variables. So here 65% variation in Ranking is explained by Private Endowment and Annual Budget variables

Here our Significance F value is 0.072 which is higher than our = 0.95 which suggests that the results are not statistically significant

So the overall multiple regression result is not reliable

Question (c)

Private endowment coefficient is -1.885 which means that the Ranking decreases as Private endowment increases with all other variables remaining constant

The p-value for Private endowment variable from the attached image is 0.094 which is more than our = 0.05 which means that it doesn't have statistically significant effect on Ranking

Question (d)

Ranking = (-1.885) * Private Endowment + (0.338) * Annual Budget + 137.301

Annual budget has a positive coefficient of 0.338 so if the annual budget decrease by $1 million, the ranking would decrease by 0.338 if all the variables remains the same.

The annual budget has p-value of 0.870 from the attached image, and is more than our = 0.05 which means that it is not statistically significant

Question (e)

Given Private endowment - $70 million

Annual budget = $40 million

Ranking = (-1.885) * Private Endowment + (0.338) * Annual Budget + 137.301

Ranking = (-1.885) * 70 + 0.338 * 40 + 137.301

= 18.47

= 18 approximately

The forecast result is not reliable since the model is not statistically significant on a while which is evident by the significant F value and individual P-values of the variables