Calculate the correlation coefficient of a portfolio

The data that appear in the following exercises can be found in the Excel data file QF (4FBL663) Seminar 5 (Data).xlsx, which is posted on Blackboard.

The data refer to Profit after Tax, Personnel Expenses, Net Turnover, Number of Employees, and Research and Development (R&D) as a percentage of Net Turnover, for a sample of 38 firms.

Construct two new variables in your Excel file, i.e. Personnel Expenses divided by Number of Employees, a variable which could be interpreted as the average wage / salary paid by each firm, and Profits per Employee, which can be interpreted as a measure of profitability.

Question 1

Derive the following scatter plots (Excel XY graphs), and use Excel to calculate the correlation coefficient associated with each graph:

  1. (a)  Number of Employees against Net Turnover
  2. (b)  Profit per Employee against Number of Employees
  3. (c)  Personnel Expenses per Employee against Number of Employees
  4. (d)  Profit against Number of Employees
  5. (e)  Personnel Expenses per Employee against Profit per Employee
  6. (f)  Personnel Expenses against Net Turnover
  7. (g)  Profit per Employee against Research and Development (R&D) as apercentage of Net Turnover

(h) As for part (g), above, but only for firms with R&D greater than zero


Within the context of each of these scatter plots and associated correlation coefficients, discuss the notion of there being a relationship between each set of two variables, and, if the data supports a relationship, how such a relationship might be justified (or indeed how such an implied relationship might be nonsense).

Question 2

Use Excel to derive the regression output relating to the implied relationships in parts (b), (d), (e) and (h) in Question 1, above. Comment on the results from these regressions.


Virgin File QF (4BFBL663) Seminar 5 (Data)

Data after awnsering question Quantitative Finance week 5 excel file


0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.