Question

Corvette, Ferrari, and Jaguar produced a variety of classic cars that continue to increase in value. The data showing the rarity rating (1–20) and the high price ($1000s) for 15 classic cars is contained in the Excel Online file below. Construct a spreadsheet to answer the following questions.

1. Develop an estimated multiple regression equation with  rarity rating and  as the two independent variables
   
   Price = (___) + (___) Rating + (___) Rating^2 (to whole numbers)

2. What is the value of the coefficient of determination? Note: report R^2 between 0 and 1.

   (___) (to 3 decimals)

   What is the value of the F test statistic?

   (___) (to 2 decimals)

   What is the p-value?

   (to 4 decimals)

3. Consider the nonlinear relationship shown by equation E(y) = β0β^x1 . Use logarithms to develop an estimated regression equation for this model.

   Log(price) = (___) + (___) log(rating) (to 3 decimals)

   What is the value of the coefficient of determination? Note: report R^2 between 0 and 1.

   (___) (to 3 decimals)

   What is the value of the F test statistic?

   (___) (to 2 decimals)

   What is the p-value?

   (___) (to 4 decimals)
   
   

   Year	Make	Model	Rating	Price ($1000)
   1984	Chevrolet	Corvette	18	1600.0
   1956	Chevrolet	Corvette 265/225-hp	19	4100.0
   1963	Chevrolet	Corvette coupe (340-bhp 4-speed)	18	1100.0
   1978	Chevrolet	Corvette coupe Silver Anniversary	19	1350.0
   1960-1963	Ferrari	250 GTE 2+2	16	340.0
   1962-1964	Ferrari	250 GTL Lusso	19	2550.0
   1962	Ferrari	250 GTO	18	350.0
   1967-1968	Ferrari	275 GTB/4 NART Spyder	17	440.0
   1968-1973	Ferrari	365 GTB/4 Daytona	17	150.0
   1962-1967	Jaguar	E-type OTS	15	80.0
   1969-1971	Jaguar	E-type Series II OTS	14	58.0
   1971-1974	Jaguar	E-type Series III OTS	16	125.0
   1951-1954	Jaguar	XK 120 roadster (steel)	17	370.0
   1950-1953	Jaguar	XK C-type	16	280.0
   1956-1957	Jaguar	XKSS	13	67.0

Answer 1

Solution

we will solve it by using excel and the steps are

Enter the Data into excel

Click on Data tab

Click on Data Analysis

Select Regression

Select input Y Range as Range of dependent variable as Price

Select Input X Range as Range of independent variable as Rating and Rating^2

click on labels if your selecting data with labels

click on ok.

So this is the output of Regression in Excel.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.8359
R Square	0.6987
Adjusted R Square	0.6485
Standard Error	679.8158
Observations	15.0000
ANOVA
	df	SS	MS	F	Significance F
Regression	2.0000	12861143.0501	6430571.5251	13.9145	0.0007
Residual	12.0000	5545794.9499	462149.5792
Total	14.0000	18406938.0000
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	34003.4758	13891.8899	2.4477	0.0307	3735.6479	64271.3036
Rating	-4596.0463	1717.1828	-2.6765	0.0202	-8337.4661	-854.6264
Rating^2	154.4653	52.6075	2.9362	0.0125	39.8433	269.0873

So from the the above output

a.

Price = 34003 - 4596 *Rating + 154*Rating^2

b.What is the value of the coefficient of determination?

coefficient of determination = 0.699

What is the value of the F test statistic?

13.91

What is the p-value?

0.0007

c.Consider the nonlinear relationship shown by equation E(y) = β0β^x1 . Use logarithms to develop an estimated regression equation for this model.

Now take the log of Rating and log of Price and build the regression equation using excel ( procedure is given at the begining)

The data is given below

Log(Price)	Log(Rating)
3.2041	1.2553
3.6128	1.2788
3.0414	1.2553
3.1303	1.2788
2.5315	1.2041
3.4065	1.2788
2.5441	1.2553
2.6435	1.2304
2.1761	1.2304
1.9031	1.1761
1.7634	1.1461
2.0969	1.2041
2.5682	1.2304
2.4472	1.2041
1.8261	1.1139

Now build regression equation

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.8856
R Square	0.7843
Adjusted R Square	0.7677
Standard Error	0.2813
Observations	15.0000
ANOVA
	df	SS	MS	F	Significance F
Regression	1.0000	3.7394	3.7394	47.2611	0.0000
Residual	13.0000	1.0286	0.0791
Total	14.0000	4.7679
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-10.2899	1.8754	-5.4868	0.0001	-14.3414	-6.2384
Log(Rating)	10.5356	1.5325	6.8747	0.0000	7.2248	13.8464

Log(price) = -10.290  + 10.536log(Rating)

What is the value of the coefficient of determination? Note: report R^2 between 0 and 1.

0.784

What is the value of the F test statistic?

47.26

What is the p-value?

0.0000