Question

Case Problem 1: Stock Market a. Using the dataset “Stock Market”, build a table with the descriptive statistics (N, Mean, Standard Deviation, Minimum, Median and Maximum) (10 points) • Which companies had a higher mean monthly return than the market (as measured by the S&P 500)? (5 points) • Which one was the most volatile (has the largest standard deviation)? Why is the S&P Index the less volatile? (5 points) b. Find the estimated regression equation relating each of the individual stocks to the S&P 500 and the value of R-Sq for each equation. (25 points) c. Find the betas (slope of estimated regression equation) for the individual stocks from the regression output. (10 points) • What does a stock with a beta greater than 1 indicate? And less than 1? What is the stock that benefits most from a rising market? Why? (25 points) d. What dp the R-Sq values indicate? (20 points)

Month	Microsoft	Exxon Mobil	Caterpillar	Johnson & Johnson	McDonald's	Sandisk	Qualcomm	Procter & Gamble	S&P 500
Jan-03	0,21799	0,27739	0,2696	0,29814	0,18557	0,05133	0,3349	0,300465	0,272585
Feb-03	0,30211	0,30293	0,36867	0,28219	0,25576	0,39363	0,21822	0,256644	0,282996
Mar-03	0,32152	0,32734	0,34681	0,40334	0,36245	0,30839	0,34251	0,387833	0,308358
Apr-03	0,35576	0,30715	0,37622	0,27391	0,48257	0,73876	0,18556	0,313588	0,381044
May-03	0,26283	0,34119	0,29144	0,26859	0,39532	0,80165	0,35395	0,321925	0,350899
Jun-03	0,34185	0,28654	0,36731	0,25124	0,47779	0,4164	0,37124	0,271248	0,311322
Jul-03	0,33003	0,29081	0,51847	0,30174	0,34306	0,69734	0,34285	0,290413	0,316224
Aug-03	0,30417	0,36661	0,36462	0,26196	0,27436	0,36633	0,40459	0,293399	0,317873
Sep-03	0,34827	0,27082	0,25837	0,29879	0,34996	0,35409	0,30823	0,363352	0,288056
Oct-03	0,24604	0,29945	0,36987	0,31636	0,36202	0,56491	0,43967	0,363833	0,354962
Nov-03	0,28355	0,29645	0,3378	0,2843	0,3412	0,30273	0,23957	0,279143	0,307129
Dec-03	0,36457	0,4326	0,39165	0,34787	0,26879	0,05724	0,51055	0,337822	0,350765
Jan-04	0,31023	0,29488	0,24556	0,33407	0,33665	0,18725	0,38678	0,31657	0,317276
Feb-04	0,25949	0,33996	0,26954	0,31367	0,39946	0,23628	0,37763	0,314147	0,312209
Mar-04	0,23969	0,28625	0,34383	0,24083	0,30954	0,41566	0,35072	0,32312	0,283641
Apr-04	0,34813	0,32308	0,28773	0,36526	0,2531	0,11629	0,24222	0,313063	0,283209
May-04	0,30383	0,3228	0,26938	0,33637	0,26952	0,36479	0,37541	0,319574	0,312083
Jun-04	0,38883	0,32682	0,35428	0,29982	0,28485	0,17992	0,38812	0,309831	0,317989
Jul-04	0,29755	0,34256	0,23026	0,29228	0,35769	0,42125	0,24834	0,262528	0,265709
Aug-04	0,26104	0,30151	0,28925	0,35636	0,28255	0,26012	0,40157	0,37325	0,302287
Sep-04	0,31282	0,34837	0,4066	0,26954	0,33738	0,54711	0,32602	0,266947	0,309364
Oct-04	0,31157	0,31842	0,30622	0,33639	0,33996	0,01669	0,36557	0,250296	0,314014
Nov-04	0,36864	0,34673	0,4367	0,33811	0,37341	0,38194	0,30048	0,344939	0,338595
Dec-04	0,29664	0,3002	0,3651	0,35139	0,34294	0,40585	0,32042	0,329918	0,332458
Jan-05	0,28353	0,30663	0,21796	0,32018	0,31029	0,28919	0,1783	0,270951	0,27471
Feb-05	0,26043	0,53217	0,36678	0,31832	0,3213	0,38826	0,26992	0,29737	0,318903
Mar-05	0,26065	0,2414	0,26202	0,32378	0,24135	0,33423	0,31609	0,298305	0,280882
Apr-05	0,34675	0,25688	0,26741	0,32189	0,24123	0,15252	0,2525	0,326981	0,279891
May-05	0,32292	0,29053	0,36882	0,28251	0,35561	0,39578	0,37079	0,318467	0,329952
Jun-05	0,26279	0,3226	0,31275	0,2687	0,1969	0,21375	0,1857	0,256482	0,299857
Jul-05	0,331	0,32227	0,4365	0,284	0,42324	0,7252	0,496	0,359905	0,335968
Aug-05	0,37224	0,32451	0,32931	0,29625	0,34107	0,44814	0,30811	0,297304	0,288778
Sep-05	0,23974	0,36077	0,35875	0,29826	0,33205	0,54234	0,42692	0,371738	0,306949
Oct-05	0,29883	0,18354	0,1994	0,28957	0,24357	0,52056	0,18849	0,246351	0,282259
Nov-05	0,38016	0,33883	0,39869	0,29138	0,3924	0,16719	0,44361	0,321432	0,335186
Dec-05	0,24473	0,26795	0,29983	0,27328	0,29616	0,53032	0,24942	0,312065	0,299048

Answer 1

Using the dataset “Stock Market”, build a table with the descriptive statistics (N, Mean, Standard Deviation, Minimum, Median and Maximum) (10 points) • Which companies had a higher mean monthly return than the market (as measured by the S&P 500)? (5 points) • Which one was the most volatile (has the largest standard deviation)? Why is the S&P Index the less volatile? (5 points)

The following steps show how to create the descriptive statistics.

Step 1 : Put the data in excel as shown.

Step 2 : go to DATA -> data analysis -> Descriptive Statistics

Step 3 : Input the values as shown.

Step 4 : The output will be generated as follows.

The needed statistics are highlighted in yellow.

Inorder to compare and answer the following questions, I have put the statistics in a easy to understand manner.

Which companies had a higher mean monthly return than the market (as measured by the S&P 500)?

Sandisk = Mean = 0.3693

Which one was the most volatile (has the largest standard deviation)?
Sandisk: Std.Dev = 0.1954

Why is the S&P Index the less volatile?
The index is made up of many stocks, hence the standard deviation of all the stock get averaged out and the index tends to be less volatile.

b. Find the estimated regression equation relating each of the individual stocks to the S&P 500 and the value of R-Sq for each equation. (25 points) c. Find the betas (slope of estimated regression equation) for the individual stocks from the regression output. (10 points) • What does a stock with a beta greater than 1 indicate? And less than 1? What is the stock that benefits most from a rising market? Why? (25 points) d. What dp the R-Sq values indicate? (20 points)
.
The following steps are given to find the regression equation for Microsoft
Step 1 : Put the data in excel as shown.

Step 2 : go to DATA -> data analysis -> regression

Step 3 : Input the values as shown.

Step 4 : The output will be generated as follows.

The coefficient that are used in the regression are highlighted in yellow and the regression equation is highlighted in green.

Similarly we find the regression for the other stocks. The final output and the regression equation along with the beta and rsquare is given.

Exxon Mobil

Caterpillar

Johnson & Johnson

McDonald's

Sandisk

Qualcomm

Procter & Gamble

The summary of the all the stocks is shown below.

What does a stock with a beta greater than 1 indicate? And less than 1?
A beta indicates how volatite a stock is . If it is greater than 1, indicating it has greater volatility than the stock index (S&P Index in this case)
If it is less than 1, indicating it has less volatility than the stock index

What is the stock that benefits most from a rising market? Why? (25 points)
Sandisk because it has a beta value of 2.60, indicating that if the stock index moves up, Scandisk will move 2.6 times more compared to the stock index.

What does the R-Sq values indicate?
It is the measure of the amount of variability in y explained by x. Its value lies between 0 and 1. Greater the value, better is the model.
In this case, the square helps us understand how much of the variability in the stock is explained by the S&P Index.