The Wall Street Journal CEO Compensation Study analyzed CEO pay from many U.S. companies

Question: The Wall Street Journal CEO Compensation Study analyzed CEO pay from many U.S. companies with fiscal year 2008 revenue of at least \$5 billion that filed their proxy statements between October 2008 and March 2009. The data are in the file P02_30.xlsx. (Note: This data set is a somewhat different CEO compensation data set from the one used as an example in the next chapter.)

a. Create histograms to gain a clearer understanding of the distributions of annual base salaries and bonuses earned by the surveyed CEOs in fiscal 2008. How would you characterize these histograms?

b. Find the annual salary below which 75% of all given CEO salaries fall.

c. Find the annual bonus above which 55% of all given CEO bonuses fall.

d. Determine the range of the middle 50% of all given total direct compensation figures. For the 50% of the executives that do not fall into this middle 50% range, is there more variability in total direct compensation to the right than to the left? Explain.

Solution:

a. The Histograms for Salary and Bonus of CEO’s from U.S. Companies are as under.

For creating the histogram of the data first create a table of counts for each of the Variable.

The Histograms for all numerical variables are as under.

For creating the each of the histograms of the data first create a table of counts for each of the Variable and follow the steps given below. This procedure can be followed for other histograms given below.

1. Select the range of the”Mid Point” and “count” from the table.

2. Now go to “insert” and “column chart” click on 2-D chart.

3. The chart will appear then one should right click on the bars.

4. Go to “format data series” and click on “secondary data” and remove the other one by pressing delete.

Here the outputs are given below and for all the histograms follow the above procedure.

Salary

The Histogram for the above data is as follows:

It is clearly visible in the graph that most of the salary values lie within a range of 0 to 20 lacs whereas the graph is not properly portrayed because of presence of some extreme values. The extreme value of the salary of a CEO that is around 81, 00,000 lakh has caused the skewness in the graph.

Bonus

The Histogram for the above data is as follows:

The graph of bonus earned by the CEO’s is skewed to the right again because of the presence of extreme values.

b. For calculating the annual salary below which 75% of all given CEO salaries fall calculate P75 of the salary data. Use the formula that is “=PERCENTILE(D2:D382,H8)”.

Therefore, the annual salary below which 75% of all given CEO salaries fall is \$12,53,700.

c. For calculating the annual bonus above which 55% of all given CEO bonuses fall calculate P55 of the bonus data. Use the formula that is given below.

“=PERCENTILE(E2:E382,H12)”.

Therefore, the annual bonus above which 55% of all given CEO bonuses fall is \$13,69,500.

d. Here first of all to find the range of middle 50% of all total direct compensation we calculate the 50th percentile that is by using Excel function which is given below.

The formula used is “=PERCENTILE(D2:D382,0.5)’. The 50th percentile is as follows.

Now to see whether the 50% execute fall or don’t fall into this middle 50% range is calculated by using the Excel function as follows.

1. First of all enter the function that is “=IF(D2<=\$K\$3,”lie”,”don’t lie”)” in first cell of column F .

2. Now drag the first cell up to last salary column and by using sort function here we sought the value that will show “don’t lie” value above and then “lie” values in the entire column and the entire function will be automatically applied to whole result column which is given below.

Hence by selecting the don’t lie cell’s and looking at the count below it can be seen that there are 190 executives and the lie’s cells are total 191 executives that is it can conclude that the 50% executives doesn’t fall under the range of middle 50% of the total compensation figures and also it can be seen that there is more variability to the right side than to left as from the total executives that is from the 381 executives 190 executives are from the left side and 191 are from the right side so it can easily conclude that there is more variability to the right side of the total compensation figures.