$100.00 60 1.40 2.00% 9.75% 0.75% INPUT DATA Common Stock Information Common shares outstanding (000) Beta risk-free rate required return on market Flotation cost on new common stock Preferred Stock Information Pp Dp Preferred shares outstanding (000) Flotation cost on preferred stock Debt Information Total market value of debt ($000) Price of existing bond Number of years until maturity Annual coupon interest rate Face Value of Debt Flotation cost on debt $41.50 $3.75 15 1.25% $1,550 $1,375.50 23 10.50% $1,000.00 2% Tax rate 35.00% a. What is the market value capital structure and the values for the w's in the WACC equation? Market Value Cap Structure Mkt Value (5000) Percentage (w) Debt $1,550 18.97% Preferred Stock $623 7.62% Common Stock $6,000 73.42% Total Market Value $8,173 100.00% b. Calculate the cost of each capital component, that is, the before-tax cost of debt, the cost of preferred stock, and the cost of equity. Before-tax cost of debt Cost of preferred stock Cost of common stock 2.802% Use rate() function in excel 9.151% 12.850% c. If BLC continues to use the same market value capital structure, what is the firm's WACC? wd x rd( 1-T) + 0.3455% wp x rp WCS X res 9.4341% WACC 10.4766% 0.6970% INPUT DATA 2021 7.0% 86.0% 5.0% 5.0% 6.0% 2022 6.8% 85.0% 6,0% 5.0% 8.0% 2023 6.0% 84.0% 6.0% 5.0% 7.5% 2024 6.0% 84.0% 6,0% 5,0% 7.0% 2025 7.5% 83.0% 6.0% 5.0% 7.0% Sales Growth Rate Non depreciation operating costs as a % of sales Growth in fixed assets Growth in NOWC Deprec as a % of Fixed Assets Long-run FCF growth (8) Tax Rate (T) Common Shares outstanding (000) WACC Market value of debt and preferred stk (5000) 3.25% 35.0% 60 From WACC Worksheet From WACC Worksheet Forecast of Cash Flows During Period of Non-constant Growth (S000) Historical 2020 Forecasted Years 2022 2023 2021 2024 2025 Sales Non-depreciation operating costs DEP - Depreciation EBIT EBIT x (1 - T) $ 6,000.00 $ 5,400.00 $ 185.00 Fixed Assets Net Operating Working capital (NOWC) CAPEX - Chg in Fixed Assets ANOWC $ 2,200.00 S 1,500.00 Free Cash Flow FCF = EBIT(1-T) + DEP - CAPEX - ANOWC PV of FCFs Horizon Value and Intrinsic Value Estimation Estimated Value at the Horizon, 2025 Free Cash Flow (2026) Horizon Value at 2025, HV2025 PV of HV2025 Value in B39 Calculation of Firm's Intrinsic Value Sum of PVs of FCFs, 2021-2025 Plus: PV of HV2025 Equals: Total Corporate Value Less: market value of debt and preferred Equals: Intrinsic value of common equity Divided by: Shares outstanding (000) Value in B13 Value in B11 Equals: Intrinsic Value Per Share e. Is BLC over- or under-valued? 1. What value of g (cell B9) causes intrinsic value to equal the current market price of common stock PO? HINT: Use the goal seek function. $100.00 60 1.40 2.00% 9.75% 0.75% INPUT DATA Common Stock Information Common shares outstanding (000) Beta risk-free rate required return on market Flotation cost on new common stock Preferred Stock Information Pp Dp Preferred shares outstanding (000) Flotation cost on preferred stock Debt Information Total market value of debt ($000) Price of existing bond Number of years until maturity Annual coupon interest rate Face Value of Debt Flotation cost on debt $41.50 $3.75 15 1.25% $1,550 $1,375.50 23 10.50% $1,000.00 2% Tax rate 35.00% a. What is the market value capital structure and the values for the w's in the WACC equation? Market Value Cap Structure Mkt Value (5000) Percentage (w) Debt $1,550 18.97% Preferred Stock $623 7.62% Common Stock $6,000 73.42% Total Market Value $8,173 100.00% b. Calculate the cost of each capital component, that is, the before-tax cost of debt, the cost of preferred stock, and the cost of equity. Before-tax cost of debt Cost of preferred stock Cost of common stock 2.802% Use rate() function in excel 9.151% 12.850% c. If BLC continues to use the same market value capital structure, what is the firm's WACC? wd x rd( 1-T) + 0.3455% wp x rp WCS X res 9.4341% WACC 10.4766% 0.6970% INPUT DATA 2021 7.0% 86.0% 5.0% 5.0% 6.0% 2022 6.8% 85.0% 6,0% 5.0% 8.0% 2023 6.0% 84.0% 6.0% 5.0% 7.5% 2024 6.0% 84.0% 6,0% 5,0% 7.0% 2025 7.5% 83.0% 6.0% 5.0% 7.0% Sales Growth Rate Non depreciation operating costs as a % of sales Growth in fixed assets Growth in NOWC Deprec as a % of Fixed Assets Long-run FCF growth (8) Tax Rate (T) Common Shares outstanding (000) WACC Market value of debt and preferred stk (5000) 3.25% 35.0% 60 From WACC Worksheet From WACC Worksheet Forecast of Cash Flows During Period of Non-constant Growth (S000) Historical 2020 Forecasted Years 2022 2023 2021 2024 2025 Sales Non-depreciation operating costs DEP - Depreciation EBIT EBIT x (1 - T) $ 6,000.00 $ 5,400.00 $ 185.00 Fixed Assets Net Operating Working capital (NOWC) CAPEX - Chg in Fixed Assets ANOWC $ 2,200.00 S 1,500.00 Free Cash Flow FCF = EBIT(1-T) + DEP - CAPEX - ANOWC PV of FCFs Horizon Value and Intrinsic Value Estimation Estimated Value at the Horizon, 2025 Free Cash Flow (2026) Horizon Value at 2025, HV2025 PV of HV2025 Value in B39 Calculation of Firm's Intrinsic Value Sum of PVs of FCFs, 2021-2025 Plus: PV of HV2025 Equals: Total Corporate Value Less: market value of debt and preferred Equals: Intrinsic value of common equity Divided by: Shares outstanding (000) Value in B13 Value in B11 Equals: Intrinsic Value Per Share e. Is BLC over- or under-valued? 1. What value of g (cell B9) causes intrinsic value to equal the current market price of common stock PO? HINT: Use the goal seek function