Question

You are the VP at a car dealership. Your boss comes to you one afternoon and asks if you could tell him the average profit per vehicle the dealership makes. He wants the information by the end of the day. Sounds easy enough except that each salesman keeps track of their average profit per vehicle and only 20 salesmen of the 40 employed work at the dealership on a given day. You collect the information below from the salesmen currently on shift. You want to give your boss an answer you are 95% confident in. What do you tell him?

Average Profit per Vehicle
$ 5,125.00	$ 1,850.00	$ 4,620.00	$ 3,448.00	$ 2,158.00
$ 2,689.00	$ 4,896.00	$ 6,411.00	$ 5,128.00	$ 4,169.00
$ 1,689.00	$ 4,855.00	$ 2,177.00	$ 3,891.00	$ 4,588.00
$ 3,485.00	$ 3,489.00	$ 2,488.00	$ 4,812.00	$ 2,588.00

Question 1:

Calculate the lower and upper confidence limit based on this information.

Enter in the name of the data points you need to solve this.

Lower Bound
Upper Bound

Question 2:

Based on your findings above, what will you tell your boss the average profit is at the company?

The next day your boss comes to you and says he reviewed the statement you made and asks if you are sure about your answer. It just so happens the other 20 salesmen were at work that day and you are able to gather the remaining data points. What do you tell your boss?

Average Profit per Vehicle
$ 5,125.00	$ 1,850.00	$ 4,620.00	$ 3,448.00	$ 2,158.00
$ 2,689.00	$ 4,896.00	$ 6,411.00	$ 5,128.00	$ 4,169.00
$ 1,689.00	$ 4,855.00	$ 2,177.00	$ 3,891.00	$ 4,588.00
$ 3,485.00	$ 3,489.00	$ 2,488.00	$ 4,812.00	$ 2,588.00
$ 4,258.00	$ 3,155.00	$ 3,892.00	$ 2,988.00	$ 3,500.00
$ 2,458.00	$ 5,348.00	$ 4,122.00	$ 5,841.00	$ 3,678.00
$ 1,244.00	$ 5,133.00	$ 2,645.00	$ 4,722.00	$ 2,841.00
$ 1,879.00	$ 3,610.00	$ 5,179.00	$ 3,615.00	$ 2,130.00

Question 3:

Calculate the lower and upper confidence limit based on this information.

Enter in the name of the data points you need to solve this.

Lower Bound
Upper Bound

Question 4:

Based on your findings above, what will you tell your boss the average profit is at the company?

Please show your work in the provided cells, and show what the formulas were used. I need this to be clarified as simply as possible.

Answer 1

Ans 2 ) using excel>addin>phstat>confidence interval

we have

Confidence Interval Estimate for the Mean
Data
Sample Standard Deviation	1325.452618	STDEV.S(Sheet1!$A$2:$A$21)
Sample Mean	3727.3	AVERAGE(Sheet1!$A$2:$A$21)
Sample Size	20
Confidence Level	95%
Intermediate Calculations
Standard Error of the Mean	296.3802154	B4/SQRT(B6)
Degrees of Freedom	19
t Value	2.0930	T.INV.2T(1-B7, B11
Interval Half Width	620.3309	B12*B10
Confidence Interval
Interval Lower Limit	3106.97	B5-B13
Interval Upper Limit	4347.63	B5+B13

upper bound = 4347.63lower bound = 3106.97

Ans 3 )using excel>addin>phstat>confidence interval

we have

Confidence Interval Estimate for the Mean
Data
Sample Standard Deviation	1266.281084	STDEV.S(Sheet1!$B$2:$B$41)
Sample Mean	3669.6	AVERAGE(Sheet1!$B$2:$B$41)
Sample Size	40
Confidence Level	95%
Intermediate Calculations
Standard Error of the Mean	200.2166192	B4/SQRT(B6)
Degrees of Freedom	39	B6-1
t Value	2.0227	T.INV.2T(1-B7, B11)
Interval Half Width	404.9763	B12*B10
Confidence Interval
Interval Lower Limit	3264.62	B5-B13
Interval Upper Limit	4074.58	B5+B13

lower bound = 3264.62

upper bound = 4074.58

Ans 4 ) we will tell the boss that we are 95 % confident that the average profit per vehicle the dealership makes is lies in between 3264.62 to 4074.58