FINC 3373 Assignment #3 Company Valuation: Free Cash Flow Method 2021 8,088 1332 DUE: February 7 0.900 5,856 5.940 2,916 2.488 1044 346 102 831 271 1. Using the data provided, complete the template on the spreadsheet (Fill in all blue shaded areas). Specifically, you need to: a. Forecast FCF for 2022 assuming that 2021 free cash flow grows at a constant annual rate of 3.5%. Note that Jan 2022 FCF is already given in the spreadsheet, you don't need to compute this value. b. Use the corporate valuation model to determine the intrinsic value of the company's stock. 2. Given Microsoft's stock price at the end of June 2021 and your estimate of the value of Microsoft's stock (determined in question 1b), determine if the company's stock is overvalued, fairly valued, or undervalued. Because of the uncertainties involved in forecasting, you decide that any estimated stock price or intrinsic value within 5% +/- of the current stock price is fairly valued. The WACC for Microsoft is 5.6% Now re-estimate the value of Microsoft assuming non-constant growth of FCF. The consensus estimate by financial analysis is that the company will grow at a 16.1% annual rate for the next 5 years (2022 through 2026), and then at a constant rate of 3.5% from 2026 onward. Again, fill-in all of the blue-shaded areas in the spreadsheet. Assume a WACC of 5.6. Determine if Microsoft is overvalued, fairly valued, or undervalued in the non-constant growth case (assume that an intrinsic value within +/- 3% of the current market price is fairly valued), CO21 224 110 043 636 393 406 303 327 511 532 79 NOTES: The FCF for the current year in the assignment (June 2021) is $36.117 billion. If you come up with a negative intrinsic value, then you have an error(s) in your work 25 63 69 57 34 Please include all work in the spreadsheet, including your answers to questions 2 and 4. Then submit the spreadsheet to the D2L dropbox. As with the first homework, all of the blue shaded cells should have Hormulas which compute the answer. Please do not type in numerical values into these cells. Provide your answers to questions 2 and 4 in the appropriate textboxes in the spreadsheet. G H 45 46 Other Data: 47 WACC 48 Tax rate 49 Stock price 50 Shares common stock outstanding (millions) 51 Total dividends ($-millions) 52 Free Cash Flow (current year, in $-millions) 53 54 Constant Growth of FCF: Jun-2021 5.60% 13.8% 270.90 7,519 16,843 36,117 Use formulas in each of the Blue shaded cells. Please do not type in numerical values in those cell. Jun-2021 Jun-2022 55 Growth Rate 3.50% 56 Forecast FCF (2020) 57 Value of Operations 58 Non-operating assets 59 Value of Firm Your Answer to Question 2: +/- 3% of the current stock price gives a range of $262.77 to $279.03 60 Debt With an intrinsic value of $238.28 MSFT is overvalued. 61 Preferred Stock 62 Value of Common Equity 63 Intrinsic Value 64 65 Non-constant Growth of FCF: Jun-2021 Jun-2022 16.10% 66 Growth rate Jun-2023 16.10% Jun-2024 16.10% Jun-2025 16.10% Jun-2026 16.10% Jun-2027 3.50% 67 Forecast FCF 68 Horizon/Terminal Value 69 Value of Operations 70 Non-operating assets 71 Value of Firm Your Answer to Question 4: +/- 3% of use current stock price gives a range of $262.77 to $279.03 72 Debt With an intrinsic value of $414.03, MSFT is undervalued. 73 Preferred Stock 74 Value of Common Equity 75 Intrinsic Value 26 Income Statement (Smillions) Sales Cost of goods sold Depreciation and amortization Gross profit Selling, general, and administrative expense Operating income (EBIT) Non-operating income Non-operating expense Interest expense Pretax income Jun-2021 168,088 41,332 10,900 115,856 45,940 69,916 2,488 . -1,044 2,346 71,102 9,831 61,271 Income tax expense Net income Balance Sheet($-millions) Cash Short-term investments Accounts receivables Inventories Other current assets Current assets 1 Property, plant, and equipment - net 2 Long-term investments 3 Intangible assets 4 Other assets 5 Total assets 6 7 Short-term debt and current portion of long-term debt -8 Accounts payable 9 Other current liabilities 10 Current liabilities 1 Long-term debt 2 Total liabilities 3 Total shareholder equity 44 Total liabilities and shareholder equity 45 Jun-2021 14,224 116,110 38,043 2,636 13,393 184,406 70,803 9,427 57,511 11,632 333,779 10,825 15,163 62,669 88,657 103,134 191,791 141,988 333,779 Sheet1 + FINC 3373 Assignment #3 Company Valuation: Free Cash Flow Method 2021 8,088 1332 DUE: February 7 0.900 5,856 5.940 2,916 2.488 1044 346 102 831 271 1. Using the data provided, complete the template on the spreadsheet (Fill in all blue shaded areas). Specifically, you need to: a. Forecast FCF for 2022 assuming that 2021 free cash flow grows at a constant annual rate of 3.5%. Note that Jan 2022 FCF is already given in the spreadsheet, you don't need to compute this value. b. Use the corporate valuation model to determine the intrinsic value of the company's stock. 2. Given Microsoft's stock price at the end of June 2021 and your estimate of the value of Microsoft's stock (determined in question 1b), determine if the company's stock is overvalued, fairly valued, or undervalued. Because of the uncertainties involved in forecasting, you decide that any estimated stock price or intrinsic value within 5% +/- of the current stock price is fairly valued. The WACC for Microsoft is 5.6% Now re-estimate the value of Microsoft assuming non-constant growth of FCF. The consensus estimate by financial analysis is that the company will grow at a 16.1% annual rate for the next 5 years (2022 through 2026), and then at a constant rate of 3.5% from 2026 onward. Again, fill-in all of the blue-shaded areas in the spreadsheet. Assume a WACC of 5.6. Determine if Microsoft is overvalued, fairly valued, or undervalued in the non-constant growth case (assume that an intrinsic value within +/- 3% of the current market price is fairly valued), CO21 224 110 043 636 393 406 303 327 511 532 79 NOTES: The FCF for the current year in the assignment (June 2021) is $36.117 billion. If you come up with a negative intrinsic value, then you have an error(s) in your work 25 63 69 57 34 Please include all work in the spreadsheet, including your answers to questions 2 and 4. Then submit the spreadsheet to the D2L dropbox. As with the first homework, all of the blue shaded cells should have Hormulas which compute the answer. Please do not type in numerical values into these cells. Provide your answers to questions 2 and 4 in the appropriate textboxes in the spreadsheet. G H 45 46 Other Data: 47 WACC 48 Tax rate 49 Stock price 50 Shares common stock outstanding (millions) 51 Total dividends ($-millions) 52 Free Cash Flow (current year, in $-millions) 53 54 Constant Growth of FCF: Jun-2021 5.60% 13.8% 270.90 7,519 16,843 36,117 Use formulas in each of the Blue shaded cells. Please do not type in numerical values in those cell. Jun-2021 Jun-2022 55 Growth Rate 3.50% 56 Forecast FCF (2020) 57 Value of Operations 58 Non-operating assets 59 Value of Firm Your Answer to Question 2: +/- 3% of the current stock price gives a range of $262.77 to $279.03 60 Debt With an intrinsic value of $238.28 MSFT is overvalued. 61 Preferred Stock 62 Value of Common Equity 63 Intrinsic Value 64 65 Non-constant Growth of FCF: Jun-2021 Jun-2022 16.10% 66 Growth rate Jun-2023 16.10% Jun-2024 16.10% Jun-2025 16.10% Jun-2026 16.10% Jun-2027 3.50% 67 Forecast FCF 68 Horizon/Terminal Value 69 Value of Operations 70 Non-operating assets 71 Value of Firm Your Answer to Question 4: +/- 3% of use current stock price gives a range of $262.77 to $279.03 72 Debt With an intrinsic value of $414.03, MSFT is undervalued. 73 Preferred Stock 74 Value of Common Equity 75 Intrinsic Value 26 Income Statement (Smillions) Sales Cost of goods sold Depreciation and amortization Gross profit Selling, general, and administrative expense Operating income (EBIT) Non-operating income Non-operating expense Interest expense Pretax income Jun-2021 168,088 41,332 10,900 115,856 45,940 69,916 2,488 . -1,044 2,346 71,102 9,831 61,271 Income tax expense Net income Balance Sheet($-millions) Cash Short-term investments Accounts receivables Inventories Other current assets Current assets 1 Property, plant, and equipment - net 2 Long-term investments 3 Intangible assets 4 Other assets 5 Total assets 6 7 Short-term debt and current portion of long-term debt -8 Accounts payable 9 Other current liabilities 10 Current liabilities 1 Long-term debt 2 Total liabilities 3 Total shareholder equity 44 Total liabilities and shareholder equity 45 Jun-2021 14,224 116,110 38,043 2,636 13,393 184,406 70,803 9,427 57,511 11,632 333,779 10,825 15,163 62,669 88,657 103,134 191,791 141,988 333,779 Sheet1 +