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
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
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
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
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
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.
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.
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?