Question

Please use Excel and Post all those answers here, if you're not going to use Excel then don't answer this question.

Case Study (Due: March 28 2019 – Hard copy) – Maximum 3 student in a group. Worth: 10%

Rob Whitner is the owner of Whitner Autoplex. Rob’s father founded the dealership in 1964, and for more than 30 years, they sold exclusively Pontiacs. In the early 1990s, Rob’s father’s health began to fail, and Rob took over more of the day-to-day operation of the dealership. At this same time, the automobile business began to change-dealers began to sell vehicles from several manufacturers-and Rob was faced with some major decisions. The first came when another local dealer, who handles Volvos, Saabs, and Volkswagens, approached Rob about purchasing his dealership. More recently, the local Chrysler dealership got into difficulty and Rob bought them out. So, now, on the same lot, Rob sells the complete line of Pontiacs, the expensive Volvos, Saabs, Volkswagens, and the Chrysler products, including the popular Jeep line. Whitner Pontiac employs 83, including 23 full-time sales people. Because of the diverse product line, there is considerable variation in the selling price of the vehicles. A top-of-the-line Volvo sells for more than twice the price of a Pontiac Grand Am. Rob would like you to develop some tables and charts that he could review monthly and would like you to report where the selling prices tend to cluster, where the variation is in the selling prices, and to note any trends.

(Reference: Chapter 2 and 3 of the text book. Note: The data set is attached, see instruction in the bottom).

WHITNER AUTOPLEX DATA

Price($000)

Buyer’s Age

23.197

46

23.372

48

20.454

40

23.591

40

26.651

46

27.453

37

17.266

32

18.021

29

28.683

38

30.872

43

19.587

32

23.169

47

35.851

56

19.251

42

20.047

28

24.285

56

24.324

50

24.609

31

28.67

51

15.546

26

15.935

25

19.873

45

25.251

56

25.277

47

28.034

38

24.533

51

27.443

39

19.889

44

20.004

46

17.357

28

20.155

33

19.688

35

23.657

35

26.613

42

20.895

35

20.203

36

23.765

48

25.783

53

26.661

46

32.277

55

20.642

39

21.981

43

24.052

56

25.799

44

15.794

30

18.263

39

35.925

53

17.399

29

17.968

30

20.356

44

21.442

41

21.722

41

19.331

35

22.817

51

19.766

44

20.633

51

20.962

49

22.845

41

26.285

44

27.896

37

29.076

42

32.492

51

18.89

31

21.74

39

22.374

53

24.571

55

25.449

40

28.337

46

20.642

35

23.613

47

24.22

58

30.655

51

22.442

41

17.891

33

20.818

46

26.237

47

20.445

34

21.556

43

21.639

37

24.296

47

Instructions:

1. Must be typed and use excel where necessary.

2. Create two frequency distribution table. One for price and another for age. Both table should include lower limit upper limit, midpoint, width, frequency, relative frequency and cumulative frequency.

3. Draw histogram and frequency polygon using excel for both price and age.

4. Write a short note about your findings (maximum half page)

5. Create two descriptive statistics table one for price and another for age using excel (Descriptive statistics should include mean, variance, standard deviation, 1st quartile, median, 3rd quartile, mode etc)

6. Create two Box plot one for price and another for age.

7. Write another short note about your findings (maximum half page)

8. For excel command check chapter 2 and 3.

9. Answer will vary you can add more analysis.

Answer 1

Frequency	Table
Xi	XiPi	XiBi
1	23.197	46
2	46.744	96
3	61.362	120
4	94.364	160
5	133.255	230
6	164.718	222
7	120.862	224
8	144.168	232
9	258.147	342
10	308.72	430
11	215.457	352
12	278.028	564
13	466.063	728
14	269.514	588
15	300.705	420
16	388.56	896
17	413.508	850
18	442.962	558
19	544.73	969
20	310.92	520
21	334.635	525
22	437.206	990
23	580.773	1288
24	606.648	1128
25	700.85	950
26	637.858	1326
27	740.961	1053
28	556.892	1232
29	580.116	1334
30	520.71	840
31	624.805	1023
32	630.016	1120
33	780.681	1155
34	904.842	1428
35	731.325	1225
36	727.308	1296
37	879.305	1776
38	979.754	2014
39	1039.779	1794
40	1291.08	2200
41	846.322	1599
42	923.202	1806
43	1034.236	2408
44	1135.156	1936
45	710.73	1350
46	840.098	1794
47	1688.475	2491
48	835.152	1392
49	880.432	1470
50	1017.8	2200
51	1093.542	2091
52	1129.544	2132
53	1024.543	1855
54	1232.118	2754
55	1087.13	2420
56	1155.448	2856
57	1194.834	2793
58	1325.01	2378
59	1550.815	2596
60	1673.76	2220
61	1773.636	2562
62	2014.504	3162
63	1190.07	1953
64	1391.36	2496
65	1454.31	3445
66	1621.686	3630
67	1705.083	2680
68	1926.916	3128
69	1424.298	2415
70	1652.91	3290
71	1719.62	4118
72	2207.16	3672
73	1638.266	2993
74	1323.934	2442
75	1561.35	3450
76	1994.012	3572
77	1574.265	2618
78	1681.368	3354
79	1709.481	2923
80	1943.68	3760

Histogram Price and Age 100% 90% 80% 70% 60% 50% 28 13 30% 20% 10% 0% (22.546, 26.046] [15.546, 19.046] (33.046, 36.546] (19.046, 22.546] (26.046, 29.546] (29.546, 33.046]

#Descriptive Statistics

Descriptive Statistics	Price($000)(Pi)	Buyer’s Age(Bi)
Mean	23.2181625	42.15
Variance	18.96112864	67.52151899
Standerd Deviation	4.35443781	8.217147862
Median	22.831	42.5
Mode	20.642	46
1st Quartile	15.546	35.75
3rd Quartile	25.787	47.25
Min	15.546	25
Max	35.925	58
lower limit	14.68346439	26.04439019
upper limit	31.75286061	58.25560981

BoxPlot of Price and Age 70 60 50 40 30 20 10 Price(5000(Pi) Buyer's Age(Bi)