The deadline for coursework one is Thursday, November 24th, 2022. The maximum length is 2000 words and the report should include Excel charts, tables as appropriate. In addition, students should submit a single Excel file to support the coursework in order that calculations may be checked. Please note that 100% of the credit available is on the mark awarded to the report. The spreadsheet may be used for checking, it will not be marked.

Question 1

In the “Chicago City Employees” exercise we used a number of Excel text tools to prepare our data

and used various Excel functions to answer a number of research questions.

Required: Find a suitable dataset that includes names and other characteristics and use this data to provide answers to 10 research questions of the type we investigated in the OPEC and Chicago examples.

I would very much like all students to find their own sources of data but for those students devoid of ideas then the current roster of NBA players, available at https://stats.nba.com/players/bio/, is a suitable data set. Likewise, the salaries databased at http://www.espn.com/nba/salaries. You will of course need to copy any data you find into Excel, clean it and manipulate it. [40 marks]

Question2

For a stock and stock index of your choice, download 5 years of weekly data from Bloomberg or Yahoo! Finance and use Excel to obtain the following:

a) Mean weekly return;

b) A manual calculation of the variance, of each return series, verified using the VARP function in Excel;

c) A manual calculation of standard deviation, of each return series, verified using the STDEVP function in Excel;

d) Two histograms of weekly returns, manually created using the Frequency function;

e) The Skewness and Kurtosis of weekly returns; A scatter diagram of weekly returns with the index on the horizontal axis and the stock on the vertical axis;

f) How many weeks (and what proportion of total weeks) do weekly returns move together/move apart?

g) A manual calculation of the covariance, of weekly returns, verified using the covariance function in Excel;

h) A manual calculation of the correlation, of weekly returns, verified using the correlation function in Excel;

i) The slope, intercept and R-Squared values from the line of best fit based on the earlier scatter diagram, verified using the SLOPE, INTERCEPT and RSQ functions.

j) A scatter diagram of weekly returns with the index on the horizontal axis and the stock on the vertical axis;

IMPORTANT - For each measure, and where appropriate, discuss the results. [30 marks]

Question 3

For three stocks and the associated market index of choice, download 5 years of weekly data, from Bloomberg or Yahoo! Finance, and use Excel to carry out the following operations:

a) Using continuously compounded returns, create three scatter plots with the return to the individual stocks on the y-axis and the return to the market index on the x-axis, fitting a trendline to each scatter plot and displaying the equation and R² of the trendline.

b) Calculate each stock’s beta using:

������ =

���������(���_���, ���_���)

���������(���_���)

c) Verify the results in (a) using the Slope, Intercept and RSQ functions

d) Using the equation below, calculate each stock's residual return:

���_{������}  = ���_{������}  − (���_��� + ���_������_{������})

Where ���_{������} is the continuously compounded return to stock i, ���_��� and ���_��� are the intercept and slope coefficients for stock i estimated in (c), and ���_{������} is the continuously compounded return on the index on day t.

e) For each stock, verify that the following equation holds:

���²  = ���²���²  + ���²

������

��� ���

������