Statistics 14: Comprehensive Analysis of Used Car Prices in Berlin

This report presents a statistical analysis of used car prices in Berlin, examining a dataset of 700 cars. The analysis includes descriptive statistics, such as mean, median, and standard deviation, for price, age, kilometers traveled, and power. The report explores the distribution of car prices, finding a right-skewed distribution, and investigates the relationship between car type (Convertible, Hatchback, Sedan, Coupe), transmission type, and fuel type. Regression analysis is employed to estimate the impact of variables like age, mileage, fuel type, and power on car prices, with results indicating that the model provides a good fit, though some variables are not statistically significant. Confidence intervals are calculated for the average price of Hatchbacks and Coupes. The report concludes that the price of used cars is not normally distributed and provides a predictive equation for car prices based on the analyzed variables, along with an example calculation for a Sedan. The ANOVA results support the model's statistical significance.

Running Head: STATISTICS
Business Analytics and Statistics
Name of the student
Name of the university
Author’s note

1STATISTICS
Abstract
The following analysis presents the prices of used cars in Berlin. From the analysis it is
seen that it is found that most of the cars have a price less than $25,000. Moreover most of the
cars are between 16 and 55 years of age. Four different types of cars: Convertible, Hatchback,
Sedan and Coupe were considered for the study. The power, kilometres travelled, fuel type, were
also used to estimate the price of a used car. The estimate was found to be a good fit for the
study, although some of the variables were not statistically significant. The price of used cars
was not normally distributed.

2STATISTICS
Table of Contents
Introduction......................................................................................................................................3
Part a............................................................................................................................................3
Part b............................................................................................................................................4
Part c............................................................................................................................................5
Part d............................................................................................................................................6
Part e............................................................................................................................................7
Part f.............................................................................................................................................7
Part g............................................................................................................................................8
Part h............................................................................................................................................8
Part i.............................................................................................................................................9
Part j...........................................................................................................................................11
Part k..........................................................................................................................................11
Part l...........................................................................................................................................11
Part m.........................................................................................................................................11
Part n..........................................................................................................................................12
Part o..........................................................................................................................................14
Part p..........................................................................................................................................14
Conclusion.....................................................................................................................................15

3STATISTICS
Introduction
The present analysis is the evaluation of prices of used cars from Berlin. The prices of
700 cars have been selected and analysed. The analysis of the cars considers the age of the cars,
kilometres driven, the fuel used and car type. The analysis of prices of cars is done using
different statistical techniques.
Part a
Table 1: Descriptive statistics for price, age, kilometres and Power of cars
price age kilometer PowerKW
Mean 15151.56 13.46 121292.86 136.80
Standard Error 675.95 0.29 1610.95 2.34
Median 9554 13 150000 124
Mode 29400 14 150000 129
Standard Deviation 17883.94 7.58 42621.62 61.98
Sample Variance 319835297.68 57.40 1816602237.89 3841.02
Kurtosis 61.61 4.42 0.02 4.77
Skewness 5.61 1.58 -1.22 1.85
Range 263865 54 145000 403
Minimum 735 1 5000 42
Maximum 264600 55 150000 445
Sum 10606092 9422 84905000 95760
Count 700 700 700 700
From the above table it is found that the average price of a car is $15151.56 with a
standard deviation of $17883.94. The median price of a used car is $9554. The mean price of a
car is higher than the median price. Thus the prices of cars is skewed right.
From the above table it is found that the average age of a car is 13.46 with a standard
deviation of 7.58. The median age of a used car is 13. The mean age of a car is higher than the
median age. Thus the age of cars is skewed right.
From the above table it is found that the average distance travelled by a car is 121292.86
km with a standard deviation of 42621.62 km. The median distance travelled of a used car is
150000. The mean distance travelled of a car is lower than the median distance travelled. Thus
the distance travelled by used cars is skewed left.
The average power of a car is 136.80kW with a standard deviation of 61.98kW. The
median power of the used car is 124kW. The mean power of a car is higher than the median
power. Thus the power of cars is skewed right.

4STATISTICS
Table 2: Distribution of Transmission
Type of transmission Count
Manual Transmission 147
Automatic Transmission 553
The analysis of the data shows that 147 cars had manual transmission while 553 cars had
automatic transmission.
Table 3: Distribution of Fuel
Fuel Count
Diesel 262
Petrol 438
The analysis of the data shows that 262 cars used Diesel while 438 cars used petrol.
Part b
25000 50000 75000 100000 125000 150000 175000 200000 225000 250000 275000
0
100
200
300
400
500
600
Distribution of Price
Price
Price
Figure 1: Distribution of Price
Figure 1 presents the distribution of price of 700 cars. From the figure it is seen that the
distribution of price is skewed right. Hence, the average price of a Mercedes is more than the
median price. In addition, it is also found that most of the cars are priced below $25,000.

5STATISTICS
Part c
Table 4: Five numbered Summary for Age of Cars
Five Numbered Summary Age
Minimum 1
First Quartile 9
Median 13
Third Quartile 16
Maximum 55
Z min = 1−13.46
7.58 =−12.46
7.58 =−1.66
Z max = 55−13.46
7.58 = 41.54
7.58 =5.48
Shape Description: Mean (13.46) > Median (7.58) = positive and right skewed
Outliers: There are outliers because one of the value is greater than 3
Age
0
2
4
6
8
10
12
14
16
18
Distribution of Age
Figure 2: Distribution of Age of Cars
Figure 2 presents the distribution for Age of cars. From the figure it is seen that the
distribution of price is skewed right. Thus most of the cars have an age more than the median age
of cars.
Part d
Table 5: Type of Vehicle

6STATISTICS
Type of Vehicle Count
Non-Convertible 626
Convertible 74
The probability that a randomly selected vehicle would be convertible = 74
700 =0.1057
Table 6: Age distribution of Convertible
Age Non-Convertible Convertible Grand Total
1-5 78 6 84
6-10 162 11 173
11-15 205 24 229
16-20 104 20 124
21-25 46 2 48
26-30 14 1 15
31-35 9 6 15
36-40 2 3 5
41-45 4 4
46-50 1 1
51-55 1 1 2
Grand Total 626 74 700
The number of Convertibles below 25 years of age = 63
The number of Convertibles above 25 years of age = 11
The likelihood that a convertible would be above 25 years = 11
74 =0.1486
Table 7: Distribution of Convertible Cars
Age Convertible Non-Convertible Total
Less than 25 63 583 646
More than 25 11 43 54
Total 74 626 700
The probability that a car is more than 25 and is convertible = 11
700 =0.0157

7STATISTICS
The probability that a car is convertible = 74
700 =0.1057
The probability that the age of a car is more than 25 = 54
700 =0.077
Thus 0.0157 ≠ 0.1057*0.077
Thus the two variables Age and convertible are not independent of each other.
Part e
Table 8: Confidence Interval for Price of Car
Hatchback Coupe
Average 3104.07 14610.07
Standard Deviation 1712.70 12973.79
Count 30 108
Standard Error 312.69 1248.40
z-value 1.96 1.96
Margin of Error 612.88 2446.87
Lower Limit 2491.19 12163.20
Upper Limit 3716.95 17056.94
The average price of a used Hatchback is $3104.07 with a standard deviation of
$1712.70. The 95% confidence interval for price of Hatchbacks is $2491.19 and $3716.95.
Hence, it can be said with 95% confidence a similar survey that the average price of Hatchbacks
would be within the limits of $2491.19 and $3716.95.
The average price of a used Coupe is $14610.07 with a standard deviation of $12973.79.
The 95% confidence interval for price of Coupe’s is $12163.20 and $17056.94. Hence, it can be
said with 95% confidence a similar survey that the average price of Coupe’s would be within the
limits of $12163.20 and $17056.94.
Part f
Table 9: Type of Transmission vs Vehicle Type
Vehicle Type Transmission
Manual Automatic Grand Total
Convertible 6 68 74
Coupe 20 86 106
Hatchback 15 15 30
Sedan 106 384 490
Grand Total 147 553 700

8STATISTICS
From the above table it is seen based that15 Hatchbacks of each of manual as well as
automatic transmission were sold. The total number of cars sold is 700.
Thus the population of manual transmission hatchbacks sold ¿ 15
30 =0.5
To test the claim of the following hypothesis is used:
Null Hypothesis: The proportion of hatchbacks having manual transmission P ≥ 0.5
Alternate Hypothesis: The proportion of hatchbacks having manual transmission P < 0.5
Thus σ = √ [ P∗(1−P)
n ]= √ [ 0.5∗0.5
30 ]=0.028868
Thus z= p−P
σ = 0.5−0.5
0.028868 =0.000
From the normal distribution calculator P(z<0.000) = 1.000
Since p-value is more than 0.05, significance level hence we do not reject Null
Hypothesis. Thus, The proportion of hatchbacks having manual transmission P ≥ 0.5
Part g
Table 10: Regression Summary

9STATISTICS
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.7261
R Square 0.5272
Adjusted R Square 0.5211
Standard Error 12376.63
Observations 700
ANOVA
df SS MS F Significance F
Regression 9 117870040626.709 13096671180.745 85.498 0.000
Residual 690 105694832451.771 153180916.597
Total 699 223564873078.480
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%
Intercept 18415.41 2668.11 6.902 0.000 13176.82 23654.00
age -108.26 74.90 -1.445 0.149 -255.32 38.81
automatic -1841.36 1245.54 -1.478 0.140 -4286.86 604.14
kilometer -0.15 0.01 -12.786 0.000 -0.18 -0.13
petrol -4772.12 1117.83 -4.269 0.000 -6966.88 -2577.36
Damage -1679.56 2048.40 -0.820 0.413 -5701.41 2342.29
PowerKW 148.38 8.90 16.665 0.000 130.89 165.86
hatchback 1779.72 2679.41 0.664 0.507 -3481.05 7040.49
sedan -8.53 1388.13 -0.006 0.995 -2733.99 2716.93
convertible 9847.76 1904.05 5.172 0.000 6109.34 13586.17
The above table presents the regression coefficients for the multiple regression.
Part h
Step 1
To test if the coefficient of Age is different than zero the hypothesis used is:
Null Hypothesis: The coefficient of Age is equal to zero.
H0 : β1=0
Alternate Hypothesis: The coefficient of Age is not equal to zero.
H A : β1 ≠ 0
Step 2

10STATISTICS
α=0.05
Step 3
We use a t-distribution with 698 degrees of freedom.
Step 4:
At the 5% level of significance with 698 degrees of freedom for a two-sided test, the
rejection region is where t > 1.96 or t < -1.96
Step 5:
The test statistics is defined as t= β
SE =−108.26
74.90 =−1.445
Step 6:
As t = -1.445<-1.96, the t-statistic is in the rejection region and can conclude that at the
5% level of significance, the coefficient of Age are statistically not different than zero.
P value:
Using the p-value approach, we cannot reject the null hypothesis if the p-value < 0.05.
From the results, the p-value = 0.149, thus we cannot reject the null hypothesis at 5%
level of significance.
Part i
Automatic - If all other variables remain the same then the sales price of a car will
decrease by 1841.36 for a car with manual transmission. This is consistent with
expectations.
Kilometer - If all other variables remain the same then the sales price of a car will
decrease by $0.15 for each kilometer the car has run. This is consistent with expectations.
Petrol -If all other variables remain the same then the sales price of a car will decrease by
$4772.12 for a car using diesel. This is consistent with expectations.

11STATISTICS
Damage - If all other variables remain the same then the sales price of a car will decrease
by $1679.56 for a car which is not damaged. This is consistent with expectations.
Powerkw - If all other variables remain constant then the sales price of a car would
increase by $148.38 for each kw increase in its power. This is consistent with
expectations.
Hatchback - If all other variables remain the same then the sales price of a car will
increase by 1779.72 if the car is a Hatchback. This is consistent with expectations.
Sedan - If all other variables remain the same then the sales price of a car will decrease
by $8.53 if the car is a Sedan. This is consistent with expectations.
Convertible - If all other variables remain the same then the sales price of a car will
increase by $9847.76 if the car is a Convertible. This is consistent with expectations.
Part j
Table 11: Regression Statistics
Multiple R 0.7261
R Square 0.5272
Adjusted R Square 0.5211
Standard Error 12376.63
Observations 700
The value of adjusted R2 is 0.5211. The adjusted R2 is R2 which has been corrected for
the number of variables. Thus 52.11% of the variability in the price of used Mercedes can be
predicted from the values of independent variables.
The value of R2 is 0.5272. From R2 52.72% of the variability in prices of used cars can be
predicted.
Thus we find that there is very less difference in the values of Adjusted R2 and R2.
Part k
Table 12: ANOVA
df SS MS F
Significance
F
Regression 9 117870040626.709 13096671180.745 85.498 0.000