I need help with some of the required calculations. I downloaded the necessary data from Yahoo Finance and started putting things together in an excel file. I have attached my working file, the excel instructions (for calculations), and the rubric. *I know the deadline is tight, I have accounted for that in the price*

Dividend Adjusted Monthly Data - Yahoo Finance Date SPY KO NFLX 8/1/2012 0 0 0 9/1/2012 129.9291 32.28661 7.777143 10/1/2012 128.2456 31.86332 11.32 11/1/2012 128.9715 32.49749 11.67286 12/1/2012 129.2074 31.27946 13.22714 1/1/2013 136.7848 32.13371 23.60572 2/1/2013 138.5301 33.41077 26.86857 3/1/2013 143.1535 34.89494 27.04 4/1/2013 146.5528 36.7902 30.86714 5/1/2013 150.0128 34.75643 32.32143 6/1/2013 147.2319 34.86073 30.15571 7/1/2013 155.6597 35.07543 34.92571 8/1/2013 150.9911 33.41266 40.55857 9/1/2013 155.0139 33.15013 44.17286 10/1/2013 162.9827 34.88096 46.06857 11/1/2013 167.8131 35.42749 52.25714 12/1/2013 171.2342 36.66986 52.59572 1/1/2014 166.0954 33.57187 58.47572 2/1/2014 173.6554 33.90919 63.66143 3/1/2014 174.3265 34.31752 50.29 4/1/2014 176.3131 36.49516 46.00571 5/1/2014 180.4047 36.60252 59.69 6/1/2014 183.251 37.89984 62.94286 7/1/2014 181.6548 35.41746 60.38857 8/1/2014 188.8236 37.60796 68.23428 9/1/2014 185.3521 38.4553 64.45429 10/1/2014 190.6041 38.02722 56.11 11/1/2014 195.8404 40.70583 49.51286 12/1/2014 194.2714 38.60093 48.80143 1/1/2015 189.5558 37.64093 63.11429 2/1/2015 200.2097 39.58835 67.84428 3/1/2015 196.1895 37.07409 59.52714 4/1/2015 199.0032 37.39093 79.5 5/1/2015 201.5616 37.75967 89.15143 6/1/2015 196.5497 36.16485 93.84857 7/1/2015 201.8804 38.18273 114.31 8/1/2015 191.0502 36.54685 115.03 9/1/2015 183.7884 37.29043 103.26 10/1/2015 201.4259 39.70419 108.38 11/1/2015 201.233 39.95731 123.33 12/1/2015 196.5853 40.58495 114.38 1/1/2016 187.9097 40.54716 91.84 Monthly Returns SPY KO #DIV/0! #DIV/0! -0.0129571936 -0.0131104195 0.0056599203 0.01990298 0.0018288617 -0.0374809516 0.0586458037 0.0273104485 0.0127590107 0.0397419682 0.0333747997 0.0444219933 0.0237458926 0.0543130849 0.0236094941 -0.0552801365 -0.0185378427 0.0030010276 0.0572418466 0.0061586485 -0.0299925037 -0.0474055227 0.026642404 -0.0078572615 0.0514068902 0.052211806 0.0296375962 0.015668579 0.0203868096 0.0350679938 -0.0300106746 -0.0844833309 0.0455158167 0.0100477575 0.0038647931 0.0120419564 0.0113958755 0.0634553647 0.0232063478 0.0029417878 0.0157774678 0.0354434925 -0.0087102441 -0.0654983292 0.0394635605 0.0618479964 -0.0183848636 0.0225308419 0.0283353363 -0.0111318079 0.0274721095 0.0704392238 -0.0080116255 -0.0517101604 -0.0242734484 -0.0248697408 0.0562044877 0.0517366335 -0.0200797358 -0.0635101063 0.0143416188 0.0085461583 0.0128561266 0.0098619649 -0.024865135 -0.0422363021 0.0271213247 0.055796921 -0.0536467636 -0.0428434508 -0.0380097196 0.020345829 0.0959661431 0.064728702 -0.0009575284 0.0063752719 -0.0230964385 0.0157077375 -0.0441314796 -0.0009312811 -0.000825998 0.0048930434 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 187.7544 199.3557 201.1737 204.596 204.245 212.7949 213.0498 211.9913 209.3683 217.081 220.1839 225.445 234.3031 233.5798 236.9246 240.2683 240.6266 246.77 247.49 40.74556 43.82532 42.65329 42.46288 43.15789 41.85791 41.66603 40.60111 41.0102 39.02739 40.96675 40.54732 40.92773 41.39592 42.45994 44.74284 44.13276 45.47813 45.19042 93.41 102.23 90.03 102.57 91.48 91.25 97.45 98.55 124.87 117 123.8 140.71 142.13 147.81 152.2 163.07 149.41 181.66 174.71 0.0617897665 0.0755852982 0.0091191905 -0.026743147 0.0170115273 -0.0044643445 -0.0017154932 0.0163676859 0.0418611335 -0.0301216737 0.0011976414 -0.0045840564 -0.004968332 -0.0255583524 -0.0123732264 0.0100758817 0.0368383591 -0.0483492108 0.0142935561 0.0496921227 0.0238942267 -0.0102382061 0.0392915216 0.0093817789 -0.0030870825 0.0114395065 0.0143198092 0.0257035476 0.0141129495 0.0537659237 0.0014910206 -0.013635366 0.0255310474 0.0304847273 0.0029177006 -0.0063262935 -1 -1 thly Returns NFLX #DIV/0! 0.4555473649 0.0311711131 0.1331538628 0.7846420047 0.1382231803 0.0063802795 0.1415363483 0.0471143686 -0.0670055749 0.1581789441 0.1612811169 0.0891127057 0.0429159039 0.1343338332 0.0064789997 0.1117961986 0.0886814964 -0.2100397211 -0.0851916269 0.2974475084 0.0544958461 -0.0405809835 0.1299204404 -0.0553973433 -0.1294605006 -0.1175752073 -0.0143685107 0.2932876147 0.0749433983 -0.1225916394 0.3355252298 0.1214016101 0.0526872548 0.2180259706 0.0062986704 -0.1023211084 0.0495835261 0.1379406294 -0.0725695683 -0.1970624374 0.0170950356 0.094422424 -0.1193387816 0.139286917 -0.1081212538 -0.0025142435 0.0679451726 0.0112879018 0.2670725439 -0.0630255691 0.0581196838 0.1365913053 0.0100916632 0.0399633631 0.0297002846 0.0714192524 -0.0837677219 0.2158490003 -0.0382582674 -1 Date Adj Close 8/1/2012 0 9/1/2012 129.929138 10/1/2012 128.245621 11/1/2012 128.971481 12/1/2012 129.207352 1/1/2013 136.784821 2/1/2013 138.53006 3/1/2013 143.153473 4/1/2013 146.55278 5/1/2013 150.012817 6/1/2013 147.231903 7/1/2013 155.659729 8/1/2013 150.991104 9/1/2013 155.01387 10/1/2013 162.982651 11/1/2013 167.813065 12/1/2013 171.234238 1/1/2014 166.095383 2/1/2014 173.65535 3/1/2014 174.326492 4/1/2014 176.313095 5/1/2014 180.404678 6/1/2014 183.251007 7/1/2014 181.654846 8/1/2014 188.823593 9/1/2014 185.352097 10/1/2014 190.604111 11/1/2014 195.840408 12/1/2014 194.271408 1/1/2015 189.555771 2/1/2015 200.209656 3/1/2015 196.189499 4/1/2015 199.003174 5/1/2015 201.561584 6/1/2015 196.549728 7/1/2015 201.880417 8/1/2015 191.050186 9/1/2015 183.788422 10/1/2015 201.425888 11/1/2015 201.233017 12/1/2015 196.585251 1/1/2016 187.909653 2/1/2016 187.75444 3/1/2016 199.355743 4/1/2016 201.173706 5/1/2016 204.595978 6/1/2016 204.244995 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 212.794922 213.049774 211.991272 209.368256 217.081039 220.183899 225.445023 234.303101 233.579788 236.924606 240.268311 240.626556 246.770004 247.490005 Date Adj Close 8/1/2012 0 9/1/2012 32.28661 10/1/2012 31.863319 11/1/2012 32.497494 12/1/2012 31.279457 1/1/2013 32.133713 2/1/2013 33.41077 3/1/2013 34.894943 4/1/2013 36.790195 5/1/2013 34.756428 6/1/2013 34.860733 7/1/2013 35.075428 8/1/2013 33.412659 9/1/2013 33.150127 10/1/2013 34.880955 11/1/2013 35.42749 12/1/2013 36.669861 1/1/2014 33.571869 2/1/2014 33.909191 3/1/2014 34.317524 4/1/2014 36.495155 5/1/2014 36.602516 6/1/2014 37.899837 7/1/2014 35.417461 8/1/2014 37.60796 9/1/2014 38.455299 10/1/2014 38.027222 11/1/2014 40.70583 12/1/2014 38.600925 1/1/2015 37.64093 2/1/2015 39.588345 3/1/2015 37.074085 4/1/2015 37.390926 5/1/2015 37.759674 6/1/2015 36.164845 7/1/2015 38.182732 8/1/2015 36.546852 9/1/2015 37.290428 10/1/2015 39.704189 11/1/2015 39.957314 12/1/2015 40.584953 1/1/2016 40.547157 2/1/2016 40.745556 3/1/2016 43.825321 4/1/2016 42.653294 5/1/2016 42.462875 6/1/2016 43.157894 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 41.857906 41.666027 40.601112 41.010204 39.027393 40.966747 40.547321 40.927727 41.39592 42.459942 44.74284 44.132755 45.47813 45.190422 Date Adj Close 8/1/2012 0 9/1/2012 7.777143 10/1/2012 11.32 11/1/2012 11.67286 12/1/2012 13.22714 1/1/2013 23.60572 2/1/2013 26.86857 3/1/2013 27.04 4/1/2013 30.86714 5/1/2013 32.32143 6/1/2013 30.15571 7/1/2013 34.92571 8/1/2013 40.55857 9/1/2013 44.17286 10/1/2013 46.06857 11/1/2013 52.25714 12/1/2013 52.59572 1/1/2014 58.47572 2/1/2014 63.66143 3/1/2014 50.29 4/1/2014 46.00571 5/1/2014 59.69 6/1/2014 62.94286 7/1/2014 60.38857 8/1/2014 68.23428 9/1/2014 64.45429 10/1/2014 56.11 11/1/2014 49.51286 12/1/2014 48.80143 1/1/2015 63.11429 2/1/2015 67.84428 3/1/2015 59.52714 4/1/2015 79.5 5/1/2015 89.15143 6/1/2015 93.84857 7/1/2015 114.31 8/1/2015 115.03 9/1/2015 103.26 10/1/2015 108.38 11/1/2015 123.33 12/1/2015 114.38 1/1/2016 91.84 2/1/2016 93.41 3/1/2016 102.23 4/1/2016 90.03 5/1/2016 102.57 6/1/2016 91.48 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 91.25 97.45 98.55 124.87 117 123.8 140.71 142.13 147.81 152.2 163.07 149.41 181.66 174.71 FIN 340 Module Two Activity Guidelines and Rubric Overview: This activity will help you gather and analyze data relating to returns and standard deviations. Prompt: Use Yahoo! Finance to get monthly pricing for the S&P 500 ETF (SPY), Coca-Cola, and Netflix for the past five years. Use the provided instructions to complete this activity. Specifically, the following critical elements must be addressed: I. II. III. IV. V. VI. Calculate the monthly returns for S&P 500 ETF (SPY), Coca-Cola, and Netflix, supporting each calculation by showing the work involved. Calculate the average monthly return for S&P 500 ETF (SPY), Coca-Cola, and Netflix, supporting each calculation by showing the work involved. Calculate the annualized returns based on the monthly average return for S&P 500 ETF (SPY), Coca-Cola, and Netflix, supporting each calculation by showing the work involved. Calculate the standard deviation of monthly returns for S&P 500 ETF (SPY), Coca Cola, and Netflix, supporting each calculation by showing the work involved. Calculate the annualized standard deviation based on standard deviation of monthly returns, supporting each calculation by showing the work involved. Compare the differences in returns and standard deviations of the three sets of data and discuss their investment implications using a cell within the spreadsheet document. Rubric Guidelines for Submission: You must submit a completed Excel spreadsheet that fulfills the requirements outlined in the Module Two Activity Instructions document. Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. Critical Elements Monthly Returns Exemplary Proficient Calculates the monthly returns for S&P 500 ETF (SPY), Coca-Cola, and Netflix, supporting each calculation by showing the work involved with no errors (100%) Needs Improvement Calculates the monthly returns for S&P 500 ETF (SPY), Coca-Cola, and Netflix, but calculations contain errors, or work to support calculations is not shown (75%) Not Evident Does not calculate the monthly returns for S&P 500 ETF (SPY), Coca-Cola, and Netflix, or provide evidence of the work to support each calculation (0%) Value 14 Average Monthly Return Calculates the average monthly return for S&P 500 ETF (SPY), CocaCola, and Netflix, supporting each calculation by showing the work involved with no errors (100%) Annualized Returns Calculates the annualized returns based on the monthly average return for S&P 500 ETF (SPY), CocaCola, and Netflix, supporting each calculation by showing the work involved with no errors (100%) Standard Deviation of Monthly Returns Calculates the standard deviation of monthly returns for S&P 500 ETF (SPY), Coca-Cola, and Netflix, supporting each calculation by showing the work involved with no errors (100%) Annualized Standard Deviation Calculates the annualized standard deviation based on standard deviation of monthly returns, supporting each calculation by showing the work involved with no errors (100%) Compares the differences in returns and standard deviations of the sets of data and discusses their investment implications (85%) Differences in Returns and Standard Deviations Meets \"Proficient\" criteria, and comparison demonstrates a deep understanding of the content (100%) Articulation of Response Meets \"Proficient\" criteria and is presented in a professional and easy-to-read format (100%) Submission is well-organized, clear, concise, convincing, and free of errors in spelling, syntax, or grammar, with relevant sources that are authoritative and properly cited (85%) Calculates the average monthly return for S&P 500 ETF (SPY), Coca-Cola, and Netflix, but calculations contain errors, or work to support calculations is not shown (75%) Calculates the annualized returns based on the monthly average return for S&P 500 ETF (SPY), Coca-Cola, and Netflix, but calculations contain errors, or work to support calculations is not shown (75%) Calculates the standard deviation of monthly returns for S&P 500 ETF (SPY), Coca-Cola, and Netflix, but calculations contain errors, or work to support calculations is not shown (75%) Calculates the annualized standard deviation based on standard deviation of monthly returns, but calculations contain errors, or work to support calculations is not shown (75%) Compares the differences in returns and standard deviations of the three sets of data and discusses their investment implications, but comparison is inaccurate and/or incomplete (55%) Submission has major errors related to citations, grammar, spelling, syntax, or organization that negatively impact readability and articulation of main ideas (55%) Does not calculate the average monthly return for S&P 500 ETF (SPY), Coca-Cola, and Netflix, or provide evidence of the work to support each calculation (0%) Does not calculate the annualized returns based on the monthly average return for S&P 500 ETF (SPY), Coca-Cola, and Netflix, or provide evidence of the work to support each calculation (0%) Does not calculate the standard deviation of monthly returns for S&P 500 ETF (SPY), Coca-Cola, and Netflix, or provide evidence of the work to support each calculation (0%) Does not calculate the annualized standard deviation based on standard deviation of monthly returns, or provide evidence of the work to support each calculation (0%) Does not compare the differences in returns and standard deviations of the three sets of data or discuss their investment implications (0%) 14 Submission has critical errors related to citations, grammar, spelling, syntax, or organization that prevent understanding of ideas (0%) 10 Total 14 14 14 20 100% FIN 340 Module Two Activity Instructions 1. Open a new Excel workbook in which you can aggregate all of the data and make the necessary calculations. 2. Create a Data tab where you will copy your price data, and a Table tab where you will put your calculations for each stock/ETF into a presentable Summary Table. 3. Go to Yahoo! and at the top of the screen, search for the three required companies. 4. For each company, go to the Historical Data section. 5. Change the Data Settings: Change the Time Period settings by entering the most recent month-end date into the End Date box, and then change the Start Date box to five years prior to the End Date and select Done. Change the Frequency setting to Monthly. Select the Apply box at the right-hand side of the Settings bar. The data should update. Select Download Data. The data should begin downloading as a CSV file. In the =(B5/B4-1) formula, B5 is the more recent date and B4 is the older date. The =(B5/B4-1) formula can be dragged by hovering over the bottom right corner of the cell until you see +. Once you see +, you can hold your left mouse button and drag that formula to other cells. If the stock price went down, you will have a negative result. 6. Open the CSV file: Depending on the browser you use, you can either open the file from the browser or go to the appropriate file path where the file was saved and open from your file explorer. 7. Copy Data: For the first of the three stocks, you will want to copy both the Date column and the Adj Close column from the CSV file to your Excel workbook tab named Data. For the second and third stocks, you only need to copy the Adj Close column. You should change the Adj Close heading for each stock to the company name or symbol so the columns are easily identifiable. After all three stocks have been copied over, scroll all the way down to the bottom of the columns and make sure they are the same length to verify that you have copied the same amount of data for each stock. 8. Calculate the monthly returns for each month. Remember that the Adj Close prices that you downloaded from Yahoo! include all dividends. Therefore, you can simply take the percentage change between each month for each stock. You can create another tab for the monthly returns, but you may prefer to keep this data in the same tab as the price data, since they are so closely linked. This will also allow you to scan and review the sets of data side-byside for accuracy checks. 9. Create the Summary Table: You will create a table in the Table tab where you can aggregate all of the summary statistics side-by-side so they are easily comparable to one another. You can determine your columns and rows by the number of stocks in the table compared to the number of summary statistics you are placing in the table. You typically want the larger number to go down the rows and the lesser number to go across the columns. Here we have five summary statistics and three stocks, so you should place statistics down the rows and stocks across the columns. Statistic Excel Functions Average Monthly Return: AVERAGE Last One-Year Return: Percentage change between two dates (Adj Close from Yahoo! includes dividend adjustments) Monthly Standard Deviation: STDEV.S Annualized Return: The annualized return transforms the average monthly return into a comparable one-year return. ((1+Avg. Monthly Return)^12)-1 Annualized Standard Deviation: The annualized standard deviation transforms the average monthly standard deviation into a comparable one-year standard deviation. Avg. Monthly St. Dev * SQRT(12)