1 Assumptions Capital Expenditure EBIT (Year 1) 1,500 USD million 170 USD million Depreciation (Year 1)...

Transcribed Image Text:

1 Assumptions Capital Expenditure EBIT (Year 1) 1,500 USD million 170 USD million Depreciation (Year 1) 150 USD million Increase in Net Working Capital (Year 1) 40 USD million Increase in Net Working Capital (Year 0) Tax Rate 30 USD million 21% FCF's Growth Rate Year EBIT Less: Taxes 0 2024 1 5% 2 5% 2% 2% 2% 2% 2% 2% 2% 2025 2026 3 2027 4 5 6 7 8 9 10 2028 2029 2030 2031 2032 2033 2034 170 Don't input anything in the gray cells because you can forecast FCFS for Years 2025-2033 based on the 36 assumptions on FCF's growth rates. E.g., Year 2 FCF = Year 1 FCF *(1+5%). See the assumptions in the 134 case instruction. 150 Specifically, first forecast Year 0 and Year 1 FCFS, then forecast FCFs after Year 1 using the above assumptions. Unlevered Net Income Add: Depreciation Less: Capital Expenditures -1,500 Less: Change in NWC -30 -40 FCF -1,530 244 Discount Factor Present Value of FCF NPV 309 PV = FV x (1+r)" 286 292 297 303 257 269 275 280 1 0.956754688 0.9153795 0.8757937 0.8379197 0.8016836 0.7670145 0.7338448 0.7021094 0.6717465 0.6426966 -1530 233.7351703 234.80858 235.88692 230.19964 224.64947 219.23312 213.94737 208.78905 203.7551 198.84252 $674 Discount Factor Refer to lecture 2 video "Use Excel to Value Any Cash Flow Str i risk free rate (10-year Treasury Yield) i equity beta expected market risk premium cost of equity rE 4.32% See the case instruction on how to find out the risk free rate. 0.5 See the case instruction on how to find out the risk free rate. 4.00% Based on the historical approach, we can simply assume the expected market risk premium is the same as the average of historical market risk premium, which is 4%. 6.32% Note that rE = risk free rate + equity beta * expected market risk premium, NOT risk free rate + equity beta * (expected market risk premium - risk free rate) a S&P's Rating debt beta cost of debt rD A+ Refer to the case instruction for details 0.05 Refer to the case instruction for details 4.52% rD = risk free rate + debt beta * expected market risk premium Cash and Short-Term Investments Debt in Current Liabilities Long-Term Debt - Total Net Debt Stock Price Shares Outstanding Market Value of Equity Enterprise Value 2022 $56,545 USD million $92,567 USD million $128,441 USD million $164,463 USD million Net debt="Debt in Current Liabilities" + "Long-Term Debt - Total" - "Cash and Short-Term Investments" 1,859.48 USD 1,356.518 millions $2,522,418 USD million $2,686,881 USD million Refer to the instruction video "WRDS - Download Financial Statements and Stock Price Data" on how to download the stock price in the WRDS. The variable "SHARES - Outstanding at Fiscal Year End" reported in the WRDS is in the unit of millions. Stock price * Shares Outstanding Enterprise Value=MV Equity (cell C42)+Net Debt(C38) D 4 WACC 6.152% wace E ED E (1), where E is Market Value of Equity (C42), D is Net Debt (C38), rE is cost of equity (C28), rD is cost of debt (C32), t is the tax rate E + D WACC =where E is Market Value of Equity, D is Net Debt, rE is cost of equity, rD is cost of debt, t is the tax rate 1 Assumptions Capital Expenditure EBIT (Year 1) 1,500 USD million 170 USD million Depreciation (Year 1) 150 USD million Increase in Net Working Capital (Year 1) 40 USD million Increase in Net Working Capital (Year 0) Tax Rate 30 USD million 21% FCF's Growth Rate Year EBIT Less: Taxes 0 2024 1 5% 2 5% 2% 2% 2% 2% 2% 2% 2% 2025 2026 3 2027 4 5 6 7 8 9 10 2028 2029 2030 2031 2032 2033 2034 170 Don't input anything in the gray cells because you can forecast FCFS for Years 2025-2033 based on the 36 assumptions on FCF's growth rates. E.g., Year 2 FCF = Year 1 FCF *(1+5%). See the assumptions in the 134 case instruction. 150 Specifically, first forecast Year 0 and Year 1 FCFS, then forecast FCFs after Year 1 using the above assumptions. Unlevered Net Income Add: Depreciation Less: Capital Expenditures -1,500 Less: Change in NWC -30 -40 FCF -1,530 244 Discount Factor Present Value of FCF NPV 309 PV = FV x (1+r)" 286 292 297 303 257 269 275 280 1 0.956754688 0.9153795 0.8757937 0.8379197 0.8016836 0.7670145 0.7338448 0.7021094 0.6717465 0.6426966 -1530 233.7351703 234.80858 235.88692 230.19964 224.64947 219.23312 213.94737 208.78905 203.7551 198.84252 $674 Discount Factor Refer to lecture 2 video "Use Excel to Value Any Cash Flow Str i risk free rate (10-year Treasury Yield) i equity beta expected market risk premium cost of equity rE 4.32% See the case instruction on how to find out the risk free rate. 0.5 See the case instruction on how to find out the risk free rate. 4.00% Based on the historical approach, we can simply assume the expected market risk premium is the same as the average of historical market risk premium, which is 4%. 6.32% Note that rE = risk free rate + equity beta * expected market risk premium, NOT risk free rate + equity beta * (expected market risk premium - risk free rate) a S&P's Rating debt beta cost of debt rD A+ Refer to the case instruction for details 0.05 Refer to the case instruction for details 4.52% rD = risk free rate + debt beta * expected market risk premium Cash and Short-Term Investments Debt in Current Liabilities Long-Term Debt - Total Net Debt Stock Price Shares Outstanding Market Value of Equity Enterprise Value 2022 $56,545 USD million $92,567 USD million $128,441 USD million $164,463 USD million Net debt="Debt in Current Liabilities" + "Long-Term Debt - Total" - "Cash and Short-Term Investments" 1,859.48 USD 1,356.518 millions $2,522,418 USD million $2,686,881 USD million Refer to the instruction video "WRDS - Download Financial Statements and Stock Price Data" on how to download the stock price in the WRDS. The variable "SHARES - Outstanding at Fiscal Year End" reported in the WRDS is in the unit of millions. Stock price * Shares Outstanding Enterprise Value=MV Equity (cell C42)+Net Debt(C38) D 4 WACC 6.152% wace E ED E (1), where E is Market Value of Equity (C42), D is Net Debt (C38), rE is cost of equity (C28), rD is cost of debt (C32), t is the tax rate E + D WACC =where E is Market Value of Equity, D is Net Debt, rE is cost of equity, rD is cost of debt, t is the tax rate