Need solution in excel with formulas
Question: A city is considering replacing its fleet of gasoline-powered automobiles with electric cars. The manufacturer of the electric cars claims that this city will experience significant cost savings over the life of the fleet if it chooses to pursue the conversion. If the manufacturer is correct the city will save about $1.5 million. If the new technology employed within the electric cars is faulty, as some critics suggest, the conversion to electric cars will cost the city $675,000. A third possibility is that less serious problems will arise and the city will break even with the conversion. A consultant hired by the city estimates that the probabilities of these three outcomes are 30%, 30% and 40% respectively. The city has an opportunity to implement a pilot program that would indicate the potential cost or savings resulting from a switch to electric cars. The pilot program involves renting a small number of electric cars for three months and running them under typical conditions. The program would cost the city $75,000. The city's consultant believes that the results of the pilot program would be significant but not conclusive. She submits a compilation of probabilities based on the experience of other cities to support her contention. For example, the first row of her table indicates that given a conversion to electric cars results in a savings of $1.5 million, the conditional probabilities that the pilot program will indicate that the city saves money, loses money, and breaks even are 60%, 10%, and 30%, respectively. Electric car data Actual outcome of conversion Savings Loss Break even Pilot program indication Savings Loss Break even 60.00% 10.00% 30.00% 10.00% 70.00% 20.00% 30.00% 10.00% 60.00% 1. What actions should the city take to maximize its expected savings? 2. When should it run its pilot program, if ever? 3. How much will city save? Question: A city is considering replacing its fleet of gasoline-powered automobiles with electric cars. The manufacturer of the electric cars claims that this city will experience significant cost savings over the life of the fleet if it chooses to pursue the conversion. If the manufacturer is correct the city will save about $1.5 million. If the new technology employed within the electric cars is faulty, as some critics suggest, the conversion to electric cars will cost the city $675,000. A third possibility is that less serious problems will arise and the city will break even with the conversion. A consultant hired by the city estimates that the probabilities of these three outcomes are 30%, 30% and 40% respectively. The city has an opportunity to implement a pilot program that would indicate the potential cost or savings resulting from a switch to electric cars. The pilot program involves renting a small number of electric cars for three months and running them under typical conditions. The program would cost the city $75,000. The city's consultant believes that the results of the pilot program would be significant but not conclusive. She submits a compilation of probabilities based on the experience of other cities to support her contention. For example, the first row of her table indicates that given a conversion to electric cars results in a savings of $1.5 million, the conditional probabilities that the pilot program will indicate that the city saves money, loses money, and breaks even are 60%, 10%, and 30%, respectively. Electric car data Actual outcome of conversion Savings Loss Break even Pilot program indication Savings Loss Break even 60.00% 10.00% 30.00% 10.00% 70.00% 20.00% 30.00% 10.00% 60.00% 1. What actions should the city take to maximize its expected savings? 2. When should it run its pilot program, if ever? 3. How much will city save
