USING EXCEL WITH DETAILED. STEPS

During the last few years the Coca-Cola Company (KO US) has been too constrained by the high cost of capital to make many capital investments. Recently, though, capital costs have been declining, and the company has decided to look seriously at a major expansion program that has been proposed by the marketing department. Assume that you are currently offered to work as an intern for the CFO.

a-Your first task is to estimate the Coca-Cola Company (KO US) weighted average cost of capital (WACC) and you have been provided with the following most recent data:

The firm's tax rate =30%

The Coca-Cola Company (KO US) has issued 6% coupon semi-annual payment non-callable bonds, with a maturity date of 2027.The bonds are sold today 25th of May 2021 at $1022. New bonds would be privately placed with no flotation cost.

The current price of the firm's preferred stock is $160.10, and the dividend is $10. The Coca-Cola Company (KO US) will incur flotation cost of $2 per share on a new issue.

The Coca-Cola Company (KO US) common stock is currently selling at $54.62 per share. Its last dividend (D0) was $1.65, and dividends are expected to grow at 3%in the foreseeable future. The Coca-Cola Companys (KO US) beta is 0.56, the yield on T-bonds is 4% and the average return on the market is 10%.

a-The target capital structure is 25% for long term debt, 5% for preferred stock and 70% for common equity. What is The Coca-Cola Company (KO US) weighted average cost of capital (WACC)?

b- What is the direct effect of a decrease of the risk free rate on the WACC. Show using the previous data to explain your answer.