City of Huntsville is considering an energy-saving project. Depending on several unpredictable factors, the project may or may not be successful. It estimated that the savings will be about $2.5 million if successful and the loss will be $500,000 otherwise. Base on the experiences in other similar cities, the City estimated the success rate for the project is 0.4. A small pilot program can be tested to better estimate if the project will be successful; it would cost $100,000. A UAH researcher compiled the following probabilities based on her research. For those successful projects, 65% of them indicate savings in the pilot project. For those projects ended up with loss, 80% of them indicate loss in the pilot project. The mayor would like to know if the city should conduct the pilot project and if it should implement the whole project. Help the mayor analyze the problem and decide the best course of action.

a) Make a recommendation to the mayor as to what actions to take and what outcomes might be in terms of the expected savings based on your analysis of the problem. Construct a model and solve it on the spreadsheet. What kind of problem is it? What’s your recommendation and estimated savings?

b) Incorporate in your model an input and an output module so it allows the user to do what if analysis by simply changing the values of input parameters.

c) How would the estimated loss of the project impact the optimal decision? Answer the question from the results of a data table. Thus, you should create a data table first, which should include a list of possible loss (ranging from $100,000 to $1,000,000 with an increment of $50,000) and their outcomes. Summarize the outcomes.