FINANCE CALCULATION IN EXCEL

QUESTION

1.                  Your company has existing forward crude oil financial swaps (hedges) in place of

(10 pts)       $105 for 2013 and $110 for 2014 and $105 for 2015 covering approximately 50% of

your expected production.  Assume the current forward price curve is $80 for each

of the three years.  Which set of future prices should you use in performing the

economic analysis of prospective wells soon to be drilled and why?

2.                 You are given the following actual dollar data:

(20 pts)                       Inflation is 3%

Interest is 10%

Depreciation is straight line over two years

Loan principal repayment is at end of two years

Tax Rate = 20%

0                      1                      2

Investment                  -100

Loan Proceeds                         50

Loan Repayment                                                        -50

Revenues                                                        220                240

Operating Expenses               20                    30

Depreciation                           50                    50

Interest                                                           5                        5

1.  Calculate Net Cash Flows in actual $
2. Calculate NPV at 10% in actual $
3. Calculate EUAC at 10% in actual $
4. Calculate IRR of the actual $ net cash flows
5. Calculate Net Cash Flows in constant $
6. Calculate NPV at the real rate of return %
7. Calculate the EUAC of the constant $ cash flows at the real rate of return
8. Calculate the IRR of the constant $ cash flows
9. Is the investment acceptable?  Why?

Note:  EUAC is Equivalent Uniform Annual Cost

3.                     Identify and describe at least five key project risks and contract asymmetries:

(20 pts)           You are a Houston natural gas storage operator.  You typically buy natural gas

From a Mexican producer in the spring and fall and resell the natural gas in the winter to a New York gas utility.  All of your contracts have a five year term or life.  You have a best efforts fixed quantity (100,000 Mcf per day) purchase contract at fixed prices in pesos with a large national oil company in Mexico.  You take title to the gas at the well head (field) in Mexico.  You have a best efforts (interruptible) gas pipeline transportation contract for 100,000 Mcf per day at a fixed per Mcf US dollar price with a large US natural gas pipeline to bring the gas to Houston.

Your US dollar sales contract to the New York utility is a firm obligation requiring you to deliver up to 100,000 Mcf per day whenever called upon by the utility.  The sales price is floating at the prevailing New York price in US dollars.  You have a gas pipeline contract at fixed prices from Houston to New York for 50,000 Mcf per day.  You pay for this firm capacity each day regardless of whether you ship gas that day or not.  The New York utility has no obligation to purchase a minimum quantity on a daily or seasonal basis.

Because times are tight, you do not carry insurance.  You are unhedged with respect to natural gas prices and currency fluctuations.

4.                     Your company has the following financial structure with a 40% tax rate:

$                                  Cost

Debt                    4,000,000                            10%

Equity                 2,000,000                            20%

Information applies to both 4.a. and 4.b.

a.Your company is considering a new product.  You have the

(15 pts)following information:

0          1          2          3          4

Revenues                                70        80        90        100

Operating Costs                                 20        25        30          35

Depreciation                           25        15          5            5

Investment (No Salvage)-50

1. Calculate net cash flows by year (tax rate = 40%)
2. Calculate NPV and identify cost of capital used
3. Calculate IRR
4. Is the investment acceptable?  Why?

b.         Your company is considering a second project with the following information:

        (15 pts)  

0          1          2          3

Revenues                                           120      140       160

Operating Costs                                  30        40         40

Depreciation                                        40        30         30

Interest                                                 5          55

Investment                          -100

Salvage                                                                        20

Loan Proceeds                                    50

Loan Repayment                                                      -50

1. Calculate net cash flows by year (tax rate = 40%)
2. Calculate NPV and identify cost of capital used
3. Calculate IRR
4. Is the investment acceptable? Why?

5.         Your company is considering two alternative production line layouts.  Revenue

(20 pts)           is the same under either alternative.  You are given the following information in actual dollars:

Alt A                            Alt B

Initial Cost                                          -50                               -75

Operating Expenses

(years 1-5)                                                30                                15

Analysis Horizon (years)                    5                                  5

Straight Line depreciation (5 yrs)      yes                               yes

Salvage                                                none                           none

Loan Proceeds                                                30                                45

Loan Interest Rate (Debt Cost)          10%                             10%

Tax Rate (Negative Taxes OK)                        50%                             50%

Cost of Equity                                     15%                             15%

Firm Debt Percentage                                    60%                             60%

Inflation Rate                                       5%                               5%

1. Calculate the NPV of each alternative in actual dollars
2. Calculate the EUAC of each alternative in actual dollars
3. Calculate the incremental IRR
4. Which alternative is preferred and why?

Note: It is possible that you have been given more data than you need.

 SOLUTION

JF87

But you can order it from our service and receive complete high-quality custom paper.  Our service offers Accounting  essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”