Easter Assignment 2018
1. This problem set counts for 20% of the final mark for this course.
2. The problem set must be handed in by 6 pm on Friday, 20rd April, 2018; No marks will be awarded if it is handed in after this date.
3. All assignments must be placed in the assignment box. This is located under the students’ pigeon-holes on the 7th floor (opposite the main lifts) of the main site. The assignment box is accessible outside of office hours. Submissions will not be accepted via email.
4. It is a requirement that you keep a photocopy of your problem set and a copy of the spreadsheets used. You are not required to submit your spreadsheet, only the answer booklet. See 6 below.
5. Attempt ALL questions.
6. Provide your answers in the Answer Booklet provided. This is in Word format to enable you to edit the document to change box sizes etc. but you must maintain the general format
This assignment is empirical and requires you to use the internet to research some of the responses.
Q1. Answer ALL parts of this question.
Briefly describe the difference between Technical and Fundamental Analysis in Portfolio construction and evaluation. (Max 5 sentences)
This question is on Technical methods of Portfolio construction.
This question requires the use of Excel. Data must be displayed as specified in the assignment. All tables and the entries in the cells must be centred.
This question requires you to construct an equity portfolio using the Treynor (Elton Gruber) and Specific-risk adjusted alphas (Treynor Black) of 4 shares from the FTSE ALL SHARE Index and a proxy for the market.
In this question assume the risk free rate is 0.50%.
Price Data adjusted for dividends of shares for use in the portfolio construction exercise.
You are provided with an Excel Spreadsheet file named, PM_Assignment_Data in Moodle which contains the Time Series monthly prices of the Alliance Trust PLC (EPIC: ATST), SGRO (Segro), SLA (Standard Life Aberdeen) and SAG (Science) for the period 31/3/2015 to 1/01/2018 comprising 33 monthly Prices.
You need to create your own proxy for the market portfolio, called MyMKT made of these 4 share companies only in the same capitalisation ratio that they have in the FTSE ALL Share Index. Use the link below to find the capitalisations in the market: http://www.stockchallenge.co.uk/ftse.php
Using Ctrl-F search the EPIC column for the particular share e.g. ATST.
Having obtained the capitalisations compare your ratio of weights should be close to one of these:
ATST:SGRO:SLA:SAG = 25%, 25%, 25%, 25% Total = 100%
ATST:SGRO:SLA:SAG = 10%, 25%, 50%, 15% Total = 100%
ATST:SGRO:SLA:SAG = 14%, 30%, 55%, 1% Total = 100%
Having picked the appropriate ratio from the list above use this picked ratio to create your own proxy for the market, MyMKT as a weighted sum of the individual prices i.e.
MyMKT = WATST*ATST Price + WSGRO*SGRO Price + WSLA*SLA Price + WSAG*SAG
As confirmation your spreadsheet should now have the following 2 rows:
Date ATST SGRO SLA SAG MyMKT
31/03/2015 5.38893 376.6945 405.0839 135.9574 337.9185
a) Calculating Price-Relatives, Mean Returns and Standard Deviations of returns:
Below the last Price row of your spreadsheet with date 1/1/2018 create new cell headings for the monthly price-relatives:
Date, PRATST, PRSGRO, PRSLA, PRSAG and PRMyMKT.
These column headings calculate the price-relatives for these companies and for MyMKT.
In the 2nd cell below the column headed PRATST corresponding to the date 30/4/15, calculate the first monthly price-relative for ATST using the formula:.
By copying this formula across the other cells find the price-returns for the all the shares and for MyMKT for the entire time period.
There should be 33 price/relative values for each share and for MyMKT.
Note: The price-relative gives the growth factor = 1 + monthly return (as a decimal)
My spreadsheet had these 1st three rows:
Date PRATST PRSGRO PRSLA PRSAG PRMyMKT
30/04/2015 1.010892 0.993473 1.067613 1.020408 1.042502
Next find the annualised average return for each share as the annualised geometric average of the price-relatives. Use the formula
Geometric average return,
Where (1+r1), (1+r2),..,(1+rT) are the T monthly price-relatives; Then annualise the return by scaling up by12 i.e.
Annualised Geometric monthly average return
To find the product in the price-relative’s formula use the excel function =product() where the brackets has the cell references of all the price-relatives.
Then find the annualised standard deviations of the monthly returns by finding the standard deviation of the monthly price-relatives and scaling up by 12. The Excel function for finding the standard deviation is =stdev().
Display the Mean returns and Standard Deviations as %s correct to 2 dec places.
Mean Returns and Standard Deviations
MEAN Return, Std. Dev
Copy Table 1a) into your Answer Booklet.
My answers for the share ATST are given below:
b) Calculating the correlation coefficient matrix
Below the rows for means and standard deviations determine the matrix of correlations.
Obtain estimates of the correlations of each pair of returns (using the columns for price relatives) and format them in matrix form as shown below:
Display the correlations correct to 4 decimal places.
Note: The diagonal values should come to 1. Why?
Use the excel function: = correl( , ) for calculating the correlations.
Copy Table 1b) into your answer booklet.
My answer for the first row is shown below:
c) Obtaining the ERBs, Betas, Specific Risk, Alphas:
Below the matrix of coefficients obtain estimates of the above for each share and MyMkt.
Beta formula where is the correlation of A with M, the market, σA is the standard
deviation of A and σM is the standard deviation of the market, M.
The ERB is the excess return to beta (Treynor Measure) required for the Elton Gruber portfolio.
is the specific-risk adjusted alpha required for the Treynor Black portfolio.
Estimates of Share Parameters
ATST SGRO SLA SAG MyMKT
Sigma, σ 69.86%
Beta, β 0.3174
Specific risk, η
Display the σ and η as percentages correct to 2 dec. places and the other rows as decimals correct to 4 dec. places.
Copy Table 1c) into your answer booklet.
d) Elton Gruber Procedure:
Using your spreadsheet complete the table:
Elton-Gruber Cut-off values
Rank, i Sharei βi ηi2 ERB Ci
Optimum cut-off rate, C* = Copy Table 1d)_1 into your answer booklet.
Display the Mean return as a % corr. to 2 dec. places and the other columns as decimals correct to 4 dec. places.
Using the optimum cut-off, C* identify the shares that will be included in the EG Portfolio. For each of these shares calculate the proportion to be included in EG Portfolio. Insert these proportions in the table below and complete the Table.
Side Bets of EG Portfolio with Market Portfolio
Proportion in EG Proportion in
Share Portfolio MyMkt Portfolio Side-bets
Note: TOTAL= The
Side bet shows by how much the proportion of the share in the EG portfolio exceeds the market proportion i.e. side bet = Proportion in EG p/f – proportion in MyMKT.
Copy Table 1d)_2 into your answer booklet.
Display the values of the above Table as percentages to 2 dec. places.
e) Treynor-Black Portfolio
Construct the TB portfolio as follows:
Find the unadjusted (un-normalised weights), w*i (i.e. that don’t necessarily sum to 1) using the following
w*i = ˆ2i
Note W* values have already been calculated in Table 1c) as .
Find the adjusted (normalised) weights, W that sum to 1 from the W* using the following:
wi = w*i
Table 1e)_1 Weights of the Active Portfolio
Share W* W
Copy Table 1e)_1 into your answer booklet.
Display the W* as decimals correct to 4 dec. places and the normalised weights as percentages corr. to 2 dec. places.
Use the adjusted weights, W from Table 1e)_1 above to calculate the estimates (ˆA , ˆA , and ˆA ) of the α, β and σε of the active portfolio. See Handout 7 for help with formulae to use.
Table 1e)_2 α, β and η of the Active Portfolio
Active Portfolio Formula Value
α = β = η = σε
Copy Table 1e)_2 into your answer booklet.
Find the proportion of the Active portfolio, AWTB and the market (MyMkt) proportion, MWTB in the TB portfolio.
Table 1e)_3 Weights of Active and Market components of the TB Portfolio
Copy Table 1e)_3 into your answer booklet.
Display as percentages to 2 dec. places.
Use the proportions from Table 1e)_3 to complete the following Table:
Side Bets of TB Portfolio with Market Portfolio
Proportion in TB Proportion in
Share Portfolio MyMkt Portfolio Side-bets
Copy TOTAL = Table
1e)_4 into your answer booklet.
Display the values of the above Table as percentages to 2 dec. places.
Carry out a portfolio performance evaluation of the Treynor-Black Portfolio by calculating the statistics shown in the following Table:
Copy Table 1f) into your answer booklet.
Comment on the performance of the TB portfolio. Which measure Sharpe’s or Treynor’s is more appropriate for the TB portfolio. Explain why?
Q2. Answer ALL parts of this question.
This question is on Fundamental analysis of a company.
You are given the following financial information on SGRO and ATST on the 20th March, 2018.
Key numbers on the 20th March, 2018 for SEGRO.
Key numbers on the 20th March, 2018 for ATST.
(i) Complete the Tables 2_1 and 2_2 by filling in the values of the empty cells:
(ii) How is ROCE defined? (Give source) Explain the difference in the ROCE of the two companies and why ROCE is not an appropriate comparative measure.
(iii) How is dividend cover defined? (Give Source) Based on the dividend cover of the companies which company is an Income rather than a Growth company? Give your reasoning.
(iv) Which company would you recommend for an investor with moderate risk who wishes to invest for Income over a moderate time horizon?
(v) Assume the required rate of return (cost of equity or capital) for the two companies is 8%. Calculate the Intrinsic share price based on the no growth model and the constant growth model. (Use EPS growth as the growth rates). Compare your prices with the actual, market prices and comment on these prices.
b) Using the link below:
obtain the following data for SGRO from the Income Statement and Balance Sheet of SGRO for Dec-2017. (Income Statement and Balance Sheet have different Tabs under Financials)
Note: Use Operating Income from the web-site’s Income Statement as ‘Net Income’ for use in DuPont Identity given in your notes.
Operating Income (£m)
Total Assets (£m)
Total stockholders’ equity (£m)
Leverage Factor ROE
Copy Table 2b)_1 in your Answer Booklet.
Complete the empty cells in the Table 2b)_1 above.
Display Profit Margin and ROE as percentages corr. to 2 dec places, the (£m) quantities corr. to 2 dec. places and the ratios correct to 4 dec. places.
(i) Use the DuPont Identity to decompose SEGRO’s ROE commenting on their relevant strengths or weaknesses of the components.
(ii) Compare this ROE with the ROE obtained in Question 2a). Explain the difference and which would you take as a more accurate measure of ROE.
Q3. Answer ALL parts of this question.
This question is on the basic analysis of the commercial bond issued by Provident Financial and traded on the LSE ORB market.
Use the following link to obtain the specification of the bond:
a) Include a copy of the specification (Bond Information Only) in your Answer Booklet specifying the date you downloaded the specification. Using your specification where necessary, answer the following questions in your Answer Booklet:
(i) Quote the Date and Bond price at time of download.
(ii) Calculate the dirty price on your date of download given in (i).
(iii) Find the current yield on the date of (i).
(iv) Use the ‘Interactive chart’ tab at the top of the web page to obtain the clean price on the most recent (2017) coupon date.
(v) What was the issue price and date of issue of the bond?
(vi) When was the latest (most recent) coupon paid? What was the price on this date?
(vii) Write down the equation for the yield to maturity, rm of the bond on its most recent (2017) coupon day assuming the coupon has already been paid. The equation must equate the price of (vi) above to the sum of the present value of the future cash flows using rm as the discount rate. Your equation must be specific to this bond and must have only rm as the variable.
(viii) In a spreadsheet solve your equation of (vii) to find rm, the yield to maturity using the price obtained in (vi). Insert your answer to 2 dec. places in the Answer booklet.
(ix) Set up and complete the following table in your spreadsheet to calculate the duration of the bond on the last coupon date. Calculate the Duration as the weighted sum of the times in years. Complete the following table in your spreadsheet. Display the PVs to 2 dec. places and weights and the duration to 4 dec.places. Copy your table it into your Answer Booklet.
Cash Flows (£)
Price (£) =
Duration (y) =
b) (i) Briefly describe what happened to the price in the period 20th august, 2017 to 25th August, 2017. Explain the reasons for this movement. You will have to research the corporate history of the company during this period e.g. ‘try price slump Provident Financial 2017’ in Google. No more than 6 sentences are required.
(ii) Estimate the yield to maturity of the bond on the 22nd August, 2017. (You may want to do this by treating this price as the price on the nearest coupon payment date of Oct, 2017.)
(iii) By looking at the 5 year graphs of the historical share price of Provident Financial comment on the relative recovery of the share price versus the Bond Price. You may want to include images of the graphs. Comment on which investment was riskier.
The following link may be useful for the graph of the share price:
END OF ASSIGMENT