A city council has estimated that it has a surplus of $100Million (M). The council is exploring the following three projects,each of which costs $100 M. a. A bridge which takes five years tobuild, then yields $50M benefit per year for the next 10 years. Thebenefits are received at the end of each year. Assume that thecosts are spread out evenly over the five years (i.e. year 1 toyear 5) and are paid at the end of each year. b. Temporaryclassrooms for schools yielding $50M benefit per year for fiveyears. The benefits are received at the end of each year. Assumethat all costs are paid at the end of year 1. c. Tax breaks peryear to a foreign auto manufacturer for a new auto plant yielding$20M benefit per year from the third year for next 5 years. Thebenefits are received at the end of each year. Assume that allcosts (i.e. tax breaks) are paid equally at the end of year 1 andyear 2. Assuming the discount rate of 5 percent, calculate thefollowing for each of the projects: Discounted Benefits, DiscountedCosts, Net Present Value (NPV) and Benefit Cost Ratio (BCR). Whichone of the above projects would you suggest that the City Councilcan undertake based on the BCR? Explain your answer. You should beable to use Excel for this exercise.
Please post excel screenshot as well as how to calculate eachcell.
