| Chiara Company management has made the projections shown in Table below |
| Use this Excel spreadsheet as a starting point to value the company as a whole. |
| The WACC for Chiara is 12 % and the long run growth rate after year 5 is 4 %. |
| The company has $5 million debt and 865,000 shares outstanding. |
| What is the value per share? |
| Assumptions: | ||
| Debt | $ 5,000,000 | |
| Number of shares outstanding | 865,000 | |
| Tax rate | 35% | |
| Opportunity cost of capital, percent (r) | 12% | |
| Long-run growth rate | 4% | |
| Current | Forecast | |||||
| Year: | 0 | 1 | 2 | 3 | 4 | 5 |
| Sales | 40,123 | 36,351 | 30,155 | 28,345 | 29,982 | 30,450 |
| Cost of goods sold | 22,879 | 21,678 | 17,560 | 16,459 | 15,631 | 14,987 |
| Other costs | 8,025 | 6,797 | 5,078 | 4,678 | 4,987 | 5,134 |
| EBITDA (1 – 2 – 3) | 9,219 | 7,876 | 7,517 | 7,208 | 9,364 | 10,329 |
| Depreciation | 5,678 | 5,890 | 5,670 | 5,908 | 6,107 | 5,908 |
| Profit before tax (EBIT) (4 – 5) | 3,541 | 1,986 | 1,847 | 1,300 | 3,257 | 4,421 |
| Tax | 1,239 | 695 | 646 | 455 | 1,140 | 1,547 |
| Profit after tax (6 – 7) | 2,302 | 1,291 | 1,201 | 845 | 2,117 | 2,874 |
| Change in working capital | 784 | -54 | -342 | -245 | 127 | 235 |
| Investment (chg. in gross fixed assets) | 6547 | 7345 | 5398 | 5470 | 6420 | 6598 |
| Free cash flow | 648.65 | -110.1 | 1814.55 | 1528 | 1677.05 | 1948.65 |
| PV Free cash flow, years 1-4 | $ 3,501.64 | |||||
| Horizon Value | $ 24,358.13 | |||||
| PV Horizon value | $ 15,480.03 | |||||
| Debt | $ 5,000,000 | |||||
| PV of company | $ 13,981.67 | |||||
| Value per share | $ 16.16 | |||||
