- Required R coding or Source code of Rfile for this CEO Compensation and Company performance assignment.
- Need an R studio R coding
Case Study CEO Compensation and Company Performance Compensation for CEO has always been an area of debate in policy circles and management decision making. Higher compensation can be justified if highly paid CEOs in the long term are able to drive better Returns to Shareholder (as measured by the ROI on the stock prices). In this assignment, we examine this linkage between compensation and company performance statistically using the tools of descriptive statistics and inference t-tests. CEO compensation is generally benchmarked to the peers within the same industry group. In this exercise we ignore the industry difference and market conditions across industries and solely study the compensation and company performance as indicated by the return on the stock. We also study the issue of outliers. The data in sample1.xls contains the data on compensation and returns on the stock for 194 companies - 1 yr , 2yr, 3 yr and five year. In the second file Sample2.xls, the compensation outliers data records have been removed on both sides of tails so that we are left with 150 Companies/CEO which are then grouped into six groups - Group 1 has the higher average compensation then Group 2 and so on. QUESTIONS TO BE ANSWERED Question-1: From the sample 1 file, examine if there is linkage between compensation and performance. Does the relationship between compensation and performance improve as longer periods are considered? Study correlations and variations. Compare variability of 1 year return with the 3 year and 5 year return. (Hint: use Coefficient of variation) Question-2: Study the same relationships between compensation and performance for sample 2 file also for each of the groups. Does the relationship metrics improve? Can you conclusively comment on compensation and performance as you study this relationship across multiple levels? Question-3: You want to establish confidence levels (90%) on compensation assuming the normal distribution for compensation as well as the expected returns - Which test is more suitable Z or t-test? Establish these levels for each of the groups in sample2 file. Question-4: Once you have determined compensation, you now want to establish benchmark on desired return. If you represent the recruitment team, you want to pay the prospective CEO towards the lower side of the band while negotiating for the highest ROI (higher Confidence limit of Return). Compute these benchmarks at 90% confidence level. Compute these benchmarks compensation confidence intervals for 95% confidence levels also. Which one would you prefer for your negotiations - higher confidence or lower confidence? Question-5: Considering the Groups in sample2 file, you are wondering if the mean compensation is the same in lower groups and whether they can be merged. ANOVA helps us to study variations within the group and between the groups but so far you only know t-test as ANOVA is part of your learning for next residency on Advance Statistics. In the meantime, you can still use t-test (two sample test for different pairs to see if the a) Mean compensation is same across groups b) Mean of 5 year and 3 year return is the same across groups. Identify the groups that can be merged if at all. Question-6: After merging the groups, re-estimate confidence intervals of compensation, 5 year return and 3 year return and prepare final recommendations on benchmark compensation range and return expectation that needs to be negotiated with the prospective CEO. Relationship between compensation and performance To study the relation between compensation and performance we first calculate the correlation matrix and see how better they related to one another. Table-1 : Correlation Matrix displaying the relation between variables (how well each variable is related to other variable) Compen Y1Return Y2Return Y3Return Y5Return Compen 1 0.15 0.26 0.31 0.37 Y1Return 0.15 1 0.78 0.86 0.75 Y2Return 0.26 0.78 1 0.85 0.88 Y3Return 0.31 0.86 0.85 1 0.96 Y5Return 0.37 0.75 0.88 0.96 1 Upon analysing the P-Values between variables while determining correlation, we can see that the relation between all these variables is significant as P-values are less than the significant level which is 0.05. As the relation between variables is significant, we can draw some inferences from the above correlation matrix. As we know that correlation value ranges between -1 to +1, of which -1 being highly negatively correlated; +1 being highly positively correlated and 0 being no association between variables. Inferences: We can conclude that: S.No 1 2 3 4 5 6 7 8 9 10 Inference Y3Return and Y5Return variables are significantly correlated with a correlation coefficient of 0.96 and P-value approximately equal to 0. Y2Return and Y5Return variables are significantly correlated with a correlation coefficient of 0.88 and P-value approximately equal to 0. Y2Return and Y3Return variables are significantly correlated with a correlation coefficient of 0.85 and P-value approximately equal to 0. Y1Return and Y5Return variables are significantly correlated with a correlation coefficient of 0.75 and P-value approximately equal to 0. Y1Return and Y3Return variables are significantly correlated with a correlation coefficient of 0.86 and P-value approximately equal to 0. Y1Return and Y2Return variables are significantly correlated with a correlation coefficient of 0.78 and P-value approximately equal to 0. Compensation and Y1Return variables have very weak relationship with a correlation coefficient of 0.15 and P-value approximately equal to 0.04. Compensation and Y2Return variables have very weak relationship with a correlation coefficient of 0.26 and P-value approximately equal to 0.0003. Compensation and Y3Return variables have weak relationship with a correlation coefficient of 0.31 and P-value approximately equal to 0.00. Compensation and Y5Return variables have weak to moderate relationship with a correlation coefficient of 0.37 and P-value approximately equal to 0.00. From above inferences (point number 7 to point number 10) We can conclude that the relationship between compensation and performance improves as longer periods are considered as Correlation Coefficient value has increased. Now that we saw there is a relation between these variables, we tried building a regression model to identify the predictor and outcome variables. There are three main values that we need to look at before coming to conclusion. Before looking at these values, we should have a look at the predictor and outcome variables and see whether they are statistically significant or not. If they are statistically significant, then we look at the below values. 1. Average variations in the observations around the regression line - [Lower the variation in the observations, better the model]. 2. R-Square or Adjusted R-Square value that ranges between 0 and 1. Represents the variation in the data which is explained by the model [Higher the R-Square value, better is the model]. 3. F-Statistic Value, which gives the overall significance of the model. [Higher the F stat value and lower the P-value, better the model]. Let's see the R-Square value and the model corresponding to variables. The regression line equation that is estimated across variables is as follows: Variables Y1Return Y2Return Y3Return Y5Return Regression Equation Compensation Intercept 0.13 3.42 0.25 14.45 0.36 4.88 0.48 27.37 Equation 0.13 * Compen + 3.42 0.25 * Compen + 14.45 0.36 * Compen + 4.88 0.48 * Compen + 27.37 Substituting the compensation value in the above regression equation, respective variables follow that pattern. Variables Y1Return Y2Return Y3Return Y5Return Compensation 2.04 3.66 4.45 5.44 t-value Statistic Intercept 1.51 6.09 1.75 8.81 Significant (Y |N) N Y N Y Higher the t-statistic, more significant is the variable (predictor and outcome). Based on the above table we can see that Y2Return and Y3Return with respect to compensation are significant. We even studied variability in the data using coefficient of variation concept. It helps us to compare the degree of variability between variables or datasets. Variable Y1Return Y2Return Y3Return Y5Return Mean 6.05 19.38 11.95 36.98 Standard Deviation 26.21 28.07 33.54 38.17 Coefficient of Variation 433.12 144.81 280.78 103.21 Interpretation: We can say that Y1Retun is more variable compared to other variables even if its Standard deviation is lower. RSE Y1Return Y2Return Y3Return Y5Return 26 27.21 32.02 35.62 R-Squared F-Statistic Compensation 0.016 4.163 0.06 13.37 0.088 19.82 0.129 29.6 p-Value 0.042 0.0003 ~= 0 ~= 0 Based on the above Y5Retun to Compensation has the highest R-Squared value and FStatistic Value. Also other variables are significant as well but not as compared to Y5 return to Compensation. Individual Groups Relationship between compensation and performance Compen Y1Return Y2Return Y3Return Y5Return Compen Y1Return Y2Return Y3Return Y5Return Compen Y1Return Y2Return Y3Return Y5Return Compen Y1Return Y2Return Y3Return Y5Return Compen 1.00 0.4 0.09 0.35 0.37 GROUP - "G1" Y1Return Y2Return 0.4 0.09 1.00 0.88 0.88 1.00 0.68 0.72 0.63 0.67 Y3Return 0.35 0.68 0.72 1.00 0.99 Y5Return 0.37 0.63 0.67 0.99 1.00 Compen 1.00 -0.17 -0.17 -0.17 -0.17 GROUP - "G2" Y1Return Y2Return -0.17 -0.17 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 Y3Return -0.17 1.00 1.00 1.00 1.00 Y5Return -0.17 1.00 1.00 1.00 1.00 Compen 1.00 0.16 0.27 0.16 0.16 GROUP - "G3" Y1Return Y2Return 0.16 0.27 1.00 0.79 0.79 1.00 1.00 0.79 1.00 0.79 Y3Return 0.16 1.00 0.79 1.00 1.00 Y5Return 0.16 1.00 0.79 1.00 1.00 Compen 1.00 -0.17 -0.15 -0.17 -0.17 GROUP - "G4" Y1Return Y2Return -0.17 -0.15 1.00 0.88 0.94 1.00 1.00 0.94 1.00 0.94 Y3Return -0.17 1.00 0.94 1.00 1.00 Y5Return -0.17 1.00 0.94 1.00 1.00 Compen Y1Return Y2Return Y3Return Y5Return Compen Y1Return Y2Return Y3Return Y5Return Compen 1.00 0.10 0.30 0.10 0.10 GROUP - "G5" Y1Return Y2Return 0.10 0.30 1.00 0.91 0.91 1.00 1.00 0.91 1.00 0.91 Y3Return 0.10 1.00 0.91 1.00 1.00 Y5Return 0.10 1.00 0.91 1.00 1.00 Compen 1 0.03 0.03 0.05 0.24 GROUP - "G6" Y1Return Y2Return 0.03 0.03 1 1.00 1.00 1.00 1.00 1.00 0.96 0.96 Y3Return 0.05 1.00 1.00 1.00 0.97 Y5Return 0.24 0.96 0.96 0.97 1 Confidence Levels on Compensation and justification on test used In this case, we have considered samples and not population. So, we don't have population variance. Also, a) We are considering individual groups to calculate confidence levels. b) We don't have population variance and c) Sample size is less than 30. Based on above conditions, we use t-test. Group-1: (90% confidence Interval) Column Compensation Lower Limit 31.06 Upper Limit 40.85 Lower Limit 30.05 Upper Limit 41.87 Lower Limit 19.86 Upper Limit 20.89 Lower Limit 19.75 Upper Limit 20.99 Lower Limit 16.71 Upper Limit 17.26 Group-1: (95% confidence Interval) Column Compensation Group-2: (90% confidence Interval) Column Compensation Group-2: (95% confidence Interval) Column Compensation Group-3: (90% confidence Interval) Column Compensation Group-3: (95% confidence Interval) Column Compensation Lower Limit 16.66 Upper Limit 17.31 Lower Limit 14.73 Upper Limit 15.09 Lower Limit 14.69 Upper Limit 15.13 Lower Limit 13.44 Upper Limit 13.65 Lower Limit 13.42 Upper Limit 13.67 Lower Limit 12.44 Upper Limit 12.65 Lower Limit 12.42 Upper Limit 12.67 Group-4: (90% confidence Interval) Column Compensation Group-4: (95% confidence Interval) Column Compensation Group-5: (90% confidence Interval) Column Compensation Group-5: (95% confidence Interval) Column Compensation Group-6: (90% confidence Interval) Column Compensation Group-6: (95% confidence Interval) Column Compensation Confidence Levels on Desired Return Now that we have confidence limits with respect to compensation, we will build these confidence limits on Returns generated. Once we do this, we will compare the limits from compensation and returns. Column\\Confidence Interval Y1Return Y2Return Y3Return Y5Return GROUP : \"G1\" 90% Lower Limit Upper Limit 3.55 19.57 42.97 57.75 43.45 66.00 82.65 106.96 Lower Limit 1.90 41.45 41.13 80.14 Column\\Confidence Interval Y1Return Y2Return Y3Return Y5Return GROUP : \"G2\" 90% Lower Limit Upper Limit 10.13 26.99 25.13 41.99 11.14 29.69 41.14 59.69 Lower Limit 8.39 23.39 9.23 39.23 Column\\Confidence Interval Y1Return Y2Return Y3Return Y5Return GROUP : \"G3\" 90% Lower Limit Upper Limit -7.93 8.01 16.78 28.98 -8.72 8.81 21.28 38.81 Lower Limit -9.57 15.52 -10.53 19.47 Column\\Confidence Interval Y1Return Y2Return Y3Return Y5Return GROUP : \"G4\" 90% Lower Limit Upper Limit -10.46 4.70 6.48 20.80 -11.51 5.17 18.49 35.17 Lower Limit -12.02 5.01 -13.23 16.77 Column\\Confidence GROUP : \"G5\" 90% 95% Upper Limit 21.22 59.27 68.33 109.47 95% Upper Limit 28.73 43.73 31.60 61.60 95% Upper Limit 9.65 30.24 10.61 40.61 95% Upper Limit 6.26 22.27 6.89 36.89 95% Interval Y1Return Y2Return Y3Return Y5Return Column\\Confidence Interval Y1Return Y2Return Y3Return Y5Return Lower Limit -9.97 4.52 -10.97 19.03 Upper Limit 8.29 22.20 9.12 39.12 Lower Limit -11.86 2.69 -13.04 16.96 GROUP : \"G6\" 90% Lower Limit Upper Limit -6.46 15.02 3.54 25.02 -6.07 15.35 8.00 29.28 Lower Limit -8.67 1.33 -8.28 5.81 Upper Limit 10.18 24.03 11.19 41.19 95% Upper Limit 17.23 27.23 17.57 31.48 Mean compensation test across groups Before doing this test of means comparison, we first need to check whether the variances are equal across groups for Compensation. Variance in Compensation across GROUPS G1 G2 G4 G5 G6 G3 G4 G5 G6 G3 G4 G5 G6 - UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L - EQUAL EQUAL - UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L - EQUAL EQUAL - UNEQUA L UNEQUA L UNEQUA L UNEQUA L Now, we use above information about variance in t-test (test for means). Means comparison test in Compensation across GROUPS G1 G2 G2 UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L G3 G1 G1 G2 G3 G4 G5 G6 - UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L UNEQUA L - Mean returns test across groups 3 Year Return Test of means across groups: Before doing this test of means comparison, we first need to check whether the variances are equal across groups for Y3Return (3 year Return). Variance in Y3Return across GROUPS G1 G2 G3 G4 G5 G6 G1 G2 G3 G4 G5 G6 EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL - Now, we use above information about variance in t-test (test for means). Means comparison test in Y3Return across GROUPS G1 (P-Value) G2 (P-Value) G3 G1 G2 G3 G4 G5 G6 - UNEQUAL UNEQUAL UNEQUAL UNEQUAL UNEQUAL UNEQUAL - UNEQUAL UNEQUAL UNEQUAL EQUAL UNEQUAL UNEQUAL - EQUAL EQUAL EQUAL (P-Value) G4 (P-Value) G5 (P-Value) G6 (P-Value) UNEQUAL UNEQUAL UNEQUAL UNEQUAL UNEQUAL EQUAL (0.063) EQUAL (0.65) EQUAL (0.90) EQUAL (0.57) EQUAL (0.77) EQUAL (0.33) EQUAL EQUAL - EQUAL EQUAL (0.52) - Inference: Based on above table for comparison of means across groups for 3 year returns, we can say that Mean of G3 group's 3 year return is approximately equal to that of G5 group's 3 year return. Before merging these groups, we will have to do a check on 5 Year returns, if the group's average is approximately equal or not. 5 Year Return Test of means across groups: Before doing this test of means comparison on 5 year returns, we first need to check whether the variances are equal across groups for Y5Return (5 year Return). Variance in Y5Return across GROUPS G1 G2 G3 G4 G5 G6 G1 G2 G3 G4 G5 G6 EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL EQUAL - Now, we use above information about variance in t-test (test for means). Means comparison test in Y3Return across GROUPS G1 (P-Value) G2 (P-Value) G3 G1 G2 G3 G4 G5 G6 - UNEQUAL UNEQUAL UNEQUAL UNEQUAL UNEQUAL UNEQUAL - UNEQUAL UNEQUAL UNEQUAL UNEQUAL UNEQUAL UNEQUAL - EQUAL EQUAL EQUAL (P-Value) G4 (P-Value) G5 (P-Value) G6 (P-Value) UNEQUAL UNEQUAL UNEQUAL UNEQUAL UNEQUAL UNEQUAL EQUAL (0.65) EQUAL (0.90) EQUAL (0.16) EQUAL (0.77) EQUAL (0.30) EQUAL EQUAL - EQUAL EQUAL (0.22) - Inference: Based on above table for comparison of means across groups for 5 year returns, we can say that Mean of G3 group's 5 year return is approximately equal to that of G5 group's 5 year return which in turn is approximately equal to that of G4 groups 3 Year and 5 Year returns. Decision on merging Groups: For now based on the above test of means on Y3 Returns and Y5 returns, we can go ahead and merge Groups G3, G4 and G5 as the means of these groups are approximately equal to each other. Confidence Limits after Merging Groups: Column\\Confidence Interval Compensation Compensation Compensation Compensation Confidence Limits - COMPENSATION 90% 95% Lower Limit Upper Limit Lower Limit Upper Limit GROUP : \"G1\" 31.06 40.85 30.05 41.87 GROUP : \"G2\" 19.86 20.89 19.75 20.99 GROUP : \"G3\"|\"G4\"|\"G5\" 14.85 15.44 14.79 15.50 GROUP : \"G6\" 12.44 12.65 12.42 12.67 Column\\Confidence Interval Y1Return Y2Return Y3Return Y5Return GROUP : \"G1\" 90% Lower Limit Upper Limit 3.55 19.57 42.97 57.75 43.45 66.00 82.65 106.96 Column\\Confidence Interval Y1Return GROUP : \"G2\" 90% Lower Limit Upper Limit 10.13 26.99 95% Lower Limit 1.90 41.45 41.13 80.14 Upper Limit 21.22 59.27 68.33 109.47 95% Lower Limit 8.39 Upper Limit 28.73 Y2Return Y3Return Y5Return 25.13 11.14 41.14 41.99 29.69 59.69 23.39 9.23 39.23 43.73 31.60 61.60 Column\\Confidence Interval Y1Return Y2Return Y3Return Y5Return GROUP : \"G3\"|\"G4\"|\"G5\" 90% 95% Lower Limit Upper Limit Lower Limit Upper Limit -5.80 3.35 -6.71 4.25 12.41 20.85 11.58 21.67 -6.39 3.69 -7.38 4.68 23.61 33.69 22.62 34.68 Column\\Confidence Interval Y1Return Y2Return Y3Return Y5Return GROUP : \"G6\" 90% Lower Limit Upper Limit -6.46 15.02 3.54 25.02 -6.07 15.35 8.00 29.28 95% Lower Limit -8.67 1.33 -8.28 5.81 Upper Limit 17.23 27.23 17.57 31.48
