Five years ago, a dam was constructed to impound irrigation water and to provide flood protection for the area below the dam. Last winter a 100-year flood caused extensive damage both to the dam and to the surrounding area This was not surprising, since the dam was designed for a 50-year flood.

The cost to repair the dam now will be $250,000. Damage in the valley below amounts to $750,000. If the spillway is redesigned for another $250,000 and the dam is repaired for another $250,000, the dam may be expected to withstand a 100-year flood without sustaining damage. However, the storage capacity of the dam will not be increased and the probability of damage to the surrounding area below the dam will be unchanged. A second dam can be constructed up the river from the existing dam for $1 million. The capacity of the second dam would be more than adequate to provide the desired flood protection. If the the second dam is built, redesign of the existing dam spillway will not be necessary, but the $250,000 of repairs must be done.

The development in the area below the dam is expected to be completed in 10 years. A new 100-year flood in the meantime would cause a $1 million loss. After 10 years the loss would be $2 million. In addition, there would be $250,000 of spillway damage if the spillway is not redesigned. A 50-year flood is also likely to cause about $200,000 of damage, but the spillway would be adequate. Similary, a 25-year flood would cause about $50,000 of damage

There are three alternatives: (1) repair the existing dam for $250,000 but not make other alterations, (2) repair the existing dam and redesign the spillway to take a 100-year flood and (3) repair the existing dam and build a second dam. Based on an expected annual cash flow analysis, and a 7% interest rate, which alternative should be selected? Draw a decision tree to clearly describe the problem.

Can You provide Excel Version of Annual flow analysis to find best alternative for above mentioned problem ??