Create an Excel spreadsheet. Do all of your work in a single Excel tab. You should format the spreadsheet so that it will print on no more than two sheets of paper. use functions and references to create a working model. Do not hard code values in any cell, other than your assumption section. Use the =Yield() function to help determine the yield to maturity of outstanding debt. All calculations should be done in the spreadsheet. You must also record all assumptions in a section of your worksheet which is you label Assumptions. The assumptions section can be above or below your model. use the percentage of sales approach and the given assumptions to build your model. Your model should determine the price per share for a company with the following information:

Sales in the base year were $295,875. Net Working Capital as a percentage of sales was 23% and depreciation as percentage of gross assets was 3%. COGS as a percentage of sales were 75% and gross fixed assets as a percentage of sales were 50%. The growth rate for years 1-4 is expected to be 16% and the growth rate from year 5 thereafter is expected to be 1.8%. The tax rate for the company is expected to be 35%. The company has 41,000 shares outstanding. The risk-free rate is 2.1% and the expected return on the market is 10.1%. The beta of the stock is 1.2. The companys capital structure is composed both debt and common stock. There is no preferred stock outstanding. The target weight of debt in the capital structure is 44.5%.

The company has three different bond issues outstanding.

The first bond issue is composed of 100 semiannual, $1000 par bonds with a settlement date of 3/5/2020 and a maturity date of 3/5/2030, a coupon rate of 6% and a market price of $970. At maturity, the bonds are expected to pay 100% of their face value.

The second bond issue is composed of 1500, semiannual, $1000 par bonds with a settlement date of 1/1/2015 and a maturity date of 1/1/2045. They are zero coupon bonds and have a market price of $115. At maturity, the bonds are expected to pay 100% of their face value.

The third bond issue is composed of 200, semiannual, $1000 par bonds with a settlement date of 1/15/2022 and a maturity date of 1/15/2052. The coupon rate on the bonds is 6% and they have a market value of $1100. At maturity, the bonds are expected to pay 100% of their face value.

Your working model should determine a price per share and if changes are made in the assumptions section, all changes should flow through to the new price per share.