FIN9010M Portfolio Analysis - University of Lincoln

Learning Outcome 1: Identify and critique the elements of portfolio theory.

Learning Outcome 2: Critically evaluate risks and returns relevant in the international securities market using financial data.

You are expected to collect data of stock prices and carry out mean variance optimization (MVO) using Excel Solver. Express your understanding about Risk, Return and Portfolio Theory by writing a 2,000-word report about your data processing, results analysis, explanation about your findings, the assumptions underlying MVO, and finally your recommendations for investors who want to hold that portfolio.

What you need to do?

Question 1. Access YahooFinance or Bloomberg to collect the daily stock price indices of the FTSE100 and any six (6) companies listed on the London Stock Exchange over the last 12 months (Hint: it is advised that you should select firms from different industrial sectors for diversification purpose). The price indices should be converted into return histories needed to find the efficient frontier.

Question 2. Plot the return histories of the stocks over time to see whether there is evidence of volatility clustering and provide descriptive statistics of data sample (mean, standard deviation, minimum, maximum return, skewness and kurtosis).

Question 3. Calculate alpha and beta values of each company stock return.

Question 4. Carry out MVO procedure with Excel Solver to find and plot the efficient frontier on a return- risk diagram. Do for both long only and long-shorts constraints. (The former means that allocations to assets must be >= 0. The long-short allows allocations to be < > 0).

Question 5. Given the annual short-term risk-free rate is 0.5% and an investor uses a quadratic utility function based on expected return and variance and a risk aversion of 3. Carry out further optimization based on maximizing the Sharpe ratio to find the composition of the portfolio of the risky assets determined by the point of tangency that line from the risk-free rate makes with the frontier. This exercise should give the optimal allocations in terms of both risky and the risk-free asset.

Format for Assessment:
The report is limited to 2,000 words (excluding Tables, Figures and Appendix). Evidence of task completion should be included within an appendix and is separate from the word count.
Your report should include the following parts:
1. Introduction
You need to introduce the work to be done, e.g. the objective of the report, selected companies, time period and summary of the methodology you applied. You should also plot the return histories of the stocks in graphs and provide descriptive statistics of data sample and summarise key findings from those statistics.
2. Alpha and Beta
In this part, you should present the method you applied to calculate company alpha and beta and the implications of those calculations about company risk and investment opportunities.
3. Mean Variance Optimization and the Efficient Frontier
- You should explain the procedure with MVO, the covariance matrix and tell us about the relationship of stocks and the potential benefits of diversification, copy the Excel output you did with MVO
- Plot the Efficient frontiers & discuss the portfolio choice for your customer based on your Efficient frontiers.
- You should do for both: long only and long-shorts constraints and compare the difference of two scenarios.
4. Construct a complete portfolio
- You are required to find the Optimal risky portfolio (ORP) and discuss about this portfolio.
- Based on the data given in the assignment, construct a complete portfolio by mixing ORP with risk free assets
5. Recommendations
- Summarize your key findings.

- Discuss any recommendations for investors who want to hold that portfolio

Attachment:- Portfolio Analysis.rar