MGT 252 Business Statistics I Semester Project Total 90 possible points plus

MGT 252 Business Statistics I

Semester Project

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.

#

Stock Symbols   

Industry

Sector 

Student assigned            

1

NKE and ADDYY

Footwear & Accessories 

Consumer Cyclical

KENDAL ALVEREZSIMMONS

2

WMT and TGT

Discount Store

Consumer Defensive

AMARA JONES

3

HD and LOW

Home Improvement Stores

Consumer Cyclical

ARLISS JACKSON

4

T and VZ 

Telecom Services

Communication Services

ASSIL BOUSSAYRI

5

F and GM

Auto Manufacturers 

Consumer Cyclical

AYANA EMMANUEL

6

GOOG and IBM

Internet Content & Information

Technology

BLAKE BELTRAN

7

HMC and WBA

Pharmaceutical Retailers

Healthcare

BRANDON INGRAM

8

AAP and AZO

Specialty Retail

Consumer Cyclical

CHERISH WOMACK

9

LNVGY and HPE

Computer Hardware

Technology

FRANKLIN WILLIAMS

10

AMZN and EBAY

Internet Retail

Consumer Cyclical

GONZALO MORENO

11

AAL and UAL

Airlines

Industrials

HALEY AVERY

12

JNJ and PFE

Drug Manufacturers

Major Healthcare

JAMIYA BURTON

13

ADDYY and SKX

Footwear & Accessories 

Consumer Cyclical

JHAMERIA JOLLEY

14

NKE and SKX

Footwear & Accessories 

Consumer Cyclical

JONATHAN PARKS

15

TGT and COST

Discount Store

Consumer Defensive

JUSTIN TURNER

16

WMT and COST

Discount Store

Consumer Defensive

KARA BORING

17

F and TM

Auto Manufacturers 

Consumer Cyclical

LIZAVETA DZEMCHANKA

18

GM and TM

Auto Manufacturers 

Consumer Cyclical

MALAESHA BENNETT

19

HMC and RAD

Pharmaceutical Retailers

Healthcare

NAKEYA ODOMS

20

WBA and RAD

Pharmaceutical Retailers

Healthcare

SANIYA PINDER

21

AAP and ORLY

Specialty Retail

Consumer Cyclical

SHA’QUERA CAMPBELL

22

AZO and ORLY

Specialty Retail

Consumer Cyclical

TYLER WILSON

23

AAL and DAL

Airlines

Industrials

ZANDREA HUMPHRIES

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.