Welcome to session three of the second week of our course on modeling risk and realities. In this session, we'll look at optimizing the allocation of a fixed budget among the two stocks using the expected return as a measure of reward and the standard deviation of the return as a measure of risk. We'll formulate an algebraic model, implement it in Excel, and investigate how the optimal budget allocation depends on the risk tolerance of the decision maker. In the first two sessions of this week, we have looked at how to use scenarios to model uncertainty and how to calculate the reward and risk measures associated with the probability distribution described by scenarios. We have also discussed the notion of correlation between random variables and in particular, the connection between correlation and the reduction of risk. In the last session of this week, we'll put these concepts together in our search for the optimal decisions in uncertain settings. In the last session, we have looked at simple examples of portfolios of two stocks. Now, we will look at the related problem in a more systemic fashion. Similar to the last session's example, consider an investor that would like to design an optimal way of allocating a sum of $100,000 today among two hypothetical stocks. A and B, with a goal of maximizing the expected profit from this investment tomorrow. In our analysis, we will use 20 scenarios for the possible values of pairs on returns on stocks A and B. In particular, we will assume that all scenarios equally likely so that each scenario will be realized tomorrow with a probability of 1 over 20 or 5%. The return scenarios are stored in the Excel file Two Stocks posted on cores side. Let's have a look, we are looking at the sheet called scenarios of the file Two Stocks. The sheet contains the data for the return scenarios, a number of calculations, as well as the setup for the portfolio optimization model. But we'll look at the model later. Now, we will look at the scenarios' data and check how the expected values and the standard deviations for the returns on two stocks as well as the correlation between the returns or our calculator. Sales, B5 through D24, contain the return values and the probabilities for each of 20 scenarios. For example, under scenario 1, the return on stock A is shown in cell B5. The return on stock B in the cell C5, and the probability for the scenario 0.05 is stored in the cell D5. In our analysis, we will use equal probability scenarios, but all the formulas we put in the cells and used to calculate various quantities are general, and can be used for any set of portfolio probabilities as long as those probabilities add up to one. In the cells, B26 through C28, we calculate the expected values, the variances and the standard deviations for the returns onto stocks. For example, the cell B26 contains the formula that calculates the expected return on stock A. This formula is the sum product of the scenario probability values from the cells D5 through D24, and the corresponding return values for stock A from the cells B5 through B24. As you can see, the value of the expected return for stock A is about 4.35%. So B27, calculates the variance of the returns on stock A. The formula in this cell is the sum product of scenario probability values from the cells D5 through D24. And the square differences between the scenario return values and the expected return. With those square differences or square deviations calculated in the cells F5 through F24. Looking for example, at the cell F5. We see that indeed it calculates the square of the difference between the return on stock A in scenario one, and the expected value of the return on stock A. Cell B28 contains the standard deviation value for the returns on stock A, which is the square root of the variance. Calculated in the cell B27. In a similar way, the expected return, the variance, and the standard deviations of the returns for stock B are calculated in the cells C26 through C28. Finally, the correlation between the returns in stock A and stock B is calculated in the cell C31. Know that the formula in the cell C31 uses the expected value of the product of two returns calculated in the cell C30. So here's the summary of the calculated values. As you can see at the first look stock B seems like an unattractive choice. It has lower expected return and higher standard deviations of the returns. But there's hope. And the hope is that the stock B can still be a useful addition to the portfolio since it is negatively correlated with stock A. So let's express our model using algebra. We need to specify three components of the model, decisions, objective, and constraints. Let's start with decisions. We need to decide how much to invest into each of the two stocks so we have two decision variables XA and XB. What about the objective? Well, the investor would like to put money in today to get as much profit as possible tomorrow. But the profit tomorrow is random, since the returns in our stocks are random. So we'll look at the measures of reward and risk. In particular, but we'll use expected profit as a reward and the standard deviation of the profit, as a measure of risk to be controlled. In our modeling, we will use several different values of the risk tolerance level to see how they affect the results. So to put it all together, we want to select the investment amounts to maximize the expected profit. While making sure that the standard deviation of the portfolio does not exceed the risk tolerance level selected by the investor. And, that the investment amounts add up to $100,000. And, that the investment amounts are a non-negative. In this context, the non-negativity constraints mean that we do not allow shorting the stocks in our model. We will use Solver to find the best portfolio. So let's go back to the Two Stocks Excel file to see how a model is set up. So here's the section of the Excel file Two Stocks that illustrates the set up of a model in the spreadsheet format. First, we will look at the decision variables. Cells F31 and G31 contain the values for the investment amounts into stocks A and B, respectively. Next, the objective function. In our model, the objective to be maximized is the expected profit value. Cell G26 contains the formula that calculates the expected profit for any combination of decision variables. That is, for any portfolio containing stocks A and B. The expected profit value in the cell G26 is calculated as a sum product of the scenario probabilities from the cells D5 through D24. And the profit values that the portfolio with investment amounts from the decision variables F31 and G31 earns in each scenario. Those profit values are stored in the cells J5 through J24. For example, cell J5, Calculates the profit. Earned in scenario one by the portfolio, expressed by cells F31 and G31. This profit is a sum product of the returns of stocks A and B in scenario one. And the investment amounts in the cell F31 and G31. Profit portfolio values in the cells G5 through G24 also used to calculate the variance as well as the standard deviation of the profit value. The variance of the profit is stored in the cell G27. The variance is calculated as a sum product of the scenario probabilities from the cells D5 through D24 and the square deviations in scenario profit values from the expected profit those values are stored in the cells K5 through K24. The standard deviation of profit is calculated in the cell G28 by taking square root of the variance in the cell G27. So we have the decision variable cells and objective function cell. Now we need to specify the constraints. The first constraint limits the value of the standard deviation of the profit as the risk measure. In particular, we will instruct the Solver to make sure that the value of the profit standard deviation does not exceed the selected level. We have put in the trial values of 50,000 into each of the decision variable cells for such portfolio. The standard deviation of the profit is calculated in the cell G27 and is equal to something about $1,446. Suppose that the investor would like to limit the standard deviation to a lower value, say $1,400. We put this limit in the cell I28, and we will tell the Solver to make sure that the value of the standard deviation of the profit, in the cell G28, does not exceed the limit in I28. The next constraint must ensure that the sum of the investment amounts for the two stocks is $100,000. In the cell I31, we add the values of two investment amounts. And we will equate it to the total investment budget in the cell K31. Now we're ready to find the best portfolio. We call the Solver > Data > Solver. We make sure that the decision variable's objective function in constraints all specified including non-negativity constraints and click on Solve. The optimum portfolio in this case allocates about 80% of the total investment to stock A and about 20% to stock B. Using the spreadsheet model implementation, we can optimize our two stock portfolio for different values of investor's risk tolerance. We have used the risk tolerance values between around $1,310 which is the lowest possible standard deviation value that can be achieved in a portfolio comprised of these two stocks to about $1,900. That's where 100% of the portfolio is allocated for the stock with the highest expected return, stock A. For each risk tolerance value plotted on the horizontal axis we have used Solver to find the optimal portfolio and the corresponding expected profit value plotted on the vertical axis. Finally, they have investigated how the optimal portfolio composition changes as the risk tolerance level increases. As we can see, for lower values of risk, the optimal portfolio requires a substantial presence of stock B. As the risk requirement is relaxed, portfolio gradually shifts towards stock A, with stock A's fraction reaching 100% when the standard deviation of the profit value is allowed to be as high as approximately $1,850. Week two of our course is behind us. This week we have looked at how scenario approach to modeling future and certain outcomes can be combined with optimization techniques in search for the best decision. Next week, you will have an opportunity to look at other methods of using historical data for modeling future outcomes.
