QUESTION 1
(a) If you roll a dice 12 times what is the probability that you will roll two 6s? Work out both by hand and using the EXCEL function for the Binomial distribution.
(b) If you roll a dice 12 times what is the probability that you will roll more than two 6s? Work out using the EXCEL function for the Binomial distribution only (that is, don’t worry about doing this calculation by hand).
(c) If you roll a dice 12 times what is the mean and the variance of the number of 6s you will throw? Please use the Binomial formulas (Mean = n.p, Variance = n.p.(1 – p)) to work these out and then check using EXCEL and the general formulas for the mean and variance of any probability distribution:
= i P(X i) and = (Xi – )2P(Xi)
(d) If you roll a dice 12 times what is the skewness and kurtosis of the number of 6s you will throw? Please just use EXCEL to work this out.
(2 marks)
QUESTION 2
It is often thought that the Australian share market takes its lead from America. To look at this you examine the monthly returns from the Australian and US share markets from 2007 to 2017. The data to be used for this question is in the Excel file ECON1095 Data Sem 1 2018.xls on Canvas.
(a) Using all of the available data for Share prices: Australia: S&P/ASX 200 (AUS) and the Share prices: United States: United States: S&P 500 (USA), examine the relationship between these two variables. To do this, graph the two variables against each other. You may do a number of graphs; that is, both as levels and as returns and as XY graphs and as line (time series) graphs. Does it look as though the variables are dependent or independent?
(b) As a further test calculate the correlation coefficient between the AUS and USA (tools, data analysis, correlation). Again, do this both for the levels and the returns. Please interpret this coefficient and offer a brief explanation for the result.
(c) Using the same data fill in the following table for the number of rises and falls .
AUS
USA
Rise No change Fall Total
Rise
No change
Fall
Total
(d) Calculate the joint probability distribution and the marginal probability distributions for the data.
(e) Using your previous answers as a guide, make a decision regarding whether the AUS and USA are dependent or independent?
(f) Due to the potential trade war between the US and China you expect the US share returns market to fall in the near future. Calculate the conditional probability distribution for AUS given that USA has decreased. Explain how this calculation could be used to guide your Australian investment decisions.
(3 marks)
QUESTION 3
You are considering moving additional funds into the Australian share market. To assist with this decision you analysis AUS from 2007 to 2017 in more detail (from the QUESTION 2 data set). I suggest using Excel’s Tools/Data Analysis/Descriptive Statistics on the continuous returns to answer these questions.
(a) Calculate the 95 % confidence interval for the AUS returns over this period. Please interpret this confidence interval.
(b) You decide that you are only prepared to put more money into Australian shares if you can confidently rule out negative returns. Therefore, the decision is to invest only if you believe that monthly returns will not go below zero. Average monthly returns for the sample period are below zero, therefore you would clearly not invest using this rule. However, you know that the early part of the sample period includes the Global Financial Crisis (GFC) and as you do not anticipate another GFC in the immediate future you decide to start the sample in 2009 (use a sample from 2009 to 2017 to answer the rest of this question). Using this smaller sample the average monthly returns should be just above zero. Therefore the question is, have monthly returns on the AUS been far enough above zero for you to be convinced that they will not go negative in the near future?’ Test to see whether the returns are less than or equal to zero using a level of significance of Would you invest?
(c) Test to see whether the AUS has a normal distribution using the Jarque-Bera test. This must be done in Excel, by calculating the Jarque-Bera statistic using the formulas from the notes and Excel’s Tools/Data Analysis/Descriptive Statistics. Using the results of this test, comment on the accuracy of the probabilities you calculated in parts (a) and (b) of this question.
(d) The AUS monthly returns over the sample period are negatively skewed. Explain why this is the case, and then explain the implications of this for your investment decision.
(e) Using the continuous returns on AUS, determine whether the rises and falls are independent using a runs test. What are the implications of your findings?
(2.5 marks)
QUESTION 4
An individual’s utility function is represented by:
Their budget constraint is:
That is, their total budget is $10,000 and the price of X1 is $2 and the price of X2 is $5. Use the Lagrangian function to find the optimal values of X1 and X2 that maximises utility subject to the budget constraint . What is the value of and what is its interpretation?
Check your answer using EXCEL. To do this open a new spread sheet and insert names for X1 and X2 in cells B2 and C2 and the starting values for these variables of 1 in both cells B3 and C3. Use the Insert, Name, Define commands to name cells B3 and C3. In cell B6 type the formula for the utility function, = (Xone^0.9)*(Xtwo^0.1) and name this UU. In cell B9 type the formula for the budget, =2*Xone+5*Xtwo and name this BB. Next, go to the solver and set target cell UU equal to maximum by changing Xone and Xtwo. Then add the constraint that BB = 10,000, then solve. When given the solver results ask for the sensitivity report as this gives .
(2 marks)
QUESTION 5
Using the following matrices:
(a) State the dimensions of A, B and C.
(b) Find A.B
(c) Find B.C
(d) Find A.C’
(e) Using excel find C-1, then multiply C-1 by C to find I.
Do (b), (c), (d) and (e) using excel.
(2.5 marks)
QUESTION 6 (please use EXCEL for this question).
You are trying to diversify your investments across different share markets around the world and so want to construct an efficient frontier. Follow the instructions presented below to construct the efficient frontier for the proportions of your funds that need to be allocated to the various markets (there are additional instructions in the Mathematical Programming notes).
Using the file ECON1095 Data Sem 1 2018.xls and the entire sample from 2007 to 2018 as a guide, an investor would like to allocate their funds across the shares markets of USA, Japan, Euro area, UK, New Zealand, Hong Kong, Taiwan, Korea, China and Australia in an optimal way by finding the various efficient allocations. An efficient allocation of funds is one in which expected risk is minimised for a given level of expected return. A graph of the possible optimal values of the returns and risk is called the efficient frontier.
Calculate the average continuous monthly returns, then convert the average monthly returns to yearly returns by multiplying each by 12. 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 these 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 this block 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 this block Unit. To find the expression for the sum of the weights by entering =MMULT (Unit, Twts) [Ctrl]+[Shft]+[Enter]. Name this cell Wtcn.
Using this work sheet and the EXCEL Solver Tool find the minimum risk for the funds 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 you must 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.
Write a brief report explaining how your portfolio changes as you try different expected returns.
(8 marks)
QUESTION 7
The data to be used for this question is in the Excel file ECON1095 Data Sem 1 2018.xlsx on Canvas. Using all of the available data for returns on Share prices: Australia: S&P/ASX 200 (RAUSt) and the returns for Share prices: United States: S&P 500 (RUSAt); examine the relationship between these two variables via regression. That is, use regression analysis to estimate RAUSt = 0 + 1RUSAt. This should be done using the three approaches used in the example on the Canvas (sample regression example.xlsx). All three approaches should yield the same answer. That is:
(a) Use the solver to find the values of 0 and 1 that minimize ei2.
(b) Ensure that the values of 0 and 1 satisfy the normal equations.
(c) Use Excel’s regression function to confirm your results in parts (a) and (b).
(d) Next, it is often thought that the Australian economy is driven by what happens in both America (US) and China. Test this by estimating a multiple regression where returns on Australian shares are a function of returns on both the US and Chinese share (China: Shanghai A), but do this using excel and the matrix formula = (XX)-1Xy , then check your results using the regression function in excel. Interpret your results.\
