Question

Scenario #3: Risk & Reward:

Some basic portfolio analysis skills will be tested. You have been given two series of prices (below) and you guess that you will have to compute the expected return and standard deviation (risk) for the two assets and portfolios containing different combinations of them. The company’s recruiters have made it clear that you can bring your spreadsheet files with you to the test and that you should prepare a basic framework with as much work pre-done as possible.

	Breville			Monadel
Date	Price	Dividend		Price	Dividend
1/12/2018	10.66			13.77
1/01/2019	10.98			14.77
1/02/2019	15.75	0.18		17.81
1/03/2019	16.28			17.35	0.25
1/04/2019	19.24			18.95
1/05/2019	16.88			19.07
1/06/2019	16.36			18.81
1/07/2019	19.23			18.93
1/08/2019	16.26			15.91
1/09/2019	16.1	0.18		15.75	0.23
1/10/2019	15.34			15.34
1/11/2019	16.98			16.02
1/12/2019	17.36			16.5
Beta	1.15			Beta	1.40

Your Mentor’s Advice: Don’t forget that the prices must be converted to returns before you can start computing averages, covariance or standard deviation. Once you have two returns series, you can compute all these things very easily using Excel. You will have to be prepared to work out the portfolio expected return for different weights, so it might be best to set up a spreadsheet based on a calculation for the equally weighted portfolio and then change the weights once you know what they are. Given that the data set contains information about the betas, you might also expect questions based on the Capital Asset Pricing Model (CAPM).

Answer 1

Working 1: Calculation of return on Breville:-

Date (i)	Opening price (ii)	Dividend (iii)	Closing price (iv)	Net return (v = [iv+iii-ii])	Percentage of return (vi= v*100/ii) {assumed as "x"}
1/12/2018	10.66		10.98	0.32	3.00%
1/1/2019	10.98		15.75	4.77	43.44%
1/2/2019	15.75	0.18	16.28	0.71	4.51%
1/3/2019	16.28		19.24	2.96	18.18%
1/4/2019	19.24		16.88	-2.36	-12.27%
1/5/2019	16.88		16.36	-0.52	-3.08%
1/6/2019	16.36		19.23	2.87	17.54%
1/7/2019	19.23		16.26	-2.97	-15.44%
1/8/2019	16.26		16.1	-0.16	-0.98%
1/9/2019	16.1	0.18	15.34	-0.58	-3.60%
1/10/2019	15.34		16.98	1.64	10.69%
1/11/2019	16.98		17.36	0.38	2.24%

Working 2: Calculation of return on Monadel:-

Date (i)	Opening price (ii)	Dividend (iii)	Closing price (iv)	Net return (v = [iv+iii-ii])	Percentage of return (vi= v*100/ii) {assumed as "y"}
1/12/2018	13.77		14.77	1	7.26%
1/1/2019	14.77		17.81	3.04	20.58%
1/2/2019	17.81		17.35	-0.46	-2.58%
1/3/2019	17.35	0.25	18.95	1.85	10.66%
1/4/2019	18.95		19.07	0.12	0.63%
1/5/2019	19.07		18.81	-0.26	-1.36%
1/6/2019	18.81		18.93	0.12	0.64%
1/7/2019	18.93		15.91	-3.02	-15.95%
1/8/2019	15.91		15.75	-0.16	-1.01%
1/9/2019	15.75	0.23	15.34	-0.18	-1.14%
1/10/2019	15.34		16.02	0.68	4.43%
1/11/2019	16.02		16.5	0.48	3.00%

Calculation of average return & standard deviation:-

Date	x	y	x2	y2	x - x̅	(x - x̅)2	y - y bar	(y - y bar)2	xy
1/12/2018	3.00%	7.26%	0.09%	0.53%	-2.35%	0.06%	5.17%	0.27%	0.22%
1/1/2019	43.44%	20.58%	18.87%	4.24%	38.09%	14.51%	20.41%	4.16%	8.94%
1/2/2019	4.51%	-2.58%	0.20%	0.07%	-0.84%	0.01%	-2.58%	0.07%	-0.12%
1/3/2019	18.18%	10.66%	3.31%	1.14%	12.83%	1.65%	10.66%	1.14%	1.94%
1/4/2019	-12.27%	0.63%	1.50%	0.00%	-17.62%	3.10%	0.63%	0.00%	-0.08%
1/5/2019	-3.08%	-1.36%	0.09%	0.02%	-8.43%	0.71%	-1.36%	0.02%	0.04%
1/6/2019	17.54%	0.64%	3.08%	0.00%	12.19%	1.49%	0.64%	0.00%	0.11%
1/7/2019	-15.44%	-15.95%	2.39%	2.55%	-20.80%	4.33%	-15.95%	2.55%	2.46%
1/8/2019	-0.98%	-1.01%	0.01%	0.01%	-6.34%	0.40%	-1.01%	0.01%	0.01%
1/9/2019	-3.60%	-1.14%	0.13%	0.01%	-8.95%	0.80%	-1.14%	0.01%	0.04%
1/10/2019	10.69%	4.43%	1.14%	0.20%	5.34%	0.29%	4.43%	0.20%	0.47%
1/11/2019	2.24%	3.00%	0.05%	0.09%	-3.11%	0.10%	3.00%	0.09%	0.07%
Total	64.23%	25.16%	30.87%	8.85%		27.43%		8.52%	14.11%

Σx = 64.23%; Σy = 25.16%; Σx2 = 30.87%; Σy2 = 8.85%; Σ(x-x̅)2 = 27.43%; Σ(y-y bar)2 = 8.52%; Σxy = 14.11%; Number of population (N) = 12

Average of x (x̅) = Σx/N = 64.23%/12 = 5.35%

Average of y (y bar) = Σy/N = 25.16%/12 = 2.10%

Standard deviation of x (Breville) = square root of {Σ(x-x̅)2/N}

= Square root of {27.43%/12}

= Square root of {2.29%}

= 15.12%

Standard deviation of y (Monadel) = square root of {Σ(y-y bar)2/N}

= Square root of {8.52%/12}

= Square root of {0.71%}

= 8.42%