Document Sample

SPRIGG LANE (A) Explanations and additional notes: Base case. This section gives additional explanatory notes to help you set up and analyze the case. You are provided with an Excel spreadsheet which is a values-only version of the base case analysis. It is a more detailed version of the base case provided in Exhibit 1 of the case, but is identical to it. Your first job is to enter the correct formulas for the Future projections and Results sections of the spreadsheet. You will then use the spreadsheet for a simulation analysis. You are also free to construct your own spreadsheet from scratch, as long as it is consistent with the data given in the case text and supplemented here. 1. For the base case, the contracted price of gas is $1.90 per decatherm. This is locked in for four years, and then rises according to the inflation rate. 2. The rate of inflation is assumed to be constant at 3.5% per year for the entire project horizon (25 years). 3. Operating expenses go up by the well-expense inflation factor every year. You may ignore the statements on page 2 which say that operating expenses rise only every 3 years, and that the increase is capped at 15%. Additionally, operating expenses do not increase from year 15 onwards, and decline by 50% in year 20. That is, the operating expenses is the same for years 15-19. For years 20-25, operating expenses is identically equal to one-half the expenses in year 19. 4. The yearly production of gas declines every year, according to the percentages given in the Assumptions and input data section of the spreadsheet. For example, the year 6 production is 12.50% lower than the year 5 production. 5. The state tax rate is 9.75%. 6. The federal tax rate is 34%. The gross federal tax is the federal tax rate times (Profit before tax – depletion – state income tax). In addition to this, the government pays a subsidy in terms of Section 29 credit. This is the Section 29 rate times the production in decatherms. The net federal tax is the gross federal tax minus the Section 29 credit. 7. Taxes may be negative. Also, taxes apply for year zero, even though the only thing we do is build the plant. A negative tax is effectively a credit from the government to Sprigg Lane. According to GAAP, this credit can be claimed against past paid taxes, or projected future tax payments. To simplify things for us, we just allow the tax to be negative and treat it as a payment by the government to Sprigg Lane. With this information, you should be able to set up the spreadsheet and compute annual cash flows, and cumulative discounted revenue. The Sprigg Lane project is analyzed on three parameters: 1. NPV: The net present value of a revenue stream at a given discount rate is the sum of each of the individual revenues, discounted according to the time at which the revenue accrues. Check the help section of Excel to see how it is calculated. We are interested in the NPV of the annual cash flow. 2. IRR: The internal rate of return is the average rate of return given an investment in the first year and a revenue stream (the returns) in the future. Again, Excel can compute this for you. We are interested in the IRR of two quantities: Cash Flow, and Profit Before Tax. 3. Payback time: This is the point of time when the cumulative cash flow becomes positive. To compute this, we track the cumulative cash flow and interpolate to obtain the month at which this quantity becomes positive. The four rows “Positive?” to “Payback time” are one possible way to compute this for us, though you can do it in any other way, such as writing your own Visual Basic macro! Simulation: You will first set up a spreadsheet which ignores the possibility of well failure. Hence, there are five uncertainities to take care of: 1. Total well cost: This is distributed normally with a mean of $160,000. There is a 95% chance that the cost is within $5,400 of this quantity, assuming normal distribution. 2. First year production: This is lognormal with a mean of 33 million cft, and a standard deviation of 4.93 million cft. 3. BTU content: Triangular, with minimum 1055, mode 1160 and maximum 1250. 4. Production decline rate: The production decline rate is different each year. For every year, the decline is obtained by multiplying the decline percentage with a random variable having a triangular distribution. The distribution has minimum 0.5, mode 1.0 and maximum 1.75. 5. Annual inflation: This also changes every year. You may use the data in Exhibit 4 in any reasonable way to model the annual inflation. One recommended strategy is the following: For each year, the inflation is chosen uniformly at random from the inflation over the past 25 years (1963-1987). The CB.custom function of Crystal Ball can help you model this. Please clearly mention how you are modelling annual inflation in your simulation. Next, we want to obtain an idea of the situation when the above five variables are set at their 1-in-100 worst levels. An easy way to compute 1-in-100 worst levels for the first four distributions is as follows. Suppose we want the 99% level of the total well cost. We select the “Define Assumption” button of Crystal Ball with the cursor on this cell, and click “Parms”, followed by “Custom”. Change the second parameter to 99%ile, and click OK. This shows you the 99% value of the distribution, which you can then copy. Note that for inflation, our 1-in-100 worst case is when the inflation is at the 1% level of its distribution, since higher inflation helps us with higher revenue. For the other variables, it is fairly obvious what the 1-in-100 worst case corresponds to. Finally, we factor in the 1-in-10 failure probability of the well. If the well fails, we shut it down in year zero, and we do not need to look at the rest of the sheet. However, the Excel IRR function fails to handle this case. Hence when we incorporate the 1-in-10 failure chance, we will only focus on NPV. When the well fails entirely, the accounting changes in a few ways. We depreciate the entire well in year zero itself; that is, year zero depreciation equals well cost if it fails. Secondly, if the well fails, the NPV is just the year zero cash flow. CrystalBall settings: Recommended settings for CrystalBall: 1. Max. number of trials: 1000. 2. Stop on Calculation errors: No. (Uncheck). 3. Sampling method: Monte Carlo. 4. Use same sequence of random numbers: Yes. (Check.) Seed = 1234. With this information, you should be ready to answer the case questions below. Case write-up: The following are some questions to guide you in preparing your case summary. In addition, a general analysis, recommendations and other original insights should be included in your summary, and your grade will be based on both components. The total length of your summary (excluding attachments such as your models and graphs) should not exceed three pages. 1. Compare the base case with the two alternative down-side scenarios: Total failure of the well, and success but all factors at their 1-in-100 worst level. Is the investment economically attractive? 2. For this question, we ignore the possibility of the well failing. Run a simulation to see what the probabilities are of meeting the three targets: Payback in 42 months or less, at least 25% cash flow IRR, and at least 15% PBT IRR. Based on these probabilities, is the investment viable? 3. We now incorporate a 10% probability that the well fails, and look at NPV for the analysis. What is the average NPV? Assuming that the well succeeds, what is the lowest expected NPV? Is the investment viable?

DOCUMENT INFO

Shared By:

Categories:

Tags:
the University of Virginia, University of Virginia, International Residential College, Emmet Street, Parking Garage, the Rector, City of Charlottesville, University of Virginia Press, handicap accessibility, Venue Details

Stats:

views: | 332 |

posted: | 5/12/2010 |

language: | English |

pages: | 3 |

OTHER DOCS BY benbenzhou

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.