90-760: Decision & Risk Modeling Midterm Solution, PM Section Spring 2015Question #1: (15 points)Below is the spreadsheet template we used in class to demonstrate forecasting with a quadratic trendsupplemented with seasonal indices. Write the Excel formulas for:a) Sum of squared errors in Cell F1: ___________________b) Cell F25: ____________________c) Cell G5: ________________Give the numerical value of the forecast for Summer 2015 that would go in: d) Cell I27: __________________e) Cell J27: __________________Solution:=SUMXMY2($E5:$E24,F5:F24) [Aside: Would be fine to multiply that by a constant]=TREND(E$5:E$24,C$5:D$24,C25:D25) [Dollar signs not strictly necessary]=E5-F525,056 + 4,685 = 29,74125,056 * 137% = 34,327 [Actual value is 34,394 but the 137% has been rounded off]Question #2: (6 points) Suppose one had a time series with 14 data points and wanted to forecast the value forperiod #15. For each method mentioned, how many of the historical data points would influence or play a rolein producing the forecast for period #15? a) Exponential smoothing: ___________________b) 4-period moving average: ____________________c) 3-period weighted moving average: ________________Solution:Exponential Smoothing: All 144-Period Moving Average: 43-Period Weighted Moving Average: 3Question #3: (16 points)I ran discriminant analysis with the DA.xla add-in on a data set (at left) with 16 past and 5 current observationsand obtained the results shown (on the right).a) Plot the centroids (as squares) and five classification sample observations (as dots) on the axes providedb) Give the confusion matrix for this analysis. SolutionQuestion #4: (9 points)For the data set graphed below, how would the point at the coordinates (0.18, -0.2) be classified by:a) The k-neighbor rule with k = 1b) The k-neighbor rule with k = 3c) The linear discriminant function shown in the graph?SolutionRed square, blue diamond, and red square, respectively, so Groups #2, #1, and #2. Question #5: (12 points)I need to hire a plumber to fix some leaky pipes. The job is big enough that I should get multiple quotes so Ican hire the plumber quoting the lowest price for the job, but it is not clear how many quotes I should obtain.Suppose I believe that the variability from plumber to plumber in prices quoted can be modeled as beingnormally distributed with a mean of $500 and a standard deviation of $125 (i.e., 95% of the quotes will bebetween $250 and $750). And suppose that I value the time and hassle of getting a quote and checking outonline quality reviews of the plumber at $20 per quote obtained. Then I can create a simple Monte Carlosimulation comparing the total cost – including monetary cost paid to the person hired plus the monetary valueof my time – for the alternative strategies of getting just 1 quote, getting 2 quotes, 3 quotes, etc.I created such a simulation and pasted below the risk-return frontier obtained for the strategies of getting 1 to 22quotes.a) Augment the frontier with a smiley face indicating the ideal corner.b) If I were risk neutral, which dot on the frontier would I prefer?c) If I were risk neutral, which dots are not on the efficient frontier because they are dominated by someother strategy?d) Indicate on the graph which strategies correspond to which dots. (You don’t need to label all 22. Justenough to make it clear to the graders that you understand which are which.) Solutiona) Smiley face goes in the lower left (low cost and low risk)b) The dot farthest to the left, which is the fourth dot starting from consulting with just one plumber(Expected cost of $500 + $20 to get quote = $520, standard deviation of $125)c) The first three dots moving to the lower left from ($525, $125) are not on the frontierd) The dot at ($525, $125) corresponds to getting just one quote. The dots moving first lower left thenlower right correspond to getting one more quote, ending with getting 22 quotes being around point($706, $66).Question #6: (12 points)I ran a Monte Carlo simulation with 40,000 to trials to model the final balance if one invested $100,000 for 8years in a fund whose annual return is normally distributed with a mean of 5% and a standard deviation of 10%.The results are:a) What does the simulation suggest is a 90% confidence interval for the amount of money that will be inthe account after 8 years?b) What does the simulation suggest is a 90% confidence interval for the mean of the distribution? c) What is the probability the fund balance will be $200,000 or greater at the end of the 8 years? Solution$89,933 - $221,556$148,340 +/ 1.65 * $40,726 / SQRT(40,000) = $148,006  $148,675A little over 10%Question #7: (24 points)Greg Greedy (GG) will take whatever job offers the highest pay. He is pursuing three professional jobs. Gregis sure he’ll get an offer from two of those employers, but is uncertain what the salary would be. The first offercould be anywhere between $40,000 and $55,000 per year, with all values equally likely. He thinks theuncertainty concerning the salary for the second offer can be well modeled as a normally distributed randomvariable with a mean of $50,000 and a standard deviation of $5,000. For the third, he thinks there is only a 20%chance he’ll get an offer, but if he does, it will be $70,000 per year. Greg created a Monte Carlo simulation with 10,000 trials in the way we’ve been doing in this course to estimatethe characteristics of the distribution of his salary next year, with a (partial) screen shot shown below. Whatformulas did Greg use in the grey-shaded cells? (Greg treated salary as a continuous variable; you do not needto worry about using =INT() or =ROUND() to convert the values into integers.)Solution:B9: =B5 + (B6B5)*RAND()C9: =NORMINV(RAND(),C5,C6)D9: =IF(RAND()<D5,D6,0) [or any of several other options]B11: =MAX(B9:D9) [Key here is MAX() not AVERAGE()]G2: =AVERAGE(G$20:G$10019)G3: =STDEV(G$20:G$10019)G6: =PERCENTILE(G$20:G$10019,$F6)G19: =B11:Question #8: (6 points)The distribution obtained from the simulation in the previous problem is pasted below.a) Would this uncertain quantity be well modeled by a normal random variable?b) Describe in a sentence or two the general shape or meaning of the distribution.Solutiona) No, not even close.b) It is basically triangular between $40,000 and about $65,000 augmented with a lump at $70,000 –specifically a 20%