| INPUTS FOR VALUATION |
| Current Inputs |
| Enter the current revenues of the firm = | | | $ 12,406 |
| Enter current capital invested in the firm = | | | $ 20,000 | { As a naïve estimate, you can use BV of debt + BV of Equity) |
| Enter the current depreciation = | | | $ 233 |
| Enter the current capital expenditures for the firm = | | | $ 298 |
| Enter the change in Working Capital in last year = | | | $ 115 |
| Enter the value of current debt outstanding = | | | $ - 0 |
| Enter the number of shares outstanding = | | | 1,500.00 |
| High Growth Period | | | | Your Inputs |
| Enter the growth rate in revenues for the next 5 years = | | | | 25.00% |
| What will all operating expenses be as a % of revenues in the fifth year? | | | | 70.00% | (Operating expenses include depreciation: This is equal to (1-Pre-tax Operating Margin)) |
| How much debt do you plan to use in financing investments? | | | | 0% |
| Enter the growth rate in capital expenditures & depreciation | | | | 25.00% |
| Enter working capital as a percent of revenues | | | | 7.50% |
| Enter the tax rate that you have on corporate income | | | | 36.00% |
| What beta do you want to use to calculate cost of equity = | | | | 1.25 |
| Enter the current long term bond rate = | | | | 6.50% |
| Enter the market risk premium you want to use = | | | | 5.50% |
| Enter your cost of borrowing money = | | | | 8.50% |
| Stable Period |
| Enter the growth rate in revenues = | | | | 6.00% | |
| Enter operating expenses as a % of revenues in stable period = | | | | 75.00% |
| Enter capital expenditures as a percent of depreciation in this period | | | | 200.00% | See capital expenditure worksheet (capex.xls) for details. |
| How much debt do you plan to use in financing investments? | | | | 5.00% |
| Enter interest rate of debt in stable period = | | | | 7.50% |
| What beta do you want to use in the stable period = | | | | 1.10 |
| ESTIMATED CASHFLOWS |
| | Base | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Growth in Revenue | | 25.00% | 25.00% | 25.00% | 25.00% | 25.00% | 21.20% | 17.40% | 13.60% | 9.80% | 6.00% |
| Growth in Deprec'n | | 25.00% | 25.00% | 25.00% | 25.00% | 25.00% | 21.20% | 17.40% | 13.60% | 9.80% | 6.00% |
| Revenues | $ 12,406 | $ 15,508 | $ 19,384 | $ 24,230 | $ 30,288 | $ 37,860 | $ 45,886 | $ 53,871 | $ 61,197 | $ 67,194 | $ 71,226 |
| Operating Expenses |
| % of Revenues | 70.00% | 70.00% | 70.00% | 70.00% | 70.00% | 70.00% | 71.00% | 72.00% | 73.00% | 74.00% | 75.00% |
| - $ Operating Expenses | $ 8,684 | $ 10,855 | $ 13,569 | $ 16,961 | $ 21,202 | $ 26,502 | $ 32,579 | $ 38,787 | $ 44,674 | $ 49,724 | $ 53,420 |
| EBIT | $ 3,722 | $ 4,652 | $ 5,815 | $ 7,269 | $ 9,086 | $ 11,358 | $ 13,307 | $ 15,084 | $ 16,523 | $ 17,471 | $ 17,807 |
| Tax Rate | 36.00% | 36.00% | 36.00% | 36.00% | 36.00% | 36.00% | 36.00% | 36.00% | 36.00% | 36.00% | 36.00% |
| EBIT (1-t) | $ 2,382 | $ 2,977 | $ 3,722 | $ 4,652 | $ 5,815 | $ 7,269 | $ 8,517 | $ 9,654 | $ 10,575 | $ 11,181 | $ 11,396 |
| + Depreciation | $ 233 | $ 291 | $ 364 | $ 455 | $ 569 | $ 711 | $ 862 | $ 1,012 | $ 1,149 | $ 1,262 | $ 1,338 |
| - Capital Expenditures | $ 298 | $ 373 | $ 466 | $ 582 | $ 728 | $ 909 | $ 1,263 | $ 1,616 | $ 1,969 | $ 2,322 | $ 2,675 |
| - Change in WC | $ 115 | $ 233 | $ 291 | $ 363 | $ 454 | $ 568 | $ 602 | $ 599 | $ 549 | $ 450 | $ 302 |
| = FCFF | $ 2,202 | $ 2,664 | $ 3,329 | $ 4,162 | $ 5,202 | $ 6,503 | $ 7,514 | $ 8,451 | $ 9,206 | $ 9,671 | $ 9,756 |
| Terminal Value (in '05) | | | | | | | | | | | $ 167,813 |
| COSTS OF EQUITY AND CAPITAL |
| | | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Cost of Equity | | 13.38% | 13.38% | 13.38% | 13.38% | 13.38% | 13.21% | 13.05% | 12.88% | 12.72% | 12.55% |
| Proportion of Equity | | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% | 99.00% | 98.00% | 97.00% | 96.00% | 95.00% |
| After-tax Cost of Debt | | 5.44% | 5.44% | 5.44% | 5.44% | 5.44% | 5.31% | 5.18% | 5.06% | 4.93% | 4.80% |
| Proportion of Debt | | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 1.00% | 2.00% | 3.00% | 4.00% | 5.00% |
| Cost of Capital | | 13.38% | 13.38% | 13.38% | 13.38% | 13.38% | 13.13% | 12.89% | 12.65% | 12.40% | 12.16% |
| Cumulative WACC | | 113.38% | 128.54% | 145.73% | 165.22% | 187.32% | 211.92% | 239.23% | 269.48% | 302.91% | 339.75% |
| Present Value | | $ 2,349 | $ 2,590 | $ 2,856 | $ 3,149 | $ 3,472 | $ 3,546 | $ 3,532 | $ 3,416 | $ 3,193 | $ 52,265 |
| FIRM VALUATION |
| Value of Firm | | $ 80,367 |
| - Value of Debt | | $ - 0 |
| Value of Equity | | $ 80,367 |
| Value of Equity per Share | | $ 53.58 |
| | | 1995 | 1996 | 1997 | 1998 | 1999 | 2000 | 2001 | 2002 | 2003 | 2004 |
| Value of Firm by year | | $ 80,367 | $ 88,453 | $ 96,954 | $ 105,760 | $ 114,703 | $ 123,542 | $ 132,250 | $ 140,844 | $ 149,448 | $ 158,314 |
| $ Value of Debt | | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ 1,235 | $ 2,645 | $ 4,225 | $ 5,978 | $ 7,916 |
WilyJo
dcfandeva3-1.xlsx
