In: Math
Conduct a hypothesis test to determine if companies with negative revenue change tend to be on the (500) market less time (and/or how much less time)?
The data is below. Please show all work in excel.
Company Name | Revenue Change | # Years on Fortune 500 List |
Walmart | 3.00% | 24 |
Exxon Mobil | 17.40% | 24 |
Berkshire Hathaway | 8.30% | 24 |
Apple | 6.30% | 24 |
UnitedHealth Group | 8.80% | 24 |
McKesson | 3.10% | 24 |
CVS Health | 4.10% | 24 |
Amazon.com | 27.10% | 17 |
AT&T | -2.00% | 24 |
General Motors | -5.50% | 24 |
Ford Motor | 3.30% | 24 |
AmerisourceBergen | 4.30% | 24 |
Chevron | 25.10% | 24 |
Cardinal Health | 6.90% | 24 |
Costco | 8.70% | 24 |
Verizon | 0.00% | 24 |
Kroger | 6.40% | 24 |
General Electric | -3.50% | 24 |
Walgreens Boots Alliance | 0.70% | 24 |
JPMorgan Chase | 8.00% | 24 |
Fannie Mae | 4.90% | 21 |
Alphabet | 22.80% | 13 |
Home Depot | 6.70% | 24 |
Bank of America Corp. | 7.00% | 24 |
Express Scripts Holding | -0.20% | 19 |
Wells Fargo | 3.80% | 24 |
Boeing | -1.20% | 24 |
Phillips 66 | 26.50% | 6 |
Anthem | 6.10% | 24 |
Microsoft | 5.40% | 24 |
Valero Energy | 26.00% | 20 |
Citigroup | 6.80% | 24 |
Comcast | 5.10% | 23 |
IBM | -1.00% | 24 |
Dell Technologies | 21.40% | 21 |
State Farm Insurance Cos. | 2.90% | 24 |
Johnson & Johnson | 6.30% | 24 |
Freddie Mac | 13.70% | 21 |
Target | 3.40% | 24 |
Lowes | 5.50% | 24 |
Marathon Petroleum | 21.00% | 7 |
Procter & Gamble | -7.70% | 24 |
MetLife | 4.20% | 24 |
UPS | 8.20% | 24 |
PepsiCo | 1.20% | 24 |
Intel | 5.70% | 24 |
DowDuPont | 30.20% | 24 |
Archer Daniels Midland | -2.40% | 24 |
Aetna | -4.10% | 18 |
FedEx | 19.80% | 24 |
United Technologies | 4.50% | 24 |
Prudential Financial | 1.50% | 24 |
Albertsons Cos. | 1.60% | 14 |
Sysco | 9.90% | 24 |
Disney | -0.90% | 24 |
Humana | -1.10% | 24 |
Pfizer | -0.50% | 24 |
HP | 7.90% | 24 |
Lockheed Martin | 0.80% | 24 |
AIG | -5.40% | 24 |
Centene | 19.30% | 9 |
Cisco Systems | -2.50% | 22 |
HCA Healthcare | 6.50% | 24 |
Energy Transfer Equity | 26.60% | 12 |
Caterpillar | 18.00% | 24 |
Nationwide | 9.60% | 24 |
Morgan Stanley | 15.00% | 24 |
Liberty Mutual Insurance Group | 11.40% | 24 |
New York Life Insurance | 3.70% | 24 |
Goldman Sachs Group | 12.00% | 19 |
American Airlines Group | 5.00% | 24 |
Best Buy | 7.00% | 24 |
Cigna | 4.90% | 24 |
Charter Communications | 43.40% | 18 |
Delta Air Lines | 4.00% | 24 |
47.10% | 6 | |
Honeywell International | 3.10% | 24 |
Merck | 0.80% | 24 |
Allstate | 5.40% | 23 |
Tyson Foods | 3.70% | 24 |
United Continental Holdings | 3.20% | 24 |
Oracle | 1.80% | 23 |
Tech Data | 40.20% | 24 |
TIAA | -2.90% | 21 |
TJX | 8.10% | 24 |
American Express | 5.20% | 24 |
Coca-Cola | -15.40% | 24 |
Publix Super Markets | 1.60% | 24 |
Nike | 6.10% | 24 |
Andeavor | 42.50% | 19 |
World Fuel Services | 24.70% | 14 |
Exelon | 6.90% | 24 |
Massachusetts Mutual Life Insurance | -11.40% | 24 |
Rite Aid | 6.90% | 24 |
ConocoPhillips | 33.80% | 24 |
CHS | 5.20% | 19 |
3M | 5.10% | 24 |
Time Warner | 6.70% | 19 |
General Dynamics | -1.20% | 24 |
USAA | 10.60% | 24 |
First sort the table based on revenue
and make two new columns for years based on positive and negative revenues
data -> sort
Company Name | Revenue Change | # Years on Fortune 500 List |
Coca-Cola | -15.40% | 24 |
Massachusetts Mutual Life Insurance | -11.40% | 24 |
Procter & Gamble | -7.70% | 24 |
General Motors | -5.50% | 24 |
AIG | -5.40% | 24 |
Aetna | -4.10% | 18 |
General Electric | -3.50% | 24 |
TIAA | -2.90% | 21 |
Cisco Systems | -2.50% | 22 |
Archer Daniels Midland | -2.40% | 24 |
AT&T | -2.00% | 24 |
Boeing | -1.20% | 24 |
General Dynamics | -1.20% | 24 |
Humana | -1.10% | 24 |
IBM | -1.00% | 24 |
Disney | -0.90% | 24 |
Pfizer | -0.50% | 24 |
Express Scripts Holding | -0.20% | 19 |
Verizon | 0.00% | 24 |
Walgreens Boots Alliance | 0.70% | 24 |
Lockheed Martin | 0.80% | 24 |
Merck | 0.80% | 24 |
PepsiCo | 1.20% | 24 |
Prudential Financial | 1.50% | 24 |
Albertsons Cos. | 1.60% | 14 |
Publix Super Markets | 1.60% | 24 |
Oracle | 1.80% | 23 |
State Farm Insurance Cos. | 2.90% | 24 |
Walmart | 3.00% | 24 |
McKesson | 3.10% | 24 |
Honeywell International | 3.10% | 24 |
United Continental Holdings | 3.20% | 24 |
Ford Motor | 3.30% | 24 |
Target | 3.40% | 24 |
New York Life Insurance | 3.70% | 24 |
Tyson Foods | 3.70% | 24 |
Wells Fargo | 3.80% | 24 |
Delta Air Lines | 4.00% | 24 |
CVS Health | 4.10% | 24 |
MetLife | 4.20% | 24 |
AmerisourceBergen | 4.30% | 24 |
United Technologies | 4.50% | 24 |
Fannie Mae | 4.90% | 21 |
Cigna | 4.90% | 24 |
American Airlines Group | 5.00% | 24 |
Comcast | 5.10% | 23 |
3M | 5.10% | 24 |
American Express | 5.20% | 24 |
CHS | 5.20% | 19 |
Microsoft | 5.40% | 24 |
Allstate | 5.40% | 23 |
Lowes | 5.50% | 24 |
Intel | 5.70% | 24 |
Anthem | 6.10% | 24 |
Nike | 6.10% | 24 |
Apple | 6.30% | 24 |
Johnson & Johnson | 6.30% | 24 |
Kroger | 6.40% | 24 |
HCA Healthcare | 6.50% | 24 |
Home Depot | 6.70% | 24 |
Time Warner | 6.70% | 19 |
Citigroup | 6.80% | 24 |
Cardinal Health | 6.90% | 24 |
Exelon | 6.90% | 24 |
Rite Aid | 6.90% | 24 |
Bank of America Corp. | 7.00% | 24 |
Best Buy | 7.00% | 24 |
HP | 7.90% | 24 |
JPMorgan Chase | 8.00% | 24 |
TJX | 8.10% | 24 |
UPS | 8.20% | 24 |
Berkshire Hathaway | 8.30% | 24 |
Costco | 8.70% | 24 |
UnitedHealth Group | 8.80% | 24 |
Nationwide | 9.60% | 24 |
Sysco | 9.90% | 24 |
USAA | 10.60% | 24 |
Liberty Mutual Insurance Group | 11.40% | 24 |
Goldman Sachs Group | 12.00% | 19 |
Freddie Mac | 13.70% | 21 |
Morgan Stanley | 15.00% | 24 |
Exxon Mobil | 17.40% | 24 |
Caterpillar | 18.00% | 24 |
Centene | 19.30% | 9 |
FedEx | 19.80% | 24 |
Marathon Petroleum | 21.00% | 7 |
Dell Technologies | 21.40% | 21 |
Alphabet | 22.80% | 13 |
World Fuel Services | 24.70% | 14 |
Chevron | 25.10% | 24 |
Valero Energy | 26.00% | 20 |
Phillips 66 | 26.50% | 6 |
Energy Transfer Equity | 26.60% | 12 |
Amazon.com | 27.10% | 17 |
DowDuPont | 30.20% | 24 |
ConocoPhillips | 33.80% | 24 |
Tech Data | 40.20% | 24 |
Andeavor | 42.50% | 19 |
Charter Communications | 43.40% | 18 |
47.10% | 6 |
negative | positive |
24 | 24 |
24 | 24 |
24 | 24 |
24 | 24 |
24 | 24 |
18 | 24 |
24 | 14 |
21 | 24 |
22 | 23 |
24 | 24 |
24 | 24 |
24 | 24 |
24 | 24 |
24 | 24 |
24 | 24 |
24 | 24 |
24 | 24 |
19 | 24 |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
21 | |
24 | |
24 | |
23 | |
24 | |
24 | |
19 | |
24 | |
23 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
19 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
24 | |
19 | |
21 | |
24 | |
24 | |
24 | |
9 | |
24 | |
7 | |
21 | |
13 | |
14 | |
24 | |
20 | |
6 | |
12 | |
17 | |
24 | |
24 | |
24 | |
19 | |
18 | |
6 |
data -> data analysis -> t-Test: Two-Sample Assuming Equal Variances
t-Test: Two-Sample Assuming Equal Variances | ||
negative | positive | |
Mean | 23.11111111 | 22.04878049 |
Variance | 3.516339869 | 19.08401084 |
Observations | 18 | 82 |
Pooled Variance | 16.38349649 | |
Hypothesized Mean Difference | 0 | |
df | 98 | |
t Stat | 1.0083 | |
P(T<=t) one-tail | 0.1579 | |
t Critical one-tail | 1.6606 | |
P(T<=t) two-tail | 0.3158 | |
t Critical two-tail | 1.9845 |
this is left tailed test
p-value = 0.1579 > alpha
hence we fail to reject the null hypothesis
we conclude that there is not sufficient evidence