Spreadsheet Exercise
Background:
The State of Florida is considering the economic impact of developing a portion of their coastline. They have two potential projects with varying objectives. Project #1 is to develop a marine park with a new beach, set of condominiums, boardwalk, and a boat marina. Project #2 is a coastal mining project that will harvest limestone from an area of old coral reefs. The coral reefs died many years ago due to ocean acidification and physical damage from boating and scuba diving. The dead coral reef area will yield significant limestone for construction materials, provide for the harvesting of calcium supplements from dead corals, and reduce pressure on live coral reefs which are under constant threat from souvenirs hunters looking to make jewelry. The dead coral reef area is also serving as a coastal erosion barrier.
Requirement 1: Build a Net-Present Value (NPV) spreadsheet to determine the economic value of both alternatives. For each potential project, the spreadsheet should combine the annual estimated costs and benefits for the next TEN years with two potential discount rates (5% & 10%). Use discrete discounting (not continuous).
A - Project 1: In Year 0 (this year), the Marine Park will cost $1.250 billion to construct. Each year (including Year 0), the Marine Park will also incur a foregone annual recreation cost of $20 million, and starting in Year 1 through Year 10, there is an annual recurring cost of $130 million. The project will yield annual benefits in increased tourism revenue of $300 million starting in Year 0 and continuing through Year 10.
B - Project 2: In Year 0, the mining project will cost $3 billion in extraction costs. Also, the project will cost $200 million each year (starting in Year 0) to abate coastal erosion due to the missing coral reefs. The project will generate $4.5 billion in revenue in Year 0 only.
Requirement 2: Conducting a sensitivity analysis with your discount rate, determine the approximate rate (to the thousandths place) that will make you indifferent between the two potential projects.
Requirement 3: List and describe other costs and benefits not reflected in the above given information that should be included in the analysis. Be sure to describe the ways used to gather this data and quantify it in monetary terms.
Requirement 4: Describe how your choice of project changed depending upon the two given discount rates (5% or 10%). Explain some criteria for selecting a discount rate, and how you would justify them to a public official.
Attachment:- Spreadsheet Exercise.rar