Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
S&P1500 CEO annual compensation data: summarizing data
The data file CEOCompensation Data.xlsx contains data on the 2022 total annual compensation of CEOs of S&P1500 companies, as reported in SEC filings. The compensation is recorded in million of US dollars, rounded to the nearest million. The following questions will help guide your analysis of the CEO compensation dataset.
1. CEO Data (1 point)
Is the average CEO compensation smaller than, equal to, or larger than the median CEO compensation?
⃝ The average CEO compensation is smaller than the median CEO compensation
⃝ The average CEO compensation is equal to the median CEO compensation
⃝ The average CEO compensation is larger than the median CEO compensation
2. CEO Data (1 point)
What is the standard deviation of the CEO compensation (in million of US dollars)?
3. CEO Data (1 point)
Which CEO has the highest compensation?
⃝ Andrew R. Jassy (AMAZON.COM INC)
⃝ Robert A. Chapek (DISNEY CO)
⃝ G. Michael Sievert (T-MOBILE US INC)
⃝ Lachlan Keith Murdoch (FOX CORP)
⃝ Sundar Pichai (ALPHABET INC)
⃝ Timothy D. Cook (APPLE INC)
4. CEO Data (1 point)
What is the proportion of CEOs whose annual compensation is $5M? (Hint. First compute how many
CEOs in the dataset have annual compensation equal to $5M.)
⃝ Less than or equal to 0.15 (15%)
⃝ Greater than 0.15 but less than or equal to 0.30 (15% – 30%)
⃝ Greater than 0.30 but less than or equal to 0.70 (30% – 70%)
⃝ Greater than 0.70 but less than or equal to 0.85 (70% – 85%)
⃝ Greater than 0.85 (85%)
5. CEO Data (1 point)
What is the proportion of CEOs whose compensation is less than or equal to $5 million?
S&P1500 CEO annual compensation data: from data to a random variable
A succinct way to represent a large dataset is with a frequency table, which reports the frequencies of each value in the dataset. The following instructions will help you build a frequency table for the 2022 CEO compensation data in the file CEOCompensation Data.xlsx and connect it with probability distributions.
To create the frequency table, please follow the following steps.
(i) Identify the range of CEO compensation.
Note that the CEO compensation values are integers ranging from 0,1,. . . , 226, and enter these values in a new worksheet in, say, column A. (There are multiple ways to do this. For instance, you could type in 0 in cell A2, type 1 in cell A3, highlight both cells A2 and A3 and then drag the bottom right corner until row 228.)
(ii) Use the COUNTIF function to calculate the frequency of each value.
In cell B2 write =COUNTIF(CEOData!C$2:C$1412,A2) and then double-click on the bottom right corner to compute the frequency for the rest of the values. You can check that all data entries are properly accounted for by computing the sum of all of the frequencies and making sure that it equals 1411. To do this you can type =SUM(B2:B228) in a new cell and observe whether it matches the number of data entries.
(iii) Compute the proportion of each unique value.
To obtain the proportion of data entries for each unique value, note that there are 1411 entries in the dataset and write =B2/1411 in cell C2. You can then double-click on the bottom right corner to compute the proportion for the rest of the values. Calculate =SUM(C2:C228) in a new cell to check that all of the proportions add up to 1.
(iv) Visualize the data.
To visualize the data, you can now add a chart by selecting columns A and C (or just selecting cells A2:A228 and C2:C228), and then choosing from the Column Chart icon on the Insert ribbon.
A top business school will invite one of the S&P 1500 CEOs as next graduation speaker. Since the identity of that CEO is not known, their annual compensation is a random variable which we denote with
X:
X = Annual compensation of the graduation speaker CEO.
Note that the distribution of the random variable X is derived from the frequency table constructed using the CEO compensation data found in CEOCompensation Data.xlsx.
6. CEO Data (2 points)
Calculate the standard deviation of the random variable X (in million of US dollars).
7. CEO Data (2 points)
What is P(X > 5)?
S&P1500 CEO annual compensation data: sum of random variables
Random variables allow for the easy computation of summary measures of linear transformations and sums.
For instance, if X is a given random variable and a is a multiplicative scaling factor, then the quantity aX is a random variable that transforms X by multiplying each of its outcomes by a. The scaling factor a affects both the expected value and the variance of aX, and one has that
E[aX] = aE[X] and Var[aX] = a
2Var[X].
(1)
Instead, if c is an additive (shift) constant, then the quantity X + c is a random variable that transforms X by shifting each of its outcomes by c. In this case, the additive constat c affects only the expected value of
X + c:
E[X + c] = E[X] + c
and Var[X + c] = Var[X].
(2)
By combining (1) and (3), we also have that the random variable aX + c has expected value and variance respectively given by
E[aX + c] = aE[X] + c
and Var[aX + c] = a
2Var[X].
(3)
If X and Y are two given random variables, a, b are two multiplicative scaling factors, and c is an additive (shift) constant, then the expected value of aX + bY + c is given by E[aX + bY + c] = aE[X] + bE[Y ] + c.
Furthermore, if the random variables X and Y are also independent, then
Var[aX + bY + c] = a
2Var[X] + b
2Var[Y ].
8. CEO Data (0 points)
Four business schools independently plan to invite a S&P1500 CEO to be their graduation speaker. What is the expected value of the average of the annual compensations of the CEOs speaking at graduation at these four schools?
(Hint. Let X1 be the random variable that denotes the compensation of the CEO speaking at the first school, X2 be the random variable that denotes the compensation of the CEO speaking at the second school, and X3 and X4, respectively be the random variables of the CEOs speaking at the third and fourth school. The question is asking to consider the random variable
X =
X1 + X2 + X3 + X4
4,
which represents the average compensation of four CEO graduation speakers, and compute its expected value.)
9. CEO Data (0 points)
What is the standard deviation of that average? (Hint. What is the standard deviation of the random variable X defined earlier?)
Managing demand uncertainty for a new product
The weekly demand for a new product is uncertain, but it is considered to be adequately described by a normal random variable with mean 500 units and variance 10,000.
10. Demand for a new product (1 point)
What is the standard deviation of the demand?
11. Demand for a new product (2 points)
What is the probability that the weekly demand for the new product is between 300 and 700?
12. Demand for a new product (2 points)
What is the probability that the weekly demand for the new product is between 300 and 600?
13. Demand for a new product (2 points)
What is the probability that the weekly demand for the new product is at least 300?
14. Demand for a new product (1 point)
The accounting department has indicated that there is a 10% chance that the new product will not
generate enough weekly sales to make a profit. What is the break-even sales level per week? (Hint. You
may want to look up the Excel function NORM.INV.)
⃝ Less than 350 units
⃝ At least 350 but less than 450 units
⃝ At least 450 but less than 550 units
⃝ At least 550 but less than 650 units
⃝ At least 650 units
Sampling New York City yellow cab data
Open the data file YellowCab TripData Sample.xlsx to obtain a sample of n = 100 New York City Yellow cab trip records. Your sample is selected at random from the dataset of all New York City Yellow cab trip annual records maintained by the New York City Taxi & Limousine Commission.
To visualize your sample, enter your unique sample code in cell D1 of the worksheet
Sample. Your unique sample code is listed among your Canvas grades for this course.
15. Sampling NYC yellow cab data (1 point)
What is your unique sample code? (Hint. Your unique sample code is listed among your Canvas grades for this course.)
16. Sampling NYC yellow cab data (2 points)
What is the average price of a yellow cab ride in your sample?