Excel统计计算代写 | W4100 Fall 2021 Homework 2

本次英国代写是Excel统计计算的一个Homework

1. A community has hired a company to estimate their storage requirements for water supply. They have specified that they would like a steady supply of 500 m³/day. The project will be built on a stream where some historical data on streamflow was available at the daily time scale. The consultant used the sequent peak method that you used in HW 1 with this demand and the daily streamflow data and estimates that a storage of 45,000 m³ will be needed to meet this demand [1]based on the historical data. Recognizing that they did not have a very long record, they also did an uncertainty analysis and computed 100 random simulations of possible streamflow sequences using a conditional bootstrap model. They computed the storage required for each of 100 simulations and this data is presented in the spreadsheet that is attached.  The Capital Cost of building the infrastructure is estimated as

CC= 0.2 S^1.5   where  S is the storage in m³ and CC is in $

The annual operation and maintenance cost is estimated to be 1% of the capital cost. A project design life of 25 years is considered, and the cost of capital is estimated to be 6% per year.

The community is willing to enter into a 25 year contract to buy water from the company at $1/m³. 

1. Does the company find it worthwhile to build the project? Compute the net present value of costs, and of revenue, and decide if the net present value of net revenue is greater than zero.
2. What is it going to cost the company to produce 1 m³ of water that they will sell for $1/m³? Annualize the total project cost and divide it by the annual water supplied.
3. What is the reliability of water supply based on this analysis? We are not given the amount that they could supply without failure from the proposed reservoir, BUT we are given the 100 equally likely estimates of the storage needed to provide 500m³/day without failure. So, if the storage needed is lower than the design storage of 45,000 m³ then the design storage is more than enough. On the other hand, if the storage required is more than the design storage then the reservoir will fail to supply the desired amount.
4. What is the average storage needed based on the 100 bootstrap values, and what is its capital cost? Why is it not equal to the capital cost of the design storage? Should we expect that the average storage from the bootstrap samples be equal to the storage estimated from the original data? Why or why not?
5. Seeing the uncertainty in storage estimates, the community wants to redesign their contract with the company. They are concerned that they may not get the desired amount of water in all years. So they ask the company to run a different analysis – if they build the proposed reservoir of 45,000 m³ what are their estimates of how much water they could supply each year without failure over the full period of historical record. This is reported in the Yield spreadsheet by the company. The community now proposes the following. If the amount of water delivered in a year is greater than or equal to 500 m³/day, the community will pay $1*500m³/day*365 days for the water, i.e., they will pay nothing for any water beyond their need of 500 m³/day. However, if the water supplied is less than 500 m³/day, then they will reduce the unit water payment by 1.5*fraction of days per year when the company fails to deliver the 500 m³/day. This calculation has been implemented in the Yield spreadsheet and the average annual revenue for each simulation is recorded there as well.
   1. Is it still profitable for the company to build this project on these terms? What is the average annual revenue? Is it higher than the annualized project cost?
   2. The company is borrowing 100% of the capital for the project and is required to make payments on it every year. Its past payment record has been excellent and this has allowed them to borrow with no down payment and a favorable interest rate. Based on the annual revenue analysis in the Yield spreadsheet, what is the probability that they will not be able to meet their total annualized costs ? Is that likely to change the answer to a) ?
6. The company decides to do a survey of the community residents to gauge their price sensitivity. At the current price of $1/m³ the demand for water is 500 m³/day. However, the price elasticity of demand is estimated from the survey to be -0.3. The company would like to propose that it is willing to meet the terms offered by the community, BUT would then like to charge $1.20/m³ for the water instead of $1/m³.
   1. What would the demand be at this price?[2]
   2. Assuming that they agree to this price and the new demand, and that all other terms remain the same what is the new average annual revenue for the company, and is it now an attractive project?[3]

What is the new probability that they will be able to meet the annualized costs for the project?

[1] The data in the spreadsheet is generated randomly and will be different for each group. Please copy that column and paste it in your own spreadsheet using PASTE VALUES option under the PASTE sub-menu under HOME in Excel.  This way those values will not randomly change with each calculation you do. You want to do this for both the Storage and the Yield

[2] E = (dy/y) /(dp/p)  or dy/y  = E dp/p   or ln(y) = E ln(p) + const  ; you can evaluate the constant by using the current value of demand and price and E. Then you can compute the y given the new p

[3] The # of days with failure and the average annual revenue formulae have been implemented in the yield spreadsheet BUT you need to put in your estimate of the new demand at the new price.