1. Download the following three data series:
I. Monthly Microsoft stock prices (MSFT) prices from Yahoo Finance at htto://www.whoo.com/finance. Monthly S&P 500 Index (405PC) from Yah00 Finance.
Three-month treasury bill rate (783MS)
https://researdestiouisfed.ore/fred2/series/T83mSkids-116 from FRED Database.
The sample period for Microsoft and S&P 500 is from January 1, 1990 to May 31, 2018 Use 'Adj. Close' prices to calculate the annualized capital returns for Microsoft and for S&P 500 Index. For the three month treasury bill rate, go to the above link on FRED and click 'Download data' on the left-hand side of the page and dick 'Download Data' to open the Excel file. Use only the data between February 1990 and May 2016. The unit of Interest rates Is In percent. Therefore, don't calculate the percentage changes (the capital returns) for the three-month treasury bill rate. You organize the data as In the columns A to F in the following table.
A B C D E F G H
1 Date MSFT SP500
2 1/2/1990 0.441 329.08 MSFT Returns 50500 Returns 7133MS MSFT EXR111 50503 EXRTN
3 2/1/1990 0.471 331.89 a(b3-b2)/b2*1200 *(c3-c2)/c2*1200 7.74 ad343 .e343
4 3/1/1990 0.529 339.94 145.834 290.106 7.90 137.934 21.206
a. USe Excel' Data Analysis to get the descriptive Statistics of Microsoft Stock prices and S&P500 I dex; the data are I columns 13 and C in he above table. Prin the descriptive statistics. Don't print the d ta.
b. Use Excel' Data Analyses to get the descriptive statistics of Microsoft stock returns, S&PSOO returns and three-month treasury bill rate; the data are in columns D, E, and Fin the above table. Prin the descriptive statistics. Don't print the data.
C. Use Excer Data Analysis to get the correlation and the covariance matrices for Microsoft stock
prices and S&P500 Index. in Excel, click "Data""Dato Analysts" and select "Covariance* or "Conelotion." Then select all data in columns 8 and
C. Print the correlation and covariance matrices. The correlation and covariance matrices show the correlation and the covariance for each pair of the two variables.
d. Use Excel's Data Analysis to get the correlation and the covariance matrices for Microsoft stock returns, S&P500 returns, and three-month treasury bill rate (the data are in columns D, E, and ). Print the correlation and covariance matrices.
e. Use the correlation matrix from part d to determine which pair has the highest correlation.
f. Compare Microsoft stock returns with S&P 500 returns. We would like to test if the mean returns for these two series are the same. Get the Excel printout for the test and conduct the test with the 5% significance level. Note that this is a "paired sample.'
g. Estimate a CAM model using the following procedure.
(I) Calculate the excess returns of Microsoft, which Is the difference between the Microsoft stock returns and three-month treasury bill rates (DASFf returns - Interest rates). You organize these differences as In the column G in the above table. Don't print the data.
(Ii) Calculate the excess returns of S&P 500, which is the difference between the S&P 500 returns and three month treasury bill rate (S&P 500 returns - interest rates). Organize these differences as in the column H in the above tat*. Don't print the data.
(o) Use the excess returns of Microsoft (MUT returns - treasure bill rate) as the dependent variable and the excess returns of S&P 500 (S&P 500 returns-treasure bill rate) as the Independent variable to get Excel regression results. Print the regression results.
h. Write the regression model for the regression In part g and write the estimated equation. I. Explain the estimated coefficients for the intercept and the slope from part g. You may check the discussion of the CAPM for their meaning.
I. Test if the intercept in part g is different from zero with the 5% significance level.
k. Test if the slope In part g is greater than one with the 5% significance level. NOte that the test statistic Is t = ta:). and you cannot use the p-value from Excel printout for the test.
2. The data file HEIM contains considerable information on randomly selected Individuals who participated In an extended interview and medical examination. Consider regression models that relate a person's physical characteristics and quality of diet consumed as measured by the Healthy Eating Index (HEI2005). The higher value of HEI 2005 indicates a higher quality of diet. The dependent variable Is HE12005 and the independent variables Include the ratio of waist measure to obese waist measure (waistper), age (age), the body mass Index (8M1), number of hours In front of computer or TV screen (green hciinfrl, and level of activities with 1=sedentary, 2=active, B.:very active (acthritv level).
a. Use Descriptive statistics In Data Analysis to get the printout of the descriptive statistics of six variables.
b. Use Correlation In Data Analysis to get the printout of the correlation matrix with six variables.
c. Based on the printout from part b, identify three pairs of two variables with the highest absolute values of correlation among all pairs.
d. Use HEI2005 as the dependent variable to get six regression results. For the first five regressions, use 11E0005 as the dependent variable and each one of the rest five variables as the independent variable to get five simple linear regression results. In the sixth regression, use HEI2005 as the dependent variable and all the rest five variables as the independent variables. Get the printouts of six regressions. Organize your six regression results as in the following table.
|
|
(1)
|
(2)
|
(3)
|
(4)
|
(5)
|
(6)
|
|
intercept
|
52.17
(0.99)
|
soccc
|
loos
|
xccc
|
=or
|
Shooc
|
|
(xxxx)
|
(>orn)
|
(=cc)
|
(=a)
|
(coot)
|
|
Waistper
|
0.919
(0.937)
|
|
|
|
|
Xs=
|
|
|
()D9C<1
|
|
Age
|
|
|
|
|
|
Shop<
|
|
|
|
hoax)
|
(xcxx)
|
|
BMI
|
|
|
>coot
|
|
|
Xxor
|
|
BcCoq
|
(%)
|
|
Screen hours
|
|
|
|
xica
|
|
X>oor
|
|
|
(moor)
|
(=or)
|
|
Activity level
|
|
|
|
|
floor
|
X>oor
|
|
|
(icoa)
|
(mac)
|
|
II-Squared
|
0.0001
|
0.roc
|
0.sca
|
0./oa
|
0.w
|
0.)ocz
|
The first column shows the regression results with HE12005 as the dependent va iable and Woisfizer as the independent variable. It includes the estimated coefficient of intercept and its standard error in the parenthesis below, the estimated coefficient of Woistper and Its standard error in the parenthesis below, and the R-squared. The second column shows the results with 14E12005 as the dependent variable and Age as the Independent variable. And so on. The results for column (1) are giving. You need to complete the rest part of the table.
You will use the regression printouts and this table to answer the following questionsi
e. For the regression results in column (3) with BMI as the independent variable, what sign do you expect for the coefficient of 8Mi? Explain why it is positive or negative. Conduct a test based on your hypothesis. Use the 5% significance level and the rejection region approach to conduct the test.