16.1 Excel Activity

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, mm Open spreadsheet a. Develop a scatter diagram of the data using the rarity rating as the independent variable and price as the dependent variable. Does a simple linear regression model appear to be appropriate Choose the correct scatter diagram scatter diagram 4500 Price 4000 -3500 3000 Autosaved at 10:47 AM Back MAH A Year 1984 1956 1963 1978 1960-1963 1962-1964 1962 1967-1968 1968-1973 1962-1967 1969-1971 1971-1974 1951-1954 1950-1953 1956-1957 B Make Model Chevrolet Corvette Chevrolet Corvette 265/225-hp Chevrolet Corvette coupe (340-bhp 4-speed) Chevrolet Corvette coupe Silver Anniversary Ferrari 250 GTE 2+2 Ferrari 250 GTL Lusso Ferrari 250 GTO Ferrari 275 GTB/4 NART Spyder Ferrari 365 GTB/4 Daytona Jaguar E-type OTS Jaguar E-type Series II OTS Jaguar E-type Series III OTS Jaguar XK 120 roadster (steel) Jaguar XK C-type Jaguar XKSS D E Rating Price ($1000) 18 1600.0 19 3900.0 18 1100.0 19 1300.0 16 350.0 19 2750.0 18 375.0 17 490.0 17 110.0 15 77.5 14 66.0 16 140.0 17 400.0 16 220.0 13 71.0 Rating Rating2 loalRating Price ($1000) log(Price) Forn Price + b. Develop an estimated multiple regression equation with = = rarity rating and as the two independent variables. Rating + Rating? (to whole numbers) What is the value of the coefficient of determination? Note: report R between 0 and 1 (to 3 decimals) What is the value of the P test statistic? (to 2 decimals) What is the p-value? (to 4 decimals) c. Consider the nonlinear relationship shown by equation E(y) = Bof 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* between 0 and 1. (to 3 decimals) What is the value of the F test statistic? C. Consider the nonlinear relationship shown by equation E(y) = B.Bi. 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 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) d. Do you prefer the estimated regression equation developed in part (b) or part (c)? Explain. The model in part is preferred because it provides a better fit. 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, mm Open spreadsheet a. Develop a scatter diagram of the data using the rarity rating as the independent variable and price as the dependent variable. Does a simple linear regression model appear to be appropriate Choose the correct scatter diagram scatter diagram 4500 Price 4000 -3500 3000 Autosaved at 10:47 AM Back MAH A Year 1984 1956 1963 1978 1960-1963 1962-1964 1962 1967-1968 1968-1973 1962-1967 1969-1971 1971-1974 1951-1954 1950-1953 1956-1957 B Make Model Chevrolet Corvette Chevrolet Corvette 265/225-hp Chevrolet Corvette coupe (340-bhp 4-speed) Chevrolet Corvette coupe Silver Anniversary Ferrari 250 GTE 2+2 Ferrari 250 GTL Lusso Ferrari 250 GTO Ferrari 275 GTB/4 NART Spyder Ferrari 365 GTB/4 Daytona Jaguar E-type OTS Jaguar E-type Series II OTS Jaguar E-type Series III OTS Jaguar XK 120 roadster (steel) Jaguar XK C-type Jaguar XKSS D E Rating Price ($1000) 18 1600.0 19 3900.0 18 1100.0 19 1300.0 16 350.0 19 2750.0 18 375.0 17 490.0 17 110.0 15 77.5 14 66.0 16 140.0 17 400.0 16 220.0 13 71.0 Rating Rating2 loalRating Price ($1000) log(Price) Forn Price + b. Develop an estimated multiple regression equation with = = rarity rating and as the two independent variables. Rating + Rating? (to whole numbers) What is the value of the coefficient of determination? Note: report R between 0 and 1 (to 3 decimals) What is the value of the P test statistic? (to 2 decimals) What is the p-value? (to 4 decimals) c. Consider the nonlinear relationship shown by equation E(y) = Bof 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* between 0 and 1. (to 3 decimals) What is the value of the F test statistic? C. Consider the nonlinear relationship shown by equation E(y) = B.Bi. 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 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) d. Do you prefer the estimated regression equation developed in part (b) or part (c)? Explain. The model in part is preferred because it provides a better fit