Question: What do you project that the current value of the stock should be if your assumptions are correct?

Your firm is considering investing its clients money in the stock of a publicly traded company and your manager has asked you to build a discounted cash flow model to determine if stock is a good investment. The stock is currently trading for $24.75 per share. After doing some research, you have gathered the following information:

Sales in fiscal year 2020 were $185,125. COGS as a percentage of sales were 75%. Net Working Capital as a percentage of sales was 23% and depreciation as percentage of fixed assets was 3%. Fixed assets as a percentage of sales was 50%. The firms sales growth rate for the next six years is expected to be 11% per year and the growth rate from year 7 thereafter is expected to be 2.9% per year.

Given recent political changes, your firm estimates that the long-term tax rate for the company is expected to be 26%. The company has a market value of $300,000 in debt and 21,000 shares of common stock outstanding. The risk-free rate is 2.1% and the expected return on the market is 10.1%. The beta of the stock is 0.9.

The firm has two, $1000 par, semi-annual bond issues outstanding. The first issue is currently priced at 97% of par, pays an 5% coupon rate, will be redeemed for 100% of the bonds face value, had a settlement date of 11/10/2020 and a maturity date of 11/10/2030.

The second issue is a group of zero-coupon bonds, currently priced at 59.425% of par, will be redeemed for 100% of the bonds face value, had a settlement date of 11/10/2020 and a maturity date of 11/10/2035.

The zero-coupon bonds represent 75% of the market value of the bonds outstanding for the firm. The overall weight of debt in the capital structure is 30%.

Your manager also suggested that if you needed help with the YTM in calculations in Excel, you should check out (see image below)

Excel YIELD Function F6 =YIELD(C9,010,C7,F5, C6, C12, C13) A B D E F 1 2. 3 YIELD function YIELD (settlement, maturity, rate, pr, redemption, frequency, basis) 4 5 Value of bond (% of Par) Yield $97.56 8.00% 6 7 8 9 10 Face Value Redemtion value (% of Par) Annual coupon rate Required return Settlement date Maturity date Years to maturity Payment frequency Basis $1,000.00 100.00 7.00% 8.00% 15-Dec-2017 15-Sep-2020 3 2 0 11 12 13 14 15 16 EXCELJET Excel YIELD Function F6 =YIELD(C9,010,C7,F5, C6, C12, C13) A B D E F 1 2. 3 YIELD function YIELD (settlement, maturity, rate, pr, redemption, frequency, basis) 4 5 Value of bond (% of Par) Yield $97.56 8.00% 6 7 8 9 10 Face Value Redemtion value (% of Par) Annual coupon rate Required return Settlement date Maturity date Years to maturity Payment frequency Basis $1,000.00 100.00 7.00% 8.00% 15-Dec-2017 15-Sep-2020 3 2 0 11 12 13 14 15 16 EXCELJET