Question

Can annual sports team revenues be used to predict franchise​ values? Use the accompanying soccer team revenues and values to complete parts​ (a) through​ (h) below.

Team   Revenue ($mil)   Value ($mil)
Team 1   554 2806
Team 2   676 3437
Team 3   371 1333
Team 4   628 3202
Team 5   559 1852
Team 6   312 691
Team 7   342 858
Team 8   355 851
Team 9   394 869
Team 10   219 482
Team 11   258 579
Team 12   224 513
Team 13   516 414
Team 14   203   347
Team 15   156    329
Team 16   177 326
Team 17   162 307
Team 18   332 599
Team 19   411 863
Team 20   157 296

A. Assuming a linear​ relationship, use the​ least-squares method to compute the regression coefficients b0 and b1.

B. Assuming a linear​ relationship, use the​ least-squares method to compute the regression coefficients b0 and b1.

C. Predict the mean value of a soccer franchise that generates ​$300 million of annual revenue.

D. Compute the coefficient of​ determination, r2​, and interpret its meaning.

E. Perform a residual analysis on the results and determine the adequacy of the fit of the model.

F. At the 0.05 level of​ significance, is there evidence of a linear relationship between the annual revenues generated and the value of a soccer​ franchise?

- The null and alternative hypotheses ?

- The value is?

- the test statistic is ?

G. Construct a 95​% confidence interval estimate of the mean value of all soccer franchises that generate ​$300 million of annual revenue.

H. Construct a 95​% prediction interval of the value of an individual soccer franchise that generates ​$300 million of annual revenue.

Answer 1

Dear student, we can answer only four subparts at a time, please help to upload other parts separately.

a)

We will be applying the Linear regression model here, it can be done by using the function LINEST(y_value, x_value, TRUE, TRUE) where y_values contain values of Value here and x_values have Revenue values.

Select 5 rows and 2 columns and then write the formula in the first cell and after that, press Shift + Ctrl + Enter.    

The equation comes out to be -
Value = -777.3 + 5.2*Revenue  

b0 = -777.3

b1 = 5.2

b)

We will be applying the Linear regression model here, it can be done by using the function LINEST(y_value, x_value, TRUE, TRUE) where y_values contain values of Value here and x_values have Revenue values.

Select 5 rows and 2 columns and then write the formula in the first cell and after that, press Shift + Ctrl + Enter.    

The equation comes out to be -
Value = -777.3 + 5.2*Revenue  

b0 = -777.3

b1 = 5.2

c)

When revenue = 300

Value = -777.3 + 5.2*300 = -777.3 + 1560 = $782.7 million

d)

The r**2 comes out to be 0.74. It means the change in value can be 74% explained by the revenue.

e)

The residuals have been calculated and shown in the excel. It comes out to be a model with good accuracy.