Reference no: EM131186004

QUESTION 1 - Regression using matrix algebra on excel.

Using excel, the matrix formula β^{^} =  (X'X)^-1X'y  and the data set Cobb.xls, obtain the regression results on page 26 of the Module 3 lecture notes:

Ln(Y?)= -3.338455 + 1.498767.Ln(X1) + 0.489858.Ln(X2)

Then using the matrix formula V(β^{^}) = σ²(X'X)^-1 obtain the standard errors for the coefficients. Check your answer using both the regression function in excel as well as Eviews.

QUESTION 2 - A Time Series Study

This question investigates the relationship between government revenue (REV)and expenditure (EXP) for Australia.  The data is on the sheet Economic in the file called Tutorial Data Sem 2 2016.xlsx and consists of monthly data in millions of Australian dollars. You should be able to do parts (a) and (b) after module 1, part (c) and (d) after module 3 and complete the question after module 4.

                        EXP_t=  b₀ + b₁REV_t+ u_t

(a) Using line graphs of the data decide what estimation period to use for your model.  Briefly justify your answer.

(b) Decide whether to use log or raw data using line graphs and scatter plots and give brief reasons for your answer. Estimate and then evaluate this model.

(c) Add monthly seasonal dummy variables to your model.

(d) Test your model for autocorrelation, normality and constant variance.  Carry out any modifications needed to obtain a model with well behaved errors.

(e) Choose a suitable date to carry out a Chow breakpoint test to find if the model is stable.

(f) Re-evaluate your best model from part (d) based on the results of the tests you have carried out on it. Test to see if the slope is one. Briefly describe the relationship between revenue and expenditure.  Does the Australian Government balance its budget on average?

Note that for each part of this question you should use your best previous model for tests.[a = 0.05 when testing hypotheses.]

QUESTION 3 - The CAPM

One approach to testing the validity of the CAPM is to use the two-pass method. At the first pass estimate the betas and the variance of the error terms for a number of firms. Use the firms listed on the worksheet called Shares in the data file Tutorial Data Sem 2 2016.xlswhich contains monthly price observations for the last five years for 52 listed mining stocks and the All Ordinaries price index (there are also some other economic series on this sheet that are not required for this question). At the second pass use these results to estimate the Security Market Line (SML). If the model is a good fit this is interpreted as an indication that the CAPM successfully explains the relationship between Risk and Return.

First calculate the continuous returns on all the shares and the market index. This is done using the following excel formula:

                        =LN(B7/B8)

then drag this across other cells to do this for the index and all shares. You may wish to do this in a new worksheet, or another part of the existing one.

Please note that the cell references in the instructions below are indicative only;they are for a different sample size and the exact cell references you use will depend on how you construct your own worksheets.

1. At the first pass calculate the systematic risk or betas for all firms with respect the All Ordinaries. Do this by estimating the market model

                        R_it        =  b₀  +  b₁R_Mt

for all firms noting the estimated values of the slopes. While it is usually a good idea to use EViews to perform most regression calculations in this case it is easier to use Excel. To find all the betas go to cell EC64 and enter 

                                                =LINEST(EC3:EC62,$EB3:$EB62)

Now highlight EC64 and drag it to cell IR64.

2. To find the average returns for each firm R_j , the squared beta values and the variances of the error terms for each market model use the following Excel procedures(note: again the cell referencesare indicative only).

     (i) To find all average returns go to cell EC65 and enter

                                    =AVERAGE(EC3:EC62)

Then click this cell and drag it to IR65.

(ii) To find the squares of the beta values go to cell EC66 and enter

                                    = EC64^2

Then click this cell and drag it to IR66.

(iii) To find the variances of the error terms in all Market Models use the STEYX function to find their standard deviation and then square this value. Use the STEYX function to enter the Y values and then the X values. To do this, go to cell EC67 and enter the following

                                    =STEYX(EC3:EC62,$EB3:$EB62)^2

Click this cell and drag it to IR67.

(a) Enter the four sets of values for β^{^}_j, E(R), β_j² and s² into an EViews work file. You can call these three variables BETA, ER, BETASQ and VAR.  Don't forget the data are in rows not columns.  Copy the first five values of each of these variables into your submission so your answers can be checked.

(b) Estimate the SML

                          E(R) =  γ₀ + γ₁β^_j + u_j

and evaluate your results.  From Quantitative Methods in Finance and from of Module One notes you should know what the values of the intercept and slope for the SML should be.

(c) To help determine whether factors other than systematic risk affect expected

returns the following model is estimated

                          E(R) =  γ₀ + γ₁β^_j + γ₂β_j² + γ₃σ²_j + u_j

     Using the t test identify the variables which do not have a significant impact on the expected returns.

     Using the F test determine whether the joint impact of β_j² and σ²_j is significant.

     [use α = 0.05 in the hypothesis testing problems.]

Attachment:- Data.rar