Corporate Finance
reded94
MGMT332_Excel_Template_ProblemSet9.xlsxProb. 1
| Apache Gate Purchase | |||||||||
| Purchase price | |||||||||
| Fit-out costs | |||||||||
| Yearly revenue | |||||||||
| Revenue inflator | |||||||||
| Operating costs (% of revenue) | |||||||||
| Discount rate | |||||||||
| Tax rate | |||||||||
| Years | |||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Investment | - 0 | - 0 | - 0 | ||||||
| Revenues | |||||||||
| Expenses | |||||||||
| Income before tax | |||||||||
| Taxes | |||||||||
| Net income after tax | |||||||||
| NPV | |||||||||
| IRR | |||||||||
Prob. 2
| Fley Airline Supply Valuation | ||||||
| Yrs. 1-6 | ||||||
| Sales growth | Cash Flows | |||||
| Costs (% of sales): | 1 | 2 | 3 | 4 | 5 | 6 |
| Cost of Goods Sold | Sales | |||||
| Advert., Prom., & Selling | Cost of Goods Sold | |||||
| General & Administrative | Advert., Prom., & Selling | |||||
| Rates: | General & Administrative | |||||
| Tax | Net Income before Tax | |||||
| Discount | Taxes | |||||
| Inflation | Net Income after Tax | |||||
| Results | Cash flow adjustments: | |||||
| PV of NCF (incl. TV) | Working Capital | |||||
| + Cash | Capital Expenditures | |||||
| - Debt | Net Cash Flows | TV | ||||
| Total Equity (M$) | NCF (incl. terminal value) | |||||
| - # of shares outstanding (M) | ||||||
| - Price/share ($) | Growth rate | |||||
| Price/share ($) for: | 3.0% | 3.5% | 4.0% | |||
| 8.0% | Disc. Rate | |||||
| 9.0% | ||||||
| 10.0% |
Prob. 3
| A | B | C | D |
| YTM | |||
| Start | |||
| 0.5 | |||
| 1.0 | |||
| 1.5 | |||
| 2.0 | |||
| 2.5 | |||
| 3.0 | |||
| 3.5 | |||
| 4.0 | |||
| 4.5 | |||
| 5.0 | |||
| 5.5 | |||
| 6.0 | |||
| 6.5 | |||
| 7.0 | |||
| 7.5 | |||
| 8.0 | |||
| 8.5 | |||
| 9.0 | |||
| 9.5 | |||
| 10.0 |
Prob. 4
| a. Option Pricing | b. Futures Prices | ||||||
| Base | Fed | Price drop | Base | Mexico | |||
| Exercise price | Bushels | ||||||
| Maturity | Bushels/contract | ||||||
| Stock price | # of contracts | ||||||
| Risk free rate | Contract price | ||||||
| Volatility | Spot price | ||||||
| BS calculations: | Profit/(Loss) to cousins | ||||||
| d1 | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||
| N(d1) | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | c. Interest Rate Swap | |||
| d2 | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | Cash Flows | |||
| N(d2) | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | Bond outstanding | Original | Swap Pmts. | Net |
| Price of call | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | Maturity (yrs.) | Year 1 | ||
| Fixed rate | Year 2 | ||||||
| Spread over LIBOR | Year 3 | ||||||
| LIBOR: | Year 4 | ||||||
| Years 1-2 | Year 5 | ||||||
| Years 3-4 | Year 6 | ||||||
| Years 5-6 | Year 7 | ||||||
| Years 7-10 | Year 8 | ||||||
| Year 9 | |||||||
| Year 10 | |||||||
| Present value of net |
