Microsoft Excel must be used. Formulas and Functions must be used as much possible with functions given priority if it's available to solve.

Project A has forecasted an outlay of $200,000 for the equipment and an additional $40,000 for installation. The firm will see an increase of $25,000 in inventories immediately to undertake this project, but they will also see an immediate increase in accounts payable of $5,000. There will be a return of the net working capital change at the end of the fourth year. Also, at the end of the fourth year the firm will sell the asset for a salvage value of $25,000. The firm has a variable cost of operations that is equivalent to 60% of sales, a sales price of $2.00 with no assumed annual inflation, and a tax rate of 26%. Depreciation will fall into the 3-year MACRS class. Unit sales are forecast to be 100,000; 110,000; 100,000; and 100,000 respectively for the first four years.

Project B has forecasted an outlay of $176,000 for the equipment and an additional $40,000 for installation. The firm will see an increase of $25,000 in inventories immediately to undertake this project, but they will also see an immediate increase in accounts payable of $25,000. There will be a return of the net working capital change at the end of the fourth year. Also, at the end of the fourth year the firm will sell the asset for a salvage value of $45,000. The firm has a variable cost of operations that is equivalent to 70% of sales, a sales price of $2.50 with no assumed annual inflation, and a tax rate of 26%. Depreciation will fall into the 3-year MACRS class. Unit sales are forecast to be 75,000; 90,000; 90,000; and 100,000 respectively for the first four years.

Both projects are considered to have similar risk. The firm has a beta of 1.10 with the risk-free rate of 5% and a market risk premium of 6%. Dividends are forecasted to be $1 with a current price of $15 and a growth rate of 4%. The bond-yield-risk premium is 3%. Current semiannual bonds are priced at $800 with 25 years to maturity and a coupon rate of 8%. The firm has no notes payable. Preferred stock has a face amount of $100 with a 7% dividend rate and are currently selling for $98 each. The firm can fund this asset growth with reinvested earnings, so they would not have to sell new securities.

The firm currently has 40,000 bonds outstanding at the $1,000 par value for use with book value and market value calculations. It also has 100,000 shares of preferred stock outstanding at the stated par value for the same use. The firm has 4,000,000 shares of common stock outstanding with the accountants indicating a book value of $30,000,000 (just giving the total here) of all common stock accounts combined. Management has just set a target capital structure of 30% debt; 10% preferred stock; and 60% common equity.

Questions:

1.) Calculate the weights if the firm used book values? Market values? Target values? Next discuss which approach is theoretically most sound and explain why for this particular scenario for this particular firm.

2.) Calculate the weighted average cost of capital using each of the three approaches in #1. Discuss your answers and highlight the rate you will use in the future questions based on your reasoning from #1.

3.) Calculate the cash flows for each of the two projects. Note these projects should be assumed to be mutually exclusive projects for the firm.

4.) Calculate the Payback Period, Internal Rate of Return, Net Present Value, and Profitability Index for each project. Use the same rate for both projects developed in #2.

5.) What decision should you make regarding acceptance or rejection by each individual technique from #1 above (NPV? PBP? PI? IRR?) for each project? Explain the reasoning for each decision. Assume managerial criteria of 3.50 for the PBP technique. In this question, you will ignore the mutual exclusive nature of the projects.

Please answer 1 through 5.