90-760: Decision & Risk Modeling Midterm Solution, AM Section Spring 2015The first two questions pertain to the following spreadsheet for implementing managerial forecasting methodson a stationary time series for weekly sales. (Sales are always integer valued, so the 10 in Week #1 meansexactly 10.000000; it is not a real number that has been rounded off.)Question #1: (12 points)Fill in the forecasts the three methods make for Week #15 by placing those values in Cells C22:E22.Solution:FormulasCell B22: =C21+C$3*(B21-C21)Cell C22: =AVERAGE(B18:B21)Cell D22: =SUMPRODUCT(B19:B21,E$1:E$3)Question #2: (9 points)a) Which method gives the best average fit to the historical data?b) Suppose one wished to use Solver to parameterize the 3 Period WMA forecasts. Which cell or cellswould contain the decision variable(s)?c) Which cell or cells would contain the objective function?Solution:Exponential Smoothing has the smallest MSE at 7.554Cells E1:E3Cell E5Question #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 10years 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 10 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 10 years? Solution$93,283 - $252,128$162,514 +/- 1.65 * $50,324 / SQRT(40,000) = $162,100 - $162,928A shade over 20%Question #7: (27 points)Often it is of interest to calculate the distribution of distances between two randomly chosen points. Forexample, consider a rectangular city that is 20 miles east-west and 10-miles north-south. Suppose that at anygiven time the city has one police vehicle that is equally likely to be anywhere in the city and a 911 emergencyoccurs at some other location that is also equally likely to be anywhere in the city. One might be interested inhow far the police must travel to get to the emergency. Here we’ll ask a particular version of this question. How much farther would a police *car* have to travel, ifthe car must move either east-west along streets or north-south on avenues but cannot drive directly toward theemergency by “cutting a diagonal” as opposed to the distance a police helicopter or UAV drone would travel ifit could fly directly to the emergency without having to follow the rectangular street grid. I address this by running an MC Simulation with 10,000 trials in the way we’ve been doing in this course with a(partial) screen shot as shown below. What formulas did I use in the grey-shaded cells:Solution:B8: =B$3+(B$4-B$3)*RAND()B9: =B$3+(B$4-B$3)*RAND()B12: =SQRT(SUMXMY2(B8:C8,B9:C9))B14: =ABS(B8-B9)+ABS(C8-C9) or SQRT((B8-B9)^2) + SQRT((C8-C9)^2)B16: =B14-B12F2: =AVERAGE(F$20:F$10019) -- Many people gave range F$20:F$10020; I didn’t mark off for thatF3: =STDEV(F$20:F$10019)F6: =PERCENTILE(F$20:F$10019,$E6)F19: =B16:Question #8: (6 points)The distribution obtained from this simulation 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 0 and about 6.5 with the mode being just a shade above 0. Onecould also view it has the right hand side of a normal or bell-shaped distribution that is centered justabove 0 and has a standard deviation of around 3. In either case, lower values are more likely thanhigher values. Note: You must speak of the outcome