URGENT FINANCE ASSIGNMENT
Leila@
finance_data.xlsx
Problem 1
| Boothink | 2011 | 2012 | 2013 |
| Sales | 6,063.7 | 6,366.9 | 6,621.6 |
| NOPAT | 2,100.0 | 2,200.0 | 2,310.0 |
| Free Cash Flows | 1,575.0 | 1,650.0 | 1,732.5 |
| Asset Value | 18,900.0 | 19,800.0 | 20,790.0 |
| Discount rate | 8.0% | 8.0% | 8.0% |
| Draftson | 2011 | 2012 | 2013 |
| Sales | 9,430.5 | 9,807.7 | 10,200.0 |
| NOPAT | 1,808.0 | 1,870.0 | 1,942.0 |
| Free Cash Flows | 1,265.6 | 1,309.0 | 1,359.4 |
| Asset Value | 13,921.6 | 14,399.0 | 14,953.4 |
| Discount rate | 7.0% | 7.0% | 7.0% |
| Hibbis | 2011 | 2012 | 2013 |
| Sales | 2,163.0 | 2,379.3 | 2,617.2 |
| NOPAT | 1,810.8 | 1,908.4 | 2,003.4 |
| Free Cash Flows | 1,448.6 | 1,526.7 | 1,602.7 |
| Asset Value | 20,281.0 | 21,373.6 | 22,438.1 |
| Discount rate | 7.5% | 7.5% | 7.5% |
| Horton | 2011 | 2012 | 2013 |
| Sales | 19,104.0 | 18,148.8 | 17,241.4 |
| NOPAT | 4,320.0 | 4,210.0 | 4,050.0 |
| Free Cash Flows | 2,592.0 | 2,526.0 | 2,430.0 |
| Asset Value | 18,144.0 | 17,682.0 | 17,010.0 |
| Discount rate | 12.0% | 12.0% | 12.0% |
| Kellco | 2011 | 2012 | 2013 |
| Sales | 6,020.8 | 6,081.0 | 6,263.4 |
| NOPAT | 2,456.5 | 2,481.1 | 2,555.5 |
| Free Cash Flows | 1,584.2 | 1,607.3 | 1,649.1 |
| Asset Value | 19,802.5 | 20,573.1 | 21,438.1 | (market value) |
| Discount Rate | | | | (see part (d)) |
Problem 2
| | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | | | | | 2017 |
| NOPAT [=EBIT*(1-t)] | 2,456.5 | 2,481.1 | 2,555.5 | 2,606.6 | 2,658.7 | 2,711.9 |
| DEPR | 361.2 | 364.9 | 375.8 | 383.3 | 391.0 | 398.8 |
| CAPEX | 1,204.2 | 1,216.2 | 1,252.7 | 1,277.7 | 1,303.3 | 1,329.4 |
| D_NWC | 29.4 | 22.4 | 29.5 | 24.0 | 24.5 | 25.0 |
| FCF | 1584.2 | 1607.3 | 1649.1 | 1688.1 | 1721.9 | 1756.3 |
Problem 3
| | Market | Rf | Kellco | | | Yields |
| 2004 | 10.7% | 1.4% | 14.3% | | 3-month | 0.05% |
| 2005 | 4.8% | 3.2% | 4.4% | | 1-year | 0.14% |
| 2006 | 15.8% | 4.8% | 17.8% | | 5-year | 1.59% |
| 2007 | 5.2% | 4.4% | 7.0% | | 10-year | 2.38% |
| 2008 | -36.8% | 1.4% | -55.4% | | 30-year | 3.16% |
| 2009 | 26.3% | 0.2% | 39.1% |
| 2010 | 15.1% | 0.1% | 20.3% |
| 2011 | 1.9% | 0.1% | 5.3% | | (1964-2013) | Average returns |
| 2012 | 16.0% | 0.1% | 24.7% | | S&P 500 | 11.3% |
| 2013 | 32.3% | 0.1% | 47.4% | | 3-Month T-bill | 5.1% |
Problem 4
| | | High-Value Manufacturing |
| | | Boothink | Draftson | Hibbis | Horton |
| | Debt | 3950.1 | 7476.7 | 1795.0 | 5103.0 |
| | Equity | 16839.9 | 7476.7 | 20643.0 | 11907.0 |
| | Beta_E | 1.1 | 1.3 | 0.9 | 2.1 |
| | Beta_D | 0.1 | 0.2 | 0.0 | 0.3 |
| | | Internet News |
| | | Puffco | Wroteit | Shale |
| | Debt | 86.0 | 0 | 265.5 |
| | Equity | 344.0 | 221 | 619.5 |
| | Beta_E | 2.0 | 1.6 | 1.9 |
| | Beta_D | 0.2 | 0.2 | 0.2 |
| | | Print Publishing |
| | | Flamingo | McGrawTim | Elementary |
| | Debt | 4655 | 8480 | 5118 |
| | Equity | 8645 | 12720 | 3412 |
| | Beta_E | 0.8 | 0.7 | 0.8 |
| | Beta_D | 0.1 | 0.2 | 0.3 |
Problem 5
| | Market | Rf | Kellco | KO |
| 2004 | 10.7% | 1.4% | 14.3% | -16.1% |
| 2005 | 4.8% | 3.2% | 4.4% | -0.7% |
| 2006 | 15.8% | 4.8% | 17.8% | 23.1% |
| 2007 | 5.2% | 4.4% | 7.0% | 30.4% |
| 2008 | -36.8% | 1.4% | -55.4% | -24.1% |
| 2009 | 26.3% | 0.2% | 39.1% | 30.3% |
| 2010 | 15.1% | 0.1% | 20.3% | 19.0% |
| 2011 | 1.9% | 0.1% | 5.3% | 9.4% |
| 2012 | 16.0% | 0.1% | 24.7% | 6.5% |
| 2013 | 32.3% | 0.1% | 47.4% | 17.2% |
| Average returns | S&P 500 | 3-Month T-bill | | SD mkt |
| 1964-2013 | 11.3% | 5.1% | | 17.10% |
