Julie Nielsen, CFO for Chadwick Manufacturing, is preparing to submit the capital budget for the next fiscal year to CEO Evelyn Chadwick. Before she can submit the capital budget, she needs you to double-check her calculations for the firms weighted average cost of capital, to make certain that the capital budget reflects the correct cost of funds. Given that her proposed capital budget for next year is $40.75 million, she wants you to confirm (or refute) all of the associated costs in your own spreadsheet.

You are given the following data to work with:

• The book values of short-term and long-term debt are $2.8 million and $17.2 million respectively;
• The firm has 200,000 shares of preferred equity that have a market price of $75.00 and pay an annual dividend of $5.00 per share;
• The firm has 800,000 shares of common stock that currently sell for $35.00, and just paid an annual dividend of $1.10;
• The firms common dividend is expected to grow at a constant rate of 6.00% annually for the foreseeable future;
• Any new debt issued by the firm, short-term or long-term, will pay an annual coupon rate of 7.20%;
• Issuing new preferred stock will cost the firm $4.00 per share in flotation costs;
• Issuing new common stock will cost the firm 8.00% of the market price in flotation costs;
• Chadwick Manufacturing has a beta of 1.45;
• The annual yield on 10-year T-notes is 3.25%;
• The annual yield on the S&P 500 index fund is 7.45%;
• The firm is expected to have $16.5 million in Retained Earnings available for the capital budget;
• The firms marginal tax rate is 21.00%.

These data are to be entered into an Excel worksheet that is clear and easy to read, with all inputs and calculations clearly marked. Either in a different part of the same worksheet or on a different worksheet, you will do the calculations needed to address the issues in the next section.

Your assignment is to address the following issues:

• Calculate the values for debt, preferred equity and common equity, and use those values to calculate the weights of each source of funds in the capital structure (the weights to use in the WACC calculation);
• Calculate the component costs of all four sources of funds, including any tax benefits as well as any flotation costs for issuing new securities (calculate the cost of retained earnings in two ways using the DDM and the CAPM approaches and then use these data to determine what cost to assign for retained earnings);
• Plug the weights and component costs into the WACC equation and calculate the firms WACC using both retained earnings and new common equity;
• Calculate the retained earnings break point for the level of retained earnings indicated, and determine if funding the capital budget of $40,750,000 will require issuing new common stock;
• Based on the relationship between the REBP and the total capital budget, identify Chadwicks marginal cost of capital.

All calculations are to be done in an Excel spreadsheet, using formulas or Excel functions hard coded calculations will earn no credit; you must make Excel do the work. Format all dollar values as dollars, and all percentages with a percent sign. Format all percentages to two decimal places (0.023485 = 2.35%), but do not round the calculation -- just change the format of the number, not its value. Format all dollar figures to the dollar (no cents).