You have been hired as an equity analyst and asked to estimate the stock price of Target Corporation (TGT). See the questions on page 2. After examining its financials and growth expectations, you decide to use the dividend discount models (DDM) and price-multiples approaches to estimate Target's intrinsic value.

Target's stock price, earnings per share (EPS), and dividends per share (DPS) for the past 5 years are given below.

Fiscal Year	Stock Price $	EPS $	DPS $
2019	124.76	6.34	2.60
2020	176.53	8.64	2.68
2021	231.44	13.56	3.16
2022	149.04	5.98	3.96
2023	142.42	8.33	4.36

Analysts have estimated Target's perpetual constant dividend growth rate at 7%. Dividends are projected to increase at an annual rate of 12% in the next five years (2024-28). Beyond 2028, the annual dividend growth is expected to decline to a constant growth rate of 7%.

The beta of Target is estimated at 1.12. The current U.S. 10-year Treasury bond yield is 4.2%. Historically, the market risk premium in the U.S. stock market has been estimated as 6%. The required return of a stock or the discount rate is estimated using the Capital Asset Pricing Model (CAPM) equation.

You have compiled the projected earnings per share for Target and Walmart in the retail store industry.

Retail Stores (Stock Symbol)	Current Stock Price $ (Feb 2024)	Projected EPS $ for 2024
Target (TGT)	152.92	9.14
Walmart (WMT)	58.61	2.35

Questions

Assume the last year (2023) as year zero. Your assignment is to value Target as of March 2024 based on the information provided. Answer the following questions.

1. What is the value of Target using the constant dividend growth model? (20 pts)
2. What is the value of Target using the two-stage dividend growth model? (40 pts)
3. What is the value of Target using the past 5-year average price-earnings multiple of Target? (20 pts)
4. What is the value of Target using the forward Price-earnings multiple of Walmart? (15 pts)
5. Would you recommend buying Target stock? How would the current and expected U.S. and global economic environment impact your decision? Explain clearly and completely. (5 points)

Assignment

Prepare solutions to the above questions in Excel. Please upload picture(s) of excel sheet with formuals shown.