Show excel formulas for each answer

Barking Dog Corp Cost of Capital Given: B Optimal Capital Structure: 25% Debt 10% Preferred Equity 65% Common Equity Net income for the coming year: $4,000,000 Use Retained Earnings for common equity until completely exhausted for that year (don't spend previous year's RE). Dividends policy is to distribute 60% of annual Nl as dividends. Current RE are 0. 30% Tax rate 0 1 2 3 4 5 6 7 8 9 0 1 Borrowing Limits and Interest Rates: Amount Borrowed O to $400,000 over $400,000 Interest Rate 8% 12% Common Stock price: Do: $60 $5 for the coming year 7% 8% Preferred Stock price: Do: $36 $4 for the coming year 6 Float: 25 a. Component costs of capital: 26 27 After-tax cost of debt, ATK (1) 5.60% per AT r = BT kd(1-TR) up to $400,000 borrowed 28 After-tax cost of debt, ATK, (2) 8.40% per AT ro = BT kd(1-TR) if over $400,000 borrowed 29 Cost of Preferred Stock, rp 11.11% per the dividend growth model 30 Cost of existing equity (RE), rre per the dividend growth model 31 Cost of new equity, IN per the dividend growth model 32 33 b. MCC break points: 34 35 Debt break point: based on 25% debt financing 36 37 Equity break point: Net Income 4,000,000 38 Dividends 2,400,000 39 RE available 1,600,000 40 Break point = based on 65% common equity financing 41 42 C. MCC figures, using higher rates for equity and debt when past break point: 43 44 WACC up to 1st break point: 45 Marginal CC between 1st & 2nd break points: Uses higher costs of debt 46 Marginal CC after 2nd break point: Uses higher costs of capital 47 Barking Dog Corp Cost of Capital Given: B Optimal Capital Structure: 25% Debt 10% Preferred Equity 65% Common Equity Net income for the coming year: $4,000,000 Use Retained Earnings for common equity until completely exhausted for that year (don't spend previous year's RE). Dividends policy is to distribute 60% of annual Nl as dividends. Current RE are 0. 30% Tax rate 0 1 2 3 4 5 6 7 8 9 0 1 Borrowing Limits and Interest Rates: Amount Borrowed O to $400,000 over $400,000 Interest Rate 8% 12% Common Stock price: Do: $60 $5 for the coming year 7% 8% Preferred Stock price: Do: $36 $4 for the coming year 6 Float: 25 a. Component costs of capital: 26 27 After-tax cost of debt, ATK (1) 5.60% per AT r = BT kd(1-TR) up to $400,000 borrowed 28 After-tax cost of debt, ATK, (2) 8.40% per AT ro = BT kd(1-TR) if over $400,000 borrowed 29 Cost of Preferred Stock, rp 11.11% per the dividend growth model 30 Cost of existing equity (RE), rre per the dividend growth model 31 Cost of new equity, IN per the dividend growth model 32 33 b. MCC break points: 34 35 Debt break point: based on 25% debt financing 36 37 Equity break point: Net Income 4,000,000 38 Dividends 2,400,000 39 RE available 1,600,000 40 Break point = based on 65% common equity financing 41 42 C. MCC figures, using higher rates for equity and debt when past break point: 43 44 WACC up to 1st break point: 45 Marginal CC between 1st & 2nd break points: Uses higher costs of debt 46 Marginal CC after 2nd break point: Uses higher costs of capital 47