Excel assignment

CF11theditionChapter11ExcelMasterstudent.xlsx

Home >Business & Finance homework help >Accounting homework help >Excel assignment

Chapter 11

			Ross, Westerfield, Jaffe, and Jordan's Spreadsheet Master
			Corporate Finance, 11th edition
			by Brad Jordan and Joe Smolira
			Version 11.0
			Chapter 11
			In these spreadsheets, you will learn how to use the following Excel functions:
			The following conventions are used in these spreadsheets:
			1) Given data in blue
			2) Calculations in red
			NOTE: Some functions used in these spreadsheets may require that
			the "Analysis ToolPak" or "Solver Add-In" be installed in Excel.
			To install these, click on the File tab
			then "Excel Options," "Add-Ins" and select
			"Go." Check "Analysis ToolPak" and
			"Solver Add-In," then click "OK."

SQRT

/xl/drawings/drawing1.xml#'Section%2011.2'!A19

COVAR

/xl/drawings/drawing1.xml#'Section%2011.2'!A84

CORREL

/xl/drawings/drawing1.xml#'Section%2011.2'!A84

Adding a trendline

/xl/drawings/drawing1.xml#'Section%2011.8'!A34

Regression estimates

/xl/drawings/drawing1.xml#'Section%2011.8'!A43

SLOPE

/xl/drawings/drawing1.xml#'Section%2011.8'!A108

INTERCEPT

/xl/drawings/drawing1.xml#'Section%2011.8'!A108

Section 11.2

	Chapter 11 - Section 2
	Expected Return, Variance, and Covariance
In Chapter 10, we used the AVERAGE, VAR, and STDEV functions to calculate the average, variance, and standard deviation for historical returns. Unfortunately, Excel does not have built-in functions that handle unequal probabilities, so we need to create our own equations.
	Supertech
	(1) State of Economy	(2) Probability of State	(3) Return if State Occurs	(4) Product (2) × (3)	(5) Deviation from Expected Return (3) - E(R)	(6) Squared Value of Deviation	(7) Product (2) × (5)
	Depression	0.25	-0.20
	Recession	0.25	0.10
	Normal	0.25	0.30
	Boom	Joe Smolira: Notice that we used the equation 1 - C9 - C10 - C11 instead of entering a probability directly. The reason we did this is that since the probabilities must sum to one, we cannot make an error in data entry and have a higher or lower sum.	0.50
			Expected return =			Variance =
The standard deviation is the square root of the variance, so the standard deviation is:
	Standard deviation:
RWJ Excel Tip
Excel has a built-in function, SQRT, that finds the square root of a number. SQRT is found under the Math & Trig tab. The function looks like this:
We should also note that the square root (or any other power) can be calculated using the caret key (^). For example, we could have entered an equation as H13^(1/2).
And for Slowpoke:
	Slowpoke
	(1) State of Economy	(2) Probability of State	(3) Return if State Occurs	(4) Product (2) × (3)	(5) Deviation from Expected Return (3) - E(R)	(6) Squared Value of Deviation	(7) Product (2) × (5)
	Depression		0.05
	Recession		0.20
	Normal		-0.12
	Boom		0.09
			Expected return =			Variance =
	Standard deviation:
To calculate the covariance and correlation, we need to calculate the product of the return deviations, multiply this product by the probability of the state of the economy, and then sum to find the covariance. Doing so, we find:
	State of Economy	Probability of State	Deviation of Supertech Return from the Expected Return	Deviation of Slowpoke Return from the Expected Return	Product of the Deviations	Probability of State of the Economy times Product of the Deviations
	Depression
	Recession
	Normal
	Boom
					Covariance =
Since the correlation is the covariance divided by the product of the standard deviations, the correlation between Supertech and Slowpoke is:
	Correlation:
Covariance and Correlation with Historic Data
While we just discussed the calculation of covariance and correlation using unequal probabilities, both calculations are often done using historic market data. When using historic data, Excel has built-in functions that will calculate the covariance and correlation for you.
Suppose we have the following returns for the market and a stock:
	Year	Market return	Stock return
	1	18%	7%
	2	27%	25%
	3	5%	21%
	4	13%	4%
	5	-17%	-16%
	6	6%	19%
	7	-21%	-38%
	8	34%	29%
	9	19%	15%
	10	11%	16%
What is the covariance and correlation of the returns between this stock and the market?
	Covariance:
	Correlation:
RWJ Excel Tip
The functions for covariance (COVAR) and correlation (CORREL) are both located under More Functions, Statistical. Both functions use similar inputs, namely the arrays that the data is located.
To use COVAR and CORREL, select the first data array, tab to Array2, and select the second data array. It is irrelevant which data array you select first. That is, the correlation between A and B is equal to the correlation between B and A.
A Quick Statistics Review
Covariance and correlation are measures of how much two variables move together. If two variables tend to vary together (that is, when one of them is above its expected value, then the other variable tends to be above its expected value too), then the covariance and correlation between the two variables will be positive. On the other hand, when one of them is above its expected value the other variable tends to be below its expected value, then the covariance and correlation between the two variables will be negative.
The main difference between covariance and correlation is the interpretation. Covariance is an unstandardized number. A large covariance can arise because the variance of the two variables is large, or because of a strong relationship between the two variables. Thus, the only interpretation we can take from the covariance is the direction, either positive or negative.
Correlation is standardized and will be between -1 and 1. The closer the correlation is to -1, the stronger the negative relationship between the variables, and the closer the correlation is to 1, the stronger the positive relationship between the two variables. Therefore, correlation measures both the direction and magnitude of the relationship between two variables.
Correlation and Diversification
So why is correlation important to diversification? Correlation (and covariance) measure how two assets move together. All else the same, the lower the correlation between two assets, the greater the diversification benefit. If you think of two assets with a negative correlation, as one asset has a return above its average, the other asset will have a return below its average. This will smooth out the returns of a portfolio of these two assets. However, if the assets have a positive correlation, as one asset has a return above its mean, the other asset will also have a return above its mean, so there is less benefit to diversification. For an application, think of GM and Ford. Both are auto manufacturers and would be expected to have a high correlation because many of the firm specific risks that would affect GM also affect Ford. However, GM is less likely to share firm specific risk with Microsoft, so we would expect GM and Microsoft to have a lower correlation than GM and Ford, and therefore have a greater diversification benefit.

Section 11.4

	Chapter 11 - Section 4
	The Return and Risk for Portfolios
In the textbook, the equation for the standard deviation of a portfolio is presented. Given the following information concerning two stocks, what is the expected return and standard deviation of the portfolio?
		Stock A	Stock B
	Expected return	9%	14%
	Standard deviation	19%	55%
	Weight of stock	30%
	Correlation	0.10
The expected return and standard deviation of the portfolio are:
	Expected return:
	Standard deviation:
Of course, we could be interested in examining the opportunity set for the two assets. To see this, we can create a table for various portfolio weights and then graph the results. The expected return and standard deviation of the two assets for various portfolio weights is:
	Weight of Stock A	Expected Return	Standard Deviation
	0.3	0.0	0.0
	0%
	5%
	10%
	15%
	20%
	25%
	30%
	35%
	40%
	45%
	50%
	55%
	60%
	65%
	70%
	75%
	80%
	85%
	90%
	95%
	100%
So what does the opportunity set for these two assets look like? Below, you will see. To examine how a change in the correlation will affect the shape of the opportunity set, change the correlation in the cell above.
So how do we find the minimum variance portfolio? The best way is to use Solver. Try this for yourself and see if you don't agree that the weight of Stock A in the minimum variance portfolio is about 92.93%

Opportunity Set of Two Assets

Risk (Standard Deviation of Portfolio's Return)

Total Return on Portfolio

Section 11.8

	Chapter 11 - Section 8
	Market Equilibrium
In this section, you will learn how beta is estimated. Before we begin that discussion, we want to start with a graph of actual stock returns. On the next tab, you will find the month end values for the S&P 500, a common proxy for the market as a whole, and the adjusted closing price for Amazon.com stock over a 60 month period. When estimating beta, 60 monthly returns is a commonly used number of historical returns. Since we are going to be using a statistical process to estimate beta, we would like as much data as possible. However, the further back in time we go, the less the company is like the current company. For example, with AT&T, we could get stock prices for more than 100 years. But is AT&T in its current form actually comparable to AT&T in 1930? Not really. For this and other reasons, 60 monthly returns has become relatively standard when estimating beta.
To begin, we would like to graph the returns of Amazon.com stock against the returns of the S&P 500. In this case, we used a scatter plot which resulted in the graph below.
Notice that we have added a trend line in this graph. This trend line is called the characteristic line. The slope of this line represents how the stock's returns respond to the market returns. The slope of this line is the beta of the stock.
RWJ Excel Tip
To add a trend line to a chart, do the following:
	1) Click anywhere in the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs.
	2) On the Layout tab, in the Analysis group, click Trend line.
	3) You can use any of the predefined options. Note that on the chart there is an equation. We went to More Options and selected the box to display the equation on the graph. We will have more to say about this equation later.
The equation in the graph above is a linear regression. We can use the trend line option on a graph to estimate a linear regression, but Excel has a tool that will estimate a linear regression as well as give us more statistical information about the regression estimate.
RWJ Excel Tip
To estimate a linear regression, go to the Data tab, Data Analysis, and select Regression, then OK.
The input box for our linear regression looks like this:
The Y input range is the dependent variable, in this case the stock returns, and the X input range is the independent variable, or market return. We included the row above the data and selected the Labels box, which will put a label on the output for the variables. Finally, we selected the Confidence Interval box and asked for a 90 percent confidence interval. The output for this regression is below.
	SUMMARY OUTPUT
	Regression Statistics
	Multiple R	0.5086032951
	R Square	0.2586773118
	Adjusted R Square	0.2458958862
	Standard Error	0.0696275499
	Observations	60
	ANOVA
		df	SS	MS	F	Significance F
	Regression	1	0.0981163237	0.0981163237	20.2385335362	0.0000333749
	Residual	58	0.2811837509	0.0048479957
	Total	59	0.3793000746
		Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
	Intercept	0.00520084	0.00937024	0.5550380779	0.5810029938	-0.0135557333	0.0239574134	-0.0135557333	0.0239574134
	X Variable 1	1.0856321021	0.241319911	4.4987257681	0.0000333749	0.6025778169	1.5686863873	0.6025778169	1.5686863873
More Regression
If you are just interested in the slope and intercept for a regression, Excel has functions that will calculate these values separately, SLOPE and INTERCEPT.
	Beta (slope):
	Intercept:
RWJ Excel Tip
Both the SLOPE and INTERCEPT functions are located under More Functions, Statistical. The inputs for each function are the Y values and the X values. Below, you will see the inputs we used for our results.

Performance of Amazon.com Stock and the S&P 500: Single Index Model

S&P 500 Return

Amazon.com Return

Return Data

Return Data
Date	S&P 500	Amazon.com
12/31/09	1115.10	$ 134.52	S&P 500	Amazon
1/4/10	1073.87	$ 125.41
2/1/10	1104.49	$ 118.40
3/1/10	1169.43	$ 135.77
4/1/10	1186.69	$ 137.10
5/3/10	1089.41	$ 125.46
6/1/10	1030.71	$ 109.26
7/1/10	1101.60	$ 117.89
8/2/10	1049.33	$ 124.83
9/1/10	1141.20	$ 157.06
10/1/10	1183.26	$ 165.23
11/1/10	1180.55	$ 175.40
12/1/10	1257.64	$ 180.00
1/3/11	1286.12	$ 169.64
2/1/11	1327.22	$ 173.29
3/1/11	1325.83	$ 180.13
4/1/11	1363.61	$ 195.81
5/2/11	1345.20	$ 196.69
6/1/11	1320.64	$ 204.49
7/1/11	1292.28	$ 222.52
8/1/11	1218.89	$ 215.23
9/1/11	1131.42	$ 216.23
10/3/11	1253.30	$ 213.51
11/1/11	1246.96	$ 192.29
12/1/11	1257.60	$ 173.10
1/3/12	1312.41	$ 194.44
2/1/12	1365.68	$ 179.69
3/1/12	1408.47	$ 202.51
4/2/12	1397.91	$ 231.90
5/1/12	1310.33	$ 212.91
6/1/12	1362.16	$ 228.35
7/2/12	1379.32	$ 233.30
8/1/12	1406.58	$ 248.27
9/4/12	1440.67	$ 254.32
10/1/12	1412.16	$ 232.89
11/1/12	1416.18	$ 252.05
12/3/12	1426.19	$ 250.87
1/2/13	1498.11	$ 265.50
2/1/13	1514.68	$ 264.27
3/1/13	1569.19	$ 266.49
4/1/13	1597.57	$ 253.81
5/1/13	1630.74	$ 269.20
6/3/13	1606.28	$ 277.69
7/1/13	1685.73	$ 301.22
8/1/13	1632.97	$ 280.98
9/3/13	1681.55	$ 312.64
10/1/13	1756.54	$ 364.03
11/1/13	1805.81	$ 393.62
12/2/13	1848.36	$ 398.79
1/2/14	1782.59	$ 358.69
2/3/14	1859.45	$ 362.10
3/3/14	1872.34	$ 336.37
4/1/14	1883.95	$ 304.13
5/1/14	1923.57	$ 312.55
6/2/14	1960.23	$ 324.78
7/1/14	1930.67	$ 312.99
8/1/14	2003.37	$ 339.04
9/2/14	1972.29	$ 322.44
10/1/14	2018.05	$ 305.46
11/3/14	2067.56	$ 338.64
12/1/14	2058.90	$ 310.35

Master it!

	Chapter 11 - Master it!
	The CAPM is one of the most tested models in Finance. When beta is estimated in practice, a variation of CAPM called the market model is often used. To derive the market model, we start with the CAPM:
	E(Ri) = Rf + b[E(RM) - Rf]
	Since CAPM is an equation, we can subtract the risk-free rate from both sides, which gives us:
	E(Ri) - Rf = b[E(RM) - Rf]
	This equation is deterministic, that is, exact. In a regression, we realize that there is some indeterminate error. We need to formally recognize this in the equation by adding epsilon, which represents this error:
	E(Ri) - Rf = b[E(RM) - Rf] + e
	Finally, think of the above equation in a regression. Since there is no intercept in the equation, the intercept is zero. However, when we estimate the regression equation, we can add an intercept term, which we will call alpha:
	E(Ri) - Rf = ai + b[E(RM) - Rf] + e
	This equation, known as the market model, is generally the model used for estimating beta. The intercept term is known as Jensen's alpha and represents the excess return. If CAPM holds exactly, this intercept should be zero. If you think of alpha in terms of the SML, if the alpha is positive, the stock plots above the SML and if alpha is negative, the stock plots below the SML.
a.	You want to estimate the market model for an individual stock and a mutual fund. First, go to finance.yahoo.com and download the adjusted prices for the last 61 months for an individual stock and a mutual fund, and the S&P 500. Next, go to the St. Louis Federal Reserve website at www.stlouisfed.org. You should find the FRED® database on this website. Look for the 1-Month Treasury Constant Maturity Rate and download this data. This will be the proxy for the risk-free rate. When using this rate, you should be aware that this interest rate is the annual interest rate, while we are using monthly stock returns, so you will need to adjust the 1-month T-bill rate. For the stock and mutual fund you select, estimate the beta and alpha of the stock using the market model. When you estimate the regression model, find the box that says Residuals and check this box when you do each regression. Because you are saving the residuals, you may want to save the regression output in a new worksheet.
	1) Are the alpha and beta for each regression statistically different from zero?
	2) How do you interpret the alpha and beta for the stock and the mutual fund?
	3) Which of the two regression estimates has the highest R squared? Is this what you would have expected? Why?
b.	In part a, you asked Excel to return the residuals of the regression, which is the epsilon in the regression equation. If you remember back to statistics, the residuals are the linear distance from each observation to the regression line. In this context, the residuals are the part of each monthly return that is not explained by the market model estimate. The residuals can be used to calculate the appraisal ratio, which is the alpha divided by the standard deviation of the residuals.
	1) What do you think the appraisal ratio is intended to measure?
	2) Calculate the appraisal ratio for the stock and the mutual fund. Which has a better appraisal ratio?
	3) Often, the appraisal ratio is used to evaluate the performance of mutual fund managers. Why do you think the appraisal ratio is used more often for mutual funds, which are portfolios, than for individual stocks?

Solution

	Master it! Solution
a.	Month/Year	S&P 500	Stock price	Mutual fund price	Risk-free rate	S&P 500 return	Stock return	Mutual fund return	Market risk premium	Stock risk premium	Mutual fund risk premium
			IBM	FMAGX
	12/31/09	1115.10	$ 116.28	$ 49.44
	1/4/10	1073.87	$ 108.72	$ 47.28	0.0017	-0.03697	-0.06501	-0.04369	-0.03864	-0.06668	-0.04535
	2/1/10	1104.49	$ 113.46	$ 48.70	0.0050	0.02851	0.04362	0.03008	0.02351	0.03862	0.02508
	3/1/10	1169.43	$ 114.44	$ 51.93	0.0100	0.05880	0.00857	0.06629	0.04880	-0.00143	0.05629
	4/1/10	1186.69	$ 115.11	$ 52.71	0.0125	0.01476	0.00585	0.01502	0.00226	-0.00665	0.00252
	5/3/10	1089.41	$ 112.34	$ 48.44	0.0125	-0.08198	-0.02402	-0.08100	-0.09448	-0.03652	-0.09350
	6/1/10	1030.71	$ 110.75	$ 45.30	0.0067	-0.05388	-0.01421	-0.06470	-0.06055	-0.02088	-0.07136
	7/1/10	1101.60	$ 115.16	$ 47.60	0.0133	0.06878	0.03984	0.05065	0.05544	0.02651	0.03731
	8/2/10	1049.33	$ 110.98	$ 44.78	0.0125	-0.04745	-0.03629	-0.05920	-0.05995	-0.04879	-0.07170
	9/1/10	1141.20	$ 120.90	$ 49.63	0.0100	0.08755	0.08942	0.10832	0.07755	0.07942	0.09832
	10/1/10	1183.26	$ 129.43	$ 51.42	0.0117	0.03686	0.07052	0.03615	0.02519	0.05886	0.02448
	11/1/10	1180.55	$ 128.07	$ 52.12	0.0108	-0.00229	-0.01052	0.01362	-0.01312	-0.02136	0.00279
	12/1/10	1257.64	$ 132.86	$ 55.60	0.0075	0.06530	0.03747	0.06662	0.05780	0.02997	0.05912
	1/3/11	1286.12	$ 146.66	$ 56.50	0.0117	0.02265	0.10384	0.01619	0.01098	0.09218	0.00452
	2/1/11	1327.22	$ 147.13	$ 58.85	0.0092	0.03196	0.00322	0.04160	0.02279	-0.00595	0.03244
	3/1/11	1325.83	$ 148.21	$ 58.61	0.0050	-0.00105	0.00735	-0.00395	-0.00605	0.00235	-0.00895
	4/1/11	1363.61	$ 155.04	$ 60.09	0.0025	0.02850	0.04605	0.02515	0.02600	0.04355	0.02265
	5/2/11	1345.20	$ 154.23	$ 58.75	0.0017	-0.01350	-0.00524	-0.02224	-0.01517	-0.00691	-0.02391
	6/1/11	1320.64	$ 156.62	$ 56.63	0.0017	-0.01826	0.01551	-0.03610	-0.01992	0.01384	-0.03776
	7/1/11	1292.28	$ 166.02	$ 55.82	0.0033	-0.02147	0.06004	-0.01440	-0.02481	0.05671	-0.01774
	8/1/11	1218.89	$ 157.63	$ 51.51	0.0017	-0.05679	-0.05054	-0.07711	-0.05846	-0.05221	-0.07877
	9/1/11	1131.42	$ 160.35	$ 45.51	0.0008	-0.07176	0.01722	-0.11657	-0.07260	0.01638	-0.11741
	10/3/11	1253.30	$ 169.29	$ 50.45	0.0008	0.10772	0.05581	0.10857	0.10689	0.05498	0.10774
	11/1/11	1246.96	$ 173.08	$ 49.31	0.0008	-0.00506	0.02235	-0.02264	-0.00589	0.02151	-0.02347
	12/1/11	1257.60	$ 169.28	$ 49.17	0.0000	0.00853	-0.02191	-0.00266	0.00853	-0.02191	-0.00266
	1/3/12	1312.41	$ 177.31	$ 52.13	0.0017	0.04358	0.04742	0.06002	0.04192	0.04576	0.05835
	2/1/12	1365.68	$ 181.82	$ 55.24	0.0050	0.04059	0.02542	0.05977	0.03559	0.02042	0.05477
	3/1/12	1408.47	$ 192.84	$ 57.22	0.0050	0.03133	0.06059	0.03576	0.02633	0.05559	0.03076
	4/2/12	1397.91	$ 191.39	$ 56.54	0.0058	-0.00750	-0.00752	-0.01187	-0.01333	-0.01336	-0.01771
	5/1/12	1310.33	$ 179.03	$ 52.48	0.0058	-0.06265	-0.06457	-0.07176	-0.06848	-0.07041	-0.07759
	6/1/12	1362.16	$ 181.51	$ 54.25	0.0042	0.03955	0.01389	0.03369	0.03539	0.00973	0.02952
	7/2/12	1379.32	$ 181.89	$ 54.88	0.0058	0.01260	0.00205	0.01168	0.00676	-0.00379	0.00585
	8/1/12	1406.58	$ 181.61	$ 56.71	0.0075	0.01976	-0.00152	0.03336	0.01226	-0.00902	0.02586
	9/4/12	1440.67	$ 193.35	$ 58.54	0.0067	0.02424	0.06467	0.03214	0.01757	0.05800	0.02548
	10/1/12	1412.16	$ 181.31	$ 57.03	0.0092	-0.01979	-0.06228	-0.02580	-0.02896	-0.07145	-0.03496
	11/1/12	1416.18	$ 177.93	$ 57.57	0.0100	0.00285	-0.01865	0.00960	-0.00715	-0.02865	-0.00040
	12/3/12	1426.19	$ 179.31	$ 58.02	0.0033	0.00707	0.00779	0.00775	0.00373	0.00445	0.00442
	1/2/13	1498.11	$ 190.10	$ 60.97	0.0042	0.05043	0.06014	0.05077	0.04626	0.05597	0.04660
	2/1/13	1514.68	$ 188.79	$ 61.38	0.0067	0.01106	-0.00687	0.00688	0.00439	-0.01353	0.00022
	3/1/13	1569.19	$ 200.52	$ 63.31	0.0067	0.03599	0.06209	0.03135	0.02932	0.05543	0.02468
	4/1/13	1597.57	$ 190.40	$ 63.94	0.0042	0.01809	-0.05045	0.01001	0.01392	-0.05461	0.00584
	5/1/13	1630.74	$ 196.47	$ 66.23	0.0017	0.02076	0.03187	0.03583	0.01910	0.03020	0.03417
	6/3/13	1606.28	$ 180.50	$ 65.12	0.0025	-0.01500	-0.08129	-0.01682	-0.01750	-0.08379	-0.01932
	7/1/13	1685.73	$ 184.21	$ 69.43	0.0017	0.04946	0.02056	0.06622	0.04780	0.01890	0.06455
	8/1/13	1632.97	$ 173.01	$ 68.14	0.0033	-0.03130	-0.06080	-0.01856	-0.03463	-0.06414	-0.02190
	9/3/13	1681.55	$ 175.77	$ 71.02	0.0017	0.02975	0.01597	0.04215	0.02808	0.01430	0.04048
	10/1/13	1756.54	$ 170.10	$ 74.04	0.0092	0.04460	-0.03224	0.04257	0.03543	-0.04141	0.03341
	11/1/13	1805.81	$ 171.47	$ 76.62	0.0042	0.02805	0.00801	0.03492	0.02388	0.00384	0.03076
	12/2/13	1848.36	$ 179.00	$ 78.55	0.0017	0.02356	0.04391	0.02511	0.02190	0.04224	0.02345
	1/2/14	1782.59	$ 168.60	$ 77.07	0.0017	-0.03558	-0.05806	-0.01884	-0.03725	-0.05973	-0.02050
	2/3/14	1859.45	$ 177.67	$ 81.07	0.0042	0.04312	0.05380	0.05197	0.03895	0.04963	0.04780
	3/3/14	1872.34	$ 184.70	$ 80.15	0.0042	0.00693	0.03953	-0.01143	0.00277	0.03536	-0.01560
	4/1/14	1883.95	$ 188.52	$ 79.23	0.0017	0.00620	0.02068	-0.01146	0.00453	0.01901	-0.01313
	5/1/14	1923.57	$ 177.93	$ 81.67	0.0025	0.02103	-0.05617	0.03082	0.01853	-0.05867	0.02832
	6/2/14	1960.23	$ 174.95	$ 83.81	0.0017	0.01906	-0.01676	0.02619	0.01739	-0.01843	0.02452
	7/1/14	1930.67	$ 184.98	$ 82.56	0.0017	-0.01508	0.05737	-0.01492	-0.01675	0.05571	-0.01659
	8/1/14	2003.37	$ 186.69	$ 86.56	0.0025	0.03766	0.00922	0.04841	0.03516	0.00672	0.04591
	9/2/14	1972.29	$ 184.29	$ 85.17	0.0008	-0.01551	-0.01284	-0.01602	-0.01635	-0.01368	-0.01685
	10/1/14	2018.05	$ 159.60	$ 87.69	0.0017	0.02320	-0.13396	0.02958	0.02153	-0.13563	0.02791
	11/3/14	2067.56	$ 158.51	$ 89.69	0.0033	0.02453	-0.00681	0.02284	0.02120	-0.01015	0.01951
	12/1/14	2058.90	$ 156.82	$ 89.62	0.0025	-0.00419	-0.01067	-0.00082	-0.00669	-0.01317	-0.00332
	b. The appraisal ratio for each regression estimate is:
	IBM:
	FMAGX:

Mutual fund

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9693688203
R Square	0.9396759097
Adjusted R Square	0.9386358392
Standard Error	0.0108307222
Observations	60
ANOVA
	df	SS	MS	F	Significance F
Regression	1	0.1059815192	0.1059815192	903.4732640205	4.63056867694684E-37
Residual	58	0.0068036635	0.0001173045
Total	59	0.1127851827
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-0.0008423156	0.0014162871	-0.594735072	0.5543337427	-0.0036773222	0.001992691	-0.0036773222	0.001992691
X Variable 1	1.1330620037	0.0376960655	30.0578319914	4.63056867694678E-37	1.0576051303	1.2085188771	1.0576051303	1.2085188771

IBM

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.5002094829
R Square	0.2502095268
Adjusted R Square	0.2372821049
Standard Error	0.0396978373
Observations	60
ANOVA
	df	SS	MS	F	Significance F
Regression	1	0.0305018101	0.0305018101	19.3549439145	0.0000470885
Residual	58	0.0914032608	0.0015759183
Total	59	0.1219050709
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-0.0025798719	0.0051911158	-0.4969782986	0.6210830052	-0.0129710193	0.0078112754	-0.0129710193	0.0078112754
X Variable 1	0.6078569859	0.1381673581	4.3994254073	0.0000470885	0.3312849693	0.8844290024	0.3312849693	0.8844290024