PART 1 [20 marks]

To successfully complete PART 1, please read each section carefully. I also encourage you to read the entire manual before you start the assignment. For Part I, all computations are required to be produced in excel spreadsheets.

1. Download historical prices for your company and market index into Excel.[5 marks]

Instructions! a. To learn about the dividend-paying company you have been allocated to, please go tohttp://www.asx.com.au/asx/research/listedCompanies.do

b. To download the data, go to http://au.finance.yahoo.com

c. Type the name of your company in the search box. Then click "Search".

d. Click the tab titled "Historical Data".

e. Set Start date as 01/04/2015 and End date as 01/04/2020 (sample period). Please note that date format should be 'dd/mm/yyyy'. Set frequency at 'Monthly'. Click "Apply".

f. Click "Download data". Save the data using the file extension .xlsx or .xls. Do not use .csv or .xlsm

g. You will see seven columns in the saved spreadsheet with data ranging from 01/04/2015 to 01/04/2020. All we need is Date and Close price [P]. Delete other columns.

h. Although shown on the screen, you will see that the downloaded data does not contain dividend figures. Please label the third column as dividend and manually enter the dividend values into the cell. For example, if the dividend amount of $0.855978 was shown on 6 September 2018, enter this value into the cell to the right of the price figure on 1/10/2018 so that the return earned from 1/09/2018 to 1/10/2018 will include dividends paid during September).

i. Replicate steps b to g to download market index data [m]. For this assignment, the Australian market index "All ordinaries" is to be used. Symbol to be entered in search box is ^AORD.

j. Display the following four columns on the same spreadsheet; i) date, ii) close price for your stock [P], iii) dividend for your stock [D], iv) close price for the market index [m]. Then save the file in .xlsx or .xls format.

2. Calculate the monthly realised returns for your stock and market index.[5marks]

Instructions! a. We learnt in Lecture 4 that realised return at t can be calculated as

=

1 + 1

b. In the spreadsheet produced above, two additional columns should be created for computing monthly realised returns for your stock and the market index, which should be available from 01/05/2015 as you don't have price data on 01/03/2014 for the calculation of returns for 01/04/2014.

c. Dividend values are to be included in the calculation of realised returns for your stock (not for the market index) only on a month dividend was paid.

d. In the end, 60 individual monthly returns should be generated for your stock and for the market index.

3. Compute the following for each of your stock and the market index.[3 marks]

3.1. Monthly average returns

3.2. Variance of monthly returns

3.3. Standard Deviation of monthly returns

Instructions! a. To compute the monthly average returns, use Excel function, =AVERAGE (data range)

b. To compute the variance, use Excel function, =VAR.S (data range)

c. To compute the standard deviation, use Excel function,=STDEV.S(data range)

Note: Your data range here is 60 monthly returns for your stock and for your index.

4. Compute the following for the returns of your stock and the market index.[2 marks]

4.1. Covariance

4.2. Correlation Coefficient

Instructions! a. To compute Covariance between your stock and the market index, use the following Excel function.=COVARIANCE.S (array1, array2) (Array 1 will be 60 monthly returns for your stock while Array 2 will be 60 monthly returns for your market index, or vice versa).

b. To compute Correlation Coefficient between your stock and the market index, use the following Excel function.=CORREL (array1, array2) (Array 1 will be 60 monthly returns for your stock while Array 2 will be 60 monthly returns for your market index, or vice versa).

5. Compute a beta using the following three methods.[ 5 marks]

5.1.Use the following Excel function. =SLOPE(known_ys, known_xs)(known_ys must be 60 monthly returns for your stock while known_xs must be 60 monthly returns for your market index.)The resulting value is your beta.

5.2. Compute the beta using the following formula. The resulting value is your beta.

5.3. Compute the beta using a regression analysis.

Instructions! A beta can also be computed using a regression analysis.

a. Open a new worksheet in the current file, copy and paste 60 monthly stock returns and 60 monthly market returns. Then go to "Data" tab "Data analysis" on the far right "Regression" then click "OK"

(If you don't see 'Data analysis' under 'Data' tab then click 'Options' under 'File' tab. Go to 'add-ins' from the left-hand side menu. On the bottom, you will see a drop-down menu next to 'Manage'. Select 'Excel Add-ins' then Click 'Go' and tick 'analysis toolpak' and click 'OK'. 'Data analysis' should now appear under 'Data' tab.)

b. You will see the following pop-up monitor.Proceed as follows.

c. Summary output will be displayed. Under ANOVA, the coefficient on X variable represents a beta of your stock. This beta estimate obtained from regression analysis should be identical to the betas obtained from the two methods conducted above.