The purpose of the assignment is to review basic hypothesis testing and regression techniques. There is an appendix in your textbook, Appendix C: Using Excel to Conduct Analysis, which may help you with running regressions in Microsoft Excel. You may also wish to use a basic statistics text for guidance if needed. I have also provided you with a table with the t distribution.
If you have an older version of EXCEL and have not previously loaded the Analysis ToolPak, go to TOOLS, ADD-INS, Analysis Tool Pak. This will load the regression software that you will need. Then go to TOOLS, DATA ANALYSIS, Regression. Now you are ready to run regressions in EXCEL. Alternatively, if you have the most recent…show more content… 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. (10 points)
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).) (10 points)
2. Multiple Linear Regression
A Brightwater car dealership, which serves the city of Brightwater and its surrounding