Complete the Corp Valuation worksheet in the template provided. Data in green shaded areas are provided. Only enter formulas or cell references (no typed numbers) in the blue shaded areas. Based on your analysis, is the company over-, under-, or fairly-valued? (If you need any more information please comment)
A B C D E F G I 1 d. 2 INPUT DATA 3 2021 2022 7.0% 6.8% 86.0% 85.0% 5.0% 6.0% 5.0% 5.0% 6.0% 8.0% 3.25% 35.0% 60 From WACC Worksheet From WACC Worksheet 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% 4. Sales Growth Rate 5 Non depreciation operating costs as a % of sales 6 Growth in fixed assets 7 Growth in NOWC 8 Deprec as a % of Fixed Assets 9 Long-run FCF growth (g) 10 Tax Rate (T) 11 Common Shares outstanding (000) 12 WACC 13 Market value of debt and preferred stk ($000) 14 B D F H 15 Forecast of Cash Flows During Period of Non-constant Growth ($000) 16 17 Historical 2020 Forecasted Years 2022 2023 18 2021 2024 2025 $ 6,000.00 $ 5,400.00 $ 185.00 22 19 20 Sales 21 Non-depreciation operating costs DEP = Depreciation 23 EBIT 24 EBIT x (1 - T) 25 26 Fixed Assets 27 Net Operating Working capital (NOWC) 28 CAPEX = Chg in Fixed Assets 29 ANOWC 30 31 Free Cash Flow 32 FCF = EBIT(1-T) + DEP - CAPEX - ANOWC 33 PV of FCFs $ 2,200.00 1,500.00 $ G E F H 54 35 Horizon Value and Intrinsic Value Estimation Estimated Value at the Horizon, 2025 37 Free Cash Flow (2026) 38 Horizon Value at 2025, HV2025 39 PV of HV2025 40 41 Value in B39 Calculation of Firm's Intrinsic Value 42 Sum of PVs of FCFs, 2021-2025 43 Plus: PV of HV2025 44 Equals: Total Corporate Value 45 Less: market value of debt and preferred 46 Equals: Intrinsic value of common equity 47 Divided by: Shares outstanding (000) Value in B13 Value in B11 48 49 Equals: Intrinsic Value Per Share 50 51 e. Is BLC over- or under-valued? 52 53 f. What value of g (cell B9) causes intrinsic value to equal 54 the current market price of common stock P,? 55 HINT: Use the goal seek function. A B C D E F G I 1 d. 2 INPUT DATA 3 2021 2022 7.0% 6.8% 86.0% 85.0% 5.0% 6.0% 5.0% 5.0% 6.0% 8.0% 3.25% 35.0% 60 From WACC Worksheet From WACC Worksheet 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% 4. Sales Growth Rate 5 Non depreciation operating costs as a % of sales 6 Growth in fixed assets 7 Growth in NOWC 8 Deprec as a % of Fixed Assets 9 Long-run FCF growth (g) 10 Tax Rate (T) 11 Common Shares outstanding (000) 12 WACC 13 Market value of debt and preferred stk ($000) 14 B D F H 15 Forecast of Cash Flows During Period of Non-constant Growth ($000) 16 17 Historical 2020 Forecasted Years 2022 2023 18 2021 2024 2025 $ 6,000.00 $ 5,400.00 $ 185.00 22 19 20 Sales 21 Non-depreciation operating costs DEP = Depreciation 23 EBIT 24 EBIT x (1 - T) 25 26 Fixed Assets 27 Net Operating Working capital (NOWC) 28 CAPEX = Chg in Fixed Assets 29 ANOWC 30 31 Free Cash Flow 32 FCF = EBIT(1-T) + DEP - CAPEX - ANOWC 33 PV of FCFs $ 2,200.00 1,500.00 $ G E F H 54 35 Horizon Value and Intrinsic Value Estimation Estimated Value at the Horizon, 2025 37 Free Cash Flow (2026) 38 Horizon Value at 2025, HV2025 39 PV of HV2025 40 41 Value in B39 Calculation of Firm's Intrinsic Value 42 Sum of PVs of FCFs, 2021-2025 43 Plus: PV of HV2025 44 Equals: Total Corporate Value 45 Less: market value of debt and preferred 46 Equals: Intrinsic value of common equity 47 Divided by: Shares outstanding (000) Value in B13 Value in B11 48 49 Equals: Intrinsic Value Per Share 50 51 e. Is BLC over- or under-valued? 52 53 f. What value of g (cell B9) causes intrinsic value to equal 54 the current market price of common stock P,? 55 HINT: Use the goal seek function
