Can you please show the formulas on how to do it in excel?
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 5%. Note that Jan 2021FCF is already given in the spreadsheet, you don't need to compute this value. b. Use the corporate valuation / FCF model to determine the intrinsic value of the company's stock. 2. Given AMD's stock price at the end of December 2021 and your estimate of the value of AMD'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. 3. Now re-estimate the value of AMD assuming non-constant / multistage growth of FCF. The consensus estimate by financial analysis is that the company will grow at a 50% annual rate in 2022,43% in 2023 , 17% in 2024, and then at a constant rate of 5% from 2025 onward. Again, fill-in all of the blue-shaded areas in the spreadsheet. 4. Determine if AMD is overvalued, fairly valued, or undervalued in the non-constant growth case (assume that an intrinsic value within +/5% of the current market price is fairly valued). NOTES: The FCF for the current year in the assignment (December 2021) is $1,591.7 million. If you come up with a negative intrinsic value, then you have an error(s) in your work 53 Question 1 54 Constant Growth of FCF: 55 Growth Rate 56 Forecast FCF (2022) 57 Value of Operations 58 Non-operating assets 59 Value of Firm 60 Debt 61 Preferred Stock 62 Value of Common Equity 63 Intrinsic Value 64 Question 3 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 5%. Note that Jan 2021FCF is already given in the spreadsheet, you don't need to compute this value. b. Use the corporate valuation / FCF model to determine the intrinsic value of the company's stock. 2. Given AMD's stock price at the end of December 2021 and your estimate of the value of AMD'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. 3. Now re-estimate the value of AMD assuming non-constant / multistage growth of FCF. The consensus estimate by financial analysis is that the company will grow at a 50% annual rate in 2022,43% in 2023 , 17% in 2024, and then at a constant rate of 5% from 2025 onward. Again, fill-in all of the blue-shaded areas in the spreadsheet. 4. Determine if AMD is overvalued, fairly valued, or undervalued in the non-constant growth case (assume that an intrinsic value within +/5% of the current market price is fairly valued). NOTES: The FCF for the current year in the assignment (December 2021) is $1,591.7 million. If you come up with a negative intrinsic value, then you have an error(s) in your work 53 Question 1 54 Constant Growth of FCF: 55 Growth Rate 56 Forecast FCF (2022) 57 Value of Operations 58 Non-operating assets 59 Value of Firm 60 Debt 61 Preferred Stock 62 Value of Common Equity 63 Intrinsic Value 64 Question 3
