AutoSave OFF ... w Assignment#3_Stock Valuation2 (1) - Compatibility Mode - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View ? Tell me Share Comments Times New... 12 A A Aa v AL AaBbCcDdF AaBbCcDdl AaBbCcDdE AaBbCcDdE AaBbCcDa Paste B I 1vab X2 Av LVAv E - v Heading 1 Heading 3 Normal No Spacing Heading 2 Styles Dictate Sensitivity Editor Pane ASSIGNMENT # 3 FIN 320: MANAGERIAL FINANCE Statistical Analysis 1. Get the monthly historical stock price data during January 1995-Present of three stocks. There are many websites that provide the historical stock price indices. The handout given will guide you through how to download the data from www.yahoo.com. Go to http://finance.yahoo.com, enter ticker symbol such "T" for "ATT" and click on "get quotes". However, you need to know the ticker symbols of the stocks you are interested. The ticker symbols can be obtained from from the company's website. 2. Get the monthly market indices during January 1995-Present. Market indices such as S&P500 (^GSPC ticker symbol from yahoo website) can be obtained from several websites. 3. Calculate the monthly returns of each stock. Return of ATT stock (0209) = Price of ATT (0209)-Price of ATT(0109) Price of ATT(0109) Then multiply by 100 to get return in %. 4. Plot the bar chart of monthly historical returns of each stock on each separate graph. Explain your findings, ie what caused the highest positive and negative returns in a particular period for a particular stock? The highest return would be attributable to the positive news about the company (introducing the new product, opening more branches etc.) and the lowest return would be attributable to the negative news about the company (CEO passed away, recall of the product, customers' lawsuit). The information needs to be more specific to the company. You can search this information by using the particulate date of highest and lowest return and name of company in google 5. Using the Excel functions for average (AVERAGE), calculate the average returns of each stock. 6. Using the Excel functions for variance (VARIANCE), calculate the variance of each stock. Variance measures the volatility or the risk of the stock. 7. Using the Excel functions for standard deviation (STDEV), calculate the standard deviation of each stock. Standard deviation measures the volatility or risk of the stock. 8. Calculate the monthly returns of S&P 500 index. Using the Excel functions for average (AVERAGE), calculate the average returns of S&P 500 index. 10. Using the Excel functions for variance (VARIANCE), calculate the variance of S&P 500 index. 11. Using the Excel functions for standard deviation (STDEV), calculate the standard deviation of S&P 500 index. 12. Plot the bar chart of monthly historical returns of S&P 500 index. Explain your findings, ie what caused the highest positive and negative return in a particular period?. Page 1 of 4 1212 words OX English (United States) Focus E EAutoSave OFF ... w Assignment#3_Stock Valuation2 (1) - Compatibility Mode - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View ? Tell me Share Comments Times New... 12 A A Aa v AL AaBbCcDdF AaBbCcDdl AaBbCcDdE AaBbCcDdE AaBbCcDa Av LVAv Heading 1 Heading 3 Normal No Spacing Heading 2 Styles Dictate Sensitivity Editor Paste B I 1vab X2 Pane 13. Compare the average monthly return and standard deviation of each stock with the S&P 500 index (market return), what can you conclude about risk and return relationship and diversification? Compare the average return of each stock and its variance (volatility/risk). Use the following Table 1 as an example to present your finding. ATT Walmart IBM S&P500 Average 0.6551 1.2157 1.4235 0.4002 Variance 60.7968 52.3132 81.7767 19.9352 Standard Deviation 7.7972 7.2328 9.0430 4.4649 Instructions on how to get the Historical Price Go to http://finance.yahoo.com Put the ticker symbol on the Get Quotes. For example: T is the ticker symbol for ATT stocks. Click on historical prices. Month Day Year Daily Weekly Start: 95 Monthly End: Dividends Ticker Symbol: Get Data You will get the following: Date Open High Low Close Volume Adj. Close* Nov 01 107.25 117 106.90 115.59 6,435,000 115.438 Nov 01 $0.14 Cash Dividend Oct 01 91.72 112.10 91.34 108.07 ,754,400 107.795 Sep 01 100.15 103.13 $7.49 91.72 12,620,800 91.4863 Aug 01 105.35 110.09 98.86 99.95 6,234,800 99.6954 The Open price is the first traded price for the day chosen in that month. The High price is the highest price during that month The Low price is the lowest price during that month. English (United States) IT Focus E E Page 2 of 4 1212 words XAutoSave OFF ... w Assignment#3_Stock Valuation2 (1) - Compatibility Mode - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View ? Tell me Share Comments Times New... 12 A A Aa v AL AaBbCcDdF AaBbCcDdl AaBbCcDdE AaBbCcDdE AaBbCcDa Paste B I 1vab X2 Av LVAv E Heading 1 Heading 3 Normal No Spacing Heading 2 Styles Dictate Sensitivity Editor Pane The Closing price is the last traded price for the day chosen in that month. The Adj. Close is a price that reflects the total return that would have been achieved by holding the stock for 1 month. It takes into consideration the effect of dividend payments and stock split. This is the price we are going to use to calculate the total returns of each stock. Hit the Download Spreadsheet Format to get the data. This will be in a csv file, so after downloading the file make sure you save it in the excel file. The close price in this file is the Adj. Close, which is the one you will use to calculate each stock's total returns. For the IBM stock, these are the numbers you will get: Date Open High Low Close Volume 01-Nov 106.977 116.846 106.628 115.438 6435000 01-Oct 91.4863 111.814 91.1073 107.795 9754400 01-Sep 99.8948 102.867 87.2671 91.4863 12620800 01-Aug 104.943 109.665 98.6081 99.6954 6234800 01-Jul 113.062 114.954 101.168 104.804 7667400 01-Jun 112.066 118.54 110.671 113.062 6806100 Then create the Excel Spreadsheet with the date and closing price. Date Stock 1 Stock 2 01-Nov 115.438 01-Oct 107.795 01-Sep 91.4863 01-Aug 99.6954 01-Jul 104.804 01-Jun 113.062 Since the output from the website starts from the most recent observation, you need to sort the date and the closing price from the past to present. To sort the date and closing price, go to Data, Sort, and Ascending. Once the data is sorted from the earliest observation to the most recent observation, the return series is created by taking the percentage price differences from month to month. For Example: Return (Feb 1999) = Close Price (Feb 1999)-Close Price (Jan 1999) *100 Close Price (Jan. 1999 Page 3 of 4 1212 words X English (United States) iT Focus EAutoSave OFF w Assignment#3_Stock Valuation2 (1) - Compatibility Mode - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View ? Tell me Share Comments Times New... 12 A A Aa v AL AaBbCcDdF AaBbCcDdl AaBbCcDdE AaBbCcDdE AaBbCcDa Paste B I 1vab X2 A LV Av E Heading 1 Heading 3 Normal No Spacing Heading 2 Styles Dictate Sensitivity Editor Pane You also need to calculate the total returns for the market index such as the S&P500 indices using the method described above. Report Instructions: Prepare a write-up of your report of at least 3 pages or more, 12 points font and double space. The write-up should be a report format, summarizing all your findings and support your findings with numbers (using the questions asked as a guideline to form the report format). The report should have three main parts, introduction, body, and conclusion. The introduction should provide a brief introduction what you will be discussing the project including the purpose of the assignment. In the body, students should summarize their findings in words and support the findings with the numbers. Insert the table that summarizes your finding where appropriate. Insert graphical representation if necessary where the text is being discussed. Use the tables to present your finding particularly when it involves comparison and numbers. Even when the data is presented in that table, the explanations of those numbers are needed. In the conclusion, you need provide the main finding of your report, reemphasize your key findings. There is no need to explain the calculation in the report. The report should not be laid out as an answer to each question. Attached all raw data, calculation, and regression output at the end of project. The assignment report has to be well-written and well- organized and free from grammatical mistakes. Please format all the number to 4 decimals for easy comparison. Please place the $ or % where it is appropriate. Insert the raw data, regression output, and long table at the end of the report. Create graphical representation as needed. The report should also contain the main Table 1 and the plots of historical returns for each stock with explanation of high and low. Attached all data and calculation to the end of project. Page 4 of 4 1212 words X English (United States) iJ Focus E E