Complete the attached “Problem Set 9.xlsx” using detailed instructions from “Problem Set 9.docx”
Instructions are: (Number are truncated due to format on this page) ref Problem Set 9 for detailed number sets
1. Capital Budgeting
Argo Airlines is looking to buy some gates at a West Coast airport. The key financial variables are below. Note that the gates revert back to the airport at the end of year 15. Note that any losses trigger tax benefits.
Purchase Price $22M
Yearly Revenue $11M
Operating Costs 43% of revenue
Discount Rate 6.6%
Gate Renovation (Fit-out Costs) $3M (in year 5 and 10)
Revenue Inflator 1.2%
Tax Rate 21%
What are the NPV and IRR of the gates? Should Argo invest in them? Why or why not?
2. Company Valuation
BOAC Airline Supply is trading at $15/share but you think that price may not be right. You have the following data and you want to use it to calculate its share price:
Gross Sales (year 1) $103M
COGS 61% of sales
General & Admin $3.4M
Annual Sales Growth Rate 3.3%
Advertising, Promotion & Selling $3.4M
Yearly Inflation for non-COGS expenses 3.0%
Tax Rate 21%
Discount Rate 6.6%
Cash Balance $2M
Shares o/s 33M
Cash Flow Adjustment Year 1 Year 2 Year 3 Year 4 Year 5 Year 6
Working Capital (2.1) (2.2) (2.3) (2.4) (2.4) (2.4)
Capital Expenditures (1.5) (1.6) (1.9) (2.2) (2.4) (3.0)
Calculate the per share price and run sensitivities for growth rates of
3.0%, 3.5%, and 4% as well as discount rates of 6%, 7%, and 8%. Put these in a matrix.
3. Bond Valuation
Given the purchase prices, coupons and maturities of four bonds, calculate the yields to maturity to you, the investor. Assume a $1,000 par value. Bonds A, B, and C are semi-annual. Bond D is a zero but calculate its yield with a semi- annual equivalency. Provide your answers to 4 significant digits (example: 6.1234%)
Bond Price Annual Coupon Maturing in
A 604.00 2.2% 8 years
B 780.00 2.4% 9 years
C 1,001.00 2.8% 10 years
D 455.00 10 years
4. Options and Futures
Your employer is offering you stock options on the firm as part of your pay package. You know the following about this offer:
Current Stock Price $13
Exercise Price $19
Maturity (yrs) 3
Risk-free Rate 2.1%
Stock Volatility 30%
What is the value of the option? Suppose the Fed raises Treasury rates to 2.3%, what is the new price of the option? After this Fed action, your company’s share price falls to $12, what is the new price of the option?
Your cousins grow corn in Wisconsin and plan to harvest 10,000,000 bushels at the end of the season. They are unsure whether to sell the futures contracts and lock the price in at $5.40/bushel or take a gamble and sell it all at the spot price at season’s end. They think they can get $4.80/bushel based on historical prices and their own analysis.
Assuming no transaction costs and each contract covers 5,000 bushels, what will the cousins’ profit/loss be if they sell the contracts and the spot price is $5.44 at maturity? Ukraine had a bumper harvest and spot prices fall to $5.33/bushel, what will the cousins’ profit/loss be now?
Because its financial position has strengthened considerably very recently, Argo Airlines is offered an interest rate swap – fixed to floating (LIBOR). The details are as follows:
Current Argo Bond Maturity 10 years
Bond Face Value $333M
Current Bond Rate 4.5% per year, fixed
Floating rate LIBOR + 100 basis points
Projected LIBOR rates 3.3% (years 1-2)
3.4% (years 3-4)
3.5% (years 5-6)
3.6% (years 7-10)
Show the cash flows for Argo and the present value gain/loss of doing the swap.