NPV Excel spreadsheet – The Jones Family Case

 need a spreadsheet with the formulas and 2 questions answered.
**Spreadhseet provided just need the formulas added. 
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.

Don't use plagiarized sources. Get Your Custom Essay on
NPV Excel spreadsheet – The Jones Family Case
Just from $13/Page
Order Essay

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.
Marsha: John, what’s wrong? Have you been selling yen again? That’s been a losing trade for
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
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.
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.
 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 
 e. (Hint: The Jones’s oil company has an excellent credit rating. Its long-term borrowing rate is only 7%.) 

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
The price is based on these factors:
Academic level
Number of pages
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more
error: Content is protected !!
Live Chat+1(978) 822-0999EmailWhatsApp

Order your essay today and save 20% with the discount code BEGOOD