I need to know how to lay this out in Excel and if possible to see the actuall e
ID: 2718306 • Letter: I
Question
I need to know how to lay this out in Excel and if possible to see the actuall equations used in excel. I am trying to understand how it all works . I got an answer 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
Here is the basic formula for weighted cost of capital = ((E/V) * Re) + [((D/V) * Rd)*(1-T)] , where
E = Market value of the company's equity
D = Market value of the company's debt
V = Total Market Value of the company (E + D)
Re = Cost of Equity
Rd = Cost of Debt
T= Tax Rate
Rate of Return expected by stockholder = 3% + 6%*1.5 = 12%
Par value of total shock = $,2000,000
Long term debt cost = 8% per year
Total long term debt value = $ 1,300,000
So, Current Weighted Cost of Capital = (2,000,000/3,300,000 * 12%) +[(1,300,000/3,300,000 * 8%) (1-34%)]
= 9.35%
Weighted cost of capital if the entire operation is funded with new stock issues :
Current value of company stock, increased in value at 12.47% per year, as on January 1, 2014 =
= $10 (1+12.47%)4= $16 per share
Number shares issued for funding $440,000 @ $16 = 27500 shares @ par value $10 per share
(a) Weighted cost of capital if the entire operation is funded with new stock issues=
= (2,275,000/3,575,000 * 12%) + [(1,300,000/3,575,000 * 8%) (1-34%)]
= 9.56 %
(b) Weighted cost of capital if the entire operation of $440,000 is funded with bank loan offered at 9% per year=
=(2,000,000/3,740,000 * 12%) + [(1,300,000/3,740,000 * 8%) (1-34%)] + [(440,000/3,740,000 * 9%) (1-34%)]
= 8.95 % <=
So, new lipstick operation of $440,000 should be funded through Bank Loan option as it bring the Weighted Cost of Capital down to 8.95%.