Please show excel formulas for each answer

Barking Dog Corp Cost of Capital Given: 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 Borrowing Limits and Interest Rates: Amount Borrowed 0 to $400,000 over $400,000 Interest Rate 8% 12% Common Stock price: Do: g Float: $60 $5 for the coming year 7% Preferred Stock price: Do $36 $4 for the coming year 8% a. Component costs of capital: After-tax cost of debt, ATK. (1) After-tax cost of debt, ATK, (2) Cost of Preferred Stock, rp Cost of existing equity (RE), PRE Cost of new equity, IN per AT ro = BT kd(1-TR) up to $400,000 borrowed per AT r = BT kd(1-TR) if over $400,000 borrowed per the dividend growth model per the dividend growth model and CAPM per the dividend growth model and CAPM 25 a. Component costs of capital: 26 27 After-tax cost of debt, ATK. (1) per AT ra = BT kd(1-TR) up to $400,000 borrowed 28 After-tax cost of debt, ATK (2) per AT rd=BT kd(1-TR) if over $400,000 borrowed 29 Cost of Preferred Stock, per the dividend growth model 30 Cost of existing equity (RE), per the dividend growth model and CAPM 31 Cost of new equity, rn per the dividend growth model and CAPM 32 33 b. MCC break points: 34 35 Debt break point: based on 25% debt financing 36 37 Equity break point: Net Income 38 Dividends 39 RE available 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 48 49 Barking Dog Corp Cost of Capital Given: 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 Borrowing Limits and Interest Rates: Amount Borrowed 0 to $400,000 over $400,000 Interest Rate 8% 12% Common Stock price: Do: g Float: $60 $5 for the coming year 7% Preferred Stock price: Do $36 $4 for the coming year 8% a. Component costs of capital: After-tax cost of debt, ATK. (1) After-tax cost of debt, ATK, (2) Cost of Preferred Stock, rp Cost of existing equity (RE), PRE Cost of new equity, IN per AT ro = BT kd(1-TR) up to $400,000 borrowed per AT r = BT kd(1-TR) if over $400,000 borrowed per the dividend growth model per the dividend growth model and CAPM per the dividend growth model and CAPM 25 a. Component costs of capital: 26 27 After-tax cost of debt, ATK. (1) per AT ra = BT kd(1-TR) up to $400,000 borrowed 28 After-tax cost of debt, ATK (2) per AT rd=BT kd(1-TR) if over $400,000 borrowed 29 Cost of Preferred Stock, per the dividend growth model 30 Cost of existing equity (RE), per the dividend growth model and CAPM 31 Cost of new equity, rn per the dividend growth model and CAPM 32 33 b. MCC break points: 34 35 Debt break point: based on 25% debt financing 36 37 Equity break point: Net Income 38 Dividends 39 RE available 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 48 49