Fred Flint recently inherited £20,000 from a wealthy relative. His stockbroker has advised him to invest in three particular stocks. Because of the volatility of the market, Fred requests the stockbroker to provide him with historical data for these stocks over the past four years, as shown in Table 4.7.

Table 4.7 Year Stock A Stock B Stock C 1 20.2% 10.9% 15.3%

2 2.4% 18.7% 6.8%

3 15.9% 9.8% 18.5%

4 4.6% 12.1% 5.7%

Fred decides to build a simple security analysis model using three of Excel’s functions – SUMPRODUCT, AVERAGE, and STDEV. He wants to perform ‘what-if’ analysis to see what happens when different amounts are invested in each stock. Being risk-averse, Fred knows that a lower standard deviation (σ-value), means a less risky stock. He thus wants the highest expected return that can be achieved without increasing the portfolio’s riskiness. Set up Fred’s ‘what-if’ model and then use it to find an acceptable solution.

(Answer: Investing 10%, 70% and 20% in stocks A, B, and C respectively gives Fred an expected return of 12.41% with a σ-value of 1.9%. Other combinations may give higher returns but also higher

σ-values)