Exploratory Data Analysis with Excel

Metropolitan Research, Inc., a consumer research organization, conducts surveys designed to evaluate a wide variety of products and services available to consumers. In one particular study, Metropolitan looked at consumer satisfaction with the performance of automobiles produced by a major Detroit manufacturer. A questionnaire sent to owners of one of the manufacturer’s full-sized cars revealed several complaints about early transmission problems. To learn more about the transmission failures, Metropolitan used a sample of actual transmission repairs provided by a transmission repair firm in the Detroit area. The following data show the actual number of miles driven for 50 vehicles at the time of transmission failure.

85,092 32,609 59,465 77,437 32,534 64,090 32,464 59,902 39,323 89,641 94,219 116,803 92,857 63,436 65,605 85,861 64,342 61,978 67,998 59,817 101,769 95,774 121,352 69,568 74,276 66,998 40,001 72,069 25,066 77,098 69,922 35,662 74,425 67,202 118,444 53,500 79,294 64,544 86,813 116,269 37,831 89,341 73,341 85,288 138,114 53,402 85,586 82,256 77,539 88,798

Create a Managerial Report based on appropriate descriptive statistics to summarize the transmission failure data.

Develop a 95% confidence interval for the mean number of miles driven until transmission failure for the population of automobiles with transmission failure. Provide a managerial interpretation of the interval estimate.

Calculating Descriptive Statistics and Confidence Interval in Excel

The descriptive statistics include measurements of mean, median, mode and Interquartile range. The mean, median and mode can be calculated easily in excel using the function of AVERAGE, MEDIAN and MODE.SNGL

The interquartile range is the difference between the first quartile (Q1) and the third quartile (Q3).

The first and third quartile can be calculated in excel using the function QUARTILE.INC

The mean is found to be 73,340. This means that on average 73,340 km miles are driven until transmission failure for the population of automobiles with transmission failure.

{`
The median is 72,705
There is no Mode
Q1 is 60,421
Q3 is 86,575
Thus, the interquartile range will be Q3-Q1 = 86575-60421 = 26,154
`}

Click here to know more about how to use R program for exploratory data analysis

How to Calculate Standard Deviation of Sample in Excel

Calculate Standard Deviation of Sample in Excel

The standard deviation of a sample can be calculated using the formula STDEV.S

For this function, we need to specify the array of sample data for which we need to find the standard deviation and the function will automatically return the sample standard deviation.

How to Calculate Confidence Interval in Excel

The confidence interval can be calculated in excel using the function CONFIDENCE. The function CONFIDENCE in excel returns the value of Margin of Error. This means that the actual confidence interval is (Sample Mean ± CONFIDENCE). In latest excel versions the Z test confidence interval is calculated by using function CONFIDENCE.NORM

For this function, we need to specify the significance level (α) value ranging from 0 to 1. Standard deviation value as well as the size of the data set.

So, in this case excel returns the value of 6901.444898. This means that the 95% confidence interval is 73340 ± 6901.444898

This means that we can be sure that the number of miles driven until transmission failure for the population of automobiles with transmission failure will be between 66438.6 km and 80241.5 km in 95 out of 100 times.

With assignmenthelp.net you can now Learn Excel for Econometrics Free

Excel Econometrics Tutorials

Multiple Regression Analysis

Time Series Analysis

Data Analysis