I need help answering the Questions below. Stock Valuation Assignment (Fall 2017

Question

I need help answering the Questions below.

Stock Valuation Assignment (Fall 2017 Data)

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!

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 Sept 1, 2007 to Sept 1, 2017). These prices have already been adjusted for dividends. List dates and prices out on your spreadsheet in order to calculate monthly returns.

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 SEPT 2007 TO SEPT 2017! SO BE CAREFUL WITH YOUR RETURN FORMULA! There are 121 months to calculate 120 monthly returns. You may post monthly returns as decimals to 6 places or percentages to 4 places. For example, average return for MSFT can be written as .009999 or .9999%.

(10 points)

At the bottom of the column for each stock calculate the Average Monthly Return (use AVERAGE() function) and 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.2310% and SPY = .6861%.

(5 points)

Calculate and Interpret the Correlation Coefficient (r1,2) between Microsoft (MSFT) and S&P 500 Index (SPY). (use CORREL() function). (5 points)

We can estimate the Beta for MSFT over the 120-month period 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 function in Excel. Be careful use RETURNS NOT prices!

How does your estimate compare to the FinanceYahoo.com beta and the Value Line beta? What does Beta represent? (10 points)

Now, let’s check the stability of Beta. You may again use the SLOPE function in Excel, where SPY is independent & MSFT is dependent variable.

Estimate Beta over the first 60 monthly returns (5 years): October 1, 2007 to September 1, 2012.

Estimate Beta over the second 60 monthly returns (5 years):         October 1, 2012 to September 1, 2017.

What is the beta for each period? Is there a substantial difference between the two Betas?

(5 points)

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.25% (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: b = 0.97

(10 points)

Based on past trends and ValueLine estimate, let’s assume MSFT will pay a dividend of $1.64 in 2018. Therefore, let’s assume that D1 = $1.64, because it will not be fully paid until the end of Year 2018. Let’s 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 (7) above, estimate the current value of MSFT using the Constant Growth DDM.   Assume that D1 = $1.64

(10 points)

Now, using the Value Line sheet, estimate the average growth rate of dividends for MSFT over the last 10 years, from 2007-2017? 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.

(5 points)

Two-stage Non-constant DDM: Now let’s assume that for the next four years MSFT will grow its dividends at the growth rate you estimated in (9) above. Assuming D1 = $1.64, what are the dividends for: D2 ; D3 ; D4 ; and D5 if they grow at the rate estimated in (9)? You may round each dividend estimate to the nearest penny.

(10 points)

Now, let’s assume that the dividend growth reverts back to a L-T sustainable growth rate = 6% after Year 5 to infinity. Estimate is D6 and P5.

(5 points)

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 (10) & (11) above; assume a L-T sustainable growth rate of g = 6% after Year 5; and the required rate of return found in (7). [HINT: You already have all the data, not much work left here….find the sum of the PV of the cash flows.]

(10 points)

Which of the two models do you think is more reasonable (Constant DDM or Non-constant DDM)? WHY?

(5 points)

What is the current market price of MSFT? Based on your analysis, would you recommend buying this stock at the current market price? Explain why or why not?

(10 points)

MSFT Stock Valuation - Fall 2017 September 1, 2007 to September 1, 2017 Microsoft S&P 500 Index Date MSFT SPY 9/1/2007 23.005114 123.184921 10/1/2007 28.744678 125.448433 11/1/2007 26.238007 120.589432 12/1/2007 27.891703 118.602097 1/1/2008 25.541286 112.022667 2/1/2008 21.310514 109.127731 3/1/2008 22.321413 107.619057 4/1/2008 22.431522 113.306641 5/1/2008 22.274223 115.019432 6/1/2008 21.716801 104.881966 7/1/2008 20.303745 104.45945 8/1/2008 21.543118 106.073692 9/1/2008 21.153511 95.53141 10/1/2008 17.697933 80.212509 11/1/2008 16.025621 74.629189 12/1/2008 15.511801 74.753479 1/1/2009 13.644643 69.172134 2/1/2009 12.886605 61.739662 3/1/2009 14.758518 66.407921 4/1/2009 16.276951 73.527817 5/1/2009 16.783087 77.82576 6/1/2009 19.21818 77.337944 7/1/2009 19.01605 83.577255 8/1/2009 19.929657 86.664581 9/1/2009 20.911688 89.312035 10/1/2009 22.545919 88.012207 11/1/2009 23.911846 93.434395 12/1/2009 24.89135 94.709175 1/1/2010 23.013071 91.75856 2/1/2010 23.413229 94.620926 3/1/2010 24.031397 99.969772 4/1/2010 25.056971 101.934349 5/1/2010 21.167976 93.835182 6/1/2010 18.964071 88.558739 7/1/2010 21.271736 95.057457 8/1/2010 19.343184 90.781731 9/1/2010 20.291504 98.384964 10/1/2010 22.097773 102.690292 11/1/2010 20.929499 102.690292 12/1/2010 23.267279 108.982224 1/1/2011 23.117222 112.108025 2/1/2011 22.158522 116.00238 3/1/2011 21.291574 115.514496 4/1/2011 21.736027 119.37632 5/1/2011 20.972919 118.037544 6/1/2011 21.946026 115.473801 7/1/2011 23.127737 113.724846 8/1/2011 22.452477 107.472748 9/1/2011 21.141701 99.497154 10/1/2011 22.619667 110.927917 11/1/2011 21.727795 110.477135 12/1/2011 22.21661 110.927917 1/1/2012 25.271822 116.808311 2/1/2012 27.163143 121.878418 3/1/2012 27.789909 125.249596 4/1/2012 27.583172 124.958702 5/1/2012 25.1453 117.454208 6/1/2012 26.524225 121.590622 7/1/2012 25.553089 123.666451 8/1/2012 26.723654 126.764626 9/1/2012 25.975487 129.288025 10/1/2012 24.910635 127.612816 11/1/2012 23.234793 128.335098 12/1/2012 23.504921 128.569839 1/1/2013 24.156122 136.109879 2/1/2013 24.464123 137.846497 3/1/2013 25.385376 142.447144 4/1/2013 29.369312 145.82959 5/1/2013 30.966436 149.272614 6/1/2013 30.861908 146.505402 7/1/2013 28.44943 154.891617 8/1/2013 29.843309 150.246063 9/1/2013 29.94562 154.248947 10/1/2013 31.862221 162.17836 11/1/2013 34.309704 166.984924 12/1/2013 33.917126 170.389221 1/1/2014 34.30698 165.276016 2/1/2014 34.733093 172.79866 3/1/2014 37.441544 173.466476 4/1/2014 36.902618 175.442932 5/1/2014 37.39587 179.514313 6/1/2014 38.358791 182.346588 7/1/2014 39.701805 180.758041 8/1/2014 41.789921 187.891373 9/1/2014 42.911755 184.437057 10/1/2014 43.457882 189.66333 11/1/2014 44.253918 194.873749 12/1/2014 43.266247 193.312485 1/1/2015 37.63092 188.620041 2/1/2015 40.844452 199.221344 3/1/2015 38.142635 195.221054 4/1/2015 45.628563 198.020798 5/1/2015 43.958771 200.566574 6/1/2015 41.685722 195.579468 7/1/2015 44.093388 200.883865 8/1/2015 41.090885 190.107056 9/1/2015 42.065155 182.881134 10/1/2015 50.029591 200.431534 11/1/2015 51.654785 200.23967 12/1/2015 53.084164 195.614822 1/1/2016 52.711006 186.981628 2/1/2016 48.682812 186.827194 3/1/2016 53.224377 198.371201 4/1/2016 48.059025 200.180191 5/1/2016 51.075367 203.585556 6/1/2016 49.656723 203.236313 7/1/2016 55.003773 211.744019 8/1/2016 55.760704 211.997604 9/1/2016 56.244946 210.944336 10/1/2016 58.510365 208.334351 11/1/2016 58.842373 216.009033 12/1/2016 61.088055 219.096573 1/1/2017 63.555569 224.331726 2/1/2017 62.896912 233.146057 3/1/2017 65.137604 232.426315 4/1/2017 67.709076 235.754608 5/1/2017 69.073936 239.081802 6/1/2017 68.564697 239.438278 7/1/2017 72.314713 245.551392 8/1/2017 74.373741 246.267838 9/1/2017 73.870003 249.113724

Explanation / Answer

Microsoft S&P 500 Index Date MSFT SPY Return MSFT Return SPY 09-01-2007 23.005114 123.184921 10-01-2007 28.744678 125.448433 0.249490787 0.018374911 11-01-2007 26.238007 120.589432 -0.087204699 -0.038733055 12-01-2007 27.891703 118.602097 0.063026738 -0.016480175 01-01-2008 25.541286 112.022667 -0.084269397 -0.05547482 02-01-2008 21.310514 109.127731 -0.165644439 -0.025842413 03-01-2008 22.321413 107.619057 0.047436632 -0.013824845 04-01-2008 22.431522 113.306641 0.004932887 0.052849227 05-01-2008 22.274223 115.019432 -0.007012409 0.015116422 06-01-2008 21.716801 104.881966 -0.02502543 -0.088136985 07-01-2008 20.303745 104.45945 -0.06506741 -0.00402849 08-01-2008 21.543118 106.073692 0.061041596 0.015453288 09-01-2008 21.153511 95.53141 -0.018084987 -0.099386396 10-01-2008 17.697933 80.212509 -0.163357185 -0.1603546 11-01-2008 16.025621 74.629189 -0.094491939 -0.0696066 12-01-2008 15.511801 74.753479 -0.032062408 0.001665434 01-01-2009 13.644643 69.172134 -0.120370162 -0.074663348 02-01-2009 12.886605 61.739662 -0.055555722 -0.107448933 03-01-2009 14.758518 66.407921 0.145260369 0.075611995 04-01-2009 16.276951 73.527817 0.102885195 0.10721456 05-01-2009 16.783087 77.82576 0.031095259 0.058453293 06-01-2009 19.21818 77.337944 0.14509208 -0.006268053 07-01-2009 19.01605 83.577255 -0.010517645 0.080675936 08-01-2009 19.929657 86.664581 0.048043994 0.036939787 09-01-2009 20.911688 89.312035 0.049274857 0.030548281 10-01-2009 22.545919 88.012207 0.078149167 -0.014553783 11-01-2009 23.911846 93.434395 0.060584224 0.061607227 12-01-2009 24.89135 94.709175 0.040963128 0.013643584 01-01-2010 23.013071 91.75856 -0.075459105 -0.031154479 02-01-2010 23.413229 94.620926 0.017388292 0.031194539 03-01-2010 24.031397 99.969772 0.026402509 0.056529208 04-01-2010 25.056971 101.934349 0.04267642 0.01965171 05-01-2010 21.167976 93.835182 -0.15520611 -0.079454738 06-01-2010 18.964071 88.558739 -0.104115056 -0.056230967 07-01-2010 21.271736 95.057457 0.121686161 0.073383136 08-01-2010 19.343184 90.781731 -0.090662652 -0.044980437 09-01-2010 20.291504 98.384964 0.049026055 0.083752897 10-01-2010 22.097773 102.690292 0.089016024 0.04376002 11-01-2010 20.929499 102.690292 -0.052868404 0 12-01-2010 23.267279 108.982224 0.111697848 0.061270952 01-01-2011 23.117222 112.108025 -0.006449272 0.028681751 02-01-2011 22.158522 116.00238 -0.041471246 0.034737522 03-01-2011 21.291574 115.514496 -0.039124812 -0.00420581 04-01-2011 21.736027 119.37632 0.020874596 0.03343151 05-01-2011 20.972919 118.037544 -0.03510798 -0.011214753 06-01-2011 21.946026 115.473801 0.046398262 -0.021719725 07-01-2011 23.127737 113.724846 0.053846241 -0.015145903 08-01-2011 22.452477 107.472748 -0.029196977 -0.054975656 09-01-2011 21.141701 99.497154 -0.058380017 -0.074210385 10-01-2011 22.619667 110.927917 0.06990762 0.114885326 11-01-2011 21.727795 110.477135 -0.039429051 -0.004063738 12-01-2011 22.21661 110.927917 0.022497221 0.004080319 01-01-2012 25.271822 116.808311 0.13751927 0.053010948 02-01-2012 27.163143 121.878418 0.074839123 0.043405362 03-01-2012 27.789909 125.249596 0.023074134 0.027660172 04-01-2012 27.583172 124.958702 -0.007439283 -0.002322514 05-01-2012 25.1453 117.454208 -0.088382583 -0.060055793 06-01-2012 26.524225 121.590622 0.05483828 0.035217248 07-01-2012 25.553089 123.666451 -0.036613172 0.017072279 08-01-2012 26.723654 126.764626 0.045809139 0.025052672 09-01-2012 25.975487 129.288025 -0.027996433 0.019906176 10-01-2012 24.910635 127.612816 -0.040994496 -0.012957186 11-01-2012 23.234793 128.335098 -0.067274158 0.005659949 12-01-2012 23.504921 128.569839 0.011626013 0.001829125 01-01-2013 24.156122 136.109879 0.027704879 0.058645481 02-01-2013 24.464123 137.846497 0.012750432 0.012758942 03-01-2013 25.385376 142.447144 0.037657307 0.033375146 04-01-2013 29.369312 145.82959 0.156938231 0.023745271 05-01-2013 30.966436 149.272614 0.054380709 0.023609913 06-01-2013 30.861908 146.505402 -0.003375526 -0.018537975 07-01-2013 28.44943 154.891617 -0.078170086 0.057241678 08-01-2013 29.843309 150.246063 0.048994971 -0.029992288 09-01-2013 29.94562 154.248947 0.003428273 0.026642189 10-01-2013 31.862221 162.17836 0.064002716 0.051406594 11-01-2013 34.309704 166.984924 0.076814576 0.029637518 12-01-2013 33.917126 170.389221 -0.011442186 0.020386852 01-01-2014 34.30698 165.276016 0.011494311 -0.03000897 02-01-2014 34.733093 172.79866 0.012420592 0.045515642 03-01-2014 37.441544 173.466476 0.077978975 0.003864706 04-01-2014 36.902618 175.442932 -0.014393797 0.011393879 05-01-2014 37.39587 179.514313 0.013366315 0.023206298 06-01-2014 38.358791 182.346588 0.025749394 0.015777433 07-01-2014 39.701805 180.758041 0.035011896 -0.00871169 08-01-2014 41.789921 187.891373 0.052594989 0.039463428 09-01-2014 42.911755 184.437057 0.026844607 -0.018384644 10-01-2014 43.457882 189.66333 0.012726746 0.02833635 11-01-2014 44.253918 194.873749 0.018317414 0.027471937 12-01-2014 43.266247 193.312485 -0.022318272 -0.008011669 01-01-2015 37.63092 188.620041 -0.13024765 -0.02427388 02-01-2015 40.844452 199.221344 0.085396052 0.056204542 03-01-2015 38.142635 195.221054 -0.066148935 -0.020079626 04-01-2015 45.628563 198.020798 0.196261428 0.014341404 05-01-2015 43.958771 200.566574 -0.036595323 0.012856104 06-01-2015 41.685722 195.579468 -0.051708657 -0.02486509 07-01-2015 44.093388 200.883865 0.05775757 0.027121441 08-01-2015 41.090885 190.107056 -0.068094178 -0.053646962 09-01-2015 42.065155 182.881134 0.023710125 -0.038009752 10-01-2015 50.029591 200.431534 0.18933571 0.095966159 11-01-2015 51.654785 200.23967 0.032484655 -0.000957255 12-01-2015 53.084164 195.614822 0.027671764 -0.023096562 01-01-2016 52.711006 186.981628 -0.007029554 -0.044133639 02-01-2016 48.682812 186.827194 -0.076420359 -0.000825931 03-01-2016 53.224377 198.371201 0.09328888 0.061789757 04-01-2016 48.059025 200.180191 -0.097048614 0.009119217 05-01-2016 51.075367 203.585556 0.062763279 0.017011498 06-01-2016 49.656723 203.236313 -0.027775503 -0.001715461 07-01-2016 55.003773 211.744019 0.107680283 0.041861151 08-01-2016 55.760704 211.997604 0.013761438 0.001197602 09-01-2016 56.244946 210.944336 0.008684288 -0.004968301 10-01-2016 58.510365 208.334351 0.040277734 -0.012372861 11-01-2016 58.842373 216.009033 0.005674345 0.036838294 12-01-2016 61.088055 219.096573 0.038164368 0.014293569 01-01-2017 63.555569 224.331726 0.040392741 0.023894272 02-01-2017 62.896912 233.146057 -0.010363482 0.039291504 03-01-2017 65.137604 232.426315 0.035624833 -0.003087086 04-01-2017 67.709076 235.754608 0.039477534 0.014319777 05-01-2017 69.073936 239.081802 0.020157711 0.014112954 06-01-2017 68.564697 239.438278 -0.007372376 0.001491021 07-01-2017 72.314713 245.551392 0.054693102 0.025531064 08-01-2017 74.373741 246.267838 0.028473155 0.002917703 09-01-2017 73.870003 249.113724 -0.006773063 0.01155606 Correlation 0.9545188 Average 0.012309969 0.006861224 STDEV 0.071709533 0.043850808 Beta 0.988175403 Yahoo 1.45

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.