Investment Portfolio Spreadsheet And Investment Analysis

Step 3

1. Produce a spreadsheet showing: the investments you have chosen; the closing share price on Thursday 12^th March (ie the price reported in the morning press on 13^th March); the number of shares you have acquired; the beta of each company; and the value of your investment. Your spreadsheet should show the weighted average beta of your portfolio, accurately calculated.   You must cut and paste the spreadsheet into the submitted document as the portal will not accept a spreadsheet in Excel format.  You will be marked to reflect your understanding of the task and the accuracy of the data.

2. Explain your reasons for choosing each investment. This should include: a brief explanation of what the company does and a discussion of why you think its share price will perform well in the short to medium term.  Your work should be supported by commentary and analysis by industry professionals in financial institutions and the financial press.  You are not expected to carry out a financial analysis of the company. You will be marked on your understanding of the task, the relevance of the material presented, the insight of your analysis and the quality and breadth of the sources used.

3. Many investment managers invest by buying shares to reflect the FTSE 100 index. Explain how the FTSE 100 index is calculated and how this behaviour by investment managers affects the market, particularly for smaller companies’ shares.  You will be marked on your understanding of the task and the insight of your analysis.

Step 3

Step 5

Company	Sector	Beta	Share Price	Investment Value	Portfolio Weights x(j)	Number of share
Restaurant Group Plc	Travel and Leisure	0.592	687.81	£         4,003.88	0.04	5
Air China	Travel and Leisure	1.75	56.85	£         6,800.95	0.07	119
Booker Group Plc	Food and Drug Retailers	0.458	160	£         4,207.05	0.04	26
Dairy Farm International Holdings	Food and Drug Retailers	0.65	1300.26	£            979.15	0.01	0
Abbott Laboratories	Health Care Equipment and Services	0.41	4636.27	£       26,616.88	0.27	5
Avacta Group Plc	Health Care Equipment and Services	1.24	0.84	£       51,723.17	0.52	61575
Berkeley Group Holdings	Household Goods	0.85	2615	£                     –	0.00	0
United Utilities Group Plc	Gas, Water and Multiutilities	0.43	902.5	£                     –	0.00	0
John Lewis of Hungerford	Household Goods	-0.437	1.13	£                     –	0.00	0
UK Mail Group Plc	Industrial Transportation	1.13	523.52	£         5,668.92	0.06	10
			Total Investment	£     100,000.00
			Portfolio weighted average beta		0.98289881
				Total number of stock		61740

2. Reason for selecting each investment

Restaurant Group Plc: It is a chain of public houses and restaurants which is headquartered in London. The company has an operation in about 350 public houses and restaurants. The share price of the company is highly valued at 687.806 which show that performance of the company is superior and due to that company is able to attract and hold their investors for so long. Apart from that, in spite of having such high share price, the beta value is only 0.592 which reflect a risky venture. Therefore, the share price of the company will effectively perform as the company is more liquid.

Air China: Air China is an airline company which is listed on LSE. In 2013, around 51 million together of international and domestic passengers were carried through Air China. It has been seen that share price of the company is constant in last one year to 56.85 which reflect that there has not been more fluctuation in the price. Therefore, it can be assumed that constant return is provided to the investors. Apart from that, risk associated with the share price is manageable (Cordis, 2014). Therefore, the investors can buy the shares to get better return.

Step 5

Booker Group PLC: This Company is one of the biggest food wholesale operators that offers private labeled goods and branded product to more than 400000 consumers. Moreover, it also provides services to grocers, independent convenience stores, restaurants and pubs. The average share price of this company in last one year is trading at 141.49 which show that company provides better return to their investors. Apart from that, the risk associated with share price of securities is also minimal to 0.458. Thus, it can be mentioned that the share price of Booker Group Plc will effectively perform in short to medium term.

Dairy Farm International Holdings: It is an Asian retail firm that is traded on LSE. The company is engaged in processing of food along with personal hygiene products. Apart from that, the products are also wholesaled. The share price of Dairy Farm as on 12^th March 2015 is 1300.26 and has low beta risk at 0.65. As a result it can be expected that its share price will be strongly traded in short term and medium term. Moreover, the risk of investment is minimal for the investors.

Abbott Laboratories: It is an American health care and pharmaceuticals products which has its operation in around 130 nations and holds more than 90000 workforces. The company is in the business since 127 years. The products in which the company deals are diagnostic assays, pharmaceutical products, animal health products, medical devices and nutritional products. As on 12^th March 2015 the share price is highly valued at 4636.27 with a minimum risk association of 0.41. Thus, the investors would not have to fear bout risk and low-risk taking investors can invest in it. As a result, the share price will perform productively in short term and medium term market.

Avacta Group Plc: The Company is engaged in providing innovative technologies along with reagents and consumables for Life Science markets. Moreover, it is involved in drug discovery and its diagnostics. Therefore, it is has good exposure globally and strongly trade on LSE. The share price of the company is very low to 0.84 and is highly risky. Therefore, it can be mentioned that it is not safe enough to perform effectively in short to medium term. Moreover, the company is less liquid in comparison to other nine companies.

Berkeley Group Holdings: It is a house-building firm that is based in Surrey. The company is specialized in residential work or neighbourhoods. Moreover, the company holds different brand under their name such as Berkeley Homes, St Edward’s Homes, St George Plc and St James Homes Brands. Being said that, the average share price of the company in last one year is 2308.441 and has a beta of 0.85. Therefore, the risk is less on the share price of the securities which ensures that investing in the securities in relation to short to medium term would be beneficial for the investors.                                                                                             

Reason for selecting each investment

United Utilities Group Plc: It is the biggest listed water company in UK and it is merger of NORWEB and North West Water. The company majorly deals in Recycled Wastewater and Drinking Water. About 4.0 gallon production is done each day. The share price will effectively perform as the margin of risk is very less on the share and share price of the company. Therefore, the both company and investors would be benefited.

John Lewis of Hungerford: This Company is involved in the home products such as fitted kitchens, bedrooms, luxury items and furniture. Therefore, it can be assumed that share value will perform as their share price is constant and will positively perform in medium term. Moreover, its beta is negative which means their shares are less volatile (Arya and Glover, 2014).  

UK Mail Group Plc: It is a private limited firm which is engaged in postal services. It provides services to large number of customers which helped in their growth. Furthermore, its share price is almost throughout the year and bears low beta which show that it will gain benefit in short to medium term.

3. FTSE 100 Index Analysis

According to Areal and Rodrigues (2011) FTSE is an index which is composed of 100 largest organizations that are listed on London Stock Exchange (LSE). The companies that are in FTSE 100 are different in sizes. It is known that the larger the size of the companies the larger is the weight of those companies. Apart from that, performance of each of the 100 companies on LSE can be effectively measured by FTSE 100. Therefore, in order to calculate FTSE 100 index, the market cap of each firm is required and also the weight-age of each company has also to be known. On the other hand, the share value of each of the company is multiplied by the total number of shares issued by the company. Therefore, it true market value of each of the company can be known (Cabeza-Pereiro, 2014).

Stock	Current Price	Market Cap	Outstanding Shares	Weighting
Anglo American	1,057.50	14,873.44	1,843.78	0.792907
Associated British Foods	2,918.00	23,425.64	313.91	1.248827
Admiral Group	1,483.00	4,172.67	801.41	0.222446
Aberdeen Asset Management	446	5,844.46	2,166.18	0.31157
Aggreko	1,604.50	4,177.29	135.48	0.222692
Ashtead Group	1,185.50	6,025.03	395.41	0.321196
Antofagasta	783.50	7,640.39	643.83	0.407311
ARM Holdings	1,118.50	15,901.10	1,420.50	0.847692
Aviva	542.25	21,845.57	2,774.76	1.164593
AstraZeneca	4,446.75	55,641.69	1,068.77	2.966273
BAE Systems	513.25	16,105.33	1,703.70	0.858579
Babcock International Group	1,107.50	5,549.27	406.76	0.295833
Barclays	270.55	43,994.08	25,112.47	2.345336
British American Tobacco	3,620.25	67,845.18	685.27	3.616844
Barratt Developments	595.25	5,833.04	2,979.38	0.310961
BG Group	1,163.75	39,408.76	2,130.41	2.100891
British Land Co	869.25	9,041.93	1,024.95	0.482028
BHP Billiton	1,401.00	29,685.17	2,756.31	1.582524
Bunzl	1,880.00	6,408.51	136.89	0.341639
BP	453.55	82,604.21	8,580.31	4.403652
Burberry Group	1,699.00	8,041.16	1,030.47	0.428676
BT Group	460.80	39,052.88	4,360.39	2.081919
Coca-Cola HBC	1,462.50	5,393.02	105.59	0.287503
Carnival	3,144.00	6,947.17	310.83	0.370355
Centrica	279.50	14,053.05	4,323.36	0.749172
Compass Group	1,150.50	19,363.02	1,443.79	1.032248
Capita Group (The)	1,277.50	8,434.01	493.17	0.449619
CRH	1,840.00	15,202.27	392.86	0.810437
Dixons Carphone	463.05	5,386.54	2,100.79	0.287158
Diageo	1,793.75	45,638.03	2,472.77	2.432975
Direct Line Insurance Group	328.85	4,897.50	1,098.20	0.261087
Experian	1,244.50	12,355.33	1,055.52	0.658666
easyJet	1,614.00	6,649.26	1,103.35	0.354474
Fresnillo	760.25	5,633.55	530.49	0.300326
G4S	295.35	4,560.14	745.03	0.243102
GKN	366.10	6,036.83	2,413.91	0.321825
Glencore	293.15	37,880.27	9,011.79	2.019407
GlaxoSmithKline	1,459.75	70,195.05	2,597.21	3.742116
Hikma Pharmaceuticals	2,136.50	4,232.17	97.17	0.225618
Hargreaves Lansdown	1,273.00	6,071.28	380.85	0.323662
Hammerson	680.25	5,517.63	718.65	0.294146
HSBC Holdings	615.10	120,337.99	4,958.67	6.41525
International Consolidated Airlines Group	556.75	11,573.22	6,304.43	0.616971
InterContinental Hotels Group	2,782.00	6,575.87	140.66	0.350561
3i Group	558.75	5,329.09	688.66	0.284095
Imperial Tobacco Group	3,273.00	31,268.16	545.91	1.666914
Intu Properties	343.5	4,685.16	441.32	0.249767
Intertek Group	2,595.50	4,305.13	92.47	0.229508
ITV	267.8	10,880.68	2,904.69	0.580052
Johnson Matthey	3,439.50	6,997.94	154.34	0.373062
Kingfisher	364.55	8,470.87	1,548.81	0.451584
Land Securities Group	1,328.50	10,775.11	1,081.09	0.574424
Legal & General Group	269.25	16,045.00	4,518.34	0.855363
Lloyds Banking Group ORD	87.64	62,808.89	31,486.28	3.348358
London Stock Exchange Group	2,431.50	8,595.20	252.07	0.458212
Merlin Entertainments	465.05	4,771.70	1,288.88	0.254381
Meggitt	513.75	4,052.24	590.95	0.216026
Marks & Spencer Group	590.25	9,650.58	2,861.64	0.514475
Mondi	1,507.00	7,210.47	699.18	0.384392
Morrison (Wm) Supermarkets	179.9	4,226.52	2,240.83	0.225317
National Grid	909.4	33,682.16	2,837.48	1.795605
Next	7,442.50	11,350.86	134.85	0.605117
Old Mutual	233.4	11,630.66	4,886.87	0.620034
Prudential	1,656.00	42,909.76	1,441.87	2.287531
Persimmon	1,923.00	5,824.30	421.54	0.310495
Pearson	1,339.50	10,991.86	587.86	0.585979
Reckitt Benckiser Group	5,859.00	41,772.28	557.4	2.226891
Royal Bank of Scotland Group (The)	353.75	22,429.65	5,369.48	1.19573
Royal Dutch Shell	1,979.00	76,762.27	1,894.82	4.092217
Reed Elsevier	1,110.50	12,631.67	1,493.18	0.673398
Rio Tinto	2,882.00	40,053.90	1,410.71	2.135284
Royal Mail	499.7	4,992.00	3,059.11	0.266125
Rolls-Royce Group	1,005.50	18,551.42	1,472.54	0.988981
Randgold Resources	4,721.50	4,472.09	167.09	0.238408
RSA Insurance Group	437.95	4,491.02	422.08	0.239417
SABMiller	3,613.50	59,022.15	489.83	3.146486
Sainsbury (J)	261.95	5,082.76	1,121.37	0.270963
Schroders	3,410.50	7,698.32	103.31	0.410399
Sage Group (The)	574	6,146.55	1,121.37	0.327674
Shire	5,522.50	32,452.10	336.48	1.73003
Sky	1,090.50	18,737.29	730.23	0.99889
Standard Life	494.7	9,676.58	1,523.99	0.515861
Smiths Group	1,184.50	4,587.31	556.05	0.244551
Smith & Nephew	1,142.50	10,181.02	1,318.61	0.542753
Sports Direct International	656.5	3,940.89	644.35	0.21009
SSE	1,659.50	16,842.40	1,005.04	0.897873
Standard Chartered	1,059.25	26,885.31	1,472.62	1.433263
St James’s Place	933.25	4,954.24	297.52	0.264112
Severn Trent	2,180.00	5,197.73	165.09	0.277092
Travis Perkins	2,170.00	5,407.12	188.52	0.288255
Tesco	220.5	17,964.02	6,688.61	0.957667
TUI AG	1,208.50	7,156.56	262.15	0.381518
Taylor Wimpey	183.95	6,214.11	16,753.87	0.331276
Unilever	2,870.50	37,220.32	810.47	1.984225
United Utilities Group	1,005.50	6,846.16	509.48	0.364971
Vodafone Group	241.4	60,077.78	28,906.81	3.202762
Weir Group	1,889.00	4,084.11	303.2	0.217725
Wolseley	4,031.50	10,477.69	227.67	0.558568
WPP Group	1,571.50	20,639.98	1,165.92	1.100323
Whitbread	5,297.50	9,650.35	124.8	0.514463
		1,875,811.49		100
FTSE 100 Index Value
Total Market Value	1,875,811.49
Index Divisor	100
Index Value	18758.1149

Calculation of FTSE 100 Index

The investment managers would be recommending the investors to the companies that are listed on the FTSE 100 index. It is known that, FTSE 100’s companies ensure better return on being invested on their shares. Apart from that, the share prices of these companies are usually stable which ensures stable return on the amount of investment made by the company. As a result of that, investors from different background are interested to invest in the companies of FTSE 100 Index. Apart from that, the investment managers would have a thought that share price of smaller companies fluctuates on a continuous basis and would not provide constant return to the investors. On the other hand, it has been mentioned by Arya and Glover (2014) that making investment in the shares of smaller firms is normally considered to hold a high percentage of risk. The reason is that the market of the smaller companies hare is repeatedly less liquid in comparison to the shares of big companies. Apart from that, it is expected by the investment managers that the smaller companies would be less mature businesses as against large companies listed on FTSE 100 Index. In addition Coakley, Kougoulis and Nankervis (2014) stated, performance of shares would be more volatile in context of small companies.

References

Araújo, V. and Assis, Í. (2014). A segmentação na legendagem para surdos e ensurdecidos (LSE) de Amor Eterno Amor: uma análise baseada em corpus. LL, 30(2), pp.156-184.

Areal, N. and Rodrigues, A. (2011). Discrete dividends and the FTSE-100 index options valuation.Quantitative Finance, 14(10), pp.1765-1784.

Arya, A. and Glover, J. (2014). Auditor Independence Revisited. Journal of Accounting, Auditing & Finance, 29(2), pp.188-198.

Cabeza-Pereiro, C. (2014). Metaphor and Lexicon in Sign Languages: Analysis of the Hand-Opening Articulation in LSE and BSL. Sign Language Studies, 14(3), pp.302-332.

Coakley, J., Kougoulis, P. and Nankervis, J. (2014). Comovement and FTSE 100 index changes.IJBAF, 4(2), p.93.

Cordis, A. (2014). Accounting Ratios and the Cross-section of Expected Stock Returns. Journal of Business Finance & Accounting, 41(9-10), pp.1157-1192.

Darrat, A., Gray, S., Park, J. and Wu, Y. (2014). Corporate Governance and Bankruptcy Risk. Journal of Accounting, Auditing & Finance.

Dichev, I., Huang, K. and Zhou, D. (2014). The Dark Side of Trading. Journal of Accounting, Auditing & Finance, 29(4), pp.492-518.

Meyer, R. and Tan, G. (2014). Provide detailed and real-time indoor environmental information using POD–LSE and limited measurements. Energy and Buildings, 73, pp.59-68.

Mu, T., Zhou, L. and Yang, J. (2014). Experimental Study of an Adaptive Sequential Nonlinear LSE with Unknown Inputs for Structural Damage Tracking. Shock and Vibration, 2014, pp.1-14.

ONISHI, K., TERASHIMA, O., SAKAI, Y., NAGATA, K. and ITO, Y. (2014). Investigation of the turbulent energy transport in a plane turbulent jet by applyning POD-LSE complementary method.Transactions of the JSME (in Japanese), 80(809), pp.FE0010-FE0010.

Peker, S., TvaronaviÄÂienÄ—, M. and Aktan, B. (2014). Sustainable risk management: fuzzy approach to volatility and application on FTSE 100 index. JESI, pp.30-36.

Purvis, J. (2014). The Official Opening of The Women’s [email protected], 12 March 2014. Women’s History Review, 23(4), pp.513-517.

Spanos, A. (2014). Reflections on the LSE Tradition in Econometrics: a Student’s Perspective.oeconomia, (4-3), pp.343-380.