Advanced Excel (Statistics)
guevara
BA3010Assignment1.docxBA 3010 Assignment #1
During this course, you will be graded on three dimensions:
1. your ability to identify the correct analytical tool to use,
2. your ability to execute the analysis you have selected,
3. your ability to interpret the results of your analysis
To that end, the assignments will try to reinforce those particular skills by directly targeting these dimensions.
All of your work should be done in an Excel spreadsheet, including your text responses. Do not send in a Word document. You should submit your assignment via the submission process in Blackboard.
Identify
Q1) Open up the file Assignment 1 and examine the data set on the worksheet Q1. This is a data set of 500 Bakersfield households that were recently surveyed. A definition for each variable (unless self-explanatory) can be found in the comments section (place your cursor over any cell with a little red triangle – a comment box will pop up). Our objective is to learn about the financial state of all Bakersfield households. Answer the following (a text box for your answers is provided to the right of the data):
a) Is this cross-sectional data or time-series data?
b) Is this a population or a sample?
c) Ignoring the variable Household, which is just an identifier variable (household #1, household #2, etc.), identify the variable type and sub-type for each variable.
d) If you wished to examine the distribution of the variable monthly payment, what would you do? List any graphs or calculations you think appropriate.
Execute
Q2) Create the following graphs. Note we are still using the data in Q1.
a) For the data in sheet Q1, do a histogram for the variable debt. Don’t be afraid to redo it a couple of times (resetting the number of bins or the “span” of the bins) until you get one that is satisfactory and revealing.
b) Again, for data in sheet Q1, do a boxplot with the variable first income.
Q3) Examine the data set in the worksheet Q3. This data set lists the 24 players who were on the roster of the Colorado Avalanche at the beginning of the 2001-2002 NHL season. The variables include the name, position, and salary for each player (see the definitions in the comments).
a) Calculate the following values for the variable Salary:
i. The average/mean
ii. The median
iii. The standard deviation
iv. The maximum, minimum, and range
v. The 1st and 3rd quartiles and the inter-quartile range
vi. The salary that 35% of players earn less than
vii. The salary that 20% of players earn more than
b) Create a box-plot for the variable Salary.
Interpret
Q4) Examine the graphs shown below. What do you learn from this graph? In other words, what can you tell us about the distribution of first_income? It might help if you pretend you are summarizing the results in a few sentences for your boss. Please type your answers on the Graphs page of the assignment 1 spreadsheet.
Graph (from data set 1 in the assignment 1 spreadsheet)
Q5) Interpret the following. What do you learn? What conclusions can you draw? Don’t forget to write your answers in the spreadsheet on the Q5 worksheet.
In May of 2004, a study was published examining the total compensation (salary and bonus) paid to the CEO’s of some 350 firms. Boxplots of the distribution of compensation are shown below (a horizontal oriented plot immediately below and a vertically oriented plot on the next page) as well as some basic summary measures. All values are in $1,000’s of dollars. What can you conclude from this analysis (besides the already well-known fact that these guys are well-paid)?
|
|
Sum03 |
|
One Variable Summary |
Data Set #1 |
|
Mean |
$2663.32 |
|
Std. Dev. |
$2986.04 |
|
Skewness |
4.8769 |
|
Median |
$2065.40 |
|
Minimum |
$0.00 |
|
Maximum |
$28000.00 |
|
Range |
$28000.00 |
|
Count |
350 |
|
1st Quartile |
$1183.00 |
|
3rd Quartile |
$3000.00 |
|
Interquartile Range |
$1817.00 |
|
1.00% |
$0.00 |
|
2.50% |
$117.50 |
|
5.00% |
$449.30 |
|
95.00% |
$7250.00 |
|
97.50% |
$9500.00 |
|
99.00% |
$17137.50 |
Histogram of First_Income / Data Set #1
0
20
40
60
80
100
120
140
160
180
$10,000$20,000$30,000$40,000$50,000$60,000$70,000$80,000$90,000
$100,000
Frequency
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
Box Plot of Sum03 / Data Set #1
050001000015000200002500030000
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
StatTools Student Version
For Academic Use Only
