[SOLVED] ECON1202 Quantitative Analysis for Business Economics T3 2024 Prolog

ECON1202 – Quantitative Analysis for Business & Economics

ECON1202 – Excel Assignment (10%)

T3, 2024

(Due Thursday 4.00pm, Week 11 (21/11/2024))

Purpose

In this assignment, you will apply some of the quantitative methods covered in this course using Excel. Read the entire assignment carefully, and follow all instructions, including the submission instructions at the bottom of this assignment.

Background

Congratulations on your new role as a Portfolio Analyst at 1202 Capital! Your supervisor, Phuc, has assigned your first task: optimising an investment portfolio. You have already completed a preliminary task: identifying the three stocks that delivered the highest annual returns over the past year. After thorough research, you determined the top performers: Alphabet Inc. (GOOGL) with a return of 78.2%, The Toro Company (TTC) with an impressive 85.4% return, and FirstService Corporation (FSV) with a 60.8% return.

To begin, you’ll work with the “Stock Data” file on Moodle, which includes monthly returns for these companies over the past five years. This information is captured in two matrices:

•   The “ returns” matrix (r) contains the average monthly returns of the three stocks.

•   The “Variance-Covariance” matrix (V) provides the monthly variances of the three stocks as well as covariances between the  stocks’  returns.  The variance of asset returns is a measure of how much an asset’s return varies with respect to its average returns. A large variance implies higher risk (in the sense that there is more variation around the average return) while a small variance indicates lower risk.  Covariance in the context of stock market indicates how any two assets’ returns move together. A positive covariance indicates that the two assets’ returns move in the same direction whereas a negative covariance implies  that the two assets’ returns move in the opposite direction. For a portfolio of 3 assets (say, A, B and C), the variance-covariance matrix will look like this:

σA2, σB(2), σC(2) : variances of the returns of assets A, B, and C.

σA,B : covariance of the returns of assets A and B. Other covariances can be interpreted similarly.

Both the “returns” matrix and the “variance-covariance” matrix have already been filled out, so please do not modify them further.

Refer to the “Q1-Q4” tabs in the spreadsheet to answer questions 1 through 4.

Question 1 [3 marks]

Phuc has decided to allocate half of the available capital to Alphabet Inc.  (GOOGL).  The remaining capital will  be split equally  between The Toro  Company  (TTC) and  FirstService Corporation (FSV). Your task is to calculate the portfolio’s monthly expected return, using the following formula:

rp  =  wrr

w: the “weight” matrix (G17:G19). This matrix contains the portfolio’s weights for the three companies.  You will need to determine  these  weights  based  on  the  capital  distribution outlined above.

r: the “ returns” matrix (G3:G5) described earlier in the assignment.

rp : the portfolio’s monthly expected return.

Report the answer in cell G21 in the spreadsheet.

Question 2 [3 marks]

Using the weights from Question 1, you will now calculate the variance of the portfolio. To do so, apply the following formula:

varp  = wr vw

w: the “weight” matrix (as calculated in Question 1)

V: the  variance-covariance  matrix  of  the  portfolio’s  returns,  described  earlier  in  the assignment.

varp : the portfolio’s variance.

Report the answer in cell G24 in the spreadsheet.

Question 3 [3 marks]

Calculate the determinant of matrix V-1. Determine whether V is singular or non-singular.

Report the determinant of matrix V-1  in cell G31 and report V-1  in G27:I29 in the spreadsheet.

Question 4 [4 marks]

After realising that the initial weights may not be optimal, Phuc has tasked you with using Excel’s Solver to determine the optimal portfolio. The goal is to maximise the reward-to-risk ratio, also known as the Sharpe ratio, which is the ratio of excess return to risk.

In this context, excess return is defined as the difference between the portfolio’s monthly expected return and the monthly return on a risk-free investment. The risk-free rate (rf) is currently 0.3% per month, based on the 10-year Commonwealth government bond yield, according to the latest data from Bloomberg.

The portfolio risk is measured by the standard deviation, which is the square root of the portfolio’s variance. Your objective is to use  Solver to optimise the weight allocation to maximise the Sharpe ratio, effectively balancing return with the risk taken.

The optimisation problem is outlined below:

Report the optimal weights in cells G37:G39, and the Sharpe ratio (sp) in cell G45.

Note: if the weight of an asset is negative, leave it as negative. This is known as short-selling.

Question 5 [7 marks]

In addition to stocks, cryptocurrencies like Bitcoin (BTC) have also generated  impressive returns over the past year. However, due to Bitcoin’s significant volatility, Phuc is uncertain whether including BTC in the portfolio will improve the reward-to-risk ratio (i.e. the Sharpe ratio,  Sp ) from Question 4.  Navigate to the  “Q5”  tab  in the spreadsheet and redo the optimisation, this time incorporating Bitcoin alongside GOOGL, TTC, and FSV in the portfolio. Your task is to determine if the inclusion of BTC enhances the reward-to-risk ratio.

Report the optimal weights in cells H17:H20, and the Sharpe ratio (Sp ) in cell H26.

Answer the following questions in the quiz on Moodle: Compared to the portfolio with 3 stocks in Question 4:

•     Does including Bitcoin lower the overall risk of the portfolio?

•     Does including Bitcoin improve the reward-to-risk ratio of the portfolio?

Note: Please upload the completed Excel file using the link provided in Moodle. Ensure that the file includes all formulas used in matrix calculations [rp, varp, V-1, det(V)]. Failure to upload the Excel file will result in a zero score for this assignment.

Submission instructions

(1) Answer  submission: Enter  your   responses  via  the  “Excel  Assignment  –  Answer Submission” quiz in Moodle. You are allowed only one attempt to submit the quiz, so please ensure that you are confident in your answers before doing so.

(2) Excel  file  submission: Upload  your  completed   Excel  file  through  the  “Excel  file submission”   link   on    Moodle.    Name    your   file    using    the   following    format: FirstName_LastName_StudentID

(3) Penalty for incomplete submission: If you upload the Excel file without completing the quiz, a 50% penalty will be applied.

(4) Late  submission  penalty:  a  penalty  of  20%  per  day  (or  part  thereof),  including weekends, will be applied for late submissions.

The assignment is due by 4pm on Thursday of Week 11 (21/11/2024).