Create a single page excel sheet. Use functions and references to create a working model. Do not hard code values in any other cells than your assumption section. You must record all assumptions in the worksheet in a column labeled Assumptions. This can be above or below your model.

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.