Using excel for solutions Part A Smith-John Widgets, Inc. has received an offer from two major manufacturing companies for the latest product offering. Based on the length of the contracts he has asked you to evaluate the offers using discounted cash flow methods by applying the time value of money concepts. The two offers are outlined as follows: Offer A: This potential customer will pay the net profit, discounted at 9%, for the purchase of the product given the sales level as follows for the next five years: Year 1 Year 2 Year 3 Year 4 Year 5 Sales: 815,200 1,019,000 1,222,800 1,467,360 1,614,09 The cost of sales are assumed to be consistent at 68% of revenues and there will be additional operating expenses to support this customer of $185,000 the first year, rising by 14% each year thereafter. The present value will be discounted at 7%. Offer B: This potential customer will pay Smith-John based on the assumed gross profit of the customer, less a special marketing fund charge of $18,000 per year. The assumed gross profit under the proposed contract would begin at $72,500 for the first year, increasing by 30% each year thereafter. This contract would pay the funds today and would not be discounted. In addition, however, the contract has a performance clause that provides for a final payment at the end of five years of $125,000. This payment would be discounted for analysis purposes at the 7% rate. Because of current production capacity, Smith-John can only accept one of the offers. Required: Make as comparison of each of the offers and then indicate which one has the higher value and, thus, should be accepted. Part B Because of the increased demand for its product, Smith-John Widgets, Inc. is in the process of expanding the production capacity. Current plans call for a possible expenditure of $400 thousand on the project. The funds for this project can be obtained by either increasing the borrowing level, issuing new Preferred Stock, or issuing new Common Stock. The current tax rate is 35%. The current capital structure, showing the current yield on the debt and the current market price for the preferred and common stock, is as follows: Yield/ Dividened Amount Prie Debt 8.0% 625,000 Preferred Stock $2.20 100,000 $72.00 Common Equity $0.75 725,000 $45.00 The Chief Financial Officer has decided to issue new debt, issue additional Preferred Stock, and sell additional Common Stock shares. The flotation cost for the Preferred Stock will be $4.75 and for Common Stock it will be $3.25. The new capital structure will be as follows should this plan be implemented: Yield/ Dividened Amount Price Float Cost Debt 7.5% 775,000 Preferred Stock $2.20 150,000 $72.00 $ 4.75 Common Equity $0.75 925,000 $45.00 $3.25 Required: Step 1 - Calculate the cost of capital for debt, preferred stock, common equity in the form of retained earnings, and the weighted average cost of capital, prior to the new plan being implemented. Note that the growth rate for the common stock dividend is 8% and the dividend presented above is the current dividend. Step 2 - After the issuance of debt, preferred and common stock, calculate the cost of capital for debt, new preferred stock, new common stock, and the weighted average cost of capital, given the execution of the new financing plan to add new capita