The World Bank has been approached for funding for a proposed renewable electricity project. Leaders in Ikato, a developing country, agree that there is an urgent need for hydroelectricity in order to have electricity available to more of the population, and for much needed industrial growth. The dam required to produce the hydroelectricity, necessitates the flooding of a large valley, resulting in loss of land currently used for agriculture, and ecological losses due to habitat destruction of endemic wildlife. There is a village in the area, however half of the village homes and people will need to be relocated (because of the lake that will form behind the dam). The agricultural land had been sufficient to support all the villagers, now it is no longer there. You work for the World Bank and have been asked to conduct a Cost-Benefit Analysis (CBA) for this proposal. You travel to Ikato to speak with the leaders, gather information and visit the proposed site. You now have more information and have done some cost estimates to use in the CBA. Your tasks are to estimate the following monetary costs and benefits for the project. Construction costs are $1180 million at the beginning of the first year of the project, and for the start of the second year of the project the final tranche of construction costs will be 18% higher than the initial costs. Relocation costs are estimated at $900 million, half of which will be required at the beginning of the first year of the project and the other half at the beginning of the second year. Operating costs are $83 million/year (starting in year 2) and then increase to $89 million/year from year 5 onward. It is estimated that there will be 3.7 billion kilowatt hours/year of hydropower generated (available from year 2), and should sell at $0.085/kilowatt hour. There will also be irrigation water available from the dam: 6 billion litres/year (available from year 2), at a price of 0.8 cents/litre. Agricultural production lost from the flooded lands is valued at $79 million/year (beginning from the end of year 1). Unless otherwise stated, costs and revenues can be recorded at the end of each year. You, as a consultant have been approached by the World Bank to carry out an analysis from which you will provide your recommendations and are instructed to use a real discount rate of 4.8%. Note: 1 billion = 1000 million Initially, complete the spreadsheet that has been set up on a template for you, 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) Since the last few years of the analysis 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/disc rate, (this capitalizes the future NCFs) ii) To create the formula for this in the NCF column, in the cell for yrs 11 infinity, use the NCF in, say, year 9 and divide this by the discount rate cell. You have now calculated the PV of all future NCFs, to the beginning of year 11 (as this is the starting point of yrs 11-infinity). iii) 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. You should have calculated your NPV, IRR and B/C to compare the project under different discount rates. Your marks is based on the following. Set up a table with a column containing various appropriate discount rates and another column for the NPVs. You can use this to create your NPV graph over different discount rates. Please do your table and your graph directly under your analysis. It will easily fit on one spreadsheet page if I want to print them off. *This spreadsheet will be submitted for marking. (20 marks) *Now set up a word document for the remainder of the assignment. I do not want a formal report written, instead I want bullet point answers for the questions below. 1) Give answers only for: a. NPV (2 marks) b. IRR (2 marks) c. B/C (4 marks) 2) Solely on this analysis, what would be your recommendation to the World Bank? Should they fund this project? (2 marks) 3) Remember that what you have used are estimates of predicted future benefits and costs. Think about which of these, that if they have been wrongly estimated, might change the outcome and hence your recommendation. Give only the key variables that could be relevant and the values they would have to change to, in order to reverse your recommendation. (10 marks) Note: You will lose some marks if you give changes for all variables. 4) Finally, think about other impacts that should be considered when deciding on a project like this one. What other costs and benefits should be considered that are not quantified in your spreadsheet analysis. Clearly list these as bullet points and for each one state (as sub bullet points) how and where you could incorporate these costs and benefits into your spreadsheet analysis. (Word limit on Question 4 of 200 words can be fewer words)