Financial Management Excel Project. See attatched excel file.

A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 B C D E F G H Chapter 9. Comprehensive/Spreadsheet problem Taussig Technologies Corporation (TTC) has been growing at a rate of 20% per year in recent years. This same growth rate is expected to last for another 2 years, then to decline to g n = 6%. a. If D0 = $1.60 and rs = 10%, what is TTC's stock worth today? What are its expected dividend and capital gains yields at this time, that is, during Year 1? 1. Find the price today. D0 rs gs gn $1.60 10.0% 20% 6% Year Dividend 0 $1.6000 Short-run g; for Years 1-2 only. Long-run g; for Year 3 and all following years. 20% 6% 1 2 3 PV of dividends = Horizon value = P2 = = rs - gn = P0 2. Find the expected dividend yield. Recall that the expected dividend yield is equal to the next expected annual dividend divided by the price at the beginning of the period. D1 Dividend yield = Dividend yield = Dividend yield = / / P0 3. Find the expected capital gains yield. The capital gains yield can be calculated by simply subtracting the dividend yield from the expected total return. Cap. gain yield = Expected total return Cap. gain yield = 10.0% Cap. gain yield = Dividend yield b. Now assume that TTC's period of supernormal growth is to last for 5 years rather than 2 years. How would this affect the price, dividend yield, and capital gains yield? 1. Find the price today. D0 rs gs gn $1.60 10.0% 20% 6% Year Dividend 0 $1.6000 Short-run g; for Years 1-5 only. Long-run g; for Year 6 and all following years. 20% 6% 1 2 3 4 5 6 PV of dividends = Horizon value = P5 = = rs gn = P0 Part 2. Find the expected dividend yield. Dividend yield = Dividend yield = Dividend yield = D1 / / P0 - Dividend yield Part 3. Find the expected capital gains yield. Cap. gain yield = Expected total return Cap. gain yield = 10.0% Cap. gain yield = I J K L M 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 A B C D E F G d. TTC recently introduced a new line of products that has been wildly successful. On the basis of this success and anticipated future success, the following free cash flows were projected: Year 1 2 3 4 5 6 7 8 9 10 H I J K L 7 $107.5 8 $128.9 9 $147.1 10 $161.3 11 FCF (in millions) $5.5 $12.1 $23.8 $44.1 $69.0 $88.8 $107.5 $128.9 $147.1 $161.3 After the 10th year, TTC's financial planners anticipate that its free cash flow will grow at a constant rate of 6%. Also, the firm concluded that the new product caused the WACC to fall to 9%. The market value of TTC's debt is $1,200 million, it uses no preferred stock, and there are 20 million shares of common stock outstanding. Use the corporate valuation model approach to value the stock. INPUT DATA: (Dollars in Millions) WACC gn Millions of shares MV of debt Year 0 FCF's PV of FCF's 9% 6% 20 $1,200 9% 1 $5.5 2 $12.1 3 $23.8 4 $44.1 5 $69.0 6 $88.8 PV of FCF1-10 = HV at Year 10 of FCF after Year 10 = FCF11/(WACC - gn): PV of HV at Year 0 = HV/(1+WACC)10: Sum = Value of the Total Corporation Less: MV of Debt and Preferred Value of Common Equity Number of Shares (in Millions) to Divide By: Value per Share = Value of Common Equity/No. Shares: versus using the discounted dividend model The price as estimated by the corporate valuation method differs from the discounted dividends method because different assumptions are built into the two situations. If we had projected financial statements, found both dividends and free cash flow from those projected statements, and applied the two methods, then the prices produced would have been identical. As it stands, though, the two prices were based on somewhat different assumptions, hence different prices were obtained. Note especially that in the FCF model we assumed a WACC of 9% versus a cost of equity of 10% for the discounted dividend model. That would obviously tend to raise the price. M