# Mastering Data Analysis in Excel Week 4 Quiz Answer

## Mastering Data Analysis in Excel

## 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.**

**Standardization Spreadsheet.xlsx**

- 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**

**Spreadsheet.**

**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.**

**Solver Add-In.xlsx**

**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**

**Model Error spreadsheet).**

**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%