ECON 2370 University of Houston Economics Data Sets Excel Questions A Dataset on Fortune 500 Companies Download the following file from Blackboard: Fortun

ECON 2370 University of Houston Economics Data Sets Excel Questions A Dataset on Fortune 500 Companies

Download the following file from Blackboard: Fortune.xlsx

Variables:

This is a dataset for a sample of 50 Fortune 500 Companies.
There are three Company Types:
M=Manufacturing
R=Retail
S=Services
Stockholders’ Equity ($)
Market Value ($)
Profit ($)
Each must have a descriptive title.
Axes in graphs and rows/columns in tables must be properly labeled.
Answer the question associated with each Graph/ Table to get full credit.

Instructions for Tables and Graphs:

Dataset Questions (40 points)
How many variables are in the dataset?
How many observations are in the dataset?
Which variables are categorical and which are quantitative?
Use Excel to find: number of observations, mean, median, 1st and 3rd quartiles, sample standard deviation, minimum and maximum for variables: Stockholders’ Equity, Market Value and Profit.

Statistics

Stockholders’ Equity

Market Value

Profit

mean

median

sample std. dev.

1st Quartile

3rd Quartile

minimum

maximum

No. of obs.

Company Type
Using a PivotTable: Prepare the percentage frequency distribution of Company Type. No need to copy and paste the table to the homework. But you need it for the next step.
Prepare a Pie Chart of the Company Type. Copy and Paste it here.
Which is the most frequent Company type in the dataset?
Company Type and Stockholders’ Equity
Using a PivotTable: Prepare a cross tabulation of Company Type (rows) and Stockholders’ Equity (columns). min= 0, max=6000, by=1000 No need to copy and paste the table to the homework. But you need it for the next step.
Prepare a 100% Stacked Bar Chart of Company Type (horizontal axis category) and Stockholders’ Equity (stacked categories). Copy and Paste it here.
Which Company Type has the highest Stockholders’ Equity?
Market Value and Stockholders’ Equity.
Do a Scatter Plot of Market Value (vertical axis) and Stockholder’s Equity (horizontal axis). Add a linear trend line. Copy and Paste it here.
Calculate the correlation between the Market Value and Stockholder’s Equity?
Comment on the relationship. Is it positive or negative? Strong or weak? Do companies with higher Market Value also have higher Stockholder’s equity?
Draw a histogram for Profit using a PivotTable. Group Settings: min= 0, max=1200, by=100.
Comment on the shape of the histogram. Is it symmetric or skewed left/right?
Probabilities and Basic Relationships (10 points)

Suppose we have a sample space: , with probabilities: , , , , , , and . Consider the following events with their corresponding set of sample points: , , and . Note:

if A and B are mutually exclusive
if A and B are mutually exclusive
if A and B are independent
= the probability of X success in n trials
= the number of successes (only two possible outcomes: success and failure)
= the probability of success of one trial (constant for all trials)
= the number of trials (all trials are identical and independent)
= combinatorial equation
= the probability of x occurrences in an interval
= the expected value or mean number of occurrences in an interval
= is the mathematical constant 2.71828

Questions:

Find the probabilities: , and .
Find the set of sample points of events (this is a list of sample points {…}) and find ?
Find the set of sample points of and find .
Now use the formula to confirm your answer from part (b).
Are events A and C mutually exclusive?
Find and
Calculate and . Are they the same?
Are events A and B independent?
Which are the parameters of this distribution function? (There are two.)
Write the equation for the binomial probability distribution function two times.
A university found that 10% of students withdraw from a math course. Assume 25 students are enrolled.
Write the prob. distribution function with the specific parameters for this problem.
Compute by hand (can use calculator but show some work) the prob. that exactly 2 withdraw.
Compute by hand (can use calculator but show some work) the prob. that exactly 5 withdraw.
Construct the probability distribution table of class withdrawals in Excel. This is a table of x and f(x). Generate one column for the number of x successes with numbers 0 to 25 in each row. Generate a second column with the probability f(x) of each success using the Excel function =BINOM.DIST(x, n, p, FALSE) in each row. Attach the table to the end of your homework.
What is the probability that 5 or less will withdraw?
Which are the parameters of this distribution function? (There is only one.)
Write the Poisson probability distribution function two times.
Customers for a restaurant arrive at an average rate of 36 customers per hour during lunchtime.
Write the Poisson probability distribution function with the specific parameters for this problem.
Calculate the probability of receiving exactly 25 customers in a 60-minute interval.
Calculate the probability of receiving exactly 30 customers in a 60-minute interval.
Construct the probability distribution table of new customer arrival. This is a table of x and f(x). Generate one column for the number of x occurrences with numbers 0-50 in each row. Generate a second column for the probability f(x) of each occurrence using the Excel function =POISSON.DIST(x, , FALSE) in each row. Attach the table to the end of your homework.
What is the probability that between 15 and 20 guests will arrive in a 60-minute interval?
Rewrite the probability distribution function to calculate the probability of having exactly 10 customers in a 30-minute interval. You do not need to calculate the probability, just adjust the parameter for a 30-minute interval instead of a 60-minute interval and write the equation.
Discrete Distributions Part 1: Binomial Probability Distribution (10 Points)

Binomial Probability Function:

Questions:

Discrete Distributions Part 2: Poisson Probability Distribution (10 Points)

Poisson Probability Function:

Questions: ECON2370 Homework 2
due Wednesday July 15, 2020
•
•
•
•
•
•
•
Out of 100 points.
Turn in one pdf file. Other file formats will not be accepted or will lose points.
Answer all questions inside this document and in the same order. Questions answered out of order
will not be graded. All questions should appear in the homework.
No late submissions will be accepted.
Copy&Paste Excel tables/graphs below each question. You will need to delete blank lines as you do
this. Be careful not to delete any questions or change the format of the number list.
Questions that must be answered by hand (indicated in the question) must have a picture pasted
into the document.
There is a penalty for up to 10 points for not following any of the instructions above.
A Dataset on Fortune 500 Companies
Download the following file from Blackboard: Fortune.xlsx
Variables:
•
•
•
•
•
This is a dataset for a sample of 50 Fortune 500 Companies.
There are three Company Types:
– M=Manufacturing
– R=Retail
– S=Services
Stockholders’ Equity ($)
Market Value ($)
Profit ($)
Instructions for Tables and Graphs:
•
•
•
Each must have a descriptive title.
Axes in graphs and rows/columns in tables must be properly labeled.
Answer the question associated with each Graph/ Table to get full credit.
Dataset Questions (40 points)
1.
2.
3.
4.
How many variables are in the dataset?
How many observations are in the dataset?
Which variables are categorical and which are quantitative?
Use Excel to find: number of observations, mean, median, 1st and 3rd quartiles,
sample standard deviation, minimum and maximum for variables: Stockholders’
Equity, Market Value and Profit.
Statistics Stockholders’ Equity Market Value Profit
mean
median
sample std. dev.
1st Quartile
3rd Quartile
minimum
maximum
No. of obs.
5.
Company Type
– Using a PivotTable: Prepare the percentage frequency distribution of
Company Type. No need to copy and paste the table to the homework. But
you need it for the next step.
– Prepare a Pie Chart of the Company Type. Copy and Paste it here.
– Which is the most frequent Company type in the dataset?
6.
Company Type and Stockholders’ Equity
– Using a PivotTable: Prepare a cross tabulation of Company Type (rows) and
Stockholders’ Equity (columns). min= 0, max=6000, by=1000 No need to
copy and paste the table to the homework. But you need it for the next step.
– Prepare a 100% Stacked Bar Chart of Company Type (horizontal axis
category) and Stockholders’ Equity (stacked categories). Copy and Paste it
here.
– Which Company Type has the highest Stockholders’ Equity?
Market Value and Stockholders’ Equity.
– Do a Scatter Plot of Market Value (vertical axis) and Stockholder’s Equity
(horizontal axis). Add a linear trend line. Copy and Paste it here.
– Calculate the correlation between the Market Value and Stockholder’s
Equity?
– Comment on the relationship. Is it positive or negative? Strong or weak? Do
companies with higher Market Value also have higher Stockholder’s equity?
Draw a histogram for Profit using a PivotTable. Group Settings: min= 0,
max=1200, by=100.
– Comment on the shape of the histogram. Is it symmetric or skewed
left/right?
7.
8.
Probabilities and Basic Relationships (10 points)
Suppose we have a sample space: = { 1 , 2 , 3 , 4 , 5 , 6 , 7 }, with probabilities:
( 1 ) = 0.05, ( 2 ) = 0.20, ( 3 ) = 0.20, ( 4 ) = 0.25, ( 5 ) = 0.15, ( 6 ) = 0.10, and
( 7 ) = 0.05. Consider the following events with their corresponding set of sample
points: = { 1 , 4 , 6 }, = { 2 , 4 , 7 }, and = { 2 , 3 , 5 , 7 }. Note:
•
•
•
•
( ∪ ) = ( ) + ( ) − ( ∩ )
( ∪ ) = ( ) + ( ) if A and B are mutually exclusive
( ∩ ) = 0 if A and B are mutually exclusive
( ) = 1 − ( )
•
( ∣ ) =
•
( ∣ ) = ( ) if A and B are independent
( ∩ )
( )
Questions:
1.
2.
3.
4.
5.
6.
7.
8.
Find the probabilities: ( ), ( ) and ( ).
Find the set of sample points of events ∪ (this is a list of sample points {…}) and
find ( ∪ ) ?
Find the set of sample points of ∩ and find ( ∩ ).
Now use the formula ( ∪ ) = ( ) + ( ) − ( ∩ ) to confirm your answer
from part (b).
Are events A and C mutually exclusive?
Find and ( )
Calculate ( ∣ ) and ( ∣ ). Are they the same?
Are events A and B independent?
Discrete Distributions Part 1: Binomial Probability
Distribution (10 Points)
Binomial Probability Function:
( ) = ( ) (1 − )( − )
•
•
•
•
•
( ) = the probability of X success in n trials
= the number of successes (only two possible outcomes: success and failure)
= the probability of success of one trial (constant for all trials)
= the number of trials (all trials are identical and independent)
( ) = combinatorial equation
Questions:
1.
2.
3.
Which are the parameters of this distribution function? (There are two.)
Write the equation for the binomial probability distribution function two times.
A university found that 10% of students withdraw from a math course. Assume 25
students are enrolled.
a. Write the prob. distribution function with the specific parameters for this
problem.
b. Compute by hand (can use calculator but show some work) the prob. that
exactly 2 withdraw.
c. Compute by hand (can use calculator but show some work) the prob. that
exactly 5 withdraw.
d. Construct the probability distribution table of class withdrawals in Excel.
This is a table of x and f(x). Generate one column for the number of x
successes with numbers 0 to 25 in each row. Generate a second column with
the probability f(x) of each success using the Excel function
=BINOM.DIST(x, n, p, FALSE) in each row. Attach the table to the end of
your homework.
e. What is the probability that 5 or less will withdraw?
Discrete Distributions Part 2: Poisson Probability
Distribution (10 Points)
Poisson Probability Function:
( ) =
•
•
•
−
!
( ) = the probability of x occurrences in an interval
= the expected value or mean number of occurrences in an interval
= is the mathematical constant 2.71828
Questions:
1.
2.
3.
4.
Which are the parameters of this distribution function? (There is only one.)
Write the Poisson probability distribution function two times.
Customers for a restaurant arrive at an average rate of 36 customers per hour
during lunchtime.
a. Write the Poisson probability distribution function with the specific
parameters for this problem.
b. Calculate the probability of receiving exactly 25 customers in a 60-minute
interval.
c. Calculate the probability of receiving exactly 30 customers in a 60-minute
interval.
d. Construct the probability distribution table of new customer arrival. This is a
table of x and f(x). Generate one column for the number of x occurrences
with numbers 0-50 in each row. Generate a second column for the
probability f(x) of each occurrence using the Excel function
=POISSON.DIST(x, , FALSE) in each row. Attach the table to the end of
your homework.
e. What is the probability that between 15 and 20 guests will arrive in a 60minute interval?
Rewrite the probability distribution function to calculate the probability of having
exactly 10 customers in a 30-minute interval. You do not need to calculate the
probability, just adjust the parameter for a 30-minute interval instead of a 60minute interval and write the equation.
Company Name
AGCO
AMP
Apple Computer
Baxter International
Bergen Brunswick
Best Buy
Charles Schwab
CMS Energy
Coca-Cola
Colgate-Palmolive
Comcast
Conagra
Conseco
Delta Air Lines
Dollar General
Federated
Fortune Brands
GAP
Hormel Foods
Ingersoll-Rand
Ingram Micro
Kimberly-Clark
Kmart
KN Energy
Kohl’s
Lowe’s
Marketspan
Maytag
Mead
Mellon Bank
Nike
Nordstrom
Nucor
OfficeMax
Owens & Minor
Phelps Dodge
Qualcomm
Republic Industries
Safeco
Safeway
Company Type
M
S
M
M
M
R
S
S
M
M
S
M
S
S
R
S
M
M
M
M
S
M
R
S
R
R
M
M
M
S
M
R
M
R
R
M
M
M
S
R
Stockholders’
Equity
982.1
2698.0
1642.0
2839.0
629.1
557.7
1429.0
2216.0
2438.0
2085.6
3815.3
2778.9
5273.6
4023.0
725.8
5709.0
4097.5
1573.7
813.3
2707.5
1399.3
3887.2
5979.0
1223.8
1162.8
3136.0
3000.0
507.6
2252.0
4521.0
3261.6
1316.7
2072.6
1138.1
161.1
2587.4
957.6
5424.2
5575.8
3082.1
Market
Value
372.1
12017.6
4605.0
21743.0
2787.5
10376.5
35340.6
4546.6
15704.9
26810.2
26746.1
15169.4
11725.7
9773.7
7193.1
8301.4
5639.2
39644.9
2710.1
8209.2
2555.6
26881.4
8539.3
1432.0
11790.5
22635.3
3850.7
4828.7
3155.8
19251.3
15824.9
5683.2
3734.3
1014.6
299.6
2797.3
5862.1
6190.9
5655.6
27700.7
Profit
60.6
2.0
309.0
315.0
3.1
94.5
348.5
285.0
142.0
848.6
972.1
613.2
588.1
1001.0
182.0
662.0
263.1
824.5
139.3
509.1
245.2
1165.8
568.0
60.0
192.3
482.4
51.3
280.6
119.7
870.0
399.6
206.7
263.7
42.6
20.1
190.9
108.5
499.5
351.9
806.7
Shaw Industries
Staples
Sunoco
SuperValue
Tenneco
Thermo Electron
Walgreen
Westvaco
Whirlpool
Xerox
M
R
M
R
M
M
R
M
M
M
797.4
1514.0
1514.0
1201.9
2504.0
2248.1
2849.0
2246.4
2001.0
5544.0
3110.3
2995.2
2995.2
2701.5
5220.2
2218.9
30324.7
2225.6
3729.4
35603.7
20.6
280.0
280.0
230.8
255.0
181.9
511.0
132.0
325.0
395.0

Purchase answer to see full
attachment