Question

Please provide excel step by step instructions

Question Help * 9.3.21 The number of initial public offerings of stock issued in a 10-year period and the total proceeds of these offerings (in millions) are shown in the table. Construct and interpret a 95% prediction interval for the proceeds when the number of issues is 624. The equation of the regression line is y 34.962x+ 16,963.740 Issues, x Proceeds, y 402 450 687 480 478 381 58 57 191 159 1 18,948 28,047 44,968 30,572 37,138 35,875 20,564 11,200 30,612 28,593 Construct and interpret a 95% prediction interval for the proceeds when the number of issues is 624. Select the correct choice below and fill in the answer boxes to complete your choice (Round to the nearest million dollars as needed. Type your answer in standard form where ร.12 million" means 3.120,000 we can be 95% confident that when there are 624 issues, the proceeds wil be between S and S B. There is a 95% chance that the predicted proceeds given 624 issues is between S and S

Answer 1

X Value=   624  
Confidence Level=   95%  
      
      
Sample Size , n=   10  
Degrees of Freedom,df=n-2 =   8  
critical t Value=tα/2 =   2.306   [excel function: =t.inv.2t(α/2,df) ]

X		(x-x̅)²
402		4583.29
450		13386.49
687		124397.29
480		21228.49
478		20649.69
381		2180.89
58		76341.69
57		76895.29
191		20534.89
159		30730.09

here, x̅ = ΣX/n = 3343/10 =   334.30

SSxx =    Σ(x-x̅)² =    390928.10

Standard Error of the Estimate,Se=   6933.50

margin of error,E=t*S(ŷ)= t*Se*√ (1+1/n+(X-X̅)²/Sxx) = 18332.5837
predicted value of Y at X = 324 is

Ŷ =   16963.740 +   34.962 *324 = 38780.333

Prediction Interval Lower Limit=Ŷ -E =   20447.749023 millions
Prediction Interval Upper Limit=Ŷ +E =   57112.916368 millions

so, prediction interval is ($20447749023, $57112916368)