# Mastering Data Analysis in Excel Week 4 Quiz Answer

## Week 4 Quiz Answer Coursera

### Parametric Models for Regression (graded)

Question 1)
A University admissions test has a Gaussian distribution of test scores with a mean
of 500 and standard deviation of 100. One student out-performed 97.4% of all test
takers.

What was their test score (rounded to the nearest whole number)?
Hint: Refer to the Excel NormSFunctions Spreadsheet.

Excel NormS Functions Spreadsheet.xlsx

• 694
• 502
• 306
• 972

Question 2)
A carefully machined wire comes off an assembly line within a certain tolerance. Its
diameter is 100 microns, and all the wires produced have a uniform distribution of
error, between -11 microns and +29 microns.

A testing machine repeatedly draws samples of 180 wires and measures the sample
mean. What is the distribution of sample means?
Hint: Use the CLT and Excel Rand() Spreadsheet.
CLT and Excel Rand.xlsx

• A Gaussian distribution that, in Phi notation, is written, ϕ(109, 133.33).
• A Gaussian Distribution that, in Phi notation, is written ϕ(109, .7407).
• A Uniform Distribution with mean = 109 microns and standard deviation = .8607 microns.
• A Uniform Distribution with mean = 109 microns and standard deviation = 11.54 microns.

Question 3)
A population of people suffering from Tachycardia (occasional rapid heart rate),
agrees to test a new medicine that is supposed to lower heart rate. In the population
being studied, before taking any medicine the mean heart rate was 120 beats per
minute, with standard deviation = 15 beats per minute.
After being given the medicine, a sample of 45 people had an average heart rate of
112 beats per minute. What is the probability that this much variation from the mean
could have occurred by chance alone?
Hint: Use the Typical Problem with NormSDist Spreadsheet.
Typical Problem_ NormSDist .xlsx

• .0173%
• 1.73%
• 29.690%
• 99.9827%

Question 4)
Two stocks have the following expected annual returns:
Oil stock – expected return = 9% with standard deviation = 13%
IT stock – expected return = 14% with standard deviation = 25%
The Stocks prices have a small negative correlation: R = -.22.
What is the Covariance of the two stocks?
Hint: Use the Algebra with Gaussians Spreadsheet.
Algebra with Gaussians.xlsx

• -.0286
• -.00219
• -.00715
• -.00573

Question 5)
Two stocks have the following expected annual returns:
Oil stock – expected return = 9% with standard deviation = 13%
IT stock – expected return = 14% with standard deviation = 25%
The Stocks prices have a small negative correlation: R = -.22.
Assume return data for the two stocks is standardized so that each is represented as
having mean 0 and standard deviation 1. Oil is plotted against IT on the (x,y) axis.
What is the covariance?
Hint: Use the Standardization Spreadsheet.

• 0
• -1
• -.22
• -.00573

Question 6)
Two stocks have the following expected annual returns:
Oil stock – expected return = 9% with standard deviation = 13%
IT stock – expected return = 14% with standard deviation = 25%
The Stocks prices have a small negative correlation: R = -.22.
What is the standard deviation of a portfolio consisting of 70% Oil and 30% IT?
Hint: Use either the Algebra with Gaussians or the Markowitz Portfolio Optimization
Algebra with Gaussians.xlsx
Markowitz Portfolio Optimization.xlsx

• 12.68%
• 10.44%
• 11.79%
• 17.93%

Question 7)
Two stocks have the following expected annual returns:
Oil stock – expected return = 9% with standard deviation = 13%
IT stock – expected return = 14% with standard deviation = 25%
The Stocks prices have a small negative correlation: R = -.22.
Use MS Solver and the Markowitz Portfolio Optimization Spreadsheet to Find the
weighted portfolio of the two stocks with lowest volatility.
Markowitz Portfolio Optimization.xlsx
What is the minimum volatility?

• 9.5%
• 10.43%
• 10.36%
• 11.58%

Question 8)
You are a data-analyst for a restaurant chain and are asked to forecast first-year
revenues from new store locations. You use census tract data to develop a linear
model.
Your first model has a standard deviation of model error of \$25,000 at a correlation of
R = .30. Your boss asks you to keep working on improving the model until the new
standard deviation of model error is \$15,000 or less.
What positive correlation R would you need to have a model error of \$15,000?
(Note: you can answer this question by making small additions to the Correlation and
Correlation and Model Error.xlsx

• R = .428
• R = .500
• R = .572
• R = .8200

Question 9)
An automobile parts manufacturer uses a linear regression model to forecast the
dollar value of the next years’ orders from current customers as a function of a
weighted sum of their past-years’ orders. The model error is assumed Gaussian with
standard deviation of \$130,000.
If the correlation is R = .33, and the point forecast orders \$5.1 million, what is the
probability that the customer will order more than \$5.3 million?
Hint: Use the Typical Problem with NormSDist Spreadsheet.
Typical Problem_ NormSDist .xlsx

• 4.3%
• 6.2%
• 12.4%
• 93.8%

Question 10)
An automobile parts manufacturer uses a linear regression model to forecast the
dollar value of the next years’ orders from current customers as a function of a
weighted sum of that customer’s past-years orders. The linear correlation is R = .33.
After standardizing the x and y data, what portion of the uncertainty about a
customer’s order size is eliminated by their historical data combined with the model?
Hint: Use the Correlation and P.I.G. Spreadsheet.
Correlation and P.I.G..xlsx

• 3.5%
• 4.2%
• 4.5%
• 5.2%

Question 11)
A restaurant offers different dinner “specials” each weeknight. The mean cash
register receipt per table on Wednesdays is \$75.25 with standard deviation of \$13.50.
The restaurant experiments one Wednesday with changing the “special” from blue
fish to lobster. The average amount spent by 85 customers is \$77.20.
How probable is it that Wednesday receipts are better than average by chance
alone?
Hint: Use the Typical Problem with NormSDist Spreadsheet.
Typical Problem_ NormSDist .xlsx

• 8.30%
• 9.15%
• 9.05%
• 90.85%

Question 12)
Your company currently has no way to predict how long visitors will spend on the
Company’s web site. All it known is the average time spent is 55 seconds, with an
approximately Gaussian distribution and standard deviation of 9 seconds. It would be
possible, after investing some time and money in analytics tools, to gather and
analyzing information about visitors and build a linear predictive model with a
standard deviation of model error of 4 seconds.
How much would the P.I. G. of that model be?
Hint: Use the Correlation and P.I.G. Spreadsheet
How to use the AUC calculator.pdf
PDF File

• 48.2%
• 61.5%
• 53.3%
• 57.2%