Please see attached file and answer problems 1 (a - f) and problem 2 (a - e). Also, please include all formulas in the answers. Book reference: Financial Analysis with Mircrosoft Excel 2013, 7 edition, author: Timothy R. Mayes.

Thank you

\f1. The Dempere Imports Company's EPS in 2014 was $1.85, and in 2009 it was $1.12. The company's payout ratio is 40%, and the stock is currently valued at $62.35. Flotation costs for new equity will be 12%. Net income in 2015 is expected to be $15 million. Banker estimate it could sell 10Yr Semi annual bond with coupon of 7%. Face value will be $1000 and floatation cost will be 3% Mkt value weight for debt is 30% and equity 70%. Tax rate is 35% 35% a. Based on the five-year track record, what is Dempere's EPS growth rate? What will the dividend be in 2015? Growth rate g = (future value/Present value)^(1) -1 Future value in 2014 = $1.85 Prsent value in 2009= $1.12 No of yrs = 2014-2009 = 5 So growth rate g = 10.56% Dividend = EPS*Payout ratio Payout Ratio= 40% Dividend 2014 = $0.74 Dividend 2015 = Div 2014*(1+g) = $0.82 b. Calculate the firm's cost of retained earnings and the cost of new common equity. We know that Current Price P0 = D1/(Ks-g) where Ks is Cost of equity Cost of Retaind Earnings Ks = D1/P0 + g D1= $0.82 P0= $62.35 So Costof retained earnings Ks = 11.87% Cost of New Equity will be with Floataion costs Kn = D1/(P0-F) + g Floatation cost = 12% So Kn = 12.05% c. Calculate the break-point associated with retained earnings. BEP = Retained Earnings/Weight of equity RE = $15 Million We = 70% SO BEP = $21.42857 Million d. What is firm AT Cost of New Debt Bond face value FV = $1,000.00 No of years 10 Coupon R 7% No of payment per year 2 Floation cost F = 3% No of period nper = 20 Coupon Payment PMT = R*FV/No of payment in a year $35.00 Present Value PV = FV*(1-F) $970.00 Cost of new Debt Kd =2*rate(nper,pmt,pv,fv) 7.43% After Tax cost of New Debt = Kd*(1-T) = 4.83% e. If Dempere's after-tax cost of debt is 6%, what is the WACC with retained earnings? With new common equity? AT Cost fo debt = Kd*(1-T) = 6% Weight of Debt Wd= Cost of Retaind Earnings Ks = 11.87% Weight of Equity We= Cost of New Common Equity Kn = 12.05% WACC (Using Ks = Wd*Kd*(1-T) + We*Ke 10.11% WACC (Using Kn = Wd*Kd*(1-T) + We*Ke 10.23% f. Scatter Plot Source Price pu Unit Total MV % of Total Debt $970.00 6,100 $5,917,000 30% common equity Total Source Common $62.35 %of Total 225,870 Max Level 70% upto 14M >14M $14,083,000 $20,000,000 AT Cost 70% 100% Break-point 11.87% $7,142,857 12.05% $14,285,714 10.80% 10.60% Debt Total Cap $0.00 $3,333,333 $7,142,857 $14,285,714 $20,000,000 $20,000,000 30% upto 6M >6M Cost of Debt Cost of Eq 4.83% 11.87% 4.83% 11.87% 6.00% 11.87% 6.00% 12.05% 6.00% 12.05% 7.43% 12.05% 10.11% 10.23% WACC 9.76% 9.76% 10.11% 10.23% 10.23% 10.66% $3,333,333 $20,000,000 10.40% 10.20% WACC (%) 10.00% 9.80% 9.60% 9.40% 9.20% $ 30% 70% WACC 10.80% 10.60% 10.40% 10.20% WACC (%) 10.00% 9.80% 9.60% 9.40% 9.20% $0 $5,000,000 $10,000,000 $15,000,000 $20,000,000 $25,000,000 $ Total Capital (a) Source Common Pref Stock Debt Retained Earning Sell Price $28.00 $67.50 $980.00 $150,000 Stock growth rate g = Common Stock Dividend D1 = Current Div Do = D1/(1+g) (b) Source Common Pref Stock Debt Total ( c) Source Common Pref Stock Debt Total (d) Cost of New EquityDiv D1 = Growth g= Float f= Price P0 = Cost of New equity Ks = D1/(Po*(1-F)) + g Book Value $25,000,000 $5,000,000 $8,600,000 $38,600,000 Sell Price $28.00 $67.50 $980.00 Cost of Retained Earnings using CAPM: beta Krf MRP Cost of RE K = Krf + beta*MRP = Cost of Pref Stock : Div Dp = Float f= Price P = Cost of Pref Stock Kp = D/(P*(1-F)) Float cost Div per share 8% $1.51 4% $7.00 2% Tax Rate Growth Rat Beta Krf MRP 35% 6% 1.25 3% 7% 6% $1.60 $1.51 Weight 65% 13% 22% 100% Qty 1250000 100000 8600 Mkt Value Weight $35,000,000 70% $6,750,000 13% $8,428,000 17% $50,178,000 100% $1.60 6% 8% $28.00 12.21% 1.25 3.00% 7.00% 11.75% $7.00 4% $67.50 10.80% Cost of New Debt : Bond issue 2015 Bond Mature 2040 No of Yrs 25 Bond face value FV = $1,000.00 No of years Coupon R 8% No of payment per year Floation cost F = 2% No of period nper = Coupon Payment PMT = R*FV/No of payment in a year Present Value PV = FV*(1-F) $980.00 25 2 50 $40.00 Cost of new Debt Kd = After Tax cost of New Debt ( e) Using Book Weights & Cost of Retained Earnings WACC 10.20% Using Book Weights & Cost of New Equity WACC 10.49% Using Mkt Weights & Cost of Retained Earnings WACC 10.54% Using Mkt Weights & Cost of New Equity WACC 10.86% 8.19% 5.32% Bond details Settlement Date June 30, 2015 Maturity Date June 30, 2040 Coupon Rate 8.00% $1,000.00 Face Value $980.00 Present Value Freq 2 Basis 0 \f1. The Dempere Imports Company's EPS in 2014 was $1.85, and in 2009 it was $1.12. The company's payout ratio is 40%, and the stock is currently valued at $62.35. Flotation costs for new equity will be 12%. Net income in 2015 is expected to be $15 million. Banker estimate it could sell 10Yr Semi annual bond with coupon of 7%. Face value will be $1000 and floatation cost will be 3% Mkt value weight for debt is 30% and equity 70%. Tax rate is 35% 35% a. Based on the five-year track record, what is Dempere's EPS growth rate? What will the dividend be in 2015? Growth rate g = (future value/Present value)^(1) -1 Future value in 2014 = $1.85 Prsent value in 2009= $1.12 No of yrs = 2014-2009 = 5 So growth rate g = 10.56% Dividend = EPS*Payout ratio Payout Ratio= 40% Dividend 2014 = $0.74 Dividend 2015 = Div 2014*(1+g) = $0.82 b. Calculate the firm's cost of retained earnings and the cost of new common equity. We know that Current Price P0 = D1/(Ks-g) where Ks is Cost of equity Cost of Retaind Earnings Ks = D1/P0 + g D1= $0.82 P0= $62.35 So Costof retained earnings Ks = 11.87% Cost of New Equity will be with Floataion costs Kn = D1/(P0-F) + g Floatation cost = 12% So Kn = 12.05% c. Calculate the break-point associated with retained earnings. BEP = Retained Earnings/Weight of equity RE = $15 Million We = 70% SO BEP = $21.42857 Million d. What is firm AT Cost of New Debt Bond face value FV = $1,000.00 No of years 10 Coupon R 7% No of payment per year 2 Floation cost F = 3% No of period nper = 20 Coupon Payment PMT = R*FV/No of payment in a year $35.00 Present Value PV = FV*(1-F) $970.00 Cost of new Debt Kd =2*rate(nper,pmt,pv,fv) 7.43% After Tax cost of New Debt = Kd*(1-T) = 4.83% e. If Dempere's after-tax cost of debt is 6%, what is the WACC with retained earnings? With new common equity? AT Cost fo debt = Kd*(1-T) = 6% Weight of Debt Wd= Cost of Retaind Earnings Ks = 11.87% Weight of Equity We= Cost of New Common Equity Kn = 12.05% WACC (Using Ks = Wd*Kd*(1-T) + We*Ke 10.11% WACC (Using Kn = Wd*Kd*(1-T) + We*Ke 10.23% f. Scatter Plot Source Price pu Unit Total MV % of Total Debt $970.00 6,100 $5,917,000 30% common equity Total Source Common $62.35 %of Total 225,870 Max Level 70% upto 14M >14M $14,083,000 $20,000,000 AT Cost 70% 100% Break-point 11.87% $7,142,857 12.05% $14,285,714 10.80% 10.60% Debt Total Cap $0.00 $3,333,333 $7,142,857 $14,285,714 $20,000,000 $20,000,000 30% upto 6M >6M Cost of Debt Cost of Eq 4.83% 11.87% 4.83% 11.87% 6.00% 11.87% 6.00% 12.05% 6.00% 12.05% 7.43% 12.05% 10.11% 10.23% WACC 9.76% 9.76% 10.11% 10.23% 10.23% 10.66% $3,333,333 $20,000,000 10.40% 10.20% WACC (%) 10.00% 9.80% 9.60% 9.40% 9.20% $ 30% 70% WACC 10.80% 10.60% 10.40% 10.20% WACC (%) 10.00% 9.80% 9.60% 9.40% 9.20% $0 $5,000,000 $10,000,000 $15,000,000 $20,000,000 $25,000,000 $ Total Capital (a) Source Common Pref Stock Debt Retained Earning Sell Price $28.00 $67.50 $980.00 $150,000 Stock growth rate g = Common Stock Dividend D1 = Current Div Do = D1/(1+g) (b) Source Common Pref Stock Debt Total ( c) Source Common Pref Stock Debt Total (d) Cost of New EquityDiv D1 = Growth g= Float f= Price P0 = Cost of New equity Ks = D1/(Po*(1-F)) + g Book Value $25,000,000 $5,000,000 $8,600,000 $38,600,000 Sell Price $28.00 $67.50 $980.00 Cost of Retained Earnings using CAPM: beta Krf MRP Cost of RE K = Krf + beta*MRP = Cost of Pref Stock : Div Dp = Float f= Price P = Cost of Pref Stock Kp = D/(P*(1-F)) Float cost Div per share 8% $1.51 4% $7.00 2% Tax Rate Growth Rat Beta Krf MRP 35% 6% 1.25 3% 7% 6% $1.60 $1.51 Weight 65% 13% 22% 100% Qty 1250000 100000 8600 Mkt Value Weight $35,000,000 70% $6,750,000 13% $8,428,000 17% $50,178,000 100% $1.60 6% 8% $28.00 12.21% 1.25 3.00% 7.00% 11.75% $7.00 4% $67.50 10.80% Cost of New Debt : Bond issue 2015 Bond Mature 2040 No of Yrs 25 Bond face value FV = $1,000.00 No of years Coupon R 8% No of payment per year Floation cost F = 2% No of period nper = Coupon Payment PMT = R*FV/No of payment in a year Present Value PV = FV*(1-F) $980.00 25 2 50 $40.00 Cost of new Debt Kd = After Tax cost of New Debt ( e) Using Book Weights & Cost of Retained Earnings WACC 10.20% Using Book Weights & Cost of New Equity WACC 10.49% Using Mkt Weights & Cost of Retained Earnings WACC 10.54% Using Mkt Weights & Cost of New Equity WACC 10.86% 8.19% 5.32% Bond details Settlement Date June 30, 2015 Maturity Date June 30, 2040 Coupon Rate 8.00% $1,000.00 Face Value $980.00 Present Value Freq 2 Basis 0