Please use Excel and Frontline Solver to solve:

In the early 1980s, New England Electric System (NEES) was deciding how much to bid for the salvage rights to a grounded ship, the SS Kuniang. If the bid were successful, the ship could be repaired and outfitted to haul coal for the company's power-generation stations. But the value of doing so depended on the outcome of a U.S. Coast Guard judgment about the salvage value of the ship. The Coast Guard's judgment involved an obscure law regarding domestic shipping in coastal waters. If the judgment were to indicate a low salvage value (an outcome with an estimated 30 percent chance of occurring), and if NEES submitted the winning bid, then NEES would be able to use the ship for its shipping needs without additional costs. If the judgment were high, the cost to NEES would run $4 million higher than if the judgment were low. The Coast Guard's judgment would not be known until after the winning bid was chosen, so there was considerable risk associated with submitting a bid. If the bid were to fail, NEES could purchase either a new ship or a tug/barge combination, both of which were relatively expensive alternatives. Analysts at NEES estimated that purchasing a new ship for these purposes would lead to profits of $3.2 million (calculated as a net present value over 20 years of shipping). Purchasing a tug/barge combination would lead to profits of only $1.6 million. By comparison, if NEES could acquire the Kuniang at the low salvage value, it could achieve profits of $15.5 million, exclusive of its bid. One of the major issues was that the higher the bid, the more likely NEES would be to win. NEES judged that a bid of $2 million would definitely not win, whereas a bid of $12 million definitely would win. As a first cut, we can represent three bids:

1. A bid of $2 million would certainly not win.
2. A bid of $8 million would have a 60 percent chance of winning.
3. A bid of $12 million would certainly win.

• The goal for NEES was to maximize its profits from supplying coal to its plants.

Answer the following questions (parts a and c only):

(a) What is the best of the three bids for NEES? What is the optimal expected profit?

(c) Enrich the bidding submodel as follows. For a bid of x (in millions of dollars), the probability of winning is W(x), where W(x) is a linear function between x=2 and x=12. What is the best value of x?

• In part (c), consider only increments of $1 million
• $2 million = 0% chance of winning
• $3 million = 10% chance of winning
• $4 million = 20% chance of winning
• ...
• $12 million = 100% chance of winning