Please answer using excel sheet
Problem 1 (50 points) Tempe is considering replacing its fleet of gasoline powered cars with electric cars. The manufacturer of the electric cars claims a significant cost savings over the life of the fleet if Tempe chooses to pursue the conversion. If the manufacturer is correct, the city will save about $1.5 million. However, some critics argue that the technology is faulty and will result in significantly higher costs - estimated at a loss of $700,000 over the life of the fleet compared to gasoline-powered cars. A third possibility, suggested by independent analysis of an engineering firm last year is that the conversion will breakeven. A consultant hired by the city to assess the conversion program estimates the probabilities of the three outcomes - savings, loss, breakeven - at 30%, 30%, 40%, respectively. The city has the opportunity to implement a pilot program that involves renting a small number of electric cars for three months and running them under typical conditions. This program would cost the city $75,000. The city's consultant believes the pilot program results will be useful for estimating the value of the conversion, but not conclusive. She has prepared the following table of probabilities based on the experience of other cities after 3- months of usage. For example, given that a conversion actually results in savings of $1.5 million, the conditional probabilities that the pilot will indicate that the city saves money, loses money, and breaks even are 60%, 10%, 30%, respectively. Indication of outcome Actual outcome over the life of the fleet from pilot program Savings Loss Breakeven Indicates savings 60% 10% 40% Indicates loss 10% 40% 20% Indicates breakeven 30% 50% 40% Questions a. Assuming the city wishes to use expected payoff as the criterion for decision making, use Precision Tree to construct and solve a neatly labeled decision tree for Tempe's decision problem. Express the optimal strategy verbally and state the corresponding optimal expected payoff. (25 points) b. Assess the sensitivity of your recommendation from part (a) to errors in some of the inputs. The inputs with the greatest uncertainty in their values are the dollar loss and the savings and loss probabilities. The loss estimate could be as much as 50% worse and no more than 10% better (e-g., payoff as low as -$1.05 million and as high as -$630,000). The savings probability could be as much as 50% lower and no more than 10% higher (e.g., as low as 15% and as high as 33%); the loss probability could be as much as 10% lower and no more than 50% higher.' Use the appropriate sensitivity tool in Precision Tree to identify when and if your optimal strategy changes as these inputs are varied (you may use 7 steps, i.e., 7 values of a parameter as it ranges from high to low). Express the results of your sensitivity analysis verbally, identifying regions of parameter values where the optimal strategy may change from your recommendation in part (a). (15 points) c. Does your recommended strategy from part (a) change if the city is risk averse with a risk tolerance parameter of $1 million (with an exponential utility function)? Express the optimal strategy verbally and state the corresponding optimal expected payoff and the certainty equivalent. (5 points) d. What is the expected value of information (EVI) under the assumptions of part (c)? Justify your answer using results generated by Precision Tree (i.e., explain how you arrived at your answer from information in your decision tree). (5 points)
