I need to know how to lay this out in Excel Jonathan Silver owns a thriving wood
ID: 2718302 • Letter: I
Question
I need to know how to lay this out in Excel Jonathan Silver owns a thriving wood pulp processing plant. He has inherited the business from his “Uncle Woody” but it is not the glamorous industry that he had aspired too during his collegiate career. Dr. Silver has an opportunity to start a new cosmetic line based on wood pulp based lipstick. He needs to determine the current weighted cost of capital and the revised weighted cost of capital with the new more glamorous product line. Since Jonathan has hired many of his college classmates to work for his company, he decided that you were the best in corporate finance and wants you to evaluate and submit your findings to him (the president). Your entire corporate future with “Uncle Woody’s Pulp Processing and Lipstick Corporation” depends on this project being completed correctly. He offers you the following data: Common Stock 200,000 shares @ $10 per share issued on January 1, 2010 Treasury Rate is 3% and market risk Premium is 6% Stock has a beta 1.5 Retained Earnings $850,000 Current Long Term Debt $1,300,000 @ 8% per year Current Corporate tax Rate is 34% The company stock has increased in value at 12.47% per year. Assume today’s date is January 1, 2014. They now pay a dividend of $2.00. (Hint: Dividend growth is 0%) The cost of the new lipstick operation is $440,000. You have two options to fund this project. First Option - A bank loan offered at 9% per year or Second Option - New stock issued at the current stock price on January 1, 2014. You need to identify the following Define the current weighted cost of capital? Define the weighted cost of capital if the entire operation is funded with new stock issues? Which option would you choose?
Explanation / Answer
Answer: Calculation of the current Weighted Cost of capital
Ke=Rf+Beta[Erm-Rf]
=3%+1.5*6%
=12%
Kd=8%(1-0.34)=5.28%
WACC:
Options:
First option =12.47%
Bank loan=9%(1-0.34)=5.94%
Bank loan should be preferred because WACC is less than other options.
Capital structure Values Weight Cost WACC Equity & retained earnings 2850000 0.686747 12% 8.240964 Long Term Debt 1300000 0.313253 5.28% 1.653976 4150000 9.89494