Can you send me a final answer picture like the picture at bottom and show me your solutions?

Assignment:

The project under consideration is a recreational complex. The proposed complex includes an extensive outdoor playground, outdoor baseball and soccer fields, and a small camping area. The base case discount rate is 5%. The project time period (n) is 11 years, meaning it starts in 2019 (t=0) and ends in 2029. The main benefit categories are benefits to team sports, camping benefits and playground benefits. Costs include land purchase costs, building costs, equipment costs and labour costs.

Tasks:

1. Set up an excel worksheet in the same fashion as the template provided below. You should use formulas wherever possible. You may also find it useful to draw a timeline.

2. Using the detailed benefit and cost information provided below, input the costs and benefits for each category into the excel worksheet. Include a formula to determine total benefits and costs in each year.

3. You are going to calculate the present value of the benefits and costs separately, and then the net present value for the project, by following a series of steps. I will outline the steps for benefits.

Input a formula for the discount factor for each year in the appropriate cell (starting at C21) in the excel workbook. The discount factor is given as: 1(1+) where t refers to the year index.

The discount factor for t=0 is always 1 since benefits and costs in this year are not discounted. Given a discount rate of 5%, the discount factor for year 3 (2022) is equal to 1/(1+i)3 or 0.8638 and would appear in cell F21. Thus, the formula will reference the cell with the base case discount rate (i.e. D2), the cell for the appropriate benefit category (i.e. total benefits) and the cell referring to the appropriate year index for the exponent (F6).

4. Net present value calculation I: In the check benefits row (row 23), calculate:

(a) the discounted value of benefits in each year using a formula. For example, discounted year 2 benefits, the formula in cell E23 multiplies total benefits in year 2 (E11) by the discount factor for year 2 (E21).

(b) the present value of benefits by adding up discounted benefits over all years using an appropriate formula in O23. Highlight this amount.

Follow the same procedure to find discounted costs in each year (check costs row) and the present value of costs (O24). Highlight the present value of costs.

In O25, enter a formula to find the difference between O23 and O24. This is the NPV of the project given a discount rate of 5%. Highlight this amount. Be sure to check your formulas and calculations for mistakes.

5. Net present value calculation II: Next, calculate the present value of benefits and costs using the NPV formula in excel. For benefits, enter the NPV formula in cell O11 to find the PV of benefits given the base case discount rate of 5%. In cell O17, use the NPV formula to find the PV of costs. These calculations should yield amounts identical to those you found in 4 (a) and (b) above. If not, you are probably using the NPV formula incorrectly or have not made necessary adjustments. You may find it helpful to refer to a timeline and you might want to read about how the NPV works in excel. Enter the NPV of the project in cell O19 using an appropriate formula. Highlight this amount.

6. Redo the NPV calculations for three alternative discount rates: 3%, 8% and 10%. Use the procedure outlined in tasks 3 and 4 if you are unsure about whether you are using the NPV formula correctly or if you cannot get the two procedures to yield the same numbers. The results for NPV given the alternative discount rates can be entered either in N19, P19 and Q19 (if using the excel NPV formula) OR add additional rows below to repeat the calculations as outlined in tasks 3 and 4 (as illustrate below). Discount factor	at 5%
check benefits	at 5%
check costs
Discount factor	at 3%
check benefits	at 3%
check costs

7. Create a line graph (below all your base case calculations) to show how the NPV changes as the discount rate increases from 3% to 10%. Given this graph, what is the internal rate of return (approximately) for this project? Indicate your answer somewhere near the figure.

8. Sensitivity analysis: You will undertake some sensitivity analysis because there is some uncertainty as to the magnitude of the benefits from the project. For all calculations, use the base case discount rate of 5%.

Copy your worksheet. Your excel file should have two sheets. Label the first sheet base case and the second sheet sensitivity analysis.

In the sensitivity analysis sheet, recalculate the NPV of the project following the procedures outlined above assuming that total benefits are (i) x%, z% and w% of the base case (where x, z and w are less than 100). You can choose the values for x, z and w.

9. Below all of your calculations, create a line graph to show how the NPV of the project changes given different assumptions about the value of total benefits. (See figure 3.1 panel (a) on page 63 in the textbook for an example of what the figure will look like).

10. Make another copy of your base case sheet. Label this Horizon value. Use the base case discount rate for this component.

Your original calculations assume that benefits and/or costs do not extend beyond 2029, meaning that the horizon or residual value is assumed to be zero. Instead, suppose that the only residual value comes from the selling the land at the end of the project in year 2029. Following the Boardman and Forbes (2011) paper and their treatment of land, assume that the price of land is unchanged over this period. Redo your NPV calculation taking into account the horizon value. Highlight the new NPV for the project.

3%

5%

Benefit and Cost Details:

Team Sports: Benefits to team sports start in 2021. These benefits are valued at $90,000 for 4 years and then increase to $110,000 for the remaining 5 years of the project.

Camping Benefits: These benefits start in 2022. They are valued at $25,000 for the first two years and then increase to $45,000 a year for the remaining life of the project.

Playground benefits: These benefits start in 2020. They are initially valued at $13,165 for the first three years. Due to population growth, the value of these benefits in the following four years are equal to $21,000. For the final three years of the project playground benefits are valued at $23,000.

Land Purchase Costs: $25,000, incurred immediately in 2019 (i.e. t=0).

Building Costs: Building costs of $3,300 are incurred in all years (2019 to 2029 inclusive).

Labour costs: Labour costs of $100,000 are incurred in all years (2019 to 2029 inclusive).

Equipment costs: Equipment costs of $7,000 are incurred in all years (2019 to 2029 inclusive).

1 PVof benefits = Bo + efits = Bo + (1+- + (1+i)n Column C DE FG H N O 1 BA ofa recerational area 2 iscount rates 0.03 0.05 0.08 0.1 2019 2020 2021 2022 202320242025 2026 2027 2028 2029 NPV NPV NPV NPV car index 103% enefits tem sports camping Playground Total benefits TEMPLATE buildings abour equipment and purchase Total costs Costs NPV 21 0scount tactor at 5% 23 chck benofits check costs 1 PVof benefits = Bo + efits = Bo + (1+- + (1+i)n Column C DE FG H N O 1 BA ofa recerational area 2 iscount rates 0.03 0.05 0.08 0.1 2019 2020 2021 2022 202320242025 2026 2027 2028 2029 NPV NPV NPV NPV car index 103% enefits tem sports camping Playground Total benefits TEMPLATE buildings abour equipment and purchase Total costs Costs NPV 21 0scount tactor at 5% 23 chck benofits check costs