Calculation of individual cost and WAAC. Dillion Labs has asked its financial manager to measure the cost of each specific type of capital as well as the weighted average cost of capital. The weighted average cost is to be measured by using the following weights: 35% long term-debt, 25% preferred stock, and 40% common stock equity (retained earnings, new common stock, or both). The firm's tax rate is .35%

See the attached document for additional information and data.

NOTE: Calculations to show the results must be displayed in an Excel Sheet format.

Please disply results in an Excel spreadsheet format. Dillion Labs a Calculating after-tax cost of debt YTM [Pre-tax cost of debt] Using appromation formula Nper PMT PV FV YTM $ $ $ 14 90.00 (935.00) 1,000.00 9.04% After tax cost of debt Pre-tax cost of debt, YTM Tax rate After tax cost of debt b 9.04% 35% 5.88% Preferred stock cost Annual Dividends Current price Flotation costs Cost of the preferred stock $ $ $ 7.50 75.00 4.00 10.56% $ $ 3.16 70.00 7.03% 11.54% c Cost of retained earning Dividend next year Price growth rate Cost of retained earnings New Cost of common stock To calculate g, growth rate Dividend 2011 Dividend 2015 g Dividend 2016, D1 Price Po Flotation cost Undervaluing g Cost Common stock d Dillion WACC 2.25 3.16 7.03% $ $ $ 3.16 70.00 4.00 8 7.03% 12.48% Using the cost of retained earning , WACC's Component of capital structure Debt Preferred stock Common stock equity Weight 35% 25% 40% WACC 9.31% Using the cost of new common stock, WACC's Component of capital structure Debt Preferred stock Common stock equity WACC Weight 35% 25% 40% 9.69% Formula Less 2% flotation cost 955-[1000*2%] rd= [C+[F-P]/ n]/[F+P]/2 D Po Underwriting cost rp= D/ [Po-flotation costs] D1 Po g re= D1/Po+g D16= D2011 *[ 1+g]^5 g= [D2016/ D 2011)^(1/5) -1 D1 Po F U g rs = D1/ [Po-F-U] +g Cost of the component capitalm 5.88% 10.56% 11.54% WACC= Wd*Kd+ Wp*Kp+ We*Ke Cost of the component capitalm 5.88% 10.56% 12.48% WACC= Wd*Kd+ Wp*Kp+ We*Ke Initial Investment Annual cash inflows Cost of Capital 2300000 270,000 11% PV of Cash flow Intial Investment PV of annual Cash flow NPV Annual EVA Overall EVA FormulaProject for calculating EVA: -2300000 2454545 154545 17,000.00 154545 Annual cash inflows-Initial Investment*Cost of Ca Initial Investment*Cost of Capital Dillion Labs a Calculating after-tax cost of debt YTM [Pre-tax cost of debt] Using appromation formula Nper PMT PV FV YTM $ $ $ 14 90.00 (935.00) 1,000.00 9.04% After tax cost of debt Pre-tax cost of debt, YTM Tax rate After tax cost of debt b 9.04% 35% 5.88% Preferred stock cost Annual Dividends Current price Flotation costs Cost of the preferred stock $ $ $ 7.50 75.00 4.00 10.56% $ $ 3.16 70.00 7.03% 11.54% c Cost of retained earning Dividend next year Price growth rate Cost of retained earnings New Cost of common stock To calculate g, growth rate Dividend 2011 Dividend 2015 g Dividend 2016, D1 Price Po Flotation cost Undervaluing g Cost Common stock d Dillion WACC 2.25 3.16 7.03% $ $ $ 3.16 70.00 4.00 8 7.03% 12.48% Using the cost of retained earning , WACC's Component of capital structure Debt Preferred stock Common stock equity Weight 35% 25% 40% WACC 9.31% Using the cost of new common stock, WACC's Component of capital structure Debt Preferred stock Common stock equity WACC Weight 35% 25% 40% 9.69% Formula Less 2% flotation cost 955-[1000*2%] rd= [C+[F-P]/ n]/[F+P]/2 D Po Underwriting cost rp= D/ [Po-flotation costs] D1 Po g re= D1/Po+g D16= D2011 *[ 1+g]^5 g= [D2016/ D 2011)^(1/5) -1 D1 Po F U g rs = D1/ [Po-F-U] +g Cost of the component capitalm 5.88% 10.56% 11.54% WACC= Wd*Kd+ Wp*Kp+ We*Ke Cost of the component capitalm 5.88% 10.56% 12.48% WACC= Wd*Kd+ Wp*Kp+ We*Ke Initial Investment Annual cash inflows Cost of Capital 2300000 270,000 11% PV of Cash flow Intial Investment PV of annual Cash flow NPV Annual EVA Overall EVA FormulaProject for calculating EVA: -2300000 2454545 154545 17,000.00 154545 Annual cash inflows-Initial Investment*Cost of Ca Initial Investment*Cost of Capital