I did questions 1 to 4 but I was wondering how to do questions 5, 6 and 7. Thank you so much for the help. I also attached a picture of the Multiple Regression analysis on excel.
An investment firm thoroughly analyzed all client data to discern specific patterns. Utilizing insights from historical data, the firm pinpointed seven security holdings that demonstrate a robust correlation with individual net worth. The accompanying data file encompasses very recent information. It includes clients' age, gender, contributions to each of the seven securities, job sector, and overall net worth. The data has been normalized, ensuring that no data point exceeds a value of 10, thereby safeguarding highly confidential information. Only age, gender, and sector remain unaltered. The sectors delineate the types of occupations clients engage in, with Sector A representing professional services, Sector B encompassing government employees, Sector C comprising banking and finance professionals, and Sector D categorizing other occupations. Leveraging the knowledge gained in Business Analytics, respond to the following questions:
1. Calculate the mean, median, and mode for each security.
Security 1:
Mean: 4.00095
Median: 4.22
Mode: 0
Security 2 :
Mean: 6.02281
Median: 6.49
Mode: 0
Security 3 :
Mean: 4.4
Median: 4.1715
Mode: 0
Security 4 :
Mean: 4.06008
Median:4.14
Mode: 0
Security 5 :
Mean: 1.7706
Median: 1.7
Mode: 0
Security 6 :
Mean: 5.17318
Median: 5.615
Mode: 0
Security 7 :
Mean: 5.92769
Median: 6.31
Mode: 0
2. Calculate the Interquartile Range (IQR) for each security.
Security 1: 2.2725
Security 2: 1.58
Security 3: 2.0525
Security 4: 3.652
Security 5: 0.6525
Security 6: 3.37
Security 7: 2.2425
3. Calculate the variance for each security and comment on the dispersion and outliers of each security.
Security 1: 3.813695893
Security 2: 4.138081696
Security 3: 3.167364114
Security 4: 5.350199994
Security 5: 1.416811114
Security 6: 6.461074963
Security 7: 4.772811976
4. For the third security, how many outliers can you identify?
(upper fence) = Q3 + (1.5*IQR) = 8.52125 -> 3 outliers on the top
(lower fence) = Q1 - (1.5*IQR) = 0.31125 -> 89 outliers on the bottom
TOTAL OUTLIERS FOR SECURITY 3 = 92
5. Utilizing the provided data, assess the overall significance of the multiple regression model, where the independent variables comprise the seven securities, and the dependent variable is the net worth. Employ a significance level of alpha = 0.01.
6. The firm is considering excluding one of the securities as a determinant for long-term net worth and replacing it with the client's salary. Which security would you recommend eliminating, and what is the rationale behind your suggestion?
7. The firm has decided to no longer prioritize securities as criteria for long-term net worth and intends to replace them with the client's salary and other factors. Nevertheless, they want to retain one security. Which security would you recommend keeping, and what is the rationale behind your suggestion?
SUMMARY OUTPUT
Regression Statistics Multiple R 0.592091663 R Square 0.350572537 Adjusted R Square 0.345989884 Standard Error 0.98052605 Observations 1000
ANOVA
af
Ss 514.8458141 953.7398839 1468.585698
MS 73.54940201 0.961431335
F 76.49990111
Significance F 1.26517E-88
Regression Residual Total
7 992 666
Coefficients 2.49769166 0.098723982 0.144905208 0.210800309 0.083161041 -0.033783747 0.026915512 0.096104466
Standard Error 0.158628482 0.017505971 0.016328247 0.01898651 0.015996422 0.026245588 0.013607852 0.016390921
t Stat 15.74554348 5.639446295 8.874511067 11.10263623 5.198727721 1.287216274 -1.977939743 5.863274424
P-value 4.9836E-50 2.22398E-08 3.22136E-18 4.44604E-27 2.4363E-07 0.198319169 0.048211889] 6.17503E-09
Lower 95% 2.186405748 0.064370996 0.112863338 0.173541975 0.051770331 0.085286993 -0.053618993 0.063939607
Upper 95% Lower 99.99% 2.808977571 1.878014059 0.133076969 0.030337411 0.176947078 0.081119381 0.248058642 0.136630055 0.114551752 0.02067148 0.017719498 -0.13631138 0.000212031 0.080074195 0.128269325 0.032073806
Upper 99.99% 3.11736926 0.167110554 0.208691034 0.284970562 0.145650603 0.068743885 0.026243172 0.160135126
Intercept Security 1 Security 2 Security 3 Security 4 Security 5 Security 6 Security 7
