in. the sample of 50 retirement funds was selected, and information on the funds and past performance history was recorded. For each of the 50 funds, data were collected on 13 variables as below.

PROVIDE DETAILS CLEARLY USING EXCEL

Fund Number	Market Cap (Large L/ Med-cap M/ Small S)	Type Growth G; Value V	Assets	Turnover Ratio	Beta	SD	Risk (Low L; Average A; High H)	1Yr Return%	3 Yr Return %	5 Yr Return %	10 Yr Return %	Expense Ratio	Star Rating
1	L	G	309.90	12.21	1.15	18.72	L	28.99	24.26	11.06	8.97	1.22	4
2	L	G	23.30	0.00	2.19	35.72	H	33.40	22.72	-4.89	0.02	1.90	2
3	L	G	141.50	147.00	2.24	36.69	H	33.98	21.91	1.53	12.55	1.92	2
4	L	G	118.50	5.00	2.24	36.63	H	33.78	21.89	1.57	12.69	1.73	2
5	L	G	575.30	121.00	0.89	14.56	L	21.62	16.47	9.40	10.30	1.41	5
6	L	G	1945.20	19.00	1.10	18.53	L	22.87	16.10	6.33	10.41	1.52	5
7	L	G	98.70	111.00	1.13	17.98	L	17.41	13.44	3.58	6.03	0.88	4
8	L	G	605.40	279.00	1.08	17.19	L	16.54	13.30	4.05	9.69	0.93	5
9	L	G	66.50	94.00	1.06	16.71	L	15.72	13.16	2.85	5.69	1.04	4
10	L	G	796.81	74.00	1.14	17.93	L	14.50	12.59	-0.33	6.77	0.81	3
11	L	G	470.20	36.00	1.09	17.88	L	17.14	12.45	4.70	9.88	1.27	5
12	L	G	15.10	58.00	1.15	18.60	L	15.23	12.39	5.85	7.44	1.18	5
13	L	G	1132.70	229.00	1.10	17.38	L	18.62	12.16	2.51	8.73	0.90	4
14	L	G	4758.60	169.00	1.04	16.78	L	22.45	12.15	2.74	9.53	0.96	5
15	L	G	2005.70	75.00	1.04	16.22	L	15.66	12.14	3.40	9.44	1.00	4
16	L	G	56.50	586.00	1.13	18.24	L	13.75	11.97	4.75	3.99	1.51	4
17	L	G	87.50	73.00	0.98	15.26	L	14.62	11.84	1.75	8.46	0.91	4
18	L	G	469.10	19.62	1.31	20.93	A	26.06	11.80	6.37	7.41	0.94	4
19	L	G	46.60	5.00	1.10	17.87	L	15.72	11.44	3.93	9.37	1.73	4
20	L	G	467.40	22.00	1.19	18.70	L	11.86	11.29	4.34	8.62	0.93	4
21	L	G	125.80	15.00	1.05	17.05	L	18.64	11.21	1.59	4.80	1.55	3
22	L	G	958.30	55.00	1.14	18.41	L	15.71	11.13	-0.12	10.32	1.00	4
23	L	G	11305.10	95.00	1.16	18.40	L	17.77	11.07	4.05	7.10	0.90	4
24	L	G	138.30	32.00	1.10	18.62	L	15.36	11.07	1.96	8.11	0.96	3
25	L	G	484.10	13.00	1.15	18.09	L	17.33	11.04	3.54	6.31	0.59	4
26	L	G	2.30	15.00	1.08	16.84	L	21.08	10.90	3.79	6.81	0.63	4
27	L	G	44.90	19.00	0.92	14.42	L	18.12	10.86	2.85	1.38	1.10	4
28	L	G	46.80	102.20	1.07	19.30	A	9.16	10.83	0.48	1.76	1.71	3
29	L	G	280.40	27.99	1.13	17.83	L	22.02	10.68	9.03	8.99	1.16	5
30	L	G	1310.60	64.00	1.08	16.94	L	16.63	10.67	1.52	8.60	0.88	4
31	L	G	123.80	11.00	1.11	17.59	L	13.08	10.62	1.93	7.97	1.47	3
32	L	G	358.20	46.00	1.07	17.21	L	12.49	10.56	2.44	7.48	1.32	4
33	L	G	533.80	51.17	1.11	17.14	L	17.72	10.54	0.89	6.11	1.03	3
34	L	G	163.40	79.00	1.01	15.70	L	13.42	10.52	2.80	3.06	0.47	4
35	L	G	6190.00	13.00	1.10	17.40	L	14.18	10.43	1.21	5.94	0.99	3
36	L	G	13.30	4.00	0.97	16.74	L	16.99	10.35	-0.42	3.80	4.16	2
37	L	G	101.00	38.00	1.10	17.28	L	13.12	10.11	0.72	6.36	1.41	3
38	L	G	1841.30	17.00	1.09	17.32	L	14.74	10.07	1.63	5.76	1.00	3
39	L	G	2437.10	21.00	1.14	17.63	L	17.93	10.06	0.04	6.81	0.73	3
40	L	G	71.70	3.58	0.99	15.35	L	12.27	10.02	2.42	6.42	1.00	4
41	L	G	5287.50	149.00	1.24	20.15	A	11.29	10.01	3.13	9.93	0.91	4
42	L	G	19.40	56.00	0.96	15.82	L	10.75	10.00	-1.50	-1.50	1.96	4
43	L	G	613.40	65.00	1.13	17.92	L	12.57	9.95	-0.34	6.79	1.33	3
44	L	G	104.60	185.00	1.13	17.62	L	17.67	9.92	1.59	6.68	1.30	3
45	L	G	170.90	99.00	1.25	19.80	A	13.60	9.92	12.33	10.25	1.55	5
46	L	G	66.60	11.00	0.96	15.24	L	12.18	9.75	3.68	6.86	1.34	4
47	L	G	4.20	23.00	1.10	17.48	L	18.31	9.74	1.60	5.43	1.51	3
48	L	G	5.70	81.00	1.06	16.80	L	17.07	9.69	1.99	8.09	1.11	4
49	L	G	22.40	52.56	1.10	17.50	L	14.88	9.67	2.49	7.71	1.63	4
50	L	G	66.50	83.00	1.14	17.93	L	15.13	9.65	0.12	7.07	1.06	3

PROVIDE DETAILS CLEARLY USING EXCEL

Q1a) Properly selected graphical displays, visualizing all these numbers and infer

Q1b) Infer based on the contingency table using fund type and risk

Q1c) What conclusion could you make after constructing histogram and frequency polygons of the one-year return with other growth funds?

Q1d) Analyze the differences in return in the past 3 years, in the past 5 years and the past 10 years to see how the growth funds, the value funds and the small, mid-cap and large market cap funds performed. (use descriptive statistics)

Q1e) Construct a correlation table among numerical variables and conclude.

Q1f) Fit a simple linear regression model, suitably defining the dependent and independent variable.

Q2) Based on the above analysis, write a managerial report.