The purpose of this analysis is to find an intrinsic value for Microsoft (MSFT) using the both the Constant Dividend Discount Model (DDM) and the Non-constant DDM. You will need to (1) estimate Beta in order to calculate the required return for MSFT; (2) estimate dividend growth rate; and (3) estimate future dividends. Submit your Excel spreadsheet with all data and formulas so that your answers can be replicated. You may answer the questions on the spreadsheet. HOWEVER, WRAP YOUR TEXT!!! I do NOT want to see text running across 40 columns. Remember, Excel is not a word processor. Do a simple draft print to see if your output is in readable form. Follow instructions as written. NEATNESS AND ORGANIZATION MATTERS! 1. You are analyzing Microsoft to find an intrinsic value for Microsoft (MSFT) using the both the Constant Dividend Discount Model (DDM) and the Non-constant DDM. I have provided you with an Excel spreadsheet of monthly prices (121 months) from December 1, 2008 to December 1, 2018). These prices have already been adjusted for dividends. List dates and prices out on your spreadsheet in order to calculate 120 monthly returns. 2. Using the prices provided, calculate the monthly returns for each of the stocks, where r = (Pt/Pt-1) 1; which is the same as [(Pt-Pt-1)/ Pt-1] as I covered in the Lecture Video. PLEASE NOTE THAT THE DATA IS LISTED FROM DEC 2008 TO DEC 2018! SO BE CAREFUL WITH YOUR RETURN FORMULA! There are 121 months to calculate 120 monthly returns. You may post monthly returns as (1) decimals to 6 places or as (2) percentages to 4 places. For example, average return for MSFT should be written as .009999 or .9999%. (10 points) 3. At the bottom of the column for each stock calculate the Average Monthly Return [use =AVERAGE() function], and for the Standard Deviation [use =STDEV.P()] population function NOT STDEV() sample function). As a check, you should find your average returns to be: MSFT = 1.7997% and SPY = 1.1048%. (5 points) 4. CALCULATE and INTERPRET the Correlation Coefficient (1,2) between the RETURNS (not Prices) of Microsoft (MSFT) and S&P 500 Market Portfolio Index (SPY) => [use the =CORREL() function]. (5 points) 5. We can estimate the Beta for MSFT over the n-monthly periods by running a Regression of SPY returns on the x-axis (independent variable) and MSFT returns on the y-axis (dependent variable). The Beta is the SLOPE of the regression. To find Beta use the =SLOPE(MSFT, SPY) function in Excel. Be careful use RETURNS NOT prices! NOTE: You may round your Beta estimates to 2-decimals for comparison to Yahoo& Value Line. Calculate the beta for each period: (a) Estimate Beta over the full 120 monthly returns (10 years): Months 1 - 120 (b) Estimate Beta over the first 60 monthly returns (5 years): Months 1 - 60 (c) Estimate Beta over the second 60 monthly returns (5 years): Months 61 120 (d) Estimate Beta over the last 36 monthly returns (3 years): Months 85 120 (10 points) 6. Define Beta; give a good explanation of what Beta represents (see Module 2). (5 points) 7. Now, lets check the stability of Beta: (a) Is there a substantial difference between the four Betas? COMMENT! (5 points) (b) How do your estimates compare to the Finance.Yahoo.com beta and the Value Line beta? (5 points) 8. Given the information below, use the CAPM to estimate the required rate of return for MSFT. Round to 2 decimals, e.g., x.xx%, 1.23% Return on the market portfolio (SPY) RSPY = 9.0% (based on 25 years of historical data); the risk free rate is Rf = 3.0% (based on L-T inflation rate of 2.0% & real return of 1.0%); USE MSFT beta estimate: = 1.10 (5 points) 9. Based on past trends and ValueLine estimate, lets assume MSFT will pay a dividend of $1.84 in 2019. Therefore, lets assume that D1 = $1.84. Lets also assume that MSFT will grow its future dividends at a L-T constant rate of g = 6%. Assuming a required rate of return found in (8) above, estimate the current value of MSFT using the Constant Growth DDM. Assume that D1 = $1.84 (10 points) 10. Now, using the Value Line sheet, estimate the average growth rate of dividends for MSFT over the last 10 years, from 2008-2018? Round your growth estimate to 4 decimal places. [Hint: The Growth rate (g) can be calculated as CPT i on your calculator or in Excel as a TVM problem. [For example: What is the rate of return if you invest $1 (div0) today and in 10 years it is worth $4 (div10)?] (5 points) 11. Two-stage Non-constant DDM: Now lets assume that for the next four years MSFT will grow its dividends at the growth rate you estimated in (10) above. Assuming D1 = $1.84, what are the dividends for: D2 ; D3 ; D4 ; and D5 if they grow at the rate estimated in (10) above? You may round each dividend estimate to the nearest penny. (10 points) 12. Now, lets assume that the dividend growth reverts back to a L-T sustainable growth rate = 6% after Year 5, that is from Year 6 to infinity. Estimate is D6 and P5. (5 points) 13. Use the Non-constant growth DDM from the Stock Video Lecture (at 17:20) to estimate the current value of MSFT using the dividend information you found in (11) & (12) above; assume a L-T sustainable growth rate of g = 6% after Year 5; and the required rate of return found in (8). [HINT: You already have all the data, not much work left here.find the sum of the PV of the cash flows.] (10 points) 14. Which of the two models do you think is more reasonable (Constant DDM or Non-constant DDM)? WHY? (5 points) 15. What is the current market price of MSFT (see finance.yahoo.com and give the date for quoted price)? Based on your analysis, would you recommend buying Microsoft stock at quoted current market price? You must provide a proper explanation of WHY or WHY NOT based on this analysis. (5 points) Microsoft S&P 500 Index (MSFT) (SPY) Obs. Date Adj Close Adj Close 1 12/1/2008 15.171647 72.986816 2 1/1/2009 13.345434 67.537422 3 2/1/2009 12.604017 60.280582 4 3/1/2009 14.434885 64.838524 5 4/1/2009 15.920021 71.790146 6 5/1/2009 16.415056 75.986549 7 6/1/2009 18.796745 75.510246 8 7/1/2009 18.599051 81.602089 9 8/1/2009 19.492626 84.616447 10 9/1/2009 20.453114 87.201355 11 10/1/2009 22.051516 85.932243 12 11/1/2009 23.387495 91.226288 13 12/1/2009 24.345514 92.470947 14 1/1/2010 22.508421 89.590057 15 2/1/2010 22.899803 92.384804 16 3/1/2010 23.504421 97.607178 17 4/1/2010 24.507513 99.525345 18 5/1/2010 20.703796 91.617607 19 6/1/2010 18.54821 86.465836 20 7/1/2010 20.805275 92.81102 21 8/1/2010 18.919022 88.636307 22 9/1/2010 19.846544 96.059845 23 10/1/2010 21.613195 100.263435 24 11/1/2010 20.470543 100.263435 25 12/1/2010 22.757057 106.40667 26 1/1/2011 22.610296 109.458611 27 2/1/2011 21.672617 113.260925 28 3/1/2011 20.824682 112.784561 29 4/1/2011 21.259386 116.555138 30 5/1/2011 20.513008 115.248009 31 6/1/2011 21.464779 112.744888 32 7/1/2011 22.620571 111.037201 33 8/1/2011 21.960123 104.932869 34 9/1/2011 20.678095 97.145752 35 10/1/2011 22.123648 108.306389 36 11/1/2011 21.251326 107.86628 37 12/1/2011 21.729425 108.306389 38 1/1/2012 24.717642 114.047813 39 2/1/2012 26.567492 118.998093 40 3/1/2012 27.180515 122.289597 41 4/1/2012 26.978304 122.005577 42 5/1/2012 24.593901 114.678444 43 6/1/2012 25.942581 118.717079 44 7/1/2012 24.992739 120.743851 45 8/1/2012 26.137638 123.76886 46 9/1/2012 25.405874 126.232666 47 10/1/2012 24.364372 124.597 48 11/1/2012 22.72529 125.302147 49 12/1/2012 22.989487 125.531403 50 1/1/2013 23.626413 132.893219 51 2/1/2013 23.927656 134.588837 52 3/1/2013 24.828709 139.080719 53 4/1/2013 28.725281 142.38324 54 5/1/2013 30.287378 145.744873 55 6/1/2013 30.185152 143.043091 56 7/1/2013 27.825571 151.23111 57 8/1/2013 29.188885 146.695374 58 9/1/2013 29.288961 150.603622 59 10/1/2013 31.163521 158.345642 60 11/1/2013 33.557331 163.038651 61 12/1/2013 33.173367 166.362473 62 1/1/2014 33.554665 161.370056 63 2/1/2014 33.971455 168.71492 64 3/1/2014 36.620502 169.36702 65 4/1/2014 36.093391 171.296707 66 5/1/2014 36.575829 175.271881 67 6/1/2014 37.517635 178.037277 68 7/1/2014 38.831207 176.486221 69 8/1/2014 40.87352 183.451004 70 9/1/2014 41.97076 180.078323 71 10/1/2014 42.504902 185.181076 72 11/1/2014 43.283485 190.268356 73 12/1/2014 42.317471 188.743988 74 1/1/2015 36.805725 184.162384 75 2/1/2015 39.948784 194.513199 76 3/1/2015 37.306213 190.607422 77 4/1/2015 44.627991 193.340836 78 5/1/2015 42.994816 195.826462 79 6/1/2015 40.771599 190.95723 80 7/1/2015 43.12648 196.136307 81 8/1/2015 40.189812 185.614197 82 9/1/2015 41.142715 178.559052 83 10/1/2015 48.932507 195.695099 84 11/1/2015 50.522064 195.507767 85 12/1/2015 51.920101 190.992233 86 1/1/2016 51.555122 182.563065 87 2/1/2016 47.615261 182.412262 88 3/1/2016 52.05724 193.683441 89 4/1/2016 47.00515 195.44931 90 5/1/2016 49.955341 198.774216 91 6/1/2016 48.567814 198.433167 92 7/1/2016 53.797611 206.740295 93 8/1/2016 54.537945 206.987869 94 9/1/2016 55.011574 205.959488 95 10/1/2016 57.227314 203.411209 96 11/1/2016 57.552036 210.904541 97 12/1/2016 59.74847 213.919083 98 1/1/2017 62.16188 219.030441 99 2/1/2017 61.517666 227.63649 100 3/1/2017 63.709213 226.933792 101 4/1/2017 66.224304 230.183411 102 5/1/2017 67.559242 233.431961 103 6/1/2017 67.061157 233.780029 104 7/1/2017 70.728943 239.748688 105 8/1/2017 72.742813 240.448196 106 9/1/2017 72.856537 244.081772 107 10/1/2017 81.35598 251.073166 108 11/1/2017 82.324265 258.747467 109 12/1/2017 84.084389 260.553741 110 1/1/2018 93.393242 276.645203 111 2/1/2018 92.174339 266.586273 112 3/1/2018 90.138329 258.24469 113 4/1/2018 92.360428 260.618988 114 5/1/2018 97.614464 266.954376 115 6/1/2018 97.809433 267.289398 116 7/1/2018 105.218788 278.436218 117 8/1/2018 111.418053 287.323883 118 9/1/2018 113.878044 287.729675 119 10/1/2018 106.350555 269.058807 120 11/1/2018 110.41301 274.049652 121 12/1/2018 101.57 248.469025