Question

Managerial Report

Morningstar tracks the performance of a large number of companies and publishes an evaluation of each. Along with a variety of financial data, Morningstar includes a Fair Value estimate for the price that should be paid for a share of the company’s common stock.

Data for 30 companies are available in the file named MeasuringStckRiskTri1. The data include the Fair Value estimate per share of common stock, the most recent price per share, and the earning per share for the company (Morningstar Stocks 500, 2008).

You have been assigned to analyze the risk characteristics of these stocks. Prepare a report that includes but is not limited to the following items.

Part 1

1. Compute descriptive statistics for data set provided
2. Develop a scatter diagram for the Fair Value and Share Price data with Share Price on the horizontal axis.
3. What is the sample correlation coefficient, and what can you say about the relationship between the variables?
4. Develop the estimated regression equation that could be used to estimate the share price given the fair value.
5. At the .05 level of significance, is there a significant relationship between the two variables?
6. Use the estimated regression equation to predict the share price for a company that has a fair value of $50.
7. Do you believe the estimated regression equation would provide a good prediction of the share price?

Part 2

1. Develop a scatter diagram for the Fair Value and Earnings per Share data with Earnings per Share on the horizontal axis.
2. What is the sample correlation coefficient, and what can you say about the relationship between the variables?
3. Develop the estimated regression equation that could be used to estimate the share price given the earnings per share value.
4. At the .05 level of significance, is there a significant relationship between the two variables?
5. Use the estimated regression equation to predict the share price for a company that has a earning per share of $50.
6. Do you believe the estimated regression equation would provide a good prediction of the share price?

attached Data for analysis

Company	Fair Value ($)	Share Price ($)	Earnings per Share ($)
Air Products and Chemicals	80	98.63	4.64
Allied Waste Industries	17	11.02	0.35
America Mobile	83	61.39	2.22
AT&T	35	41.56	1.92
Bank of America	70	41.26	4.40
Barclays PLC	68	40.37	5.58
Blackrock	168	216.80	6.70
Citigroup	53	29.44	3.72
Costco Wholesale Corp.	75	69.76	2.45
Covidien, Ltd.	58	44.29	-0.69
Darden Restaurants	52	27.71	1.48
Dun & Bradstreet	87	88.63	4.70
Equifax	42	36.36	2.03
Gannett Co.	38	39.00	4.96
Genuine Parts	48	46.30	2.93
Qualcomm	48	39.35	1.95
GlaxoSmithKline PLC	57	50.39	3.78
Iron Mountain	33	37.02	0.80
ITT Corporation	83	66.04	3.92
Johnson & Johnson	80	66.70	3.55
Las Vegas Sands	98	103.05	0.54
Macrovision	23	18.33	0.73
Marriott International	39	34.18	1.81
MasterCard	184	215.20	6.03
Nalco Holding Company	29	24.18	0.91
National Interstate	25	33.10	2.26
Portugal Telecom	15	13.02	0.96
Royal Dutch Shell Ltd.	87	84.20	7.90
SanDisk	60	33.17	0.29
Time Warner	42	27.60	1.09

Answer 1

Part 1

Descriptive statistics

The descriptive statistic is obtained in excel by following these steps,

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2:DATA > Data Analysis > Descriptive Statistic > OK. The screenshot is shown below,

Step 3: Select Input Range: All the column, tick on the Summary statistic. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

Scatter diagram

Fair Value and Share Price

The scatterplot is obtained in excel in following steps,

Step 1: Write the data values in excel.

Step 2: Select the Fair Value and Share Price column then INSERT > Recommended Charts > XY Scatter > OK. The screenshot of the chart is shown below,

Sample correlation coefficient

The sample correlation coefficient value is obtained in excel using the function =CORREL(). The screenshot is shown below,

Conclusion:

There is a strong positive linear association between Fair Value and Share Price

Regression analysis

The regression equation is defined as,

where Y = share price and, X = fair value.

Now, the regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Share Price' column, Input X Range: 'Fair value' column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

The regression equation is,

Significance of relationship

From the regression model summary,

	P-value
Fair Value ($)	3.23E-16	<	0.05	Significance

The P-value for independent variable Fair value is less than 0.05 at a 5% significance level hence we can conclude that independent variable is statistically significant in the model.

Prediction:

For X = 50

Conclusion: Yes

Because the model significantly fit the data with good R square value.

Part 2;

Scatter diagram

Follow the similar steps as in part 1. The screenshot of the plot is shown below,

Sample correlation coefficient

r = 0.6243

There is a moderate positive linear association between Fair Value and Earning per Share.

Regression Analysis between Share Price and the Earning per Share

Follow similar steps as in part 1. The screenshot is shown below,

The regression equation is,

Significance of relationship

From the regression model summary,

	P-value
Earnings per Share ($)	0.000417	<	0.05	Significant

The P-value for independent variable Earning per Share is less than 0.05 at a 5% significance level hence we can conclude that the independent variable is statistically significant in the model.

Prediction:

For X = 50

Conclusion: NO

However, the model is significant but weak R square value.