MATH 122 QCC Statistics Excel Sheet

MATH 122 – Statistics
QCC – Summer, 2020
Prof. Ken Friedman
kfriedman@qcc.mass.edu
Chapter 3 Problem Set
Part I: Calculations for Summary Statistics
[A] For the following set of paired data values, calculate the summary statistics.
i
1
2
3
4
X
14
20
10
16
Y
18
24
8
6
[Q#1]
[Q#2]
[Q#3]
[Q#4]
[Q#5]
[Q#6]
What is the mean for X ?
What is the mean for Y ?
What is the standard deviation for X?
What is the standard deviation for Y?
What is the Covariance between X and Y?
What is the Correlation between X and Y?
[B] For the following set of paired data values, calculate the summary statistics.
i
1
2
3
4
5
X
10
15
9
13
18
Y
6
9
20
14
6
[Q#1]
[Q#2]
[Q#3]
[Q#4]
[Q#5]
[Q#6]
What is the mean for X ?
What is the mean for Y ?
What is the standard deviation for X?
What is the standard deviation for Y?
What is the Covariance between X and Y?
What is the Correlation between X and Y?
Part II: Weighted Mean Calculations
[A] Portfolio Investment
Consider an investor with $1 million dollars invested in the following seven stocks.
The investment rate of return for each stock is listed.
[Q#1] Calculate the expected port-folio rate of return as a weighted average.
[Q#2] Compute the investor’s total $ return.
Company
Citigroup
General Electric
Kimberly-Clark
Oracle
Pharmacia
Verizon
General Motors
Total Portfolio Value:
Amount
Invested
$ 120,000
$ 100,000
$ 150,000
$ 80,000
$ 200,000
$ 130,000
$ 220,000
Expected Rate
of Return
8.0%
15.5%
10.0%
20.0%
7.5%
16.0%
9.0%
=
=
=
=
=
=
=
.08
.155
.10
.20
.075
.16
.09
$ 1,000,000
[B] Company Market Share
Industry Sales
Desktop PC
$180,000,000 _____
APPLE’s
Market Share
8% = .08 _____________ ______
Laptop PC
$220,000,000 _____
10% = .10 _____________ ______
PC Tablet
$120,000,000 _____
6% = .06 _____________ ______
Smartphone
$480,000,000 _____
50% = .50 _____________ ______
Industry Total = $1,000,000,000
[Q#1] What is the $ value of Apple’s sales revenue for all PC types combined?
[Q#2] What is Apple’s market share for the entire computer industry?
Part III: Positional Analysis – Data Set Evaluation
[A] The course handout titled: “Positional Analysis for Descriptive Statistics”
evaluated an Excel sheet with home selling price data titled:
“Home Selling Prices in Worcester County”. Work further with this data set
and calculate the following values:
[Q#1]
[Q#2]
[Q#3]
[Q#4]
the
the
the
the
7th Decile value: D7
Hint: this is the 70th percentile, P70
3rd Decile value: D3
Hint: this is the 30th percentile, P30
65th Percentile Value: P65
32nd Percentile Value: P32
For each question, average the two home selling prices around the position.
[B] The following is a list of the selling price for twenty (n=20) houses that
sold in Worcester this month. Prices are in thousands of dollars ($K).
$400
$252
$380
$240
$362
$222
$350
$210
$338
$200
$322
$188
$310
$182
$298
$172
$282
$168
$268
$160
Utilize the method of averaging two values around the positional location.
(1)
(2)
(3)
(4)
(5)
(6)
(7)
What is the Median value?
What are the three Quartile values? Q3, Q2(median), Q1 .
What are the four Quintile values? QN4, QN3, QN2, QN1 .
What are the nine Decile values? D9, D8, D7, D6, D5,(median) D4, D3, D2, D1
What is the Full Range = MAX – MIN
What is the Inter-Quartile Range: Q3 – Q1
What is the P90 – P10 range?