Statistical Analysis on Excel

This document provides a statistical analysis on Excel for two and three year old cars. It includes a frequency histogram, descriptive statistics, price comparison between dealer and seller, scatter plots for age and odometer, and correlation coefficients for age and price, and odometer and price.

Running head: STATISTICAL ANALYSIS ON EXCEL
Statistical Analysis on Excel
Name of the Student:
Name of the University:
Author Note:

1STATISTICAL ANALYSIS ON EXCEL
Table of Contents
Price of two and three year old cars...........................................................................................2
Construction of Frequency Histogram for the Price of Two and Three Year Old Cars........2
Calculation of Descriptive Statistics for the Price of Two and Three Year Old Cars...........3
Difference in price between dealer and seller............................................................................4
Boxplots for Prices of Private Seller and for Used Car Dealer Prices:..................................4
Descriptive Statistics for Private Sale Prices and for Used Car dealer Prices.......................5
Relationship for Price and Age and Between Price and Odometer Reading.............................6
Scatter Plot for Age and Price................................................................................................6
Scatter Plot for Odometer and Price.......................................................................................7
Correlation Coefficient for Age and Price.............................................................................8
Correlation Coefficient for Odometer and Price....................................................................9

2STATISTICAL ANALYSIS ON EXCEL
Price of two and three year old cars
Construction of Frequency Histogram for the Price of Two and Three Year Old Cars
> $13600 13600-14600 14600-15600 15600-16600 16600-17600 17600-18600 18600-19600
0
1
2
3
4
5
6
7
8
9
10
Distribution of Car prices
Prices
Frequency
Figure 1: Frequency Histogram of the Prices of Two and Three year Old Cars
The figure 1 shows the car price is slightly positively skewed.

3STATISTICAL ANALYSIS ON EXCEL
Calculation of Descriptive Statistics for the Price of Two and Three Year Old Cars
Table 1: Descriptive Statistics for the Price of Two and Three Year Old Cars
Price
Mean 16005.68
Standard Error 242.29
Median 15867.50
Mode 16990.00
Standard Deviation 1493.60
Sample Variance 2230845.19
Kurtosis -0.64
Skewness 0.22
Range 5990
Minimum 13000
Maximum 18990
Sum 608216
Count 38
Distribution of two and three year old car price is positively skewed and less extreme
than the normal distribution as kurtosis is negative. The average price of a two and three year
old car is 16005.68.

4STATISTICAL ANALYSIS ON EXCEL
Difference in price between dealer and seller
Boxplots for Prices of Private Seller and for Used Car Dealer Prices:
Figure 2: Boxplots for Prices of Private Seller and For Used Car Dealer Prices
The prices of dealer is more likely higher as the median value is quite high. However,
the median of the prices of private seller is in the middle which means the prices of private
seller is average.

5STATISTICAL ANALYSIS ON EXCEL
Descriptive Statistics for Private Sale Prices and for Used Car dealer Prices
Table 2: Descriptive Statistics for Private Sale Prices and for Used Car dealer Prices
Dealer Private
Mean 12408.93 11325.45
Standard Error 503.65 611.39
Median 13988.00 11495.00
Mode 16990.00 13000.00
Standard Deviation 4243.81 4055.51
Sample Variance 18009936.49 16447183.00
Kurtosis -0.97 -0.85
Skewness -0.40 -0.30
Range 15690 15499
Minimum 3300 2500
Maximum 18990 17999
Sum 881034 498320
Count 71 44
The distribution of both the prices are less extreme and negatively skewed. The price
of dealer is more extreme and negatively more skewed than price of private seller.

6STATISTICAL ANALYSIS ON EXCEL
Relationship for Price and Age and Between Price and Odometer Reading
Scatter Plot for Age and Price
Figure 3: Scatter Plot for Age and Price
The figure 3 shows the negative relation between age and price of car. This implies
old cars are cheaper.
0 5 10 15 20 25
$0
$2,000
$4,000
$6,000
$8,000
$10,000
$12,000
$14,000
$16,000
$18,000
$20,000
Relation of Age and Price
Age of Cars
Price of Cars

7STATISTICAL ANALYSIS ON EXCEL
Scatter Plot for Odometer and Price
0 50,000 100,000 150,000 200,000 250,000 300,000
$0
$2,000
$4,000
$6,000
$8,000
$10,000
$12,000
$14,000
$16,000
$18,000
$20,000
Relation of Odometer and Price
Odometer (km)
Price of Cars
Figure 4: Scatter Plot for Odometer and Price
The scatter diagram shows the negative relation between odometer and price of car.

8STATISTICAL ANALYSIS ON EXCEL
Correlation Coefficient for Age and Price
Table 3: Correlation Table for Age and price
Age Price
Age 1.0000
Price -0.8924 1.0000
The table 3 describes the strong negative relation between age and price of car.

9STATISTICAL ANALYSIS ON EXCEL
Correlation Coefficient for Odometer and Price
Table 4: Correlation Table for Odometer and Price
Odometer (kms) Price
Odometer (kms) 1.0000
Price -0.8332 1.0000
The table shows the strong negative relation between odometer and price of car.
However, the odometer and price of car is less strong than the relation between age and price
of car.