Please check file named "Term Project Corporate" for instructions. Do it in excel and paste it in word file with proper analysis description. Attaching an excel file as a sample for your assistance.

Choose any real company of which information is available.

SUMMARY OUTPUT Regression Statistics Multiple R 0.512473 R Square 0.262629 Adjusted R 0.24694 Standard E 0.05792 Observatio 49 ANOVA df Regression Residual Total SS MS F Significance F 1 0.056158 0.056158 16.73995 0.000167 47 0.157673 0.003355 48 0.213832 Coefficients Standard Error t Stat P-value Lower 95%Upper 95%Lower 95.0% Upper 95.0% Intercept 0.002998 0.008633 0.34722 0.729976 -0.01437 0.020365 -0.01437 0.020365 X Variable 1.174179 0.286984 4.091449 0.000167 0.596842 1.751515 0.596842 1.751515 Date WBA ReturS&P Return 6/1/2013 -0.074539 -0.014999 7/1/2013 0.136878 0.049462 8/1/2013 -0.043383 -0.031298 9/1/2013 0.119201 0.02975 10/1/2013 0.101115 0.044596 11/1/2013 -0.000675 0.028049 12/1/2013 -0.02973 0.023563 1/1/2014 -0.001567 -0.035583 2/1/2014 0.18483 0.043117 3/1/2014 -0.028256 0.006932 4/1/2014 0.028321 0.006201 5/1/2014 0.059057 0.02103 6/1/2014 0.030872 0.019058 7/1/2014 -0.072305 -0.01508 8/1/2014 -0.119965 0.037655 9/1/2014 -0.020654 -0.015514 10/1/2014 0.083516 0.023201 11/1/2014 0.068359 0.024534 12/1/2014 0.110625 -0.004189 1/1/2015 -0.032152 -0.031041 2/1/2015 0.126509 0.054893 3/1/2015 0.019259 -0.017396 4/1/2015 -0.020666 0.008521 5/1/2015 0.03509 0.010491 6/1/2015 -0.016309 -0.021012 7/1/2015 0.144363 0.019742 8/1/2015 -0.104315 -0.062581 9/1/2015 -0.039861 -0.026443 10/1/2015 0.019013 0.082983 11/1/2015 -0.007676 0.000505 12/1/2015 0.013448 -0.01753 1/1/2016 -0.06388 -0.050735 2/1/2016 -0.009784 -0.004128 3/1/2016 0.06714 0.065991 4/1/2016 -0.058879 0.002699 5/1/2016 -0.023713 0.015325 6/1/2016 0.07584 0.000911 7/1/2016 -0.048277 0.03561 8/1/2016 0.018423 -0.001219 9/1/2016 -0.001115 -0.001234 10/1/2016 0.026172 -0.019426 11/1/2016 0.024175 0.034175 12/1/2016 -0.02325 0.018201 1/1/2017 -0.009908 0.017884 2/1/2017 0.054186 0.037198 3/1/2017 -0.038551 -0.000389 Dividend 2012 2013 2014 2015 2016 0.275 0.315 0.338 0.36 0.375 1.1 1.26 1.352 1.44 1.5 g 0.1454545 0.0730159 0.065089 0.041667 2017 g = ROE x retention ratio ROE = net income/shareholders' equity 2016 2015 4,220,000 net income 4,173,000 Shareholder29,880,000 30,861,000 ROE = 13.97% 13.67% retention ratio RE RE 2016 2016 2015 2014 27,684,000 25,089,000 22,327,000 2,595,000 2,762,000 2016 2015 Net incom 4,173,000 4,220,000 retention r 0.6218548 0.6545024 g= DDM 0.0868474 0.0894981 R = D0(1+g)/P0 + g P0= g= D0= R= CAPM 77.69 0.0868474 1.5 0.1078317 beta= Rf= Rm= CAPM Annual report released on October 20, 2016 Stock price on October 20, 2016 g= 0.041667 R= 0.061779 1.17 (see tab 5) 2.26% June 12, 2017, 10-year T-note 10.30% Ri= 11.66% Vcs = D0 (1+g)/(R-g) Vcs = 54.757482 20.84632 4/1/2017 0.042023 0.009091 5/1/2017 -0.063786 0.011576 6/1/2017 0.005307 0.007268 0.00858 0.102959 Rm 2.26% 10 Y T Note areholders' equity ed on October 20, 2016 77.69 Issuer Name WALGREENS BOOTS ALLIANCE INC Symbol WAG4182649 WAG4182652 WAG4182651 WAG4182655 WAG4182648 WAG4182650 WAG4369076 WAG4369077 WAG4369073 Callable Yes Yes Yes Yes Yes Yes Yes Yes Sub-Product Type Corporate Bond Corporate Bond Corporate Bond Corporate Bond Corporate Bond Corporate Bond Corporate Bond Corporate Bond Coupon Maturity WALGREENS BOOTS ALLIANCE INC 1.75 11/17/2017 WALGREENS BOOTS ALLIANCE INC 2.7 11/18/2019 WALGREENS BOOTS ALLIANCE INC 3.3 11/18/2021 WALGREENS BOOTS ALLIANCE INC 3.8 11/18/2024 WALGREENS BOOTS ALLIANCE INC 4.5 11/18/2034 WALGREENS BOOTS ALLIANCE INC 4.8 11/18/2044 WALGREENS BOOTS ALLIANCE INC 3.45 6/1/2026 WALGREENS BOOTS ALLIANCE INC Corporate Bond 4.65 1.75 6/1/2046 5/30/2018 Moody's Baa2 Baa2 Baa2 Baa2 Baa2 Baa2 Baa2 Baa2 WR S&P BBB BBB BBB BBB BBB BBB BBB BBB BBB Price 100.149 101.499 103.535 103.705 103.237 105.522 100.333 104.001 100.808 Yield 1.395 2.042 2.422 3.218 4.23 4.446 3.406 4.402 0.923 Outstanding 750,000.00 ### ### ### 500,000.00 ### ### Market value 751117.5 1268738 1294188 2074100 7.19% 12.15% 12.40% 19.87% Weight Weight * Yield 516185 2004918 1906327 4.94% 19.20% 18.26% 600,000.00 WR 624006 5.98% 0.100369 0.248167 0.300254 0.639341 0.209152 0.853853 0.621955 0.263121 Stock Number of shares Price per share 12/06 1.08 billion 80.99 MV debt 87.4692 Billion 10.43958 Billion 97.90878 RE = CAPM WACC 0.106463 We Wd 0.893374 0.106626 RE= DDM g= ROE x retention ratio WACC 0.079865 MV equity WALGREENS BOOTS ALLIANCE INC WALGREENS BOOTS ALLIANCE INC WAG4369074 WAG4369075 Corporate Bond Corporate Bond 2.6 3.1 6/1/2021 6/1/2023 WR WR BBB BBB WR 101.05 102.691 2.318 2.599 WR 10,150,000.00 10439578.5 MV debt 0.0323621293 Rd FIN 310 Corporate Finance Group Project 20 marks Deadline: June 22nd at 10:30 am You can work individually or form a teams of no more than four students. Continue working on company you analyzed in the first part of the project. Instructions Company Valuation 1. Calculate the average growth rate in dividends over the last 5 years 2. Use the dividend discount model to calculate the required return on the stock Beta and Expected Return 3. Extract the historical monthly prices of the selected company over the last 48 months Information can be found on: http://finance.yahoo.com/q/hp?s=WAG+Historical+Prices 4. Extract the historical monthly prices of S&P 500 over the last 48 months http://finance.yahoo.com/q/hp?s=%5EGSPC+Historical+Prices 5. Use the extracted data to calculate the monthly returns of the company and S&P 500 Calculate Beta of the selected company 6. Rf = 10 year T-note 7. Use the CAPM to calculate the required return on the selected company 8. Use the required return found from previous question to estimate the intrinsic value of the selected company using the dividend discount model WACC Find the market value of the equity and the market value of the debt 1. Calculate the weights of the equity and debt in the capital structure Rdebt is the yield to maturity on the bonds http://cxa.marketwatch.com/finra/BondCenter/Default.aspx 2. Calculate the WACC