Home
Search
Login
<mosaic.cnfolio.com>

Coursework report


Introduction


To build a portfolio of companies that will yield the lowest possible value after 100 days a wide group of different under performing or in trouble companies will be chosen and various risk analysis methods will be performed on them.

Choosing the companies


To select a wide group of companies several methods will be used:


Market areas affected by the current economic climate


The credit crunch and nationalisation of Bradford and Bingley has had a significant affect on people as well as companies. Fuel and food prices increased as well as the overall cost of living. These increases caused people to buy cheaper products and services and buy fewer expensive items such as cars, computers, property, flights and holidays.

Companies and markets dealing with expensive items had decreased sales due to this and were affected significantly more where as companies such as Tesco, McDonald's and Poundland have benefited from the increase in customers.

Companies in the affected market areas:

Cars

Mobiles / Computers

Property

Airlines

Holidays

Banks / Credit Card Companies

Known companies in trouble


The car companies Ford, GM and Chrysler asked for a bail out from the government for $25bn as they were running out of cash and the microprocessor company AMD had to cut 500 jobs and is known to be struggling against Intel.

Companies that have under performed in the past


The companies that have been known to under perform in the past (2006) are:


Chosen list of companies


The list below shows all of the companies chosen in various market areas.


To simplify retrieving information about the companies, only the companies that are available on Yahoo Finance will be analysed. The list below shows all of the available companies.


Outline of methods used for analysis


To work out the best combination of companies for a portfolio (in this case, to lose as much money as possible) several methods will be used. Secondly, the risk of investing in each individual company will be looked at. Thirdly, the value at risk will be analysed for each company.


Financial Ratios


Three financial ratios were calculated for each company, current ratio, quick ratio and sales to working capital.


The percentage difference between quick and current ratio's were also calculated which can show how much the company is dependent on inventory (which is harder to turn into cash).
Each of the companies liquidity (using quick ratio) and efficiency (sales to working capital) was separated into categories. For liquidity, if the ratio was over 1 then it was classed as liquid otherwise its classed as non liquid. For efficiency, if the ratio was above 1 then it was classed as efficient, between zero and one was classed as not efficient and below 0 classed as trouble. The companies were sorted by these categories which allowed the worst companies to be seen visually as shown below:

Sorted Financial Ratios

The three companies at the bottom didn't have any of the required information available so could not be calculated. Seven companies were both not liquid and in trouble where they had their quick ratio less than 1 and a negative sales to working capital. This shows that they have more short-term liabilities than they do short-term assets. Sovereign Bancorp Inc showed that its been losing money. Google although categorised as not efficient by having a low sales to working capital has a massive current (and quick) ratio of over eight showing that they can easily pay off any existing short-term liabilities.

The companies chosen to be analysed further are shown below and contain the worst seven companies and a selection from the companies with one bad category (not liquid or not efficient or in trouble).


Risk of a single stock


The next method used is called risk of a single stock which looks at each individual company again and calculates the risk in investing in that company. Risk in this case is how much the companies share price varies over time. A low risk investment would be in a company thats share price varies very little and as a result there isn't much risk in it changing much and the investor losing (or gaining) a lot of money. A high risk company would have its share price vary drastically giving higher losses or gains but with less predictability. To lose as much money as possible very risky companies should be invested in.

The risk for each company is gotten by calculating the standard deviation of a companies returns over a time period. The higher the standard deviation, the higher the risk. The screen-shot below shows statistics for Ford.

Single Stock Risk for Ford

The previous screen-shot shows three sections containing different information. Firstly using historical data investment of £1000 in 1998 shows that after 5 years the investment is worth £526.20 and after 10 years it's worth only £96.43. Secondly there is a monthly summary showing statistics on the monthly returns such as minimum, maximum, range etc as well as the standard deviation (risk). Thirdly, the annual summary is shown showing the annual standard deviation (risk) and a probability that the investment will be worth greater or equal to the original investment after a year. For ford the probability of the investment being the same or more than the original £1000 investment is only 39.36% which shows there is a very high chance that an investment in this company will lose money.

The following table shows the expected monthly return and standard deviation (risk) for each of the eleven companies:

Table of Single Stock Risk for all Companies

The most risky companies shown here are:


The least risky company is HSBC with 6%. The majority of the companies have a negative average expected return showing that they are more suitable companies to lose money. Virgin Mobile has the highest standard deviation as well as the largest negative expected return of -6%.

Companies won't be filtered at this stage because further calculations and analysis needs to be performed and used in combination with these results.

Value at risk


To look at risk further for individual companies the value at risk was be calculated.

The value is calculated at different confidence levels (example: 95%, 99%) and gives the amount of money that the investor will lose up to at that confidence level. For example a value at risk of £1000 at 95% confidence says that there is only 5% chance that the investor will lose more than £1000. Calculating the value at risk for multiple confidence levels can show the value changes as the confidence level increases.

The value at risk is calculated using the historical data from each company which gives more realistic results but is limited to the time period used in that data. The parametric approach is also used which takes a smoothed version of the historical data and is used to predict the value at risk over longer time periods.

The value at risk calculations are shown in the screen-shot below and are sorted by the parametric 1 Day at 1% column. The value at risk is based off of a £100,000 investment.

Table of Value at Risk for all Companies Sorted

This again shows HSBC as the least risky company only having a value at risk of £6,138.91 with 99% confidence. Sovereign Bancorp shows a large value at risk of £21,424.00 with 99% confidence. It also has large jumps between confidence levels (£3765.63 from 95 to 98% and £2510.44 from 98 to 99%).

The companies with the higher value at risks are:


Correlation


Pairs of companies that have high correlations between their returns gives a higher risk because the companies follow (or track) each other to a certain degree. This means that if the return for one company drops then theres a higher chance that the returns for the other company will also drop. Companies that are negatively correlation would have reduced risk because if one companies returns drop then there is a chance that the other companies returns will increase and hence cancel out.

The correlation is calculated for every pair of companies and as a large number of companies are being compared the correlation was calculated using Visual Basic for Applications (VBA). This also allows different companies to be used and the correlations can be calculated again with no changes to any formulas.

Table of Companies Correlations

Companies with higher correlations (P >= 0.6) are:

Companies with medium correlation (0.4 >= P < 0.6) are:

Companies with relatively high negative correlation (P <= -0.3) are:

Mean-Variance Portfolio Theory


The goal is working out which set of companies when combined with with different weights gives the most risk. The risk is again the standard deviation of the companies returns.
This method now takes into account multiple companies with different weights and the correlation between them. This uses the values calculated from risks of a single stock and correlation. The expected return based on the weighted companies is also calculated which shows on average how much the investor can expect to gain (or lose if negative).

When normally building a portfolio of companies the objective is to minimise risk. The more companies added to a portfolio the more the diversifyable risk decreases. To make the risk as large as possible the minimum number of companies needs to be used.

The companies used were based on a combination of previous analysis. The table below shows the highest risk companies from previous methods of analysis.

Table comparing companies with highest risks

The three companies Virgin Mobile, Delta and Continental are both high risk for single stock and value at risk. Delta and Continental have a high correlation as well. Virgin Mobile has a high correlation with GM and GM consistently under performs and has a high value at risk. Sovereign Bancorp Inc and OfficeMax have a high value at risk and have medium correlation between them. Other high correlations contain at least one other company that doesn't have as high risk so are not going to be used.
The list of companies that will be used with MVPT are:


To be able to compare a large number of different weights with various numbers of companies (5 and up), VBA code was written to automatically calculate the expected return and standard deviation (risk) as well as display the weights. It iterates through every possible weighting (in specific increments such as 5 or 10) and only displays the valid combinations (all weights add up to 100%). At 5% increments and 6 companies, 34,886 valid combinations are calculated, for excel 2003 it's impossible to calculate it for more companies before it reaches the limit of 65535 combinations.

The results were sorted by their standard deviation (risk), the screen-shot below shows some of the highest risk combinations.

Sorted table of weighted company portfolios

From the screen-shot its clear that the highest risk is caused by investing the majority of the money in Delta and the rest spread out between four or five other companies. This is to be expected because the more companies in a portfolio the more the risk is reduced and by placing most of the investment in one company is trying to do the opposite and make the portfolio behave more like a single investment.

Although the highest risk from the screen-shot includes OfficeMax, Delta, Virgin Mobile, Continental and Sovereign Bancorp Inc, GM was chosen instead of OfficeMax because GM is known to consistently under perform and is currently in a lot of trouble. There is also only a slight difference between these combinations.

The final portfolio is shown below:

Table of the final portfolio

Note: NYSE:SOV was available on Yahoo Finance during writing this report but is now no longer available, it is still available on Google Finance Here

Expected Return


Expected monthly return was calculated previously for the entire portfolio which was equal to -1.595%. There are 3 months and 1 week from December 1st till March 10th (minus 1 week). An estimate of the expected return for the whole period is -1.595% ^ 3.25 = -5.28%.

The value of the investment after 100 days is estimated at $100,000 + (-5.28% of $100,000) = $94720. A loss of $5,280.

References


Elliott, L. (2008, August). Credit crisis - how it all began. guardian.co.uk. Retrieved February 2, 2009, from http://www.guardian.co.uk/business/2008/aug/05/northernrock.banking

Wearden, G., Treanor, J. (2008, September). Banking shares plunge as crisis deepens. guardian.co.uk. Retrieved February 2, 2009, from http://www.guardian.co.uk/business/2008/sep/29/bradfordbingley.banking5

Kollewe, J. (2008, September). Tesco defies credit crunch with sales and profit rise. guardian.co.uk. Retrieved February 2, 2009, from http://www.guardian.co.uk/business/2008/sep/30/tesco.supermarkets

“Daily Mail Reporter“. (2008, August). McDonalds in recruitment drive as credit crunch Britons turn to cheap burgers. dailymail.co.uk. Retrieved February 2, 2009, from http://www.dailymail.co.uk/news/article-1042351/McDonalds-recruitment-drive-credit-crunch-Britons-turn-cheap-burgers.html

Finch, J. (2008, December). How one-price-fits-all is making this man Britain's most cheerful retailer. guardian.co.uk. Retrieved February 2, 2009, from http://www.guardian.co.uk/business/2008/dec/06/poundland-recession-creditcrunch

Butterscotch. (n.d.). Vauxhall Hit By The Credit Crunch. cararticles.co.uk. Retrieved February 2, 2009, from http://www.cararticles.co.uk/uk-vauxhall-hit-by-the-credit-crunch.html

“whatcar?”. (2008, November). Credit crunch hits Euro car buyers. whatcar.com. Retrieved February 2, 2009, from http://www.whatcar.com/news-article.aspx?NA=236034

Webologist. (2008, October). Computer Chips are Down, Credit Crunch Affecting Communications Hardware Sector. webologist.co.uk. Retrieved February 2, 2009, from http://www.webologist.co.uk/2008/10/computer-chips-are-down-credit-crunch-affecting-communications-hardware-sector.html

First-timers face battle to get on property ladder as credit crunch bites. (2008, December). Retrieved February 2, 2009, from http://www.mirror.co.uk/advice/homes/2008/12/10/first-timers-face-battle-to-get-on-property-ladder-as-credit-crunch-bites-115875-20960104/

Symonds, T. (2008, November). Is the worst over for the airlines?. Retrieved February 2, 2009, from http://news.bbc.co.uk/1/hi/business/companies/7711353.stm

"Daily Mail Reporter". (2008, November). Ryanair boss claims only four European airlines (including his own) will survive recession.Retrieved February 2, 2009, from http://www.mailonsunday.co.uk/news/article-1082585/Ryanair-boss-claims-European-airlines-including-survive-recession.html

Ryanair says recession 'good for airline industry'. (2008, November). Retrieved February 2, 2009, from http://news.cheapflights.co.uk/flights/2008/11/ryanair-says-recession-good-for-airline-industry.html

Milmo, D. (2008, October). Global demand for flights nosedives. guardian.co.uk. Retrieved February 2, 2009, from http://www.guardian.co.uk/business/2008/oct/25/ryanair-aviation-airline-industry-valencia

Harvey, D. (2008, September). Credit crunch hits travel company. bbc.co.uk. Retrieved February 2, 2009 from http://news.bbc.co.uk/1/hi/england/bristol/7594668.stm

Butterworth, M. (2009, November). Peter Mandelson issues warning to credit card companies. Telegraph.co.uk. Retrieved February 2, 2009, from http://www.telegraph.co.uk/finance/economics/interestrates/3526881/Peter-Mandelson-issues-warning-to-credit-card-companies.html

O'Sullivan, A. (2008, November). Credit card companies given ultimatum. thisismoney.co.uk. Retrieved February 2, 2009, from http://www.thisismoney.co.uk/credit-and-loans/article.html?in_article_id=458570&in_page_id=9

Nichols, S. (2008, November). AMD announces further job cuts. accountancyage.com. Retrieved February 2, 2008, from http://www.accountancyage.com/vnunet/news/2230015/amd-cut-500-jobs

Gaudin, S. (2008, April). Intel, economy hit struggling AMD with one-two punch. computerworld.com. Retrieved February 2, 2008, from http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9075858

US car companies seek $25bn aid. (2008, November). Retrieved February 2, 2009, from http://news.bbc.co.uk/1/hi/business/7736301.stm

McKinley, C,, Macht, P. (2006, April). CalPERS Focus List Targets Six Underperforming Companies. calpers.ca.gov. Retrieved February 2, 2009, from http://www.calpers.ca.gov/index.jsp?bc=/about/press/pr-2006/april/focus-list.xml

Attachment Timestamp Size
final_portfolio_table.PNG 2009-02-02 16:00 15.93 KB
mvpt_risk_compare_table.PNG 2009-02-02 15:56 12.58 KB
single_stock_table.PNG 2009-02-02 15:49 16.65 KB
MVPT_sorted.PNG 2009-02-02 15:45 42.57 KB
correlation.PNG 2009-02-02 15:44 29.74 KB
var_sorted.PNG 2009-02-02 15:44 22.23 KB
single_stock_ford.PNG 2009-02-02 15:44 10.31 KB
financial_ratios_sorted.PNG 2009-02-02 15:44 79.66 KB
Page revised 2009-03-02 12:40 • ©2012 University of Portsmouth • This website uses wikkawiki.org software.