+61480015851
ECON1095: Price Index Mkt - Data Analysis/Descriptive Statistics - Finance Assessment Answer
Download Solution
Order New Solution
Subject Code: ECON1095
Internal Code: 1AIADE
That is, conduct the following test, 
Would she invest further in the Australian share market using this rule?
(c) Test to see whether the daily returns on MKT 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 Excel’s Data Analysis/Descriptive Statistics. 2 Using the results of this test, comment on the accuracy of the probabilities you calculated in parts (a) and (b) of this question.
QUESTION 2
(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 = 
. Do this in excel using the matrix formulas 
for the coefficients and 
for the standard errors. Check your answers using excels regression function.
(b) With the utility function estimated in part (a) and the budget constraint: 
budget is $2,000 and the price of X 1 is $6.5 and the price of X 2 is $4.5), use the Lagrangian function to find
2 the optimal values of X 1 and X 2 . That is, find the values of X 1 and X 2 that maximise this individual’s utility subject to the budget constraint 3 . What is the value of ? and what is its interpretation?
QUESTION 3 (please use EXCEL for this question).
Although the conclusion from QUESTION 1 may have been to not put additional funds into the Australian share market, with the lower value of the Australian dollar the investor is ‘bullish’ about the 32 Australian listed Basic Materials shares. Therefore, she decides that a portfolio of these types of shares could present profit opportunities in the future. Using data on the Unadjusted Share Prices and the same sample period as earlier, follow the instructions below to construct an efficient frontier for the proportions of your funds that need to be allocated to the different Australian Basic Materials shares. There are additional instructions in Mathematical Programming notes.
Calculate the average continuous daily returns, then convert to average yearly returns by multiplying each by your sample period (n). 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 daily 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 =n*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 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 worksheet 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.
Finance Assessment Answer
TASK: QUESTION 1 An investor is considering putting additional funds into the Australian share market; to assist with this decision she analyses the continuous daily returns for S&P/ASX 200 - PRICE INDEX (MKT) from 19 February 2018 to 19 February 2019 using Excel’s Data Analysis/Descriptive Statistics 1. This data is available in ECON1095 Data Sem 1 2019.xlsx. (a) Calculate the 95 % confidence interval for the daily returns for MKT over this period.ECON1095 (b) The investor decides that she will only put extra money into the Australian share market if she can rule out negative returns. The average daily returns for the sample period should be above zero. Therefore, the question is, ‘have the daily returns for MKT been far enough above zero for the investor to be confident that they will not go below this level?’ Test to see whether the daily returns on MKT are less than or equal to zero using a level of significance ofThat is, conduct the following test, Would she invest further in the Australian share market using this rule?
(c) Test to see whether the daily returns on MKT 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 Excel’s Data Analysis/Descriptive Statistics. 2 Using the results of this test, comment on the accuracy of the probabilities you calculated in parts (a) and (b) of this question.
QUESTION 2
(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 = . Do this in excel using the matrix formulas for the coefficients and for the standard errors. Check your answers using excels regression function.
(b) With the utility function estimated in part (a) and the budget constraint: budget is $2,000 and the price of X 1 is $6.5 and the price of X 2 is $4.5), use the Lagrangian function to find
2 the optimal values of X 1 and X 2 . That is, find the values of X 1 and X 2 that maximise this individual’s utility subject to the budget constraint 3 . What is the value of ? and what is its interpretation?
QUESTION 3 (please use EXCEL for this question).
Although the conclusion from QUESTION 1 may have been to not put additional funds into the Australian share market, with the lower value of the Australian dollar the investor is ‘bullish’ about the 32 Australian listed Basic Materials shares. Therefore, she decides that a portfolio of these types of shares could present profit opportunities in the future. Using data on the Unadjusted Share Prices and the same sample period as earlier, follow the instructions below to construct an efficient frontier for the proportions of your funds that need to be allocated to the different Australian Basic Materials shares. There are additional instructions in Mathematical Programming notes.
Calculate the average continuous daily returns, then convert to average yearly returns by multiplying each by your sample period (n). 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 daily 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 =n*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 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 worksheet 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.
This Finance Assessment has been solved by our Finance experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.