Suppose that a Frankford town homeowner is considering a project to plant several trees in her front and back yards. Planting the trees would involve an upfront (year 0) expense of $1,000. Starting in year 1, the homeowner would incur an additional expense of $100 per year to maintain the trees (trimming, monitoring health, etc.). The trees would provide several benefits directly to the homeowner, including shade (lowering cooling costs in the summer) and improving the scenery. The monetary value of these benefits to the homeowner is $150 per year, starting in year 1. The trees would also henefit to the residents of the neighborhood and beyond. The trees would reduce air pollution, reduce ambient noise levels in the neighborhood, reduce stormwater runoff, and improve the aesthetics of the area, among other possible benefits. The monetary value of these external benefits is $100 per year, starting in year 1. Scenarios Scenario A (Baseline)-For your baseline BCA, assume that the trees will last 20 years after planting (t.e, through year 20). Assume that the project should be evaluated with a 5% discount rate. For this scenario, consider only the benefits and costs to the homeowner. Scenario B (External benefits)-For this scenario, use the same assumptions as the Baseline scenario, except that you include the external benefits of the trees (to other residents of the neighborhood and beyond), as well as the benefits to the homeowner, in the analysis. Scenario C (Longer life) - For this scenario, use the same assumptions as the Baseline scenario, except that you assume that trees will last 30 years (Le, through year 30) after planting Scenario D (Low discount rate)-For this scenario, use the same assumptions as the Baseline scenario. except that you use a 2% discount rate in the analysis. Scenario E (High discount rate) - For this scenario, use the same assumptions as the Baseline scenario, except that you use a 10% discount rate in the analysis Scenario F (Low costs)-For this scenario, use the same assumptions as the Baseline scenario, except that the annual expense for maintaining the trees is only $50, rather than $100 Questions Set up a spreadsheet to conduct a benefit-cost analysis (BCA) of this situation. (The sample lines below provide some guidance for setting up your spreadsheet.) For each scenario, use your spreadsheet to find the following values. You may want to write your answers in table provided. I Total benefits (undiscounted) - Find the sum of all benefits considered for that scenario over the life of the project. Do not discount future benefits. Total costs (undiscounted) - Find the sum of all costs over the life of the project. Do not discount future costs. Net present value (NPV) - Find the net present value of the project (i.e., the sum of the discounted net benefits over the life of the project). Implement? - Based on the net present value of the project, does benefit-cost analysis suggest that the project should be implemented (yes or no)? The following are the first few lines from a spreadsheet based on this problem (scenario A). You can use them for guidance in structuring your spreadsheet and making sure that your formulas are correct (at least for the first couple of lines). Year Benefits 0 1 2 3 0 150 150 Etc. Costs Net Benefits Discount 1000 100 100 Etc. -1000 50 50 Etc. 5% 5% 5% Etc. Present Value -1000 47.62 45.35 Etc.