Chiara Companys management has made the projections shown in Table. Use this Excel spreadsheet as a starting point to value the company as a whole.

Chiara Company’s management has made the projections shown in Table. 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 pershare?

TABLE Historical Forecast Year: -2 1. Sales 2. Cost of goods sold 3. Other costs 4. EBITDA (1-2-3) 5. Depreciation 6. EBIT (Pretax profit) (4-5) 7. Tax at 35% 8. Profit after tax (6-7) 9. Change in working capital 35,348 39,357 40,123 36,351 30,155 28,345 29,982 30,450 17,834 18,564 22,879 21,678 17,560 16,459 15,631 14,987 6,968 7,645 8,025 6,797 5,078 4,678 4,987 5.134 10,546 13,148 9,219 7,876 7,517 7,208 9,364 10,329 5,671 5,745 5,678 5,890 5,670 5,908 6,107 5,908 4,875 7,403 3,541 1,986 1,847 300 3,257 4,421 455 1,140 1,547 845 2,117 2,874 -245 127 235 5,235 6,467 6,547 7,345 5,398 5,470 6,420 6,598 1,706 2,591 1,239 695 646 3,169 4,812 2,302 1,291 1201 784 54 -342 325 566 10. Investment