corporate finance assignment
renenex3
MGMT332_Excel_Template_Final_Exam.xlsxProb. 1
| Argo Airlines | ||||||||||||||||
| Purchase price | 22,000,000 | |||||||||||||||
| Fit-out costs | 3,000,000.0 | |||||||||||||||
| Yearly revenue | 11,000,000.0 | |||||||||||||||
| Revenue inflator | 1.2% | |||||||||||||||
| Operating costs (% of revenue) | 43.0% | |||||||||||||||
| Discount rate | 6.6% | |||||||||||||||
| Tax rate | 21.0% | |||||||||||||||
| Years | ||||||||||||||||
| Start | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | |
| Investment | 22,000,000.0 | 3,000,000.0 | 3,000,000.0 | |||||||||||||
| Revenues | 11,000,000.0 | 11,132,000.0 | 11,265,584.0 | 11,400,771.0 | 11,537,580.0 | 11,676,031.0 | 11,816,144.0 | 11,957,937.0 | 12,101,433.0 | 12,246,650.0 | 12,393,610.0 | 12,542,333.0 | 12,692,841.0 | 12,845,155.0 | 12,999,297.0 | |
| Expenses | 4,730,000.0 | 4,786,760.0 | 4,844,201.0 | 4,902,332.0 | 4,961,160.0 | 5,020,693.0 | 5,080,942.0 | 5,141,913.0 | 5,203,616.0 | 5,266,059.0 | 5,329,252.0 | 5,393,203.0 | 5,457,922.0 | 5,523,417.0 | 5,589,698.0 | |
| Income before tax | (22,000,000.0) | 6,270,000.0 | 6,345,240.0 | 6,421,383.0 | 6,498,439.0 | 3,576,421.0 | 6,655,338.0 | 6,735,202.0 | 6,816,024.0 | 6,897,817.0 | 3,980,590.0 | 7,064,357.0 | 7,149,130.0 | 7,234,919.0 | 7,321,738.0 | 7,409,599.0 |
| Taxes | (4,620,000.0) | 1,316,700.0 | 1,332,500.0 | 1,348,490.0 | 1,354,672.0 | 751,048.0 | 1,397,621.0 | 1,414,392.0 | 1,431,365.0 | 1,448,541.0 | 835,924.0 | 1,483,515.0 | 1,501,317.0 | 1,519,333.0 | 1,537,565.0 | 1,556,016.0 |
| Net income after tax | (17,380,000.0) | 4,953,300.0 | 5,012,740.0 | 5,072,892.0 | 5,133,767.0 | 2,825,372.0 | 5,257,717.0 | 5,320,809.0 | 5,384,659.0 | 5,449,275.0 | 3,144,666.0 | 5,580,842.0 | 5,647,812.0 | 5,715,586.0 | 5,784,173.0 | 5,853,583.0 |
| NPV | 29,317,111 | |||||||||||||||
| IRR | 27% | Text answer here. | ||||||||||||||
Prob. 2
| BOAC Airline Supply | ||||||||||
| Yrs. 1-6 | ||||||||||
| Sales growth | Cash Flows | |||||||||
| Costs: | 1 | 2 | 3 | 4 | 5 | 6 | ||||
| Cost of Goods Sold (% of sales) | Sales | 103.0 | ||||||||
| 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% | |||||||
| 6.0% | Disc. Rate | |||||||||
| 7.0% | ||||||||||
| 8.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 | Ukraine | ||||||
| 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 |
v. MAR 2021
| No content - Page intentionally left blank. |
