## Description

Use only clean sheets of good quality 8 1/2" x 11" white paper. Text should be typed on one side only. Do not put any perfume or cologne on the sheets, neither try to decorate the sheets of paper; remember, it's an academic piece of writing. A title page is essential. Pages should be consecutively numbered, with numbers put in the upper right hand corner, flush with the right margin and 1/2" from the top with 12 font size and 1.5 spacing.

The data in Used Cars represent characteristics of cars that are currently part of an inventory of a used car dealership. The variables included are car, year, age, price($), mileage, power(hp), fuel (mph), Region of origin (manufactured in USA or in a foreign country), and single ownership (Yes= owned by one or No= owned by more than one owner). The excel file for this problem is stored under module called “Used Cars”. You want to describe each of these variables, and you would like to predict the price of the used cars. Make sure to take appropriate steps to analyze this data set and write a mini report for the Car Dealer. Also, do you think that the model is missing some important variables? If so, what are those missing variables? Please explain.

**And a file is about the data.**

**Other two files are two examples about the assignment, do not copy!**

## Explanation & Answer

Hello! I have uploaded the answer to this question. Please have a look at it and get back to me in case of anything. Thanks.

Running Head: USED CAR PROJECT

1

Used Car Project

Name

Institution

Date

USED CAR PROJECT

2

Report on Determinants of Used Car Prices

Determinants of any used prices are numerous. This report, therefore, presents a discussion

of the most significant factors based on statistical analysis. To come up with a reliable conclusion,

a sample of 500 used cars at a dealer shop was selected, and data on the selected sample was

collected. Both qualitative and quantitative data were collected. The quantitative data collected

included the price of the car, year of manufacture and hence the age of the car, mileage covered so

far, power and fuel. Qualitative data included information about whether the car was manufactured

in the United States or a foreign country and whether the car was owned by a single person or more

than one person.

From the data collected, y-variable is the price of the used car. This is because it is the

dependent variable whereas the rest of the variables were considered x-variables because they in

one way or another influences the price. To understand the significance and relationship between

the dependent and the independent variable, a regression analysis was performed. But before

running the regression analysis, the data was analyzed using descriptive statistics.

As mentioned above, the data collected had both quantitative data and qualitative data. To

make qualitative data quantitative, the data was coded using the integer 1 and 0 (McNeil &

Chapman, 2005; Carlberg, 2014). For origin, those cars manufactured in the US were coded 1

whereas those manufactured in a foreign country were coded 0. Similarly, those cars that were

initially owned by a single person were recorded 1 while those with more than one owner were

coded 0. Coding make analyzing quantitative data possible.

USED CAR PROJECT

3

Descriptive Data

Price

Mean

Standard

Error

Median

Mode

Standard

Deviation

Minimum

Maximum

Sum

Count

Age

Mileage

Power

(HP)

Fuel

(MPG)

Region

of

Single

origin owner

9057.80

8.36

92492.00

174.78

25.47

0.71

0.67

93.44

9100.00

10600.00

0.21

8.00

3.00

2303.83

91000.00

40000.00

1.34

180.00

130.00

0.42

27.00

12.00

0.02

1.00

1.00

0.02

1.00

1.00

2089.47

4800.00

13400.00

4528900.00

500.00

4.79

1.00

16.00

4181.00

500.00

51515.22

5000.00

184000.00

46246000.00

500.00

29.92

130.00

220.00

87390.00

500.00

9.44

12.00

42.00

12735.00

500.00

0.46

0.47

0.00

0.00

1.00

1.00

353.00 334.00

500.00 500.00

From the descriptive statistics above, it’s apparent that the sample consists of 353 cars

manufactured in the US and 147 from foreign countries. The sample was also made up of 334

single owned cars, and 166 non-single owned cars. The statistics also shows the average price of

a used car to be $9,057.80, and we can also say that based on the sample, the vehicles in the dealer

shop are between a year old to 16 years old with the most cars being 3 years old. Furthermore, we

can say that the largest mileage covered by the cars in the shop is 184,000miles while the smallest

mileage is 5,000 miles and most vehicles have actually covered 4,000 miles. The descriptive

statistics gives an overview of the sample data. Therefore, to comprehensively analyze the data

and use this analysis to make inference about the entire population, there was a need for performing

a regression analysis.

USED CAR PROJECT

4

Regression Analysis

Regression analysis is a statistical technique that is used determines the relationship

between variables (Cowan, 2004). Regression analysis results normally have three with the main

metric being the intercept, the coefficient, the R-squared, the adjusted R-squared and the p-value.

Before using the regression analysis results to form a regression model, there was the need to

determine if all the variables were statistically significant (Cowan, 2004). A variable is considered

significant if its p-value is less than the significance level used in the regression analysis (Wang &

Jain, 2003). The results below shows part three of the regression analysis result and it was used to

determine the significance of the variable.

Intercept

Year

Age

Mileage

Power (HP)

Fuel (MPG)

Region of origin

Single owner

Coefficients

Standard Error

12953.32793

768.6667128

0

0

-285.1267654

46.32344455

-0.012553909

0.004304894

-8.370190934

8.111615837

42.6670949

25.7252244

-28.59946237

52.29839736

69.22228311

50.44309571

t Stat

16.85168319

65535

-6.155128752

-2.916194718

-1.031877138

1.658570368

-0.546851602

1.372284594

P-value

1.18357E-50

0

0

0.003704752

0.302635614

0.097838437

0.584728116

0.170598887

Based on the results above, only mileage and age are significant. I have intentionally left

year because the year of manufacture determines the how old a car is. The rest of the variables

have p-value more than a significance level of 0.05. However, it would not be prudent to eliminate

all the variables with a p-value greater than 0.05 at once. We, therefore, eliminated the variables

one at a time starting with one that has the highest p-value. As highlighted above, the region of

origin is the least significant and therefore it was eliminated, and a regression analysis performed

USED CAR PROJECT

5

again. This step was repeated until a result with variables' p-value less than 0.05 was found. The

final regression results were, therefore, has shown below.

Part 1

Regression Statistics

Multiple R

0.96742

R Square

0.93589

Adjusted

R

Square

0.93349

Standard Error

530.636

Observations

500

Part 2

ANOVA

df

Regression

Residual

Total

4

496

500

SS

2038918613

139660967

2178579580

Coefficients

12192.3

0

-292.91

-0.0118

16.1198

Standard

Error

81.31208179

0

46.08750159

0.004284727

2.532104958

MS

509729653

281574.53

F

2413.711

Significance

F

0

Part 3

Intercept

Year

Age

Mileage

Fuel (MPG)

t Stat

149.943992

65535

-6.3555889

-2.764776

6.36616152

P-value Lower 95%

0

12032.5

0

0

0

-383.464

0.0059

-0.02026

0.0000

11.14481

Upper

95%

12352.02

0

-202.362

-0.00343

21.09476

Part 1 of the regression analysis above consist of five metrics namely the multiple R, Rsquared, adjusted R-squared, observations and standard error. The multiple R metrics measures

the strength of the linear relationship between the variables (Wang & Jain, 2003). Based on our

sample data, the multiple R is 0.9674 showing a very strong correlation between the age, mileage,

USED CAR PROJECT

6

fuel, and price of a used car. The second metric is the R-squared, and it shows what percentage of

the variation in the y-variable is explained by the x-variable (McNeil & Chapman, 2005). From

the analysis above, it is apparent that 93.59% of the variation in used car prices is explained by

variation in age of the car, mileage covered and fuel. The R-squared is mostly relied upon when

the regression is has a single independent variable. However, where the regression involves

multiple x-variables, then adjusted R-squared is preferred (McNeil & Chapman, 2005). The sample

adjusted R-squared is 0.9335 meaning 93.35% of the variation in the used car price is explained

by a variation in the car’s age, mileage and fuel consumption. The other two metrics in this section

are the standard error and the observation. Observation shows the sample size while the standard

error is basically used to test whether the coefficient is different from zero.

The second part of the regression analysis is the sample ANOVA. This section is made of

the degree of freedom, the sum of residuals and the sample f-test. This section is not commonly

utilized, and for this project, it won't be utilized either.

The third and last part of the regression analysis is the intercept and the coefficient section.

This section is made up of the intercept coefficient and the x-variable coefficient. The section also

entails the p-value, t-statistics, lower limit and the upper limit (Carlberg, 2014). The p-value has

been discussed above, and it shows the statistical significance of a variable. The upper and lower

limit indicates the confidence interval of a variable. The most important metric in this section is

the coefficient. The variable coefficient shows the average change in the car price given a unit

change in the x-variable while holding other x-variable constant (Carlberg, 2014). A positive

coefficient indicates a direct relationship while a negative coefficient indicates an inverse

relationship. The coefficient of the intercept, on the other hand, indicates the average price given

that all the other variables are zero. Therefore the regression model for used car price determination

USED CAR PROJECT

7

is y = 12,192 – 292.91x1 - 0.01x2 + 16.11x3 ; given x1 represent age, x2 represent mileage and x3

represent fuel.

In conclusion, we can say that the older a car is, the cheaper it is, and the same applies to

mileage covered. However, a car with higher fuel consumption will definitely cost more. It is also

prudent to conclude that the regression model established indicates a very strong relationship as

indicated by an adjusted R-squared of 93.59%. However, this value is not 100% meaning there are

still unexplained 6% determinants of the used car price. One of such factor would the car brand.

Different new cars have different prices based on their brands. This factor does not change just

because the car is second hand. And therefore brand is a significant determinant of a used car price.

USED CAR PROJECT

8

References

Carlberg, C. G. (2014). Statistical Analysis: Microsoft Excel 2013. INpolis, IN: Que.

Cowan, G. (2004). Statistical data analysis. Oxford: Clarendon Press.

McNeill, P., & Chapman, S. (2005). Research methods. London: Routledge.

Wang, G. C., & Jain, C. L. (2003). Regression analysis: modeling & forecasting. Flushing,

NY: Graceway Pub.

Used Car Project

Thesis statement: This report presents a discussion of the most significant factors based on

statistical analysis.

I.

II.

III.

Report on Determinants of Used Car Prices

Descriptive Data

Regression Analysis

Car

Car-01

Car-02

Car-03

Car-04

Car-05

Car-06

Car-07

Car-08

Car-09

Car-10

Car-11

Car-12

Car-13

Car-14

Car-15

Car-16

Car-17

Car-18

Car-19

Car-20

Car-21

Car-22

Car-23

Car-24

Car-25

Car-26

Car-27

Car-28

Car-29

Car-30

Car-31

Car-32

Car-33

Car-34

Car-35

Car-36

Car-37

Car-38

Car-39

Car-40

Car-41

Car-42

Car-43

Year Age Price($) Mileage Power (HP) Fuel (MPG)

2010

2 13400

21000

140

15

2009

3 11400

33000

160

21

2008

4 10600

54000

210

36

2000 12

7600 123000

140

15

1997 15

6000 160000

220

42

1998 14

7000 161000

150

18

2006

6

9700

69000

170

24

2009

3 11600

30000

160

21

2011

1 12000

17000

160

21

2002 10

8000 108000

160

21

2000 12

7400 123000

200

33

2005

7 10500

74000

150

15

2008

4

9700

51000

150

18

2009

3 11300

45000

180

27

1997 15

6400 158000

210

36

2008

4 10600

48000

160

21

2010

2 11400

22000

170

24

1999 13

6800 146000

220

42

2007

5 10900

53000

200

33

1999 13

7400 142000

150

15

2005

7 10300

81000

210

36

2008

4 11700

48000

170

24

2007

5

9600

66000

190

30

2000 12

8000 125000

160

21

2000 12

7600 127000

130

12

2002 10

9000 109000

150

15

2010

2 11000

20000

160

21

2009

3 11000

36000

130

12

2004

8

9800

81000

130

12

2006

6 10700

61000

210

36

1997 15

5600 165000

130

12

2002 10

8600 112000

210

36

2011

1 11800

15000

150

18

2005

7 10600

72000

180

27

1997 15

7300 167000

180

27

1998 14

7000 149000

210

36

1999 13

8000 140000

180

27

2007

5 11200

51000

200

33

2010

2 11700

30000

180

27

2007

5 10600

52000

190

30

1996 16

6500 179000

150

18

2002 10

8600 111000

180

27

2005

7

8600

78000

130

12

Region of origin Single owner

USA

Yes

USA

Yes

Foreign

Yes

USA

Yes

Foreign

Yes

Foreign

Yes

Foreign

Yes

USA

Yes

USA

Yes

USA

Yes

USA

Yes

USA

Yes

USA

Yes

Foreign

Yes

Foreign

Yes

Foreign

Yes

USA

Yes

USA

Yes

USA

Yes

USA

Yes

USA

Yes

USA

No

Foreign

No

Foreign

No

Foreign

No

Foreign

No

USA

No

USA

No

USA

No

USA

No

USA

No

USA

No

Foreign

No

Foreign

Yes

Foreign

Yes

Foreign

Yes

Foreign

Yes

USA

Yes

USA

Yes

USA

Yes

USA

Yes

USA

Yes

USA

Yes

Car-44

Car-45

Car-46

Car-47

Car-48

Car-49

Car-50

Car-51

Car-52

Car-53

Car-54

Car-55

Car-56

Car-57

Car-58

Car-59

Car-60

Car-61

Car-62

Car-63

Car-64

Car-65

Car-66

Car-67

Car-68

Car-69

Car-70

Car-71

Car-72

Car-73

Car-74

Car-75

Car-76

Car-77

Car-78

Car-79

Car-80

Car-81

Car-82

Car-83

Car-84

Car-85

Car-86

Car-87

2001

2000

2001

2000

2009

2010

2010

1998

2005

2008

1996

2003

2006

1996

2000

2005

2005

2008

2008

1996

2003

2001

1998

1998

1996

2010

2006

2006

2005

2009

1998

1998

1996

2007

1996

2002

2007

1996

2000

1999

2009

2000

2003

2005

11

12

11

12

3

2

2

14

7

4

16

9

6

16

12

7

7

4

4

16

9

11

14

14

16

2

6

6

7

3

14

14

16

5

16

10

5

16

12

13

3

12

9

7

7600

7900

8400

7800

11300

11800

12000

6900

9000

11200

6700

8200

10700

6100

7000

9000

10000

10600

11700

6500

9500

8200

6400

6600

6000

12100

10300

10400

9300

10700

7200

5800

5800

10400

6100

9000

11400

5900

6300

6600

11900

7200

9700

9300

114000

125000

123000

138000

35000

24000

26000

144000

80000

38000

175000

91000

75000

178000

130000

84000

77000

43000

40000

166000

98000

123000

155000

154000

180000

21000

65000

72000

74000

39000

161000

148000

180000

55000

180000

105000

64000

175000

127000

135000

27000

132000

101000

86000

150

130

150

190

220

170

130

150

190

210

130

170

210

190

150

210

150

140

210

220

130

190

180

170

130

180

180

190

170

220

180

140

200

220

180

150

220

210

130

150

200

180

140

170

15

12

18

30

39

24

12

18

30

36

12

24

36

30

18

36

18

15

36

42

12

30

27

24

12

27

27

30

24

39

27

15

33

42

27

15

42

36

12

15

33

27

15

24

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

Foreign

Foreign

Foreign

Foreign

Foreign

Foreign

Foreign

Foreign

Foreign

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

Foreign

Foreign

Foreign

Foreign

Foreign

USA

USA

USA

USA

USA

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

No

No

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

Car-88

Car-89

Car-90

Car-91

Car-92

Car-93

Car-94

Car-95

Car-96

Car-97

Car-98

Car-99

Car-100

Car-101

Car-102

Car-103

Car-104

Car-105

Car-106

Car-107

Car-108

Car-109

Car-110

Car-111

Car-112

Car-113

Car-114

Car-115

Car-116

Car-117

Car-118

Car-119

Car-120

Car-121

Car-122

Car-123

Car-124

Car-125

Car-126

Car-127

Car-128

Car-129

Car-130

Car-131

2010

2000

2009

2001

1997

2008

2011

2006

1998

2006

2000

2006

2004

2007

2005

2004

1996

1997

2001

2004

1999

2008

2006

1997

1999

2010

1997

2005

2004

2009

2009

2006

2004

2009

2006

2009

2011

2000

2011

2008

2000

2005

2006

2000

2

12

3

11

15

4

1

6

14

6

12

6

8

5

7

8

16

15

11

8

13

4

6

15

13

2

15

7

8

3

3

6

8

3

6

3

1

12

1

4

12

7

6

12

11800

7200

11600

7200

6200

11200

13300

11000

7100

10800

7300

9300

9000

10800

10300

8900

5300

5400

7800

9100

6500

11000

9500

6200

7800

12100

6800

10100

9900

12100

11300

10200

8800

10500

10000

12400

12800

7600

11900

10300

7900

9600

10900

8200

31000

126000

43000

127000

162000

50000

6000

68000

144000

74000

126000

77000

95000

55000

86000

94000

179000

170000

112000

92000

145000

55000

77000

171000

138000

34000

160000

70000

86000

34000

32000

63000

87000

30000

62000

41000

20000

135000

8000

45000

140000

80000

64000

131000

220

130

150

150

130

160

160

190

130

180

190

210

130

160

220

130

220

190

190

150

220

140

160

180

210

200

150

190

180

220

220

210

210

180

180

190

210

130

180

130

210

130

220

220

42

12

18

15

12

21

21

30

12

27

30

36

12

21

42

12

39

30

30

18

42

15

21

27

36

33

15

30

27

42

39

36

36

27

27

30

36

12

27

12

36

12

39

42

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

Foreign

Foreign

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

Foreign

Foreign

Foreign

Foreign

Foreign

Foreign

Foreign

Foreign

Foreign

No

No

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Car-132

Car-133

Car-134

Car-135

Car-136

Car-137

Car-138

Car-139

Car-140

Car-141

Car-142

Car-143

Car-144

Car-145

Car-146

Car-147

Car-148

Car-149

Car-150

Car-151

Car-152

Car-153

Car-154

Car-155

Car-156

Car-157

Car-158

Car-159

Car-160

Car-161

Car-162

Car-163

Car-164

Car-165

Car-166

Car-167

Car-168

Car-169

Car-170

Car-171

Car-172

Car-173

Car-174

Car-175

2002

2002

1999

1999

2009

2001

2007

2003

2008

1997

2010

1996

2010

2002

2011

2011

2003

1998

2006

2003

2001

2000

2004

2006

1996

2009

2002

1996

2003

2011

2011

2008

2000

2007

1999

2003

2005

2002

2011

2006

1999

2001

2003

2011

10

10

13

13

3

11

5

9

4

15

2

16

2

10

1

1

9

14

6

9

11

12

8

6

16

3

10

16

9

1

1

4

12

5

13

9

7

10

1

6

13

11

9

1

9200

7200

7500

6700

11400

7800

10900

9200

10600

6600

11100

6200

11400

8700

12400

12200

8500

6500

10500

9600

8600

7700

8900

9600

6500

10700

8200

6100

8100

11100

11700

11000

6800

10900

7500

9000

9900

8700

12000

9600

7700

7800

10000

11900

119000

103000

140000

140000

28000

126000

52000

94000

40000

162000

27000

170000

20000

106000

5000

8000

109000

147000

59000

96000

113000

125000

97000

59000

167000

38000

119000

171000

100000

14000

11000

44000

134000

48000

134000

91000

82000

116000

6000

64000

148000

130000

93000

17000

210

130

140

210

160

180

130

190

140

210

220

200

180

160

200

220

160

170

180

190

210

160

140

160

180

150

190

190

190

160

190

210

190

210

150

160

210

200

220

160

160

130

190

140

36

12

15

36

21

27

12

30

15

36

42

33

27

21

33

39

21

24

27

30

36

21

15

21

27

15

30

30

30

21

30

36

30

36

18

21

36

33

39

21

21

12

30

15

Foreign

Foreign

Foreign

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

Foreign

Foreign

Foreign

Foreign

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

USA

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

No

No

No

No

No

No

Car-176

Car-177

Car-178

Car-179

Car-180

Car-181

Car-182

Car-183

Car-184

Car-185

Car-186

Car-187

Car-188

Car-189

Car-190

Car-191

Car-192

Car-193

Car-194

Car-195

Car-196

Car-197

Car-198

Car-199

Car-200

Car-201

Car-202

Car-203

Car-204

Car-205

Car-206

Car-207

Car-208

Car-209

...