LI I T B Problem 12-11 Use the data far below to estimate the beta of Nike (NKE) and HP (HPQ) stock based on their monthly returns from 2011-2017. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Nike beta HP beta \fA B C D E F G H 62 Feb-15 0.000080 0.058862 -0.035704 0.056205 0.058782 0.035784 0.056125 63 Mar-15 0.000250 0.033052 -0.095513 -0.015661 0.032802 0.095763 -0.015911 64 Apr-15 0.000050 0.014851 0.058087 0.009834 0.014901 ).058037 0.009784 65 May-15 0.000030 0.034298 0.013042 0.012856 0.034268 0.013012 0.012826 66 Jun-15 0.000100 0.062457 -0.089894 -0.019986 0.062357 0.089994 -0.020086 67 Jul-15 0.000580 0.066654 0.016994 0.022148 0.066074 0.016414 0.021568 68 Aug-15 0.000030 0.030116 -0.080603 0.061345 -0.030146 -0.080633 -0.061375 69 Sep-15 0.000050 0.105414 -0.073501 -0.024889 0.105364 0.073551 0.024939 Oct-15 0.000680 0.065544 0.052714 0.085286 0.064864 0.052034 0.084606 Nov-15 0.001700 0.009540 0.024224 0.003655 0.007840 0.022524 0.001955 Dec-15 0.001480 0.050197 -0.045933 0.017291 0.051677 -0.047413 -0.018771 Jan-16 0.003000 0.007840 0.17989 0.049787 0.010840 0.182899 0.052787 Feb-16 0.003080 0.006773 0.100927 0.000826 0.009853 0.097847 -0.003906 Mar-16 0.001930 0.000649 0.164079 0.067212 0.001281 0.162149 0.065282 Apr-16 0.001930 -0.041158 -0.004058 0.003941 0.043088 0.005988 0.002011 May-16 0.002830 0.063115 0.090465 0.017012 -0.065945 0.087635 0.014182 78 Jun-16 0.002480 0.002535 -0.052765 0.003424 0.000055 -0.055245 0.000944 79 Jul-16 0.002400 0.005435 0.116335 0.036471 0.003035 0.113935 0.034071 80 Aug-16 0.003200 0.038559 0.025696 0.001198 0.035359 0.022496 0.002002 81 Sep-16 0.002600 0.083796 0.089353 0.000010 0.086396 0.086753 -0.002590 82 Oct-16 0.002950 0.046914 -0.066967 0.017337 -0.049864 -0.069917 -0.020287 83 Nov-16 0.004680 0.002192 0.062802 0.036838 -0.006872 0.058122 0.032158 Dec-16 0.004800 0.018774 0.027747 0.020324 0.013974 0.032547 0.015524 85 Jan-17 0.005000 0.040724 0.014151 0.017895 0.035724 0.009151 0.012895 Feb-17 0.005230 0.080529 0.154153 0.039292 0.075299 .148923 0.034062 Mar-17 0.007380 0.021868 0.037000 0.001281 0.029248 0.029620 0.006099 88 Apr-17 0.007800 0.005742 0.052573 0.009926 -0.013542 0.044773 0.002126 89 May-17 0.009500 -0.043674 -0.003188 0.014113 -0.053174 -0.012688 0.004613 90 Jun-17 0.009930 0.116814 -0.061157 0.006391 0.106884 -0.071087 -0.003539 91 Jul-17 0.010530 0.000847 0.092677 0.020554 0.009683 0.082147 0.010024 92 Aug-17 0.009780 0.102625 -0.001047 0.002918 -0.112405 0.010827 -0.006862 93 Sep-17 0.010280 0.018178 0.053076 0.020102 -0.028458 0.042796 0.009822 94 Oct-17 0.011230 0.060559 0.079659 0.023564 0.049329 0.068429 0.012334 95 Nov-17 0.01238 0.098745 -0.004640 0.030566 0.086365 0.017020 0.018186 96 Dec-17 0.013550 0.038563 -0.014019 0.012079 0.025013 -0.027569 -0.001471 97 98 Requirements 99 1. Start Excel - completed. 100 2. In cell Do, by using cell references and the function SLOPE, calculate the beta of the Nike stock (1 pt.). 101 Hint: Use the excess return for Nike stock as the known y's and the excess return of the S&P 500 as the known x's. 102 3. In cell D7, by using cell references and the function SLOPE, calculate the beta of the HP stock (1 pt.) 103 Hint: Use the excess return for HP stock as the known y's and the excess return of the S&P 500 as the known x's. 04 4. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 105