The purpose of this analysis is to find an intrinsic value for Microsoft $($MSFT$)$ using the both the Constant Growth Dividend

Discount Model $($DDM$)$ and the Non$-$Constant Growth DDM$.$ You will need to $(1)$ estimate beta to calculate the required

return for MSFT; $(2)$ estimate dividend growth rate; and $(3)$ estimate future dividends.

An excel template has been provided with the questions and space for the answers. Use this template. Submit your Excel

spreadsheet with all data and formulas using cell references so that your answers can be replicated. Where applicable,

please wrap your text and do a draft print to see if your output is in readable form. Adjust formats as needed, to ensure

consistency, readability, and appropriateness. Follow instructions as written. Neatness and organization matter.

Calculate and interpret the Correlation Coefficient $(1,2)$ between MSFT and SPX$.[$use $=$CORREL$()$ function$].$ Use

the returns $($and not the prices$)$ to find correlation. Format to $4$ decimal places.

Microsoft S&P $500$ Index

$($MSFT$)($SPY$)$

Obs. Date Adj Close Adj Close

$13/31/1440\mathrm{.}991872\mathrm{.}34$

$24/30/1440\mathrm{.}401883\mathrm{.}95$

$35/30/1440\mathrm{.}941293\mathrm{.}57$

$46/30/1441\mathrm{.}701960\mathrm{.}23$

$57/31/1443\mathrm{.}161930\mathrm{.}67$

$68/29/1445\mathrm{.}432003\mathrm{.}37$

$79/30/1446\mathrm{.}361972\mathrm{.}29$

$810/31/1446\mathrm{.}952018\mathrm{.}05$

$911/28/1447\mathrm{.}812067\mathrm{.}56$

$1012/31/1446\mathrm{.}452058\mathrm{.}90$

$111/30/1540\mathrm{.}401994\mathrm{.}99$

$122/27/1543\mathrm{.}852104\mathrm{.}50$

$133/31/1540\mathrm{.}662067\mathrm{.}89$

$144/30/1548\mathrm{.}642085\mathrm{.}51$

$155/29/1546\mathrm{.}862107\mathrm{.}39$

$166/30/1544\mathrm{.}152063\mathrm{.}11$

$177/31/1546\mathrm{.}702103\mathrm{.}84$

$188/31/1543\mathrm{.}521972\mathrm{.}18$

$199/30/1544\mathrm{.}261920\mathrm{.}03$

$2010/30/1552\mathrm{.}642079\mathrm{.}36$

$2111/30/1554\mathrm{.}352080\mathrm{.}41$

$2212/31/1555\mathrm{.}482043\mathrm{.}94$

$231/29/1655\mathrm{.}091940\mathrm{.}24$

$242/29/1650\mathrm{.}881932\mathrm{.}23$

$253/31/1655\mathrm{.}232059\mathrm{.}74$

$264/29/1649\mathrm{.}872065\mathrm{.}30$

$275/31/1653\mathrm{.}002096\mathrm{.}96$

$286/30/1651\mathrm{.}172098\mathrm{.}86$

$297/29/1656\mathrm{.}682173\mathrm{.}60$

$308/31/1657\mathrm{.}462170\mathrm{.}95$

$319/30/1657\mathrm{.}602168\mathrm{.}27$

$3210/31/1659\mathrm{.}922126\mathrm{.}15$

$3311/30/1660\mathrm{.}262198\mathrm{.}81$

$3412/30/1662\mathrm{.}142238\mathrm{.}83$

$351/31/1764\mathrm{.}652278\mathrm{.}87$

$362/28/1763\mathrm{.}982363\mathrm{.}64$

$373/31/1765\mathrm{.}862362.72$

$384/28/1768\mathrm{.}462384\mathrm{.}20$

$395/31/1769\mathrm{.}842411\mathrm{.}80$

$406/30/1768\mathrm{.}932423\mathrm{.}41$

$417/31/1772\mathrm{.}702470\mathrm{.}30$

$428/31/1774\mathrm{.}772471\mathrm{.}65$

$439/29/1774\mathrm{.}492519\mathrm{.}36$

$4410/31/1783\mathrm{.}182575\mathrm{.}26$

$4511/30/1784\mathrm{.}172647\mathrm{.}58$

$4612/31/1785\mathrm{.}542673\mathrm{.}61$

$471/31/1995\mathrm{.}012823\mathrm{.}81$

$482/28/1893\mathrm{.}772713\mathrm{.}83$

$493/29/1891\mathrm{.}272640\mathrm{.}87$

$504/30/1893\mathrm{.}522648\mathrm{.}05$

$515/31/1898\mathrm{.}842705\mathrm{.}27$

$526/29/1898\mathrm{.}612718\mathrm{.}37$

$537/31/18106\mathrm{.}082816\mathrm{.}29$

$548/31/18112\mathrm{.}332901\mathrm{.}52$

$559/28/18114\mathrm{.}372913\mathrm{.}98$

$5610/31/18106\mathrm{.}812711\mathrm{.}74$

$5711/30/18110\mathrm{.}892760\mathrm{.}17$

$5812/31/18101\mathrm{.}572506\mathrm{.}85$

$591/31/19104\mathrm{.}432704\mathrm{.}10$

$602/28/19112\mathrm{.}032784\mathrm{.}49$

$613/29/19117\mathrm{.}942834\mathrm{.}40$