Question

A city council has estimated that it has a surplus of $100 Million (M). The council is exploring the following three projects, each of which costs $100 M. a. A bridge which takes five years to build, then yields $50M benefit per year for the next 10 years. The benefits are received at the end of each year. Assume that the costs are spread out evenly over the five years (i.e. year 1 to year 5) and are paid at the end of each year. b. Temporary classrooms for schools yielding $50M benefit per year for five years. The benefits are received at the end of each year. Assume that all costs are paid at the end of year 1. c. Tax breaks per year to a foreign auto manufacturer for a new auto plant yielding $20M benefit per year from the third year for next 5 years. The benefits are received at the end of each year. Assume that all costs (i.e. tax breaks) are paid equally at the end of year 1 and year 2. Assuming the discount rate of 5 percent, calculate the following for each of the projects: Discounted Benefits, Discounted Costs, Net Present Value (NPV) and Benefit Cost Ratio (BCR). Which one of the above projects would you suggest that the City Council can undertake based on the BCR? Explain your answer. You should be able to use Excel for this exercise.

Please post excel screenshot as well as how to calculate each cell.

Answer 1

Solution uploaded