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 $

$2019124\mathrm{.}766\mathrm{.}342\mathrm{.}60$

$2020176\mathrm{.}538\mathrm{.}642\mathrm{.}68$

$2021231\mathrm{.}4413\mathrm{.}563\mathrm{.}16$

$2022149\mathrm{.}045\mathrm{.}983\mathrm{.}96$

$2023142\mathrm{.}428\mathrm{.}334\mathrm{.}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\mathrm{.}12.$ The current U$.$S$.10-$year Treasury bond yield is $4\mathrm{.}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\mathrm{.}929\mathrm{.}14$

Walmart $($WMT$)58\mathrm{.}612\mathrm{.}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.

What is the value of Target using the constant dividend growth model? $(20$ pts$)$

What is the value of Target using the two$-$stage dividend growth model? $(40$ pts$)$

What is the value of Target using the past $5-$year average price$-$earnings multiple of Target? $(20$ pts$)$

What is the value of Target using the forward Price$-$earnings multiple of Walmart? $(15$ pts$)$

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 pictures of excel sheet.