NPV Excel spreadsheet - The Jones Family Case

MINI-CASE ● ● ● ● ●

254 Part Two Risk

bre13901_ch09_228-256 254 10/15/18 07:12 PM

The two wells are intended to develop a previously discovered oil field. Unfortunately there is still a 20% chance of a dry hole in each case. A dry hole means zero cash flows and a complete loss of the $10 million investment.

Ignore taxes and make further assumptions as necessary.

a. What is the correct real discount rate for cash flows from developed wells? b. The oil company executive proposes to add 20 percentage points to the real discount

rate  to offset the risk of a dry hole. Calculate the NPV of each well with this adjusted discount rate.

c. What do you say the NPVs of the two wells are? d. Is there any single fudge factor that could be added to the discount rate for developed wells

that would yield the correct NPV for both wells? Explain.

● ● ● ● ●

FINANCE ON THE WEB

You can download data for the following questions from finance.yahoo.com.

1. Look at the companies listed in Table 8.2. Calculate monthly rates of return for two succes- sive five-year periods. Calculate betas for each subperiod using the Excel SLOPE function. How stable was each company’s beta? Suppose that you had used these betas to estimate expected rates of return from the CAPM. Would your estimates have changed significantly from period to period?

2. Identify a sample of food companies. For example, you could try Campbell Soup (CPB), General Mills (GIS), Kellogg (K), Mondelez International (MDLZ), and Tyson Foods (TSN).

a. Estimate beta and R2 for each company, using five years of monthly returns and Excel functions SLOPE and RSQ.

b. Average the returns for each month to give the return on an equally weighted portfolio of the stocks. Then calculate the industry beta using these portfolio returns. How does the R2 of this portfolio compare with the average R2 of the individual stocks?

c. Use the CAPM to calculate an average cost of equity (requity) for the food industry. Use current interest rates—take a look at the end of Section 9-2—and a reasonable estimate of the market risk premium.

The Jones Family Incorporated The Scene: It is early evening in the summer of 2018, in an ordinary family room in Manhat- tan. Modern furniture, with old copies of The Wall Street Journal and the Financial Times scat- tered around. Autographed photos of Jerome Powell and George Soros are prominently displayed. A picture window reveals a distant view of lights on the Hudson River. John Jones sits at a com- puter terminal, glumly sipping a glass of chardonnay and putting on a carry trade in Japanese yen over the Internet. His wife Marsha enters.

Marsha: Hi, honey. Glad to be home. Lousy day on the trading floor, though. Dullsville. No vol- ume. But I did manage to hedge next year’s production from our copper mine. I couldn’t get a good quote on the right package of futures contracts, so I arranged a commodity swap.

John doesn’t reply.

Final PDF to printer

Chapter 9 Risk and the Cost of Capital 255

bre13901_ch09_228-256 255 10/15/18 07:12 PM

Marsha: John, what’s wrong? Have you been selling yen again? That’s been a losing trade for weeks.

John: Well, yes. I shouldn’t have gone to Goldman Sachs’s foreign exchange brunch. But I’ve got to get out of the house somehow. I’m cooped up here all day calculating covariances and efficient risk-return trade-offs while you’re out trading commodity futures. You get all the glamour and excitement.

Marsha: Don’t worry, dear, it will be over soon. We only recalculate our most efficient common stock portfolio once a quarter. Then you can go back to leveraged leases.

John: You trade, and I do all the worrying. Now there’s a rumor that our leasing company is going to get a hostile takeover bid. I knew the debt ratio was too low, and you forgot to put on the poison pill. And now you’ve made a negative-NPV investment!

Marsha: What investment?

John: That wildcat oil well. Another well in that old Sourdough field. It’s going to cost $5 million! Is there any oil down there?

Marsha: That Sourdough field has been good to us, John. Where do you think we got the capital for your yen trades? I bet we’ll find oil. Our geologists say there’s only a 30% chance of a dry hole.

John: Even if we hit oil, I bet we’ll only get 75 barrels of crude oil per day.

Marsha: That’s 75 barrels day in, day out. There are 365 days in a year, dear.

John and Marsha’s teenage son Johnny bursts into the room.

Johnny: Hi, Dad! Hi, Mom! Guess what? I’ve made the junior varsity derivatives team! That means I can go on the field trip to the Chicago Board Options Exchange. (Pauses.) What’s wrong?

John: Your mother has made another negative-NPV investment. A wildcat oil well, way up on the North Slope of Alaska.

Johnny: That’s OK, Dad. Mom told me about it. I was going to do an NPV calculation yesterday, but I had to finish calculating the junk-bond default probabilities for my corporate finance homework. (Grabs a financial calculator from his backpack.) Let’s see: 75 barrels a day times 365 days per year times $100 per barrel when delivered in Los Angeles . . . that’s $2.7 million per year.

John: That’s $2.7 million next year, assuming that we find any oil at all. The production will start declining by 5% every year. And we still have to pay $20 per barrel in pipeline and tanker charges to ship the oil from the North Slope to Los Angeles. We’ve got some serious operating leverage here.

Marsha: On the other hand, our energy consultants project increasing oil prices. If they increase with inflation, price per barrel should increase by roughly 2.5% per year. The wells ought to be able to keep pumping for at least 15 years.

Johnny: I’ll calculate NPV after I finish with the default probabilities. The interest rate is 6%. Is it OK if I work with the beta of .8 and our usual figure of 7% for the market risk premium?

Marsha: I guess so, Johnny. But I am concerned about the fixed shipping costs.

John: (Takes a deep breath and stands up.) Anyway, how about a nice family dinner? I’ve reserved our usual table at the Four Seasons.

Everyone exits.

Announcer: Is the wildcat well really negative-NPV? Will John and Marsha have to fight a hostile takeover? Will Johnny’s derivatives team use Black–Scholes or the binomial method? Find out in the next episode of The Jones Family Incorporated.

Final PDF to printer

256 Part Two Risk

bre13901_ch09_228-256 256 10/15/18 07:12 PM

You may not aspire to the Jones family’s way of life, but you will learn about all their activities, from futures contracts to binomial option pricing, later in this book. Meanwhile, you may wish to replicate Johnny’s NPV analysis.

QUESTIONS 1. Calculate the NPV of the wildcat oil well, taking account of the probability of a dry hole, the

shipping costs, the decline in production, and the forecasted increase in oil prices. How long does production have to continue for the well to be a positive-NPV investment? Ignore taxes and other possible complications.

2. Now consider operating leverage. How should the shipping costs be valued, assuming that output is known and the costs are fixed? How would your answer change if the shipping costs were proportional to output? Assume that unexpected fluctuations in output are zero-beta and diversifiable. (Hint: The Jones’s oil company has an excellent credit rating. Its long-term borrowing rate is only 7%.)

Final PDF to printer