case 10 aspeon excel

ASPEON SPARKLING WATER, INC. Capital Structure Policy This case illustrates the capital structure decision for a firm that begins with zero debt financing. The numerical analysis assumes that all cash flows are perpetuities. The model uses income statements to examine the effect of financial leverage on ROE and risk. Additionally, the model uses valuation equations to calculate the impact of debt financing on firm value and stock price. Note that the model extends out to Column H. The spreadsheet contains three graphs: [1] a plot of debt level versus stock price and EPS, [2] a graph of the effect of the tax rate on the optimal debt level, and [3] a graph of the effect of changes in EBIT on the optimal debt level. If you are using the student version of the model, the cells containing formulas have been blanked out. Before using this model, it is necessary to fill in the empty cells with the appropriate formulas. Once this is done, the model is ready for use. ================ ================ ================ ================ ================ INPUT DATA: KEY OUTPUT: Operating Data: Total assets Cost of debt EBIT Tax rate No. of shares Impact on ROE: $120,000,000 13.0% $32,000,000 40% 10,000,000 All equity: Expected ROE SD of ROE Expected ROE SD of ROE kd Valuation Analysis: ----------$0 25,000,000 50,000,000 75,000,000 100,000,000 125,000,000 D 24.2% 15.6% 50/50 mix: Capital Cost Data: D 16.0% 7.8% ------0 10.0% 11.0% 13.0% 16.0% 20.0% D ks ----------$0 25,000,000 50,000,000 75,000,000 100,000,000 125,000,000 P ----------$0 25,000,000 50,000,000 75,000,000 100,000,000 125,000,000 ------$12.00 13.23 14.09 14.53 14.57 14.12 ------16.0% 16.5% 17.5% 19.0% 21.0% 26.0% ================ ================ ================ ================ ================ ================ ================ ================ TABLE 1. MODEL-GENERATED DATA ROE Analysis: -----> -----> -----> 50% Debt All Equity -----------------------------------0.25 $10,000,000 0 ----------$10,000,000 4,000,000 ----------$6,000,000 =========== 5.0% n.a. Probability EBIT Interest EBT Taxes Net Income ROE TIE E(ROE) Std dev ROE CV 0.50 $32,000,000 0 ----------$32,000,000 12,800,000 ----------$19,200,000 =========== 16.0% n.a. 16.0% 7.8% 0.49 -----------------------------------0.25 0.25 $10,000,000 $54,000,000 0 7,800,000 --------------------$54,000,000 $2,200,000 21,600,000 880,000 --------------------$32,400,000 $1,320,000 =========== =========== 27.0% 2.2% n.a. 1.28 Valuation Analysis: D (000s) -----> S ----------$0 $25,000 $50,000 $75,000 $100,000 $125,000 ----------$120,000,000 107,272,727 90,857,143 70,263,158 45,714,286 16,153,846 V D/V ----------120,000,000 132,272,727 140,857,143 145,263,158 145,714,286 141,153,846 P ------0% 19% 35% 52% 69% 89% -----> WACC ------$12.00 13.23 14.09 14.53 14.57 14.12 0.50 $32,000,000 7,800,000 ----------$24,200,000 9,680,000 ----------$14,520,000 =========== 24.2% 4.10 24.2% 15.6% 0.64 0.25 $54,000,000 7,800,000 ----------$46,200,000 18,480,000 ----------$27,720,000 =========== 46.2% 6.92 -----> # Shares ------16.0% 14.5% 13.6% 13.2% 13.2% 13.6% ----------10,000,000 8,109,966 6,450,304 4,836,957 3,137,255 1,144,414 EPS ------$1.92 2.18 2.47 2.76 3.06 3.67 /gnu~q Optimal Debt Level: /gnu{left}~q SENSITIVITY ANALYSES: /gnu{right}~q Tax Rate Optimal Debt Level 5 $4,000,000 16.0% $12,800,000 27.0% 21,600,000 2.2% 880,000 24.4% 9,680,000 46.2% 18,480,000 EBIT Optimal Debt Level $10,000,000 $32,000,000 $54,000,000 $10,000,000 $32,000,000 $54,000,000 ================ ================ ================ ================ ================ ================ ================ ================ END Effect of Tax on Optima l Debt Level 12 10 8 6 4 2 0 1 2 3 4 5 Column B 6 7 Effect of EBIT on Optima l Debt Level 12 10 8 6 4 2 0 1 2 3 4 5 Column E 6 7 Price a nd EPS ASPEON SPARKLING WATER, INC. Debt versus Price an d EPS 12 10 8 6 4 2 0 1 2 3 4 5 Dollars of Debt (000s) Column E 6