Question

Using the data below test the hypothesis that on average a Fortune 500 CEO makes $11 million.

Test it using the significance level 0.01.

Use this formula PAY= β0+β1REVENUE+β2EMPLOYEES+β3PROFITS+ε to have a linear relationship between dependent variables and independent variables.

Random #	Company Name		CEO Pay ($M)	Revenue ($M)	Profits ($M)	Employees
487	Zoetis		$                   11.60	$          5,825.0	1,428.00	10,000
91	Publix		$                     2.00	36,395.70	2,381.20	202,000
65	Cigna		$                   49.00	48,650.00	2,637.00	73,800
85	TJX		$                   19.00	38,972.90	3,059.80	270,000
77	Honeywell Intn.		$                   25.00	41,802.00	6,765.00	114,000
67	HCA Healthcare		$                   21.40	46,677.00	3,787.00	229,000
98	Capital One Financial		$                   17.40	32,377.00	6,015.30	37,346
456	Marathon Oil		$                   11.90	6,582.00	1,096.00	2,400
9	AT&T		$                   14.50	170,756.00	19,370.00	268,220
55	HP		$                   19.20	58,472.00	5,327.00	55,000
12	Ford Motor		$                   17.70	160,338.00	3,677.00	199,000
113	PBF Energy		$                   15.20	27,186.10	128.30	3,266
262	LKQ		$                     3.80	11,876.70	480.1	51,000
300	Discovery		$                129.40	10,553.00	594.00	9,000
19	Verizon		$                   18.60	130,863.00	15,528.00	144,500
72	American Express		$                   17.30	43,281.00	6,921.00	59,000
374	Expeditors Intl. of Wash.		$                   11.20	8,138.40	618.00	17,400
199	Texas Instruments		$                   17.50	15,784.00	5,580.00	29,888
145	Auto Nation		$                   12.40	21,412.80	396	26,000
238	Dominion Energy		$                   14.90	13,366.00	2,447.00	16,100

Answer 1

ANSWER:

Excel > Data > Data Analysis > Regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.188187603
R Square	0.035414574
Adjusted R Square	-0.145445194
Standard Error	28.72064546
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	3	484.5623855	161.5207952	0.195812338	0.897706448
Residual	16	13198.00761	824.8754759
Total	19	13682.57
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	27.3211488	9.587459619	2.849675502	0.011588756	6.996642346	47.64565525	6.996642346	47.64565525
Revenue ($M)	5.73949E-05	0.000239959	0.239186859	0.813997604	-0.000451294	0.000566084	-0.000451294	0.000566084
Employees	-5.39779E-05	9.50628E-05	-0.567813515	0.578043312	-0.000255502	0.000147546	-0.000255502	0.000147546
Profits ($M)	-0.000597117	0.002042817	-0.292300661	0.77381306	-0.004927695	0.003733461	-0.004927695	0.003733461

the estimated equation for CEO pay

PAY= β0+β1 * REVENUE+β2 * EMPLOYEES+β3 * PROFITS+ε

CEO Pay ($M) = 27.3211 + 0 * REVENUE- 0 * EMPLOYEES - 0.0006 * PROFITS

R-Square = 0.0354 = 3.54% of variation in Y variable is explained by regression analysis

or

3.54% of the variation in the dependent variable that is explained by the variation in the independent variables

Based on t critical value

Coefficients	t Stat		t critical(df = 16,alpha=0.05)	Significance
Revenue ($M)	0.239186859	<	2.1199	Not
Employees	-0.567813515	<	2.1199	Not
Profits ($M)	-0.292300661	<	2.1199	Not

Intercept t stat = 2.8497 > t critical = 2.119

Only intercept is statistically significant

Based on P value

Coefficients	P-value		alpha(0.05)	Significance
Revenue ($M)	0.813997604	>	0.05	Not
Employees	0.578043312	>	0.05	Not
Profits ($M)	0.77381306	>	0.05	Not

Intercept P value = 0.0116 < 0.05, Statistically significant