Parametric Models for Regression (graded) >> Week 4 >> Mastering Data Analysis in Excel
1. Question 1 A manufacturer has developed a specialized metal alloy for use in jet engines. In its pure form, the alloy starts to soften at 1500 F. However, small amounts of impurities in production cause the actual temperature at which the alloy starts to lose strength to vary around that mean, in a Gaussian distribution with standard deviation = 10.5 degrees F. If the manufacturer wants to ensure that no more than 1 in 10,000 of its commercial products will suffer from softening, what should it set as the maximum temperature to which the alloy can be exposed? Hint: Refer to the Excel NormSFunctions Spreadsheet. Excel NormS Functions Spreadsheet.xlsx 1 point 1496.281 1460.9503 F 39.0497 F 1539.0497 2. 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 1 point A Uniform Distribution with mean = 109 microns and standard deviation = .8607 microns. 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 = 11.54 microns. 3. 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 1 point 99.9827% .0173% 29.690% 1.73% 4. 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 1 point -.00219 -.0286 -.00573 -.00715 5. 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 1 point -.22 -.00573 0 -1 6. 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 1 point 10.44% 17.93% 11.79% 12.68% 7. 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? 1 point 10.43% 10.36% 9.5% 11.58% 8. Question 8 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. To the nearest dollar, what is the range above and below each Point Forecast required to have 90% confidence that the dollar value of next years’ orders will fall within that range? Hint: you can answer this question by making small modifications to the Correlation and Model Error Spreadsheet. Correlation and Model Error.xlsx 1 point The 90% confidence interval is from $164,831 below to $164,831 above the Point Forecast. The 90% confidence interval is from $83,831 below to $83,831 above the Point Forecast The 90% confidence interval is from $316,831 below to $316,831 above the Point Forecast. The 90% confidence interval is from $213,831 below to $213,831 above the point forecast. 9. 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 1 point 93.8% 12.4% 4.3% 6.2% 10. 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 1 point 4.2% 3.5% 4.5% 5.2% 11. 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 1 point 9.05% 90.85% 8.30% 9.15% 12. 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 1 point 53.3% 61.5% 57.2% 48.2%
*Please Wait 15 Seconds To Get The Pdf Loaded
