FIN615 CTU Natural Gas Investment Morrison Oil Analysis Please refer to attached you find the questions and excell Using Derivatives to Analyze a Natural G

FIN615 CTU Natural Gas Investment Morrison Oil Analysis Please refer to attached you find the questions and excell Using Derivatives to Analyze a Natural Gas Investment Morrison Oil and Gas is faced with an interesting investment opportunity. The investment involves the exploration for a significant deposit of natural gas in southeastern Louisiana near Cameron. The area has long been known for its oil and gas production, and the new opportunity involves developing and producing 50,000 cubic feet (MCF) of gas. Natural gas is currently trading at around See the discussion found in the Technical Insight Box on page 407.
Given:
MCF
Year
1
2
3
4
5
Solution Legend
One thousand cubic feet
MCF
100000
100000
100000
100000
100000
= Value given in problem
= Formula/Calculation/Analysis require
= Qualitative analysis or Short answer
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
Forward
Price/MCF
4.5
4.75
5.3
6
6.5
Solution:
Year
1
2
3
4
5
Forward
MCF
Price/MCF
100,000 $
4.50
100,000
4.75
100,000
5.30
100,000
6.00
100,000
6.50
Projected
Revenues
Solution Legend
ven in problem
/Calculation/Analysis required
ive analysis or Short answer required
ek or Solver cell
Ball Input
Ball Output
PROBLEM 11-5
Given
Available gas (MCF)
Price of Gas (today)
Gas Price Next Year
High
Low
Forward price for next year
Development cost per MCF
Debt (on the property)
Interest rate on debt
Debt maturity
Asking price for Equity
Risk free rate of interest
Income tax rate
Option Exercise price/MCF
$
$
$
$
$
$
$
$
50,000,000
14.03 per MCF
18.16
12.17
14.87
4.00
450,000,000
10%
1 year
50,000,000
6.0%
0.0%
13.90
Solution
a. Hedging (with futures) analysis
Revenue (hedged)
Less: Development cost
Less: Interest expense
EBT
Less: Taxes
Net Income
Less: Principal Payment
Equity FCF
$
(450,000,000)
Since there is only one Equity FCF (a sure value) and it is less than the $50 million asking price for
the equity, the investment should not be made if the future oil revenue is going to be hedged.
Estimated value of the equity
b. Real Option analysis
High Price for Gas
Revenue (Not hedged)
Less: Development cost
Less: Interest expense
EBT
Less: Taxes
Net Income
Less: Principal Payment
Equity FCF
$
Calculating the risk neutral probabilities
Low Price for Gas
(450,000,000) $
(450,000,000)
Risk Neutral Pbs
Option Payout
Product
High price oil
Low price oil
Sum
Risk Neutral Expected Equity FCF
Equity Value
$
$
–
c. Valuing a Call Option on natural gas with an exercise price of 13.90 per MCF
Option Payouts
Risk Neutral Pb
High price oil ($18.16/MCF)
Low price oil ($12.17/MCF)
Expected Payout
Call Value
Buy 50 m calls
Product
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
Present value of
expected Equity
FCF for year 1
where gas revenues
are sold forward
(hedged).
Discounted at the
risk free rate.
Use forward price to calculate the risk neutral
probability, i.e.,
Low 
 Forward


−
Risk Neutral  Price
Price 
=
Low 
Probabilit y
 High


−
Price 
 Price
The option to
produce only when
conditions are
favorable is
obviously valuable.
It doubles the value
of the equity in the
gas venture.
PROBLEM 11-6
Given
Available oil (barrels)
Oil Price Next Year
High
Low
Forward price of oil for next year
Development cost
Extraction cost/barrel
Risk free rate of interest
Income tax rate
20,000
$
$
$
$
$
50.00
35.00
40.00
600,000.00
8.00
5%
0%
Solution
If the investment is hedged by selling the oil in the forward market
Revenue (hedged)
$
800,000
Less: Development cost
(600,000)
Less: Extraction cost
$
(160,000)
EBT
$
40,000
Less: Taxes
$
Net Income
$
40,000
Equity FCF
$
40,000
Since there is only one Equity FCF and it less than the $50 million asking price for the equity, the
investment should not be made where the future oil revenue is hedged.
Estimated value of the equity =
$
38,095
If the firm waits until the end of the year to decide whether to exercise the option to develop
Oil Price Scenario
High
Low
Revenue (Not hedged)
Less: Development cost
(600,000)
(600,000)
Less: Extraction cost
EBT
Less: Taxes
Net Income
Equity FCF
Calculating the risk neutral probabilities
Risk Neutral Pbs
High price oil
Low price oil
Option Payout
Sum
Risk Neutral Expected Equity FCF
Equity Value
Product
Obviously the option to wait and only exercise the right to develop and produce the oil after the future price
of oil is known is a valuable thing.
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
This wasn’t part of the question.
The $50M asking price was for the previous problem.
Pampa shouldn’t sell oil it’s not sure it would have.
Present value of
expected Equity FCF for
year 1 where oil
revenues are sold
forward (hedged).
Discounted at the risk
Use forward price to calculate the risk neutral
probability, i.e.,
Low 
 Forward


−
Risk Neutral  Price
Price 
=
Low 
Probabilit y
 High


−
Price 
 Price
PROBLEM 11-7
Strategy of shorting calls and going long on the project
Call strike price
$
13.90
Market (traded) price of call per MCF
$
1.86
Number of MCF
$ 50,000,000.00
At time t = 0
Proceeds from shorting call
Investment in project
Net proceeds at t = 0
Gas price
Payoffs on short call
Proceeds from project
Net proceeds at t = 1
Solution L
= Value given in proble
= Formula/Calculation
= Qualitative analysis o
= Goal Seek or Solver
= Crystal Ball Input
= Crystal Ball Output
(50,000,000.00)
At time t = 1
High Price
$
18.16
$
Low Price
12.17
You make $43,000,000 at t = 0 a
there are no cash obligations nex
period because we do not produc
in the low price state. The strateg
of investing in the project and
shorting calls results in sure profi
of $43 million today.
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
ake $43,000,000 at t = 0 and
re no cash obligations next
because we do not produce
ow price state. The strategy
sting in the project and
g calls results in sure profits
million today.
PROBLEM 11-8
Given
Initial investment
Total Ore Quantity
% Pure Copper/ton
Life of project
Ore mined each year
Cost/ton for processing
Tax rate
Risk free rate
WACC
Growth in copper prices
$
$
So
60,000,000
75,000 tons
15%
5 years
15,000 tons
150.00
30%
5.5%
9.5%
12%
Expected Prices
per Ton
Copper Price/Ton
$
7,000 $
7,000
7,150
7,840
7,200
8,781
7,300
9,834
7,450
11,015
Forward Price Curve
2011
2012
2013
2014
2015
change dates: future dates are
Solution
a.
Revenues
Year
2011
2012
2013
2014
2015
Processing
Costs
Depreciation/
Depletion
NOI
Processing
Costs
Depreciation/
Depletion
NOI
b.
NPV $ (60,000,000.00)
c.
Revenues
Year
2011
2012
2013
2014
2015
NPV
Tracking portfolio
Year
2011
2012
2013
2014
2015
Cost at t = 0
Total cost (Track. Port)
Cost of investment
Savings
Forward price
$
7,000
7,150
7,200
7,300
7,450
Expected Price
$
7,000
7,840
8,781
9,834
11,015
Gain (loss) on FOR
Bond Payoffs
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
ge dates: future dates are 2016-2020
NOPAT
Plus:
Depreciation
Project FCF
NOPAT
Plus:
Depreciation
Project FCF
Total payoffs
PROBLEM 11-9
Given
Risk free rate
Spot price (gold)
Number of ounces
Cost of 175 ounces
Maturity
Forward price (175 ounces)
5.00%
571.43
175
$ 100,000.00
1
$ 104,000.00
$
Solution
Strategy of Client
(Short gold today, invest in treasury and go long on forward contract)
TODAY
Short gold (175 ounces)
Invest treasury (@ 5%)
Long forward (@104,000)
Net
Cash Flow
NEXT YEAR
$ 100,000.00 Cover short position
$ (100,000.00) Treasury bill payoff
0 Close forward
$
Net
Cash Flow
-P
Jim Lytle Strategy
(Buy 175 ounces of gold today and sell next year)
TODAY
Cash Flow
NEXT YEAR
Cash flow
Buy gold (175 ounces)
$ (100,000.00) Sell gold at P
P
Borrow 100,000
$ 100,000.00 Repay risk free loan
Net
$
Net
Jim Lytle’s strategy has uncertain payoffs next year and a zero net investment today.
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
P is the REALIZED price of 175
ounce of gold next year
If the price of 175 ounces of gold
goes above $106,000, Jim Lytle’s
strategy pays off more than the
client’s strategy. On the other hand if
the price of 175 ounces of gold stays
below $106,000, the client’s strategy
provides greater payoffs.
PROBLEM 11-10
Given
Initial investment
Total PCs scrapped
Electronic scrap per PC
Gold content
Life of project
PCs scrapped per year
Tons of scrap per year
Current price of gold
Cost/ton for processing
Tax rate
Risk free rate
WACC
Growth in gold prices
Forward Price Curve
2016
2017
2018
2019
2020
$
$
$
450,000
1,000,000
6
0.33
5
200,000
600
592.80
67.50
30.0%
5.0%
10.5%
7.0%
Solutio
units
pounds
ounce per ton
years
units
per ounce
Gold
Expected Prices
Price/Ounce
per Ounce
$
679.40 $
634.30
715.10
678.70
750.60
726.21
786.90
777.04
822.80
831.43
Solution
a.
Year
2016
2017
2018
2019
2020
Revenues
Processing
Costs
Depreciation/
Depletion
NOI
Revenues
Processing
Costs
Depreciation/
Depletion
NOI
b.
NPV
c.
Year
2016
2017
2018
2019
2020
NPV
To solve for equivalent growth rate, use the following:
Year
2016
2017
2018
2019
2020
Expected Price
Revenues
Processing
Costs
Depreciation/
Depletion
NPV
Difference
Use Solver. Set difference to zero by changing
growth rate cell (yellow highlighted)
M 11-10
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
on
NOPAT
Plus:
Depreciation
Project FCF
NOPAT
Plus:
Depreciation
Project FCF
o zero by changing
NOI
NOPAT
Plus:
Depreciation
Project FCF
PROBLEM 12-1
Given
Risk free rate
Dividend yield
Exercise price (I)
Current value (V)
Volatility
Volatility ^2
NPV
$
$
$
5%
10%
50,000,000
45,000,000
20%
0.0400
(5,000,000)
The right to develop the casino can be thought of as a American Ca
Immediate exercise of this option results in a negative NPV (
the real estate development option has a value of $3.317 million be
uncertainty in the prospects of the casino industry in that region. Th
may be worth waiting.
Solution
b
Call Option Value
V*
Call (Infinite life option)
NPV*
Casino Development Option Value
Critical Value
Critical NPV value
So
be thought of as a American Call Option with infinite life.
ults in a negative NPV (-50 m + 45 m = -5 million). However,
as a value of $3.317 million because of the inherent
asino industry in that region. This uncertainty suggest that it
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
PROBLEM 12-2
Given
Quantity
Price (Year 0)
P-high
P-low
Forward price
Extraction costs
Solution L
$
$
$
$
$
1000
20
25
15
20
17
= Value given in problem
= Formula/Calculation/An
= Qualitative analysis or
= Goal Seek or Solver ce
= Crystal Ball Input
= Crystal Ball Output
Solution
You can lock into the forward r
same spot rate. The payoffs u
Selling today is better because
Profit (sell today)
Cert. Equiv (sell forward)
Profit-high
Profit-low
Risk neutral probability
Value of public land
a.
b.
Uncertain Profits
Extract oil only in this state of
the world
Do not extract oil in this state
of the world
Equate forward price = expe
neutral probabilities:
20 = 25 * p + 15 * (1
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
ou can lock into the forward rate or sell immediately at the
ame spot rate. The payoffs under either action is the same.
elling today is better because of time value of money.
Equate forward price = expected cash flows based on risk
neutral probabilities:
20 = 25 * p + 15 * (1 – p).
PROBLEM 12-3
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
PROBLEM 12-4
Given
Solution L
VARIATION IN PROBLEM: High price of copper
is 2.8 and low price is 1.2
Ore purity
0.375
Ore quantity (lbs)
5,000,000
Current price
$
2.20
P-high
$
2.80
P-low
$
1.20
Expected price
$
2.50
Forward price
$
2.31
Development costs
$ 1,200,000.00
Amount payable today
75.00%
Amount due next year
25.00%
Extraction costs (per lb of copper) $
1.60
Cost of capital (exploration)
Cost of capital (development)
Risk free rate
25.00%
15.00%
5.00%
Solution
a. Value of the hedged lease
Certainty Equivalent cash flows
NPV
Risk neutral probability
Risk neutral probability
b. Value of the undhedged lease without any option
In the high price scenario
In the low price state
Certainty equivalent
NPV
Note: Extraction costs of $1.6
is shifted to the end of the sec
This makes all cash flows in th
of cash flows).
NPV of project if Newport commits to
a forward price.
Use forward price to calculate the ris
probability, i.e.,
Use todays’ price to calculate the ris
c, d. Value of the (unhedged) lease with option
In the high price scenario
In the low price state
Certainty equivalent
NPV
Newport should go ahead with the development of the mine.
 Current 

1 +
Risk Neutral  Price 
=
Probability
 Hi

 Pr
Value of the (unhedged) lease witho
any real option
Value of the (unhedged) lease with re
option
e. Strategy of short positions on 1.875m lbs. call options
(exercise price = $1.68, maturity = 2, call premium = $0.70/lb)
and long position in project.
Price = $2.80
Price = $1.20
Gain/loss on options
Gain/loss on production
Net payoffs at t = 2
Payoff from sale of option
(at t = 1)
Investment in project
Net cash flows at t = 0
This strategy resullts in a sure
profit of
Fair price of option
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
Extraction costs of $1.60 incurred at the beginning of the second year
is shifted to the end of the second year by multiplying by the risk free rate.
This makes all cash flows in the computation consistent (with regard to timing
of cash flows).
PV of project if Newport commits to production today and locks in
orward price.
e forward price to calculate the risk neutral
obability, i.e.,
Low 
 Forward


−

Risk Neutral  Price
Price 

=
Low 
Probabilit y
 High

 Price − Price 



e todays’ price to calculate the risk neutral probability, i.e.,
 Current 
Risk Free 
Low 

1 +
 − Price 
Rate
Risk Neutral  Price 


=
Low 
Probability
 High


−
Price 
 Price
lue of the (unhedged) lease without
y real option
lue of the (unhedged) lease with real
PROBLEM 12-5: Valuation an American Option
Given
Risk-neutral probability
Risk free Interest rate
Discount factor = exp(Risk free interest rate)
Strike (in $ millions)
0.4626
5%
$
23.00
Solution
Today
Text Color Legend
Value of Beginning Oil Field Operations Now $
NPV of waiting (NPV-Waiting)
NPV of Exercising Now (NPV-Now)
Value of American Option = max (NPV-Waiting,NPV-Now)
25.000
Cell Outline Legend
Stock
Wait
Exercise
a.
b.
Solution
= Value given in problem
= Formula/Calculation/Analysis
= Qualitative analysis or Short
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
ation an American Option
ution
Year One
Year Two
$
$
$
Solution Legend
alue given in problem
ormula/Calculation/Analysis required
Qualitative analysis or Short answer required
Goal Seek or Solver cell
rystal Ball Input
rystal Ball Output
35.8300
$
26.5500
$
19.6600
$
14.5700
23.5500
20.8800
$
$
31.7800
28.1900
$
Year Three
17.4400
PROBLEM 12-6
Given
Price of Gas
Solution Legend
$
8.00
Price of Fuel Oil
Max
Most likely
Minimum
Revenues
WACC
$ 12.00
$ 7.00
$ 2.00
$ 10.00
10%
= Value given in problem
= Formula/Calculation/Analysis re
= Qualitative analysis or Short an
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
Solution
a. Estimate the power plant’s cash flow
Revenues
Less: Fuel cost
Cash flow
b.
c.
$ 10.00
$ 7.00
$ 3.00
Solution Legend
given in problem
la/Calculation/Analysis required
ative analysis or Short answer required
Seek or Solver cell
l Ball Input
l Ball Output
PROBLEM 12-7
Given
Cost of building the refinery
$
2,000,000,000
Refinery life
5 years
Depreciation = straight line with no salvage value
Gasoline prices (next year)
Maximum
$
Most likely
$
Minimum
$
Expected price
$
4.00
2.50
1.75
2.75
Jet fuel prices (next year)
Maximum
$
5.00
Most likely
$
3.25
Minimum
$
2.50
Expected price
$
3.58
Refining capacity
Crude oil in bbls
12,000,000
Conversion to gasoline
90%
Conversion to jet fuel
70%
Cost of purchasing crude/bbl
$
25.00
Other costs of refining
35% price of fuel
Cost of capital
10%
Tax rate
30%
Riskfree rate
5.5%
1
Price of gasoline
Price of jet fuel
$
2.75
3.58
Revenues
Gasoline
Jet fuel
Revenues for Output Choice
Less: Cost of purchasing crude
Less: Cost of refining
Less: Depreciation
EBIT
Less: Taxes
NOPAT
Plus: Depreciation
Cash flow
NPV
Year
3
2
($2,000,000,000.00)
$
2.75
3.58
$
2.75
3.58
Switch to jet fuel
0
0
0
7
STATIC ANALYSIS
(based on expected prices of gas and Jet fuel)
Margin analysis per barrel of crude processed
Gasoline
Jet Fuel
Price/bbl of output
$
121.00 $ 157.67
Qty of output/bbl of crude
0.9
0.7
Revenue/bbl of crude
$
108.90 $ 110.37
Cost/bbl of crude
$
25.00 $ 25.00
Proc cost/bbl of crude
$
38.12 $ 38.63
Gross Profit/bbl of crude
$
45.79 $ 46.74
This implies that jet fuel is preferable (provides higher
margins)
Revenues (Jet Fuel)
$
1,324,400,000
Cost of purchasing crude
$
(300,000,000)
Cost of refining
$
(463,540,000)
Depreciation
$
(400,000,000)
EBIT
$
160,860,000
Less: Taxes
$
(48,258,000)
NOPAT
$
112,602,000
Depreciation
$
400,000,000
Project FCF
$
512,602,000
NPV
$
(56,835,120)
ear
4
$
5
2.75
3.58
$
2.75
3.58
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis require
= Qualitative analysis or Short answer
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
Negative NPV based on
expected prices of jet fuel
0
0
Solution Legend
given in problem
la/Calculation/Analysis required
ative analysis or Short answer required
Seek or Solver cell
l Ball Input
l Ball Output
PROBLEM 12-8
Do nothing
Solution Legend
Property 1
Units
Rent/month
Yearly revenues
Potential value
4
$
Property 2
Units
Rent/month
$
Yearly revenues
Potential value
2,000
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
4
1,500
New construction
Units
10
Rent/month
$
2,500
Yearly revenues
Value
Cost
$
1,500,000
NPV
Assume: risk free rate of 5%
and volatility of 20%
r
0.05
T
1
sigma
0.2
X
1500000
Underlying
3000000
d1
N(d1)
d2
N(d2)
Basic
Call
Now include foregone property
Property 1 NPV
Property 2 call
Property 2 has a greater NPV
because forgeone value is less.
a.
b.
c.
swer required
PROBLEM 13-1
Solution Legend
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
PROBLEM 13-2
Given
First Plant
Assume perpetual returns
IRR
Initial investment
$
Annual cash flow
$
Initial investment
Initial investment
Solution Legend
13.00%
600 million
78 million
Second Plant
$
= Value given in problem
= Formula/Calculation/Analysis required
= Qualitative analysis or Short answer req
= …
Purchase answer to see full
attachment