QUESTION 1

Although recent events in Hong Kong have created uncertainty, often such things can also provide opportunities for investors. As a first step, an investor considering the Hong Kong share market, examines the long term continuous returns for the Hang Seng. Use Excel's Data Analysis/Descriptive Statistics to find the Hang Seng monthly returns from start of 1991 until August or September 2020[1].

(a)Calculate the 95% confidence interval for the monthly returns for the Hang Seng over this period.

(b)As the investor is concerned about the possibility of negative returns, test to see whether the average monthly returns for the Hang Seng are less than or equal to zero.[2] Making reference to this hypothesis test, briefly discuss whether the investor should be worried about the possibility of negative returns if they invest in the Hong Kong share market. Discuss type 1 and type 2 error and how they may relate to this hypothesis test.

(c)Test to see whether the monthly returns on the Hang Seng over this period are normally distributed using the Jarque-Bera test. This should be done in Excel, by calculating the Jarque-Bera statistic using the formulas from the notes and Data Analysis/Descriptive Statistics.^[3] Using the results of this test, comment on the accuracy of the probabilities calculated in parts (a) and (b) of this question.

(d)The Hang Seng monthly returns over the sample period are negatively skewed. Explain why this is the case and the implications for investment decisions.

(e)Using the continuous returns on the Hang Seng, determine whether the rises and falls are independent using a runs test. What are the implications of your findings?

QUESTION 3

Use regression to look at the relationship between returns on the Hang Seng and US over the same period used in the previous questions. That is, estimate Hang Seng_t = b₀ + b₁US_t. This is to be done using the three approaches in the regression example on Canvas (sample regression example.xlsx).All three approaches should yield the same answer. That is:

(a)Use the solver to find the values of b₀ and b₁ that minimize e_i².

(b)Ensure that the values of b₀ and b₁ satisfy the normal equations.

(c)Use Excel's regression function to confirm the results in parts (a) and (b).^[5]

(d)Next, evaluate these results by:

Interpreting the coefficient of determination and the F-statistic.

Conduct a t-test on the slope coefficient.

Examine and graph the residuals, then comment.

Briefly explain the implications of these findings.

QUESTION 4

It is possible that returns on other share markets could also be important drivers of the Hang Seng - to investigate:

(a)Use excel and the monthly continuous returns data on all the international share price indices for the same period to obtain the correlation coefficient matrix (10 x 10). Briefly comment on any interesting results.

(b)Use Excel's regression function to estimate the multiple regression with the returns on the Hang Seng as the dependent variable and the returns on the other nine international share price indices as independent variables.

(c)Evaluate these results by:

Interpreting the coefficient of determination and the F-statistic.

Use the t-statistics and the slope coefficients to explain which share price indices are significantly related to the Hang Seng.

Briefly explain the implications of these findings.

QUESTION 5^[6]

(a)Suppose a hypothetical individual's Utility (U) can be explained through their consumption of two goods (X1 & X2) such that:

Use linear regression to estimate this individual's utility function which is represented by U = b₀X₁^b1X₂^b2. calculate this in excel using the matrix formulas =(XX)^-1Xy for the coefficients and V() = ²(XX)^-1 for the standard errors.Check your answers using excels regression function.

(b)With the utility function estimated in part (a) and the budget constraint: 1000 = 6X₁ + 4X₂ (i.e. total budget is $1,000 and the price of X₁is $6 and the price of X₂is $4), use the Lagrangian function (showing each step) to find the optimal values of X₁and X₂. That is, find the values of X₁and X₂that maximise this individual's utility subject to the budget constraint^[7]. What is the value of l and what is its interpretation?

Check your answer using the solver in excel.To find this open a new spreadsheet and insert names for X₁and X₂in cells B2 and C2 and the starting values for these variables of 1 in both cells B3 and C3 and name these cells Xone and Xtwo. In cell B6 type the formula for the utility function, = b₀(Xone^b₁)*(Xtwo^b₂) and name this UU.In cell B9 type the formula for the budget, =6*Xone+4*Xtwo and name this BB.Next, go to the solver and set the target cell UU equal to maximum by changing Xone and Xtwo. Then add the constraint that BB = 1000 and solve. When given the solver results ask for the sensitivity report as this gives l.

QUESTION 6

Rather than just focusing on Hong Kong the investor decides to consider a broad portfolio of investments across a number international share markets. To inform these investment decisions they want to look at the long-term relationship between risk and return across these markets. Using excel and the instructions presented below construct the efficient frontier for the proportions of funds that need to be allocated to the ten share markets (there are additional instructions in the Mathematical Programming notes).

Using the file ECON1095 Data Sem 2 2020.xls (including the up-dates) and the same sample used in the previous questions as a guide, the investor would like to allocate their funds across these share markets in an optimal way by finding the various efficient portfolios. This requires funds to be allocated so that the expected risk is minimised for a given level of expected return. A graph of the possible optimal values of returns and risk is called the efficient frontier.

Calculate the average continuous monthly returns, then convert to yearly returns by multiplying each by twelve. Transpose this block of cells and name the average returns Ret. Use the covariance command from excel's Data Analysis Tools to find the variance-covariance matrix for the monthly returns. This matrix is symmetrical, so the missing elements can be easily filled in. Name this matrix Mvac. Convert Mvac into the variance-covariance matrix for yearly returns by highlighting the cells and entering =12*Mvac [Ctrl]+[Shft]+[Enter]. Name this block Vac. Enter the initial guesses for the optimal weights for the shares and name this block of cells Wts. Transpose these weights and name Twts. Find the expected return for the portfolio using =MMULT (Wts, Ret) [Ctrl]+[Shft]+[Enter]. Call this cell Pret. Finding the variance of portfolio returns requires three stages. First, highlight the appropriate cells and enter =MMULT (Wts,Vac) [Ctrl]+[Shft]+[Enter]. Name this block Tvac. Second, highlight a single cell and enter =MMULT (Tvac, Twts) [Ctrl]+[Shft]+[Enter]. Call this cell Pvar. Next, find the portfolio risk, or square root of the portfolio variance and name this cell Prsk. To ensure that the portfolio weights sum to one, enter ten 1's and name Unit. To find the expression for the sum of weights entering =MMULT (Unit, Twts) [Ctrl]+[Shft]+[Enter]. Name this cell Wtcn.

Using this work sheet and the excel's Solver find the minimum risk for the portfolio allocations for the various expected returns (I suggest performing the exercise for about ten different expected returns, chosen to ensure a solution can be found). In each case constrain the weights so they are non-negative and sum to one. Use these values to graph the Efficient Frontier with risk on the horizontal axis and returns on the vertical axis.

please give a brief report explaining how the portfolio changes for different expected returns.

QUESTION 7

Now the investor would like to know how various major international events affected these different share markets in the past - as this may provide a guide as to what could happen in the future. Given the size of the American economy the returns on US shares may be considered equivalent to the market returns, therefore can be used to estimate the market model for the other share markets.

(a)Estimate the the systematic risk or betas for each of international share markets with respect to the US market. find it using excel's regression function to estimate the market model: R_it = b₀ + b₁R_USt , where i refers to the other nine share markets. Interpret the coefficient of determination, the F-statistic and the size and significance of the slope coefficients.

(b)Conduct t-tests against unity for the betas to determine if these share markets would be considered 'passive', 'aggressive' or 'neutral' relative to the US.

(d)Construct event dummy (or binary) variables to represent three international events that may have affected these markets:

(i)The Asian financial crisis - one from Aug 1997 to Jul 1998, zero otherwise.

(ii)The Global Financial Crisis (GFC) - one from Feb 2007 to Feb 2009, zero otherwise.

(iii)Covid-19- one from Jan 2020 to Mar 2020, zero otherwise.

Re-estimate each of your market models for the international share markets with the addition of these event dummy variables as both intercept and slope coefficients. Evaluate these new models using the significance or otherwise of these dummy variables to identify how the international events affected the share markets. Briefly discuss your results.

QUESTION 8

(a)Select one of the international events listed in part (d) of QUESTION 7 that you think might yield interesting results. Construct a state dummy variable then re-estimate Hong Kong's market model including this new dummy variable as well as the original event dummy - included both for the intercept and slope coefficients. Interpret your results.

(b)Repeat part (a) of this question but do so for another country's market model with either the same or a different international event.

(c)Drawing on any of your findings in this assignment, briefly (less than one page) make some comments about the risk and return trade-off across these international share markets, and how this may have been differentially affected by the various international events that you have analysed.

Foot Notes

[1] Use the same time-period for all questions that use this data set in the assignment.

[2] Use a level of significance of a = 0.05 for this and all other tests in this assignment.

[3] Note that when Excel calculates kurtosis it subtracts 3 from the value.

[4] The IF function in EXCEL is very useful when completing this table.

[5] Refer to the course notes: Mathematical Programming, Simple and Multiple Regression and Finance Applications for additional help.

[6] This question does not relate to the international share price indices, it is testing technical skills.

^[7] Please imply it algebraically, although there is no need to check the second order conditions. Also, it is fine to work with rounded numbers. Use your own judgement when selecting the number of decimal places presented

Please Send email in the comment section for the excel Data File