The data in the attached excel file comes from Consumer Reports and was collected over a...

The data in the attached excel file comes from Consumer Reports and was collected over a two-year period. It gives the average mpg over a 195-mile trip, the weight of the vehicle (pounds), engine displacement (liters), number of cylinders, horsepower, type of transmission (0 = manual, 1 = automatic), the number of gears and whether the car was foreign (1) or domestic (0).

Part a: Build a model for predicting the average mpg based on the data in the attached excel spread sheet. Show and annotate your work in the excel spread sheet. Include an interpretation of the final model.

Part b: Using your final model from part a, construct an interval estimate of the average mpg of a vehicle which weighs 3000 pounds, with an engine displacement of 2.5 liters, 4 cylinders, 150 hp, manual transmission, 5 gears and is a domestic car. Explain what this means.

CASE	TRIP MPG	WEIGHT	DISPLACE	NO CYL	HP	TRANS	GEARS	FOR/DORM
1	32	2365	1.6	4	113	0	5	1
2	33	2430	1.6	4	108	0	5	1
3	42	1895	1.3	4	60	0	4	1
4	36	2320	1.6	4	74	1	3	0
5	32	2330	1.6	4	82	1	3	1
6	34	2255	1.5	4	68	1	3	1
7	36	2350	1.6	4	74	1	3	1
8	41	1635	1.3	4	58	0	4	1
9	36	2070	1.6	4	82	0	4	1
10	34	2115	1.5	4	68	0	4	1
11	35	1840	1.1	4	52	0	4	1
12	43	1970	1.5	4	78	0	4	1
13	51	1575	1.0	3	48	0	5	1
14	37	2185	1.5	4	68	0	4	1
15	36	2115	1.8	4	81	0	4	1
16	28	3040	2.2	4	145	1	4	1
17	34	2620	2.0	4	108	1	3	1
18	25	3230	3.0	6	142	1	4	1
19	29	2745	2.0	4	102	1	4	1
20	28	2573	1.9	4	110	1	4	1
21	27	2802	2.3	4	100	1	3	0
22	31	2699	2.0	4	90	1	3	0
23	36	2695	2.2	4	110	0	5	0
24	31	2885	2.5	4	100	0	5	0
25	23	3310	5.0	8	225	1	4	0
26	23	3430	5.0	8	170	1	4	0
27	29	2670	2.2	4	97	1	3	0
28	35	2925	2.0	4	115	1	4	1
29	28	2735	2.5	4	98	1	3	0
30	29	3155	3.0	6	140	1	4	0
31	30	2995	3.0	6	150	1	4	0
32	27	3150	3.0	6	136	1	3	0
33	29	2950	2.8	6	125	1	3	0
34	26	3295	3.8	6	140	1	4	0
35	28	2915	2.5	4	100	1	3	0
36	29	3220	2.8	6	125	1	4	0
37	26	2900	2.2	4	146	1	3	0
38	27	3205	2.5	4	153	1	4	1
39	26	2930	2.2	4	103	1	3	0
40	25	3320	3.0	6	157	1	4	1
41	26	3080	2.3	4	114	1	4	1
42	24	3625	3.0	6	136	1	3	0
43	23	3665	3.0	6	145	1	4	0
44	22	3625	2.4	4	106	1	4	1
45	23	3415	2.4	4	107	1	4	1

Solutions

Expert Solution

using excel>data>data analysis>Regression

we have

SUMMARY OUTPUT

Regression Statistics
Multiple R	0.916998
R Square	0.840885
Adjusted R Square	0.810783
Standard Error	2.665201
Observations	45

ANOVA
	df	SS	MS	F	Significance F
Regression	7	1388.956	198.4222	27.93382	6.28E-13
Residual	37	262.8221	7.103299
Total	44	1651.778

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	47.63431	4.710892	10.11153	3.39E-12	38.08914	57.17948
WEIGHT	-0.00951	0.001707	-5.56943	2.4E-06	-0.01296	-0.00605
DISPLACE	-1.68525	1.631644	-1.03286	0.308375	-4.99128	1.62077
NO CYL	1.000934	0.835687	1.197738	0.238636	-0.69233	2.694198
HP	-0.02358	0.027863	-0.84642	0.40276	-0.08004	0.032872
TRANS	2.091025	2.04431	1.022851	0.313019	-2.05114	6.23319
GEARS	2.743086	1.128762	2.430173	0.02006	0.455998	5.030174
FOR/DORM	-1.5362	1.106093	-1.38885	0.173181	-3.77736	0.704956

a ) the regression model is

mpg = 47.634-0.0095*weight - 1.685*displacement +1 *Nocyl-0.0236 *HP+2.0910*TRANS +2.743 GEARS-1.5362*FOR/DORM

since p value of f stat is less than 0.05 so the model is significant to use

b ) weighs 3000 pounds, with an engine displacement of 2.5 liters, 4 cylinders, 150 hp, manual transmission(=0), 5 gears and is a domestic car

mpg =47.634-0.0095*3000 - 1.685*2.5 +1 *4-0.0236 *150+2.0910*0 +2.743*5-1.5362*1 =27.56

an interval estimate of the average mpg of a vehicle which weighs 3000 pounds, with an engine displacement of 2.5 liters, 4 cylinders, 150 hp, manual transmission, 5 gears and is a domestic car. is 27.56

orchestra answered 1 year ago

Next > < Previous

Related Solutions

The data set for this question set (Tab Q1 in the Excel data file) comes from...

The data set for this question set (Tab Q1 in the Excel data file) comes from a research project that tracks the elderly residents in a community to monitor their cognitive function and general health. Based on the literature, education is considered a protective factor against dementia, and memory decline is usually the first sign of dementia. So the researchers would like to know whether education level (measured in number of years of formal schooling) is correlated with memory function...

The attached Excel workbook, BUSI1013 Bank Dataset.xlsx, contains the data collected from research study. Answer the...

The attached Excel workbook, BUSI1013 Bank Dataset.xlsx, contains the data collected from research study. Answer the following questions using this data set. (18 points) What would the population of interest be for this study? Which method of sampling was used in this study? How big is the sample size? How many variables are in the data set? Which of the variables are in nominal scale? Name the variable(s) measured in ratio scale. Name the variable(s) measured in interval scale. Which...

Please also go over the Excel file attached to this assignment in order to familiarize yourself...

Please also go over the Excel file attached to this assignment in order to familiarize yourself with the different ways Excel can be used to solve Time Value of Money/Dividend Discount Model problems. There are three worksheets in the Excel file. This Excel file with examples is just that: a file to show you some examples of using Excel to solve TVM /DDM problems. Do not confuse this posted Excel file with the separate Excel file you need to create...

You have an Excel file attached to this link. You are to first transpose the data...

You have an Excel file attached to this link. You are to first transpose the data so that, instead of being in the horizontal format, it will be converted to the vertical form. Then to run a Multiple Regression and see which of the independent variables are significant and whether the overall model is significant. You also should be able to comment on the goodness of the fit. All of these require that you have thoroughly watched the video lectures...

Please answer the following questions using the data in the attached Excel file. You are thinking...

Please answer the following questions using the data in the attached Excel file. You are thinking of investing in Abercrombie and Fitch Co. (ANF). The returns for ANF are embedded in an Excel document below (Source: yahoo.com). 1. For the investment in ANF that you are considering, for all of 2012 determine the following items: a) the mean return b) the median return c) the standard deviation d) the variance e) the coefficient of variation The weekly rates of return...

The data on a loan has been collected in the Microsoft Excel Online file below. Open...

The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Amortization schedule Loan amount to be repaid (PV) $39,000.00 Interest rate (r) 11.00% Length of loan (in years) 3 a. Setting up amortization table Formula Calculation of loan payment #N/A Year Beginning Balance Payment Interest 1 2 3 b. Calculating % of Payment Representing Interest and Principal for Each Year Year Payment...

The data on a loan has been collected in the Microsoft Excel Online file below. Open...

The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. a. Complete an amortization schedule for a $22,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 8% compounded annually. Round all answers to the nearest cent. Beginning Repayment Ending Year Balance Payment Interest of Principal Balance 1 $ $ ...

Look at the attached Excel file which contains two column charts identifying data for a company:...

Look at the attached Excel file which contains two column charts identifying data for a company: Week 1 Discussion Topic 2 Ethics.xls. Notice that the data is depicted differently, however, the data presented is really the same. Is it ethical to present data in a different way in order to make the data appear more favorable? Explain your reasoning.

The attached Excel sheet contains data on annual returns on IBM and 3M stocks over the...

The attached Excel sheet contains data on annual returns on IBM and 3M stocks over the period 1990-2002, as well as annual returns on S&P 500 Index and one-year U.S. Treasury bills for this period. You are asked to evaluate performance of IBM and 3M stocks using the CAPM’s Security Market Line with S&P 500 Index as proxy for the market return. (i) Compute the annual excess returns of IBM, 3M, and the S&P 500. (ii) Use Excel to compute...

Enter the Shoe Size and Height data from the In class data Excel file (use the...

Enter the Shoe Size and Height data from the In class data Excel file (use the excel in class data lab file loaded into the files section of Canvas) into the week 7 Regression Excel sheet. Create a scatter plot for the data, store and graph the regression equation, and note the r2 and r value. Looking at the graph and the r, and r^2 value, do you feel that shoe size is a good predictor for height? Explain your...

Subjects