Tasks 1. Firm and project valuations: 1.1. Construct one spreadsheet template: 1.1.1. that is applicable to both firm and project valuations for the following scenarios: firm valuation with an infinite horizon, firm valuation with a finite horizon, and project valuation with a finite horizon; 1.1.2. that incorporates three valuation methods (i.e., WACC, FTE, and APV); 1.2. Use given input values to complete the valuations based on the requirements in Section 1.1. 2. Excel skills: 2.1. Add spin button" (see below) in input values for Revenue (t=0) with an incremental change of 1,000 and for NWC (0) with an incremental change of 220 to make the values interactive; 10,000 2.2. Add option buttons" (see below) to allow input choices; Valuation Object Firm Project 2.3. Use IF statement to incorporate different Section 1.1.1); ut to incorporate different input values into various scenarios (see 2.4. Use "comments" to add a note that provide the formula for estimating the unie note that provide the formula for estimating the unlevered cost of equity. 3. Questions: Requirements: Responses to the following questions must be shown in a separate tab. 3.1. Write the instructions on how to incorporate the spin button and the option button in the template. 3.2. Based on your results for firm valuations, what is the relationship between WACC and FTE estimates? 3.3. Elaborate the concepts, if any, with which you have the most difficulty during the estimation process. Explanations 1. Discounted Cash Flow (DCF) Method The DCF method to determine firm value is based on the present value of cash flows over the firm's life. A firm is assumed to have an infinite life that includes a finite horizon and an infinite horizon. The finite horizon usually consists of five or ten years and, in our project, a forecast period of five years is used. The cash flow estimates during the infinite period are subject to forecast accuracy issues. Therefore, all cash flows beyond the finite period is captured in the terminal value estimated in the last year of the finite period. The estimation of the terminal value is built on the steady-growth assumption that the firm does not experience abnormal growth. Firm value is the sum of the present value of cash flows during the finite horizon and the terminal value at the end of the period. 2. Net Operating Profits After Taxes (NOPAT) NOPAT is equal to EBIT(1-Tax Rate). The measure captures earnings after taxes that are available for all capital providers and therefore do not account for the deductions for financing costs. 3. Terminal Value Consistent with the steady growth assumption for th the steady growth assumption for the infinite period, the constant growth equation (similar to the dividend growth model) is used to estimate the terminal value. For example, to estimate the terminal value of cash flows beyond the finite period by applying the WACC approach, FCFFinite Horizon Terminal Value = WACC -9 where FCF Finite Horizon is the expected Free Cash Flow in the first year of the infinite horizon, WACC is the weighted average cost of capital, and g is the expected growth rate during the infinite period. Under the steady growth assumption, all financial statement line items (e.g., revenue, costs) grow at the steady growth rate. 4. Discount Rate The appropriate discount rate depends on the employed valuation approach. For example, the discount rate is the weighted average cost of capital (WACC) when applying the WACC approach. It is calculated as the required rate of returns on debt and equity, weighted by their corresponding proportion of the firm's market value. In the Mergers & Acquisition setting, the appropriate discount rate is based on the WACCs of firms in the target firm's industry. Specifically, it can be estimated as the mean or median WACC of the industry. Check Figures The WACC approach based on given input values shows that firm value is $78,175 with the infinite horizon and project NPV is $250 with finite horizon. Input values Valuation Object Valuation Object Firm Project 20,000 4,125 10,000 Inputs for Firm Valuation Revenue (t=0) NWC (t=0) GPPE (t=0) Forcasts for Next 5 Years Revenue Growth COGS/Revenue SG&A/Revenue NWC/Revenue Annual Depreciation Increase in GPPE After-Tax Salvage Value (For Finite Horizon only) Forcasts for Infinite Horizon (l.e., Steady Growth) Growth Rate 6.0% 50.0% 15.0% 21.0% 1,000 250 4,000 5.5% Inputs for Project Valuation $ 800 Capital Expenditure Required for the Project Revenue COGS Depreciation New Borrowing (Repayment) After-Tax Salvage Value 650 $ 410 $ 60 $ 690 S 435 S 60 $ 7206 445 $ 60 $ 755 6 470 $ 60 $ 775 470 60 200 (200) 600 Other Inputs Tax Rate Target Debt Ratio Cost of Equity = Pre-Tax Cost of Debt = 39.0% 25.0% 13.1% 7.4%! rite Hortzun Yes Include Infinite Horizon? NO Spreadsheet layout 1st Stage: Finite Horizon 2nd Stage: Infinite Horizon 1. WACC Revenue COGS Gross Profit SG&A Depreciation EBIT Taxes NOPAT = EBIT(1-Tax Rate) Add: Depreciation GPPE Less: Capital Expenditure Add: After-Tax Salvage Value NWC Less: Increase in NWC FCF Terminal Value Present Value (t=0) Firm Value (or Project NPV) Value of Equity Value of Debt 2. FTE 3. APV Cost of Capital Estimates Cost of Capital Estimates Unlevered Cost of Equity Tasks 1. Firm and project valuations: 1.1. Construct one spreadsheet template: 1.1.1. that is applicable to both firm and project valuations for the following scenarios: firm valuation with an infinite horizon, firm valuation with a finite horizon, and project valuation with a finite horizon; 1.1.2. that incorporates three valuation methods (i.e., WACC, FTE, and APV); 1.2. Use given input values to complete the valuations based on the requirements in Section 1.1. 2. Excel skills: 2.1. Add spin button" (see below) in input values for Revenue (t=0) with an incremental change of 1,000 and for NWC (0) with an incremental change of 220 to make the values interactive; 10,000 2.2. Add option buttons" (see below) to allow input choices; Valuation Object Firm Project 2.3. Use IF statement to incorporate different Section 1.1.1); ut to incorporate different input values into various scenarios (see 2.4. Use "comments" to add a note that provide the formula for estimating the unie note that provide the formula for estimating the unlevered cost of equity. 3. Questions: Requirements: Responses to the following questions must be shown in a separate tab. 3.1. Write the instructions on how to incorporate the spin button and the option button in the template. 3.2. Based on your results for firm valuations, what is the relationship between WACC and FTE estimates? 3.3. Elaborate the concepts, if any, with which you have the most difficulty during the estimation process. Explanations 1. Discounted Cash Flow (DCF) Method The DCF method to determine firm value is based on the present value of cash flows over the firm's life. A firm is assumed to have an infinite life that includes a finite horizon and an infinite horizon. The finite horizon usually consists of five or ten years and, in our project, a forecast period of five years is used. The cash flow estimates during the infinite period are subject to forecast accuracy issues. Therefore, all cash flows beyond the finite period is captured in the terminal value estimated in the last year of the finite period. The estimation of the terminal value is built on the steady-growth assumption that the firm does not experience abnormal growth. Firm value is the sum of the present value of cash flows during the finite horizon and the terminal value at the end of the period. 2. Net Operating Profits After Taxes (NOPAT) NOPAT is equal to EBIT(1-Tax Rate). The measure captures earnings after taxes that are available for all capital providers and therefore do not account for the deductions for financing costs. 3. Terminal Value Consistent with the steady growth assumption for th the steady growth assumption for the infinite period, the constant growth equation (similar to the dividend growth model) is used to estimate the terminal value. For example, to estimate the terminal value of cash flows beyond the finite period by applying the WACC approach, FCFFinite Horizon Terminal Value = WACC -9 where FCF Finite Horizon is the expected Free Cash Flow in the first year of the infinite horizon, WACC is the weighted average cost of capital, and g is the expected growth rate during the infinite period. Under the steady growth assumption, all financial statement line items (e.g., revenue, costs) grow at the steady growth rate. 4. Discount Rate The appropriate discount rate depends on the employed valuation approach. For example, the discount rate is the weighted average cost of capital (WACC) when applying the WACC approach. It is calculated as the required rate of returns on debt and equity, weighted by their corresponding proportion of the firm's market value. In the Mergers & Acquisition setting, the appropriate discount rate is based on the WACCs of firms in the target firm's industry. Specifically, it can be estimated as the mean or median WACC of the industry. Check Figures The WACC approach based on given input values shows that firm value is $78,175 with the infinite horizon and project NPV is $250 with finite horizon. Input values Valuation Object Valuation Object Firm Project 20,000 4,125 10,000 Inputs for Firm Valuation Revenue (t=0) NWC (t=0) GPPE (t=0) Forcasts for Next 5 Years Revenue Growth COGS/Revenue SG&A/Revenue NWC/Revenue Annual Depreciation Increase in GPPE After-Tax Salvage Value (For Finite Horizon only) Forcasts for Infinite Horizon (l.e., Steady Growth) Growth Rate 6.0% 50.0% 15.0% 21.0% 1,000 250 4,000 5.5% Inputs for Project Valuation $ 800 Capital Expenditure Required for the Project Revenue COGS Depreciation New Borrowing (Repayment) After-Tax Salvage Value 650 $ 410 $ 60 $ 690 S 435 S 60 $ 7206 445 $ 60 $ 755 6 470 $ 60 $ 775 470 60 200 (200) 600 Other Inputs Tax Rate Target Debt Ratio Cost of Equity = Pre-Tax Cost of Debt = 39.0% 25.0% 13.1% 7.4%! rite Hortzun Yes Include Infinite Horizon? NO Spreadsheet layout 1st Stage: Finite Horizon 2nd Stage: Infinite Horizon 1. WACC Revenue COGS Gross Profit SG&A Depreciation EBIT Taxes NOPAT = EBIT(1-Tax Rate) Add: Depreciation GPPE Less: Capital Expenditure Add: After-Tax Salvage Value NWC Less: Increase in NWC FCF Terminal Value Present Value (t=0) Firm Value (or Project NPV) Value of Equity Value of Debt 2. FTE 3. APV Cost of Capital Estimates Cost of Capital Estimates Unlevered Cost of Equity