Question

(please provide detailed solution with formula and figures)

the researcher wishes to use numerical descriptive measures to summarize the data on each of the two variables: hours worked per week and yearly income. a) Prepare and display a numerical summary report for each of the two variables including summary measures such as mean, median, range, variance, standard deviation, smallest and largest values and the three quartiles. Notes: Use QUARTILE.EXC command to generate the three quartiles. b) Compute the correlation coefficient using the relevant Excel function to measure the direction and strength of the linear relationship between the two variables. Display and interpret the correlation value.

Hours Per Week	Yearly Income ('000's)
18	43.8
13	44.5
18	44.8
25.5	46.0
11.5	41.2
18	43.3
16	43.6
27	46.2
27.5	46.8
30.5	48.2
24.5	49.3
32.5	53.8
25	53.9
23.5	54.2
30.5	50.5
27.5	51.2
28	51.5
26	52.6
25.5	52.8
26.5	52.9
33	49.5
15	49.8
27.5	50.3
36	54.3
27	55.1
34.5	55.3
39	61.7
37	62.3
31.5	63.4
37	63.7
24.5	55.5
28	55.6
19	55.7
38.5	58.2
37.5	58.3
18.5	58.4
32	59.2
35	59.3
36	59.4
39	60.5
24.5	56.7
26	57.8
38	63.8
44.5	64.2
34.5	55.8
34.5	56.2
40	64.3
41.5	64.5
34.5	64.7
42.3	66.1
34.5	72.3
28	73.2
38	74.2
31.5	68.5
36	69.7
37.5	71.2
22	66.3
33.5	66.5
37	66.7
43.5	74.8
20	62.0
35	57.3
24	55.3
20	56.1
41	61.5

Answer 1

(a) The Snapshot of the summary data is given below

	Hours	Income
Mean	29.874	57.328
Median	30.5	56.2
Mode	34.5	55.3
Range	26.5	33.6
Variance	64.727	68.344
SD	8.045	8.267
Smallest	18	41.2
Largest	44.5	74.8
Q1	24.5	51.5
Q2	30.5	56.2
Q3	36	63.7

Median = Middle Value

For odd number of observations, the median is the (n + 1) / 2 th number = (65 + 1) / 2 = 33rd number

The 33 number for number of hours = 30.5 and yearly income = 55.3

Mode = The value that occur the maximum number of times = 34.5 for number of hours (5 times) and 55.3 (2 times) for Income.

Range = The difference between the largest and the smallest value

For Number of Hours = 44.5 - 18 = 26.5

For Yearly Income = 74.8 - 41.2 = 33.6

Q1 and Q3 have been calculated by the excel function QUARTILE.

Q2 is the same as the median value

Calculation for the Mean, Variance and Standard Deviation:

Mean = Sum of observation / Total Observations

Standard deviation = SQRT(Variance)

Variance = Sum Of Squares (SS) / n - 1, where SS = SUM(X - Mean)².

The data below has been arranged in ascending order

Hours Per week				Yearly Income
#	X	(X - Mean)2		#	X	(X - Mean)2
1	18	140.991876		1	41.2	260.112384
2	11.5	337.603876		2	43.3	196.784784
3	13	284.731876		3	43.6	188.457984
4	15	221.235876		4	43.8	183.006784
5	16	192.487876		5	44.5	164.557584
6	18	140.991876		6	44.8	156.950784
7	18	140.991876		7	46	128.323584
8	18.5	129.367876		8	46.2	123.832384
9	19	118.243876		9	46.8	110.838784
10	20	97.495876		10	48.2	83.320384
11	20	97.495876		11	49.3	64.448784
12	22	61.999876		12	49.5	61.277584
13	23.5	40.627876		13	49.8	56.670784
14	24	34.503876		14	50.3	49.392784
15	24.5	28.879876		15	50.5	46.621584
16	24.5	28.879876		16	51.2	37.552384
17	24.5	28.879876		17	51.5	33.965584
18	25	23.755876		18	52.6	22.353984
19	25.5	19.131876		19	52.8	20.502784
20	25.5	19.131876		20	52.9	19.607184
21	26	15.007876		21	53.8	12.446784
22	26	15.007876		22	53.9	11.751184
23	26.5	11.383876		23	54.2	9.784384
24	27	8.259876		24	54.3	9.168784
25	27	8.259876		25	55.1	4.963984
26	27.5	5.635876		26	55.3	4.112784
27	27.5	5.635876		27	55.3	4.112784
28	27.5	5.635876		28	55.5	3.341584
29	28	3.511876		29	55.6	2.985984
30	28	3.511876		30	55.7	2.650384
31	28	3.511876		31	55.8	2.334784
32	30.5	0.391876		32	56.1	1.507984
33	30.5	0.391876		33	56.2	1.272384
34	31.5	2.643876		34	56.7	0.394384
35	31.5	2.643876		35	57.3	0.000784
36	32	4.519876		36	57.8	0.222784
37	32.5	6.895876		37	58.2	0.760384
38	33	9.771876		38	58.3	0.944784
39	33.5	13.147876		39	58.4	1.149184
40	34.5	21.399876		40	59.2	3.504384
41	34.5	21.399876		41	59.3	3.888784
42	34.5	21.399876		42	59.4	4.293184
43	34.5	21.399876		43	60.5	10.061584
44	34.5	21.399876		44	61.5	17.405584
45	35	26.275876		45	61.7	19.114384
46	35	26.275876		46	62	21.827584
47	36	37.527876		47	62.3	24.720784
48	36	37.527876		48	63.4	36.869184
49	36	37.527876		49	63.7	40.602384
50	37	50.779876		50	63.8	41.886784
51	37	50.779876		51	64.2	47.224384
52	37	50.779876		52	64.3	48.608784
53	37.5	58.155876		53	64.5	51.437584
54	37.5	58.155876		54	64.7	54.346384
55	38	66.031876		55	66.1	76.947984
56	38	66.031876		56	66.3	80.496784
57	38.5	74.407876		57	66.5	84.125584
58	39	83.283876		58	66.7	87.834384
59	39	83.283876		59	68.5	124.813584
60	40	102.535876		60	69.7	153.066384
61	41	123.787876		61	71.2	192.432384
62	41.5	135.163876		62	72.3	224.160784
63	42.3	154.405476		63	73.2	251.920384
64	43.5	185.667876		64	74.2	284.664384
65	44.5	213.919876		65	74.8	305.270784
Total	1941.8	4142.50554		Total	3726.3	4374.01016
Mean	29.874	64.727		Mean	57.328	68.344
		8.045				8.267

(b) Using the Excel Function CORREL(Hours, Yearly income) = 0.67

The sign is positive and 0.67 is close to 0.5, therefor there is a positive correlation, i.e as number of hours increase, the annual income also increases. Since the value is closer to 0.5, the strength is moderate.