FIN 6150 Online Problems: Spreadsheet Assignment Chapter 7 1. Go to Yahoo Finance and look up...

 

Transcribed Image Text:

FIN 6150 Online Problems: Spreadsheet Assignment Chapter 7 1. Go to Yahoo Finance and look up the annual dividends of Walmart (WMT) for the last six years (2017-2022). Add up the quarterly dividends to get the annual dividend figure for each year. Calculate the annual growth rate of the dividends for the five years 2018-2022. Use the average annual growth rate of dividends as g, the future growth dividend rate. Use the 2022 dividend as Do. Using the Dividend Growth Model, calculate the stock price of WMT assuming a required rate of return of: 9% 10% a. b. C. 11% d. e. f. 12% 13%. (Solve for R) If you were to purchase WMT at today's price what would your expected return be? (Use the 2022 year end stock price for Po). 2. Redo problem 1 completing all the parts, but instead of Walmart (WMT), use the pharmaceutical firm Pfizer (PFE). Corporate Valuation Problems 3. The Widget Company forecast free cash flow for the next five years is as follows: Free cash flows 2023 2024 2025 2026 2027 2,131 2,301 2,463 2,706 2,818 Ms. Stephanie Olsen, CFO, reports that the weighted average cost of capital of the company is 13% and that the expected growth rate after 2027 is 4% per year. Given these numbers and assumptions: a. Calculate the horizon value as of 2027. b. Calculate the value of the Widget Co as of today. C. d. Recalculate the value assuming a cost of capital of 13% and a growth rate of 6%. What effect does increasing or decreasing the growth rate have on value? Recalculate the value assuming a cost of capital of 15% and a growth rate of 4%. What effect does increasing or decreasing the cost of capital have on value? FIN 6150 Online Problems: Spreadsheet Assignment Chapter 7 1. Go to Yahoo Finance and look up the annual dividends of Walmart (WMT) for the last six years (2017-2022). Add up the quarterly dividends to get the annual dividend figure for each year. Calculate the annual growth rate of the dividends for the five years 2018-2022. Use the average annual growth rate of dividends as g, the future growth dividend rate. Use the 2022 dividend as Do. Using the Dividend Growth Model, calculate the stock price of WMT assuming a required rate of return of: 9% 10% a. b. C. 11% d. e. f. 12% 13%. (Solve for R) If you were to purchase WMT at today's price what would your expected return be? (Use the 2022 year end stock price for Po). 2. Redo problem 1 completing all the parts, but instead of Walmart (WMT), use the pharmaceutical firm Pfizer (PFE). Corporate Valuation Problems 3. The Widget Company forecast free cash flow for the next five years is as follows: Free cash flows 2023 2024 2025 2026 2027 2,131 2,301 2,463 2,706 2,818 Ms. Stephanie Olsen, CFO, reports that the weighted average cost of capital of the company is 13% and that the expected growth rate after 2027 is 4% per year. Given these numbers and assumptions: a. Calculate the horizon value as of 2027. b. Calculate the value of the Widget Co as of today. C. d. Recalculate the value assuming a cost of capital of 13% and a growth rate of 6%. What effect does increasing or decreasing the growth rate have on value? Recalculate the value assuming a cost of capital of 15% and a growth rate of 4%. What effect does increasing or decreasing the cost of capital have on value?