MGT 252 Business Statistics I
Total 90 possible points plus 10 bonus points if submitted by 11:59 pm, Nov. 23.
Deadline for part I (dataset) submission: 11:59 pm, Nov. 16.
Deadline for part II (project) submission (without bonus points): 11:59 pm, Dec. 3.
Deadline for GRADUATING SENIOR STUDENTS: 11:59 pm, Nov. 21.
NOTE: Any LATE submission will not be accepted after the deadlines listed above. Please do not bother to contact me to negotiate on this; neither would I reply.
Please read the instructions carefully before you start working on the Excel project:
Use Microsoft Excel to complete this statistic project for two company stocks assigned in the list below. If you prefer to work on the other stocks of your interest, you should email me for approval first, and the two stocks picked should come from the same Industry & Sector.
Copy and paste all your Excel results and answers to each of the questions in a Word document, upload the Word (or PDF) document to the required place in Blackboard to get graded (Please first uploaded your cleaned dataset to the required place by its separate deadline).
To get full credits, please make sure you read the questions carefully and to answer each of the parts completely. Remember to submit two items- your dataset in Excel and the report in Word.
Make sure you label answers for each part/question clearly, if I can’t find it, I can’t grade it. Fail to follow the above instructions will get points deducted accordingly.
NKE and ADDYY
Footwear & Accessories
WMT and TGT
HD and LOW
Home Improvement Stores
T and VZ
F and GM
GOOG and IBM
Internet Content & Information
HMC and WBA
AAP and AZO
LNVGY and HPE
AMZN and EBAY
AAL and UAL
JNJ and PFE
ADDYY and SKX
Footwear & Accessories
NKE and SKX
Footwear & Accessories
TGT and COST
WMT and COST
F and TM
GM and TM
HMC and RAD
WBA and RAD
AAP and ORLY
AZO and ORLY
AAL and DAL
Part I (submit your cleaned dataset by 11:59 of Nov. 16):
First, please access the data from: Navigate to Yahoo Finance https://finance.yahoo.com/.
a. Lookup your two stock symbols from the search bar on the top, for example “WMT”.
b. Navigate down to “Historical Data”.
b. Query stock data from “Time Period” 01/01/2010 to 1/31/2018
c. Select Monthly data for “Frequency”, then click on Apply on the right.
d. Download the monthly stock data, an Excel file will be downloaded.
e. Using the “Adj Close” column to compute the monthly return for each of your two stocks following this example: If the January 2018 adj close price is $60 and the 2018 February adj close price is $67, the monthly return for January 2018 is calculated in Excel as =((67/60)-1) * 100, which is equal to 11.67 (percent). Now create a new column labeled “Monthly return” and perform the calculation for each month of the two stocks respectively (remember to use the “drag down” trick I showed you).
f. Now create a new Excel worksheet, copy and paste the “Date”, “Monthly return (the one you just calculated)” and “Volume” columns from each of the two stocks to this new worksheet, and label them clearly. You will have something like the one below
Note: You will not have the return of January 2010, as it requires adj close price of December 2009. So, in your dataset to submit, return observations start from February 2010.
Now, please submit your cleaned dataset to the required place in Blackboard by Nov 16 to get possible full credits for this part. (10 pts)
Part II: Now, apply the knowledge and Excel skills you learned in this course, write a report on the performance of the two stocks, following the order of the questions below.
Use the return data and follow the example in the slides of Chapter 2.4 to generate a scatterplot between the monthly return of the two stocks (Hint: you are generating A scatterplot between these two stock returns, so just one figure should be reported) (5 pt). Describe what relation you observed between the return of the two stocks (positive, negative or no relation; strong, moderate or weak). (5 pt)
Calculate the arithmetic mean, median, mode, range and MAD for the investment return in both of the stocks. Report the results clearly with THE FOMULAS YOU USED TO GET FULL POINTS to get full points. (10 pts)
Calculate the variance and standard deviation for returns of BOTH of the two stocks. Report the results clearly with THE FOMULAS YOU USED TO GET FULL POINTS to get full points (6 pts). Which sector has higher expected return, and which sector has higher risk? (4 pts)
Based on the mean, variance and standard deviation calculated in 2&3, calculate the coefficient of variation (CV) for investment returns for BOTH of the stocks (6 pts). Which stock is a better investment, suggested by CV? Report the results clearly with THE FOMULAS YOU USED TO GET FULL POINTS. (4 pts)
Suppose that you have invested in total of $10,000 in the two stocks, among which 40% is invested in stock one and 60% in stock two, based on the mean, variance and standard deviation calculated, calculate your portfolio expected return of the entire investment of $10,000 (Hint: Refer to section 5.2~5.3, particularly starting from page 24 of your Chapter 5 slides). Report your results with THE FOMULAS YOU USED TO GET FULL POINTS. (15 pts)
Use CORREL (array1, array2) function to calculate the correlation of investment return between the two stocks (6 pts). What relation does the covariance suggest of the returns of the two stocks? (4 pts)
Use the correlation you just calculated in 6 to calculate the portfolio variance (VAR(Rp)) of your entire investment of $10,000 (Hint: Use the formula on page 24 of Chapter 5 slides. You have already calculated the standard deviation of stock 1 (σ1) and stock 2 (σ2) in part3. ρ12 is also calculated in part 6, which is the correlation. Also refer to the example on page 25 of Chapter 5 slides to calculate the weights w1 and w2.) Report your results with THE FOMULAS YOU USED TO GET FULL POINTS. (15 pts)
Lastly, before submitting your Word document and your Excel data to Blackboard, please go back to check all your answers to see if you have missed any part and make sure I can find and understand all of them to get graded!! If I can’t read or find it, I can’t grade it.