Question

Part II: To be completed on Excel when indicated. Otherwise on standard paper and pencil

(4 Non - Excel Problems)

(4 Excel Problems)

Problem 1 (Excel-1)

First-time patients at North Shore Family Practice are required to fill out a questionnaire that gives the doctor an overall idea of each patient’s health. The first question is: “In general, what is the quality of your health?” The patient chooses Excellent, Good, Fair, or Poor. Over the past month, the responses to this question from first-time patients were:

Fair

Good

Fair

Excellent

Good

Good

Good

Poor

Excellent

Excellent

Poor

Good

Fair

Good

Good

Good

Good

Poor

Fair

Excellent

Excellent

Good

Good

Good

I. Construct frequency and relative frequency distributions that summarize the responses to the questionnaire. (Do not round intermediate calculations. Round "Relative Frequency" to 3 decimal places.).

II. What is the most common response to the questionnaire?

III. Construct a Pie Chart based on the relative frequency distributions

Problem 2

Scores on the final in a statistics class are as follows.

75

25

75

62

80

85

80

99

90

60

86

92

40

74

72

65

87

70

85

70

Calculate the 25th, 50th, and 75th percentiles. (Do not round intermediate calculations. Round your answers to 2 decimal places.)

Problem 3

Home Depot and Lowe’s are the two largest home improvement retailers in the U.S. The following table shows the total revenue (in billions) for each retailer for the years 2008–2010.

Year

Home Deposit

Lowe's

2008

$77.35   

$48.28   

2009

71.29   

48.23   

2010

66.18   

47.22   

I. Calculate the growth rate for 2008–2009 and 2009–2010 for each retailer. (Negative values should be indicated by a minus sign. Round your answers to 4 decimal places.)

II. Calculate the average growth rate for each retailer. (Negative values should be indicated by a minus sign. Do not round intermediate calculations. Round final answers to 3 decimal places.)

III. Which Firm had the higher growth rate

Problem 4 (Excel-2)

Consider the following data set:

4

10

8

7

6

10

11

14

13

14

3

9

8

5

7

6

10

3

11

11

8

8

4

5

5

12

12

3

8

8

10

–9

28

14

–5

9

11

5

8

–3

33

–4

2

3

22

25

5

29

26

0

–8

–5

0

15

–4

35

21

15

19

23

4

6

–2

12

24

36

15

3

–5

2

I. Construct a frequency distribution using classes of [−10 up to 0], [0 up to 10], etc.

a. How many of the observations are at least 10 but less than 20?

II. Construct a relative frequency distribution and a cumulative relative frequency distribution.

a. Construct a relative frequency distribution and a cumulative relative frequency distribution. (Round "Relative Frequency" and "Cumulative Relative Frequency" to 3 decimal places.)

III. Construct a Histogram based on the frequency distribution

IIII. Construct Cumulative Relative frequency and construct an Ogive based on the cumulative relative frequency

Problem 5 (Excel-3)

I. Using the data below calculate the following

Sample

75

25

75

62

80

85

80

99

90

60

II.

a. Arithmetic mean

b. Mode

c. Median

d. Range

e. Variance

f. Standard Deviation

g. Coefficient of Variation

Problem 6 (excel-4)

Consider the following data set:

1

13

11

4

4

7

13

11

13

15

2

11

5

5

3

4

8

6

7

7

5

8

2

8

11

10

6

2

10

10

12

-10

29

17

-6

6

11

5

11

0

32

-8

2

5

28

28

5

28

29

0

-6

-10

1

17

-5

32

26

16

19

28

4

11

-3

9

25

37

14

1

-7

3

a) Construct a frequency distribution using classes of −10 up to 0, 0 up to 10, etc.

b) Calculate the population Mean

c) Calculate the population variance and the population standard deviation

d) Construct a relative frequency distribution and a cumulative relative frequency distribution. (Round "Relative Frequency" and "Cumulative Relative Frequency" to 3 decimal places.)

e) Construct a pie Chart to illustrate Part “d” (Relative Frequency)

f) Construct a less than Ogive to illustrate part “d”. (Cumulative relative frequency)

Problem 7

The returns from an investment are 2% in Year 1, 5% in Year 2, and 1.8% in the first half of Year 3. Calculate the annualized return for the entire period. (Round your intermediate calculations to 4 decimal places and final answer to 2 decimal places.)

Problem 8

An investor bought common stock of Microsoft Corporation on three occasions at the following prices.

Date

Price per Share

Number of Shares

January 2009

$19.58

70

July 2009

$24.06

80

December 2009

$29.54

50

Calculate the average price per share at which the investor bought these shares. (Round your answer to 2 decimal places.)

Answer 1

1.

Response	Frequency	Relative frequency
Excellent	5	0.208333333
Fair	4	0.166666667
Good	12	0.5
Poor	3	0.125
Total	24	1

'Good' was the most common response.

2.

First we sort the data.

25

40

60

62

65

70

70

72

74

75

75

80

80

85

85

86

87

90

92

99

There are 20 scores. Then 25th percentile is the AM of 20*(25/100) or 5th and 6th smallest observation which is 67.5.

Then 50th percentile is the AM of 20*(50/100) or 10th and 11th smallest observation which is 75.

Then 25th percentile is the AM of 20*(75/100) or 15th and 16th smallest observation which is 85.5.

3.

Growth rate is calculated as: 100 x (current - old)/old %

2008–2009 for the Home deposite retailer the growth rate is -7.8345%

2008–2009 for the Lowe's retailer the growth rate is -.1036%

2009–2010 for the Home deposite retailer the growth rate is -7.1697%.

2008–2009 for the Lowe's retailer the growth rate is -2.0941%.

4.

Bin	Frequency
0	11
10	33
20	15
30	8
40	3
More	0

33 observations are less than 20 but atleast 10.

Frequency	Rel Freq	Rel Freq(>)
11	0.157143	0.15714286
33	0.471429	0.62857143
15	0.214286	0.84285714
8	0.114286	0.95714286
3	0.042857	1

5.

Mean	73.1
Standard Error	6.505467786
Median	77.5
Mode	75
Standard Deviation	20.57209545
Sample Variance	423.2111111
Range	74

6.

Bin	Frequency
0	10
10	32
20	17
30	8
40	3
More	0

Population mean is 9.4.

Population variance is 115.40.

Population Standard deviation is 10.7425.

Bin	Frequency	Rel Freq
0	10	0.142857
10	32	0.457143
20	17	0.242857
30	8	0.114286
40	3	0.042857
More	0

7. Annualised return is 2.6%.

8.The average price per share is 23.86