The World Bank is considering an application from the country of Equatoria for a large dam project. In order to build this dam, villagers from an entire village will need to be relocated. In addition, there will be some watershed damage and ecological losses due to habitat destruction. Monetary costs and benefits for the dam project are given as follows: Construction costs: $650 million at the beginning of the first year of the project. Construction costs for the start of the second year of the project will be 12% higher than the first year costs, and at the start of year 3 (the final year of construction) the costs will be 15% higher than the first year costs. Operating costs: $75 million/year (starting in year 3); and then increase to $80 million/year from year 8 onward Hydropower generated: 4 billion kilowatt hours/year (available from year 3) Price of electricity: $0.08/kilowatt hour Irrigation water available from the dam: 20 billion litres/year (available from year 3) Price of water: $0.008/litre Agricultural production lost from flooded lands: $54 million/year (loss from year 2) Forestry production lost from flooded lands: $28 million/year (loss from year 2) Note: 1 billion = 1000 million You, as a consultant have been approached by the World Bank to carry out an analysis from which you will provide your recommendations. Your tasks: A. Calculate the NPV and the B/C using a real discount rate of 4.6%. Set up a basic template to complete your CBA. Put the variables in the clearly labeled cells above your Large Dam Project. Since we are given agricultural and forestry production losses, if we incorporate these we are automatically creating a withwithout project analysis. Next complete your Large Dam Project with clearly labelled columns. Do this by creating formulae using your variables in the cells above. Always use formulae linked back to the data cells above so that if you wish to change a data cell value (for a sensitivity analysis) then all your calculations will automatically 2 change. Once you have created a formula in one cell of a column you should be able to drag down so that it accurately duplicates the formula right down the column. To do the analysis I want you to complete the columns initially for 10 years of the project. However, we can assume that once built this dam will remain in place indefinitely. So to extend the analysis to cover the long term, follow these instructions: i) Underneath Yr 10 put Yr 11 to infinity (11 infinity). ii) Since the last few years have the same Net Cash Flow (NCF) and we do not have any further information, we can assume that the future NCFs will remain the same. This means that we can calculate the future NCFs based on these last NCFs and use the formula for a perpetuity that states: PV of all future expected cashflows = NCF/discount rate, (this effectively capitalizes the future NCFs) iii) To create the formula for this in the NCF cell for yrs 11 infinity (in your NCF column), use the NCF cell in year 9 and divide this value by the discount rate cell. (Use the year 9 NCF rather than the one in year 10, because you will shortly be changing the NCF entry in year 10). You have now calculated the PV of all future NCFs, to the beginning of year 11 (as this is the beginning point of yrs11-infinity). iv) But you want your final PV to be today (year 0) not year 11. Remember that the beginning of year 11 is the same as the end of year 10, so now you can add this cell containing the large value for (NCF/disc. rate) into your calculation for the year 10 NCF. v) Your final column is where you calculate the PV(NCF)s i.e. the PV of each entry in your NCF column. vi) Finally, sum your PV(NCF) column and this sum will give you your NPV. vii) Similarly, for your B/C you will need to calculate the PV(future Costs) and PV(future Bens) in a similar manner (as you did for the NCF) for years 11 infinity. This is so that you capture all future streams of costs and also of benefits. Then finally calculate the NPV(Costs) and NPV(Benefits) so that you can use these to calculate your B/C. Also begin a Word doc. and for Part A. give the NPV(Large Dam) and also the B/C(Large Dam). (10 marks) 3 B. Now consider an alternative project: several smaller dams constructed to avoid flooding significant agricultural land or forested areas. For this project, total construction costs are exactly half the costs of the large dam project, while operating costs are two-thirds those of the large dam. Power and irrigation benefits are also half as much as those for the large dam. Agricultural land and forests are not affected and the project incurs no ecological or resettlement costs. (i) Calculate the NPV of this Smaller Dams Project using the discount rate of 4.6%. You already have all the variables set up (for the Large Dam project) so do not create new cells but work from the original cells. Conduct your analysis for the Smaller Dams Project on a new table beneath that for the Large Dam Project (using some or all the same columns). Label the Small Dams Project clearly (all on the one sheet of your spreadsheet file this makes marking easier). Include the years 11 infinity in your Small Dams analysis in exactly the same way as you did for the Large Dam project. On your Word doc. for B. (i) give the NPV(Smaller Dams). (4 marks) (ii) Now you are to compare both projects under different discount rates. Set up a table with a column containing various discount rates, another column for the NPV for the Large Dam Project and a third column for the NPV for the Small Dams Project. Complete the table and use it to draw a graph that shows the changing NPVs plotted against the discount rate, for both projects on the one graph. Make sure your graph is properly labelled and has an appropriate title that contains your name. On your Word doc. for B (ii) if your decision regarding the two different dam projects were to be based solely on the CBAs calculated thus far, give the range of discount rates for which you would choose the Large Dam Project over the Smaller Dams Project, and vice versa.