homewrork

Sample Midterm Assignment

For the questions below, please review the single Excel file you have been provided with.  The file contains data regarding seventy physicians including the number of years they have been in practice and the average number of patients they see each week.

Make sure you follow the instructions below carefully and include answers for each question.   Submit your Excel answers in one Excel file with two tabs (one for Part A and one for Part B) and complete the questions below in the text box at the bottom of this page.   

Frequencies in Excel – Years In Practice (PART A)

1. Separate the sample into several logical groups.

2. Construct a frequency, relative frequency, and cumulative frequency distribution based upon your groups. (Use the Excel formulas we learned in class – I need to see your work in Excel).

a. Show your Histogram that you use to build your distribution table.

b. Use the absolute cell value function for the relative frequencies.

c. Create two descriptive columns – one for your frequencies and one for your cumulative frequencies.

3. Create a pie chart based upon your relative frequency values.  Label the chart appropriately. 

4. Create a bar chart based on your groups’ frequency values.  Label the chart appropriately.

5. Generate a descriptive statistics summary table for your overall sample (not for the individual groups) detailing at least the mean, median, mode, range, skewness, and standard deviation.  Also include a fractile analysis at the 45th percentile, 8th decile, and 3rd quartile. 

6. Use the mean, mode, median, skew, and standard deviation Excel functions to compute the applicable values as well.

Place all of your Excel work in a single Excel file in ONE tab.

Coefficient of Correlation in Excel – Both Variables (PART B)

Generate a coefficient of correlation for the two variables in your sample (Years in Practice and Patients Seen Per Week) via the appropriate Excel function (Correl).  Generate an appropriately labeled scatterplot with trend line. 

Discussion Questions (complete in a Word file)

1.  Describe your sample (only the “Years in Practice” variable) “in words your grandmother would understand.”  Include descriptions of your overall sample including sample size, mean, range, and standard deviation.  Be a thorough as possible.

2.  Describe your sample based on the groupings you have created (X% in this group, Y% in this group, etc.).  Provide at least one description using the cumulative frequency distribution in your frequency table.

3.  Describe your sample based on the fractiles listed above. 

4.  State whether or not your sample is skewed, and if it is skewed, in what direction.  What does this tell you about your sample’s distribution?

5.  Give a brief but overall interpretation of your sample.  The more information you can give, in the simplest language, the better (some of this you may have already answered in #1 – now, just give me a more subjective interpretation – what does this mean to you and what would it mean to your boss?). 

6.  Describe the correlation between the years in practice and number of patients seen per week. Interpret the strength and direction of the relationship, if any, between the two variables. What does this indicate?

7. Create a null and
two-tailed
research hypothesis using the YEARS IN PRACTICE and PATIENTS SEEN PER WEEK variables using both the written (sentence format) and the mathematical format.

HINT: One way to do this is to divide your sample into two halves (low/high) by one of two variables (Years OR Patients), and then see if there is a difference between the two groups.

Revised: 9/18/12

2

>Raw Data

0

– Sample Midterm

Practice

Physicians

Practice Frequency

3

33

0

0

7

60

4

62

70

32 33 70

4

10

80

80

80

12

80

38 85

11

5

60

7

10 100

10 100

13 100

16 100

19 100

17 20 100
50

100

30 100

100

35 100

38 100

100

6 13

15 20 110

25 110

5

9

11 120

13 120

18 120

31 12

7 18 125

125

40 48 125

12

8 37 130
70 5

30 6 140
3 20 140
5 38 140
1 8

11 150

21 24 150
45 26 150

32 150

20

24

29 180

2 11

65 11 200

13 200

16 15 200

18 200

8

20 11

18 21 250
11 26

19 20

25 300

42 300

													Sample of			7	Physicians	Cumulative
Years In	Number of	Relative	Years in	Cumulative
ID	Years in		Practice	Patients Seen Per Week	BIN		Frequency
			3			4
2			8						5	50
		6	1			60
	62				12
	17
47				10		65
57							20				70
			38			32
36					80
							11
	9			16
		24			19
		30
54			15		85
		25
66	90
44	92
				18	95
				13													100
14
52
	35
68
59
		21
43
64		31
55
	37
58		45
		110
		26
46	115
	48				120
69
	29
67
			125
53		40
	42		130
			140
				150
39
23
34	160
27		180
49
				200
22
63
56	225
	250
280
		300
41
51