Green is given. Blue is input. need help with missing cells (parts d,e,f)

Instructions: Do not change data in the green shaded areas. Only enter formulas and cell references in the blue shaded areas. $100.00 INPUT DATA Common Stock Information P. Common shares outstanding (000) Beta risk-free rate required return on market Flotation cost on new common stock Preferred Stock Information 1.30 2.00% 8.00% 0.25% $42.16 S3.60 SO 35 Preferred shares outstanding (000) Flotation cost on preferred per share Debt Information Total market value of debt (5000) Price of existing bond Number of years until maturity Annual coupon interest rate Face Value of Debt Flotation cost on debt $1,300 $1,375.50 11.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 Debt Preferred Stock Common Stock Total Market Value Mkt Value (.000 $13091 $632) $6,000 $7.9321 Percentage (w Cost 16.399 8.132% 7.97% 8.611% 75 64% 9.100% WACC 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 8.132% Use rate function in excel 8.611% 9.800% INPUT DATA 2020 6.5% 85.0% 2021 6.0% 84.0% 2019 7.0% 86.0% 5.0% 5.0% 6.0% 2022 6.0% 84.0% 6.0% 5.0% 7.0% 2023 5.5% 83.0% 6.0% 5.0% 7.0% 6.0% 6.0% 5.0% 8.0% 5.0% 7.5% 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 (g) Tax Rate (T) Common Shares outstanding (000) WACC Market value of debt and preferred stk ($000) 3.00% 35.0% 8.965% From WACC Worksheet $1,932.36 From WACC Worksheet Forecast of Cash Flows During Period of Non-constant Growth (5000) Historical 2018 Forecasted Years 2020 2021 2019 2022 2023 Sales Non-depreciation operating costs DEP = Depreciation EBIT EBIT x (1 - T) 6,000.00 5,400.00 185.00 $415.00 $269.75 $6,420.00 $5,521.20 $138.60 $760.20 $494.13 $6,837.30 $5,811.71 $195.89 $829.71 $539.31 $7,247.54 $6,087.93 $194.66 $964.94 $627.21 $7,682.39 $6,453.21 $192.59 $1,036.60 $673.79 $8,104.92 $6,727.09 $204.14 $1,173.69 $762.90 $ S $2,310.00 2,200.00 1,500.00 $2,448.60 $2,751.25 $2,595.52 $2,916.32 Fixed Assets Net Operating Working capital (NOWC) CAPEX = Chg in Fixed Assets ANOWC $110.00 $138.60 $146.92 $155.73 $165.07 Free Cash Flow FCF = EBIT(1-T) + DEP - CAPEX - ANOWC PV of FCFs 447.73 (What you should get for first 410.18 ( what you should get for first Horizon Value and Intrinsic Value Estimation Estimated Value at the Horizon, 2023 Free Cash Flow (2024) Horizon Value at 2023, HV2023 PV of HV 2023 $0.00 FCF 2024/ (WACC - g) Value in B39 Calculation of Firm's Intrinsic Value Sum of PVs of FCFS, 2019-2023 Plus: PV of HV 2023 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? f. What value of g (cell B9) causes intrinsic value to equal the current market price of common stock P,? HINT: Use the goal seek function. Instructions: Do not change data in the green shaded areas. Only enter formulas and cell references in the blue shaded areas. $100.00 INPUT DATA Common Stock Information P. Common shares outstanding (000) Beta risk-free rate required return on market Flotation cost on new common stock Preferred Stock Information 1.30 2.00% 8.00% 0.25% $42.16 S3.60 SO 35 Preferred shares outstanding (000) Flotation cost on preferred per share Debt Information Total market value of debt (5000) Price of existing bond Number of years until maturity Annual coupon interest rate Face Value of Debt Flotation cost on debt $1,300 $1,375.50 11.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 Debt Preferred Stock Common Stock Total Market Value Mkt Value (.000 $13091 $632) $6,000 $7.9321 Percentage (w Cost 16.399 8.132% 7.97% 8.611% 75 64% 9.100% WACC 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 8.132% Use rate function in excel 8.611% 9.800% INPUT DATA 2020 6.5% 85.0% 2021 6.0% 84.0% 2019 7.0% 86.0% 5.0% 5.0% 6.0% 2022 6.0% 84.0% 6.0% 5.0% 7.0% 2023 5.5% 83.0% 6.0% 5.0% 7.0% 6.0% 6.0% 5.0% 8.0% 5.0% 7.5% 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 (g) Tax Rate (T) Common Shares outstanding (000) WACC Market value of debt and preferred stk ($000) 3.00% 35.0% 8.965% From WACC Worksheet $1,932.36 From WACC Worksheet Forecast of Cash Flows During Period of Non-constant Growth (5000) Historical 2018 Forecasted Years 2020 2021 2019 2022 2023 Sales Non-depreciation operating costs DEP = Depreciation EBIT EBIT x (1 - T) 6,000.00 5,400.00 185.00 $415.00 $269.75 $6,420.00 $5,521.20 $138.60 $760.20 $494.13 $6,837.30 $5,811.71 $195.89 $829.71 $539.31 $7,247.54 $6,087.93 $194.66 $964.94 $627.21 $7,682.39 $6,453.21 $192.59 $1,036.60 $673.79 $8,104.92 $6,727.09 $204.14 $1,173.69 $762.90 $ S $2,310.00 2,200.00 1,500.00 $2,448.60 $2,751.25 $2,595.52 $2,916.32 Fixed Assets Net Operating Working capital (NOWC) CAPEX = Chg in Fixed Assets ANOWC $110.00 $138.60 $146.92 $155.73 $165.07 Free Cash Flow FCF = EBIT(1-T) + DEP - CAPEX - ANOWC PV of FCFs 447.73 (What you should get for first 410.18 ( what you should get for first Horizon Value and Intrinsic Value Estimation Estimated Value at the Horizon, 2023 Free Cash Flow (2024) Horizon Value at 2023, HV2023 PV of HV 2023 $0.00 FCF 2024/ (WACC - g) Value in B39 Calculation of Firm's Intrinsic Value Sum of PVs of FCFS, 2019-2023 Plus: PV of HV 2023 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? f. What value of g (cell B9) causes intrinsic value to equal the current market price of common stock P,? HINT: Use the goal seek function