1.Measuring Systematic Risk: Beta Coefficients

The management of a publicly traded firm is interested in determining the firm's cost of equity capital using the security market line (SML) version of the capital asset pricing model (CAPM). Management has measured the weekly returns for the market (S&P 500), its own stock, and the risk-free rate. The returns were annualized. The annualized percentage returns for each of the last 20 weeks are provided.

1a. See data in Excel file provided with this assignment. Using Excel, determine the excess rate of return on the firm's stock (firm return less risk-free return) and the excess rate of return on the market (market return less the risk-free return). Put the two new variables (the excess return on the firm and the excess return on the market) that you have created into separate columns.

1b. Determine the alpha and beta coefficients for this stock by running a simple linear regression. Use the file from part (1a) and regress the excess rate of return for the firm against the excess rate of return for the market.

The "excess rate of return for the firm" data is the Input Y Range (dependent variable) and the "excess rate of return for the market" is the Input X Range (In Excel, the Data Analysis menu is under Tools (older version of Excel) or Data (newer version)).

If you include the row with the variable name in your Input Y Range and your Input X Range, check the box LABELS, and Excel will automatically name your variables in the Excel output. 

Hint: the alpha coefficient estimate is the estimated intercept coefficient. The beta coefficient is the estimated coefficient for the independent or X variable, the excess rate of return for the market.

1c. Assuming that the market return for the coming year is expected to be 12 percent and that the risk-free rate is expected to be 8 percent, use market model (your regression model) to estimate the expected rate of return to the firm's shareholders for the coming year.

(Hint: You will first need to calculate the expected excess rate of return for the market. To calculate the expected excess rate of return to the firm's shareholders, you will then plug into the regression model estimated in part (1b). You will also use the coefficient estimates estimated in part (1b).)

2. Multiple Linear Regression

A Brightwater car dealership, which serves the city of Brightwater and its surrounding communities, was taken over about four years ago by a group of investors led by Jake Rogers. Jake had previously studied marketing and economics at Brightwater University. After taking over the dealership, Jake decided to apply some of the knowledge he had gained from his studies to selling cars.

After a few months of operation, he began experimenting with the price of cars and the monthly expenditure on radio advertising. He varied the price and advertising expenditure each month and kept track of the average rate of interest on automobile loans for the month.

The data on per car price in thousands (Pr), advertising in thousands (Ad), interest rate (IR), month in which the values applied (Mth), and sales in the thousands (Sales) appear in the data table. Jake would like to know the functional relationship between sales, the price, advertising expenditure, and interest rate on car loans. He is also interested in determining whether there is a trend to the firm's sales.

2a. See data is sheet 2 of Excel file with data for this assignment.

2b. Run a multiple linear regression on the data file for part (2a). (EXCEL Data, Data, Analysis, Regression). Since "Sales" is the dependent (or regressor) variable, the sales data is the Input Y Range. The other 4 variables (Month, Price, Advertising, and Interest Rate) are independent (or explanatory) variables; the explanatory variable data is the Input X Range. (Hint: Excel will permit you to include multiple columns and rows in your X range.) (10 points)

2c. From the regression results you obtain in part (2b), determine if each of the explanatory variables used in the regression is statistically significant at a 5 percent level (This means 2.5 percent in each tail of the distribution). You will need to use the t distribution table for this purpose. In your answer, make sure you state what the critical value of t is for each independent variable.   The critical value is the value of t, such that if the t statistic for your independent variable (from your Excel output) is greater than the critical t or less than (-1) times the critical t, then you reject the null hypothesis. Hint: To determine the critical t, you will need both the level of significance (2.5% in this case) and the degrees of freedom.   You will need to calculate the degrees of freedom, which is the number of observations minus the number of independent variables minus 1. Degrees of freedom = N-k-1.

2d.       Rerun the regression using only those explanatory variables which were found to be significant in part (2c). Note that some of the explanatory variable coefficient estimates are positive and some are negative.   Do the signs (positive or negative) on the explanatory variable coefficients make sense? Discuss.