90-760: Decision & Risk Modeling Midterm Solution, Spring 2017Question #1: (6 points)With regard to some random quantity of interest Y, we contrasted the 90% confidence interval for its mean E[Y]and the 90% confidence interval for Y itself. Assuming you have built a Monte Carlo simulation of thisimportant outcome Y, concisely and precisely explaina) How you would create each 90% CI from the simulation resultsb) The conditions under which one vs. the other would be more relevant for decisions makers thinkingabout Y.Solution: The 90% CI of E[Y] = sample average +/ 1.65 * sample standard deviation / SQRT(n),where thesample average is found by taking the =AVERAGE() of the simulation outcomes,sample standard deviation likewise =STDEV() of those outcomes, and n = # of trials in the simulation.The 90% CI for Y is obtained just by reading off the 5th and 95th percentiles of the list of simulation trialresults. The first is relevant to decision makers who will get to “roll the dice” (confront this particular uncertainty)many times, sufficiently many that the law of large numbers applies and the average outcome will convergeto E[Y]. The 2nd CI is relevant for decision makers who “only get to live once” (confront this particularuncertainty once).Question #2: (12 points)Suppose you had built a spreadsheet that models some outcome of interest Y as a function f(X) of one or moreunknown inputs X. We noted in class that E[f(X)] is not necessarily the same as f(E[X]). a) How would you compute f(E[X]) with your spreadsheet?b) How would you compute E[f(X)] with your spreadsheet?c) Under what conditions would they be same?d) When they differ, which is more informative about the central tendency of the unknown quantity ofinterest Y?Solution: a) Plug in your best guess for all unknowns in your spreadsheet model.b) Model those unknowns with random variables (“cells that wiggle”), connect that stochastic model to a Monte Carlo simulation and find the average result from a suitably large number of simulation trials.c) If the function f() is linear.d) The 2nd, E[f(X)].Question #3: (16 points)I’ve posted below a simplified version of a spreadsheet we used in class for doing time series forecasting,specifically using regression with a quadratic trend supplemented with additive seasonal adjustments. It appliesthe same method you would have used on the sea ice data in the first homework.a) What formula belongs in Cells F5? __________________________________________b) What formula belongs in cell G5? __________________________________________c) What formula belongs in cell H25 if you want to be able to copy that formula down to all of the otherrows? __________________________________________d) How is the -5409 in cell K5 calculated? (You can simply give the formula, but since it is trickier thanmost, you can get full credit for a clear English sentence explaining what it does.)Solution:a) =TREND(E$5:E$24,C$5:D$24,C5:D5) (dollar signs not necessary)b) =E5-F5c) =$F5+VLOOKUP(B5,J$5:K$8,2,0) (partial credit for F5 + K5)d) =AVERAGEIF($B$5:$B$24,$J5,G$5:G$24) or the statement that this cell contains the average ofall of the errors in column G for seasons that are “Winter” seasons as indicated in column B. Question #4: (20 points)I ran discriminant analysis with the DA.xla add-in on a data set concerning job applicants and obtained theresults shown below, with some rows not shown (rows 11-24).a) How many variables were used to make these forecasts?b) How many observations were there in the training and classification data sets, respectively?c) Fill in the 9 missing cells marked in grey, including the % correct for the training sample. (If theshading is hard to read on the printed exam, these are cells E26, E27, E32, B33, B34, D34, B36, E42, &E43.)d) What is the meaning of the number 85.00 in cell B5?e) What is the meaning of the number 2.97 in cell B9?Solution:a) There are 4 predictors, X1, X2, X3, and X4.b) 20 & 5. c) See below.d) The average value of predictor X1 among those in the training data who were truly in Group #3 was 85.0. e) The first observation in the training data was 2.97 units of “distance” away from the centroid for group#1.Question #5: (18 points)Below is a 2 X 2 “confusion matrix” for an (imperfect) cancer screening test. Fill in the nine quantities belowthe matrix. Note: As I warned in recitation, I have jumbled up rows and columns relative to the display I usedin class, so you need to think through what is a “true positive” vs. a “false positive” from first principles, notjust by remembering that this or that quantity is associated with the lower right or lower left cell. To be clear,the actual condition we are testing for is cancer being present. Solution:Question #6: (9 points)I modified the symphony net revenue model we used in class to make the random variables follow a triangledistribution and adjusted a number of the parameters. The result of 10,000 simulation trials is show below.a) What Excel formula belongs in Cell I2? __________________________________________?b) What Excel formula belongs in Cell I17? __________________________________________?c) Based on this simulation, approximately what is the probability that the symphony will have negativenet revenues?Solution:a) =AVERAGE(I18:I10017) (=AVERAGE(I17:I10016) and =AVERAGE(I19:I10018) acceptablesince it is a little hard to read the row label with the graph in the way)b) =F14c) 24% (answers between 20% and 25% are acceptable. Partial credit if you said “between 10% and25%. You should be able to tell it is much closer to 25% than to 10%.) Question #7: (24 points)Below is a screen shot of a payoff matrix indicating the cost in dollars for each of 8 alternatives under 5 possiblestates of nature, along with its associated calculations & risk-return frontier plotting standard deviation vs.expected value (i.e., expected cost). a) What numbers belong in Cells I4, K4? __________________________________________b) What Excel formula belongs in Cell I5? __________________________________________c) What Excel formula belongs in cell J5? __________________________________________d) What Excel formula belongs in cell L5? __________________________________________e) What option should a risk neutral decision maker prefer?f) Draw a smiley face in the most desirable corner of the risk-return frontier.g) Circle the dots