90-760: Decision & Risk Modeling Final Exam, Spring 2016Do not turn the page until you are told to do so. Note how much each problem is worthand budget your time. Total points possible: 221Name: ______________________________(Write answers in the space provided. If you need more space, you may use additionalsheet(s), but be sure to put your name on them and label with the problem number.)Forecasting (62 points)Q1: _________ out of 15 possible pointsQ2: _________ out of 12 possible pointsQ3: _________ out of 10 possible pointsQ4: _________ out of 25 possible pointsSimulation & DM under Uncertainty (95 points)Q5: _________ out of 20 possible pointsQ6: _________ out of 10 possible pointsQ7: _________ out of 5 possible pointsQ8: _________ out of 15 possible pointsQ9: _________ out of 20 possible pointsQ10: _________ out of 15 possible pointsQ11: _________ out of 10 possible pointsQueueing (32 points)Q12: _________ out of 12 possible pointsQ13: _________ out of 20 possible pointsProject Management (32 points)Q14: _________ out of 20 possible pointsQ15: _________ out of 12 possible pointsQuestion #1: (15 points)I’ve pasted below a screen shot from the workbook we used in class to fit a linear regression to time series dataand then adjust that trend with both additive and multiplicative seasonal indices.a) What is the Excel formula in cell E25?b) What number (not formula) belongs in cell H25?c) What number (not formula) belongs in cell I25?[If you do not have a calculator, you can get full credit for writing a simple arithmetic statement whose numberswill evaluate to the correct number. Again, I do not want you to give the Excel formula for parts b & c.]Question #2: (12 points)Which of the following time series display seasonal variation? (Circle all that apply)a) Average annual temperature in Pittsburghb) Daily data on the number of people eating at the Subway on Craig Streetc) Annual voter turnout in American electionsd) Unemployment rates over the course of a business cyclee) Attendance at the final game of the “March Madness” NCAA basketball tournamentf) Average hourly arrivals at an emergency room over the course of a 168-hour weekQuestion #3: (10 points)The spreadsheet below applies the k-nearest neighbor rule after standardizing the data by converting them intoz-scores and using straightforward Pythagorean distances. The distances for the 4th observation in theclassification data set are shown in Column N. What group does this method predict that that observationbelongs to using the 1-, 3-, and 5- nearest neighbor rules? Question #4: (25 points)I ran a discriminant analysis and obtained the following classification table. Assume that Group 2 correspondsto the condition in question being present.a) Fill in the seven missing numbers in Cells A8:A11, A13:14, and A16. (Fill in with numbers, notformulas.)b) Sketch and label the axes of an ROC curve and plot the point associated with this table on those axes. Question #5: (20 points) Stock A has a 40% chance of going up by $5 and a 60% chance of going down by $2. Independent of that, Stock B has a 50%/50% chance of either going up by $3 or down by $2.a) Create a payoff matrix that compares three alternatives concerning what to buy now and then sell after theprice change:Alternative #1: 2 shares of Stock AAlternative #2: 2 shares of Stock BAlternative #3: 1 share of eachb) Compute the expected value of each alternative.Question #6: (10 points)I’ve pasted below the risk-return frontier produced by a simulation describing net revenues earned by a hospital.a) Draw a smiley face in the most desirable corner of the risk-return frontier.b) Circle the dots corresponding to the option(s) that are not on this frontier. Question #7: (5 points)I ran another (different) Monte Carlo simulation and obtained the following distributions of outcomes forOptions #1 and #2. Which cumulative risk profile corresponds to Option #1, the solid line or the dashed line?Question #8: (15 points)I ran a simulation comparing total project cost (in thousands of dollars) for three options for expanding theCollege’s classroom space to accommodate expanded increases in enrollment. (See cumulative risk profilesbelow.)a) Which option would you recommend?b) Which pair of options display 1st order stochastic dominance?c) Which pair of options display 2nd order stochastic dominance? d) Describe a set of risk preferences under which a decision maker might prefer Option #2 over Option #3.e) Describe a set of risk preferences under which a decision maker might prefer Option #3 over Option #2.Question #9: (20 points). A city is trying to sell a parcel of land. It has a firm offer in hand for $100,000, andfour more prospects whose chances of materializing vary from 30% to 70%. If Prospect #1 makes an offer, itwould be for $125,000. It is unknown how much Prospect #4 would offer, but the city judges that offer can bemodeled by a normal random variable with mean $105,000 and standard deviation $5,000, if it comes throughat all. The other two prospects – if they make any offer – are expected to submit offers of between $75,000 -$125,000 and $85,000 - $125,000, as indicated in the (partial) spreadsheet below.The offer in hand expires in 24 hours. If none of the other four prospects come through, the city would be stuckselling at a fallback price of $50,000. The city has negotiated the ability to extend the current offer by a weekby paying $5,000, with a week being long enough to hear from the first two prospects, or extend it by twoweeks for $10,000, which would allow the city to hear from all four other prospective bidders.The city has four sensible strategies:1) Accept the current offer2) Pay $5,000 to extend the offer for a week and sell to the best offer as of that time3) Pay $10,000 to extend the offer for 2 weeks and sell to the highest bidder4) Let the current offer lapse and sell 2 weeks from now to the highest bidder – either one of the fourprospects or the fall back sale for $50,000.Indicate what formulas you would write in the following cells to simulate the payoffs for these four strategies.a) Cell H7: _______________________________________________b) Cell H8: _______________________________________________c) Cell H9: _______________________________________________d) Cell H10: