a. In Excel: Value a company using the following information: i . Bonds Outstand

Question

a. In Excel: Value a company using the following information:

i. Bonds Outstanding: 8,000 bonds selling at 97 with a face value of $1,000, coupon rate of 4.5%, annual coupons, and 22 years to maturity.

ii. Common Stock Outstanding: 10 million shares outstanding, price of $94, risk-free rate of .005, expected market return of .125, and beta of 1.4.

iii. Preferred Stock Outstanding: 1 million shares outstanding, price of $10, and dividend of $0.40

iv. Tax rate: 39%

v. Projections:

1. Sales of $320 million expected to grow at 7% per year over the next 10 years.

2. COGS of $75 million expected to grow at 4% per year over the next 10 years.

3. Depreciation expense expected to stay constant at $25 million over the next 10 years.

4. Net capital spending of $70 million next year, with no other net capital spending expected after the first year.

5. Change in net working capital of 5% of sales per year.

6. All above listed items are expected to settle to a constant growth of 3% per year after the 10th year, except depreciation which will become $0 after year 10.

Explanation / Answer

Details Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Terminal Sales      352.00      387.20      425.92      468.51      515.36      566.90      623.59      685.95      754.54      830.00      854.90 COGS 78         81.12         84.36         87.74         91.25         94.90         98.69      102.64      106.75      111.02      114.35 Depreciation 25 25 25 25 25 25 25 25 25 25 25 Profit Before Tax      249.00      281.08      316.56      355.77      399.11      447.00      499.89      558.31      622.79      693.98      715.55 Tax         97.11      109.62      123.46      138.75      155.65      174.33      194.96      217.74      242.89      270.65      279.06 PAT      151.89      171.46      193.10      217.02      243.46      272.67      304.94      340.57      379.90      423.33      436.48 Add: Depreciation 25 25 25 25 25 25 25 25 25 25 25 Cash Profit      176.89      196.46      218.10      242.02      268.46      297.67      329.94      365.57      404.90      448.33      461.48 Less Capital Spending -70 Change in Workng Capital           1.60           1.76           1.94           2.13           2.34           2.58           2.83           3.12           3.43           3.77           0.75 Net Cash flow      108.49      198.22      220.03      244.15      270.80      300.25      332.77      368.68      408.33      452.10      462.23 WACC 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% DF         0.867         0.753         0.653         0.566         0.491         0.426         0.370         0.321         0.278         0.241           0.15 Discounted Cash flow         94.11      149.16      143.63      138.25      133.02      127.94      123.01      118.22      113.58      109.09 3,025.54 Total value of the Firm 4,275.56 Workings Details Details Cost Weight=Details Weight WACC Bond 2.88%       77,60,000.00 0.8% 0.02% Preference D0/P0 4.0%     100,00,000.00 1.0% 0.04% Equity Rf+(Rm-Rf)*Beta 15.50% 9400,00,000.00 98.1% 15.21% 15.28% Cost of Bond Market Price(Present Value of the Bond) C*((1-1+r^-n)/r)+(P*(1+r^-n)) Where C= annual Coupon amount=1000*4.5% 45 r=Required rate(YTM) 4.72% P=Principal 1000 n=No of periods=22 22 Market Price(Present Value of the Bond)                       970.29 Cost of Bond is the rate which equates cash flow to current Market price 4.72% After ax cost of Bond 2.88%

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.